NOTES & CORRECTIONS: --- • I have come to find that this is actually Square's ONLY system design interview question for at least L5 and L6! • Files: • Pastebin of the text file from the right half of my screen: pastebin.com/uzyyrXJQ • Excalidraw link for the final diagram from the video: excalidraw.com/#json=qqyfbvt3VZW0fCgCzdg6r,6CNHryEEkM0xH_qfcidxnQ • Imgur album of each "checkpoint" and approach from the video: imgur.com/kUGt514 I've gotten a lot of feedback that the scale I designed for was very unrealistically high (which was kind of the point, because none of the 4-6 system design books that I own actually cover distributed transactions all that well...) AND a viewer has found a phenomenal way to do manual sharding that would avoid having to do distributed transactions at all, so: I have created the following draft of 8 total approaches to this problem: imgur.com/R8LO4Rm • Approach 3 is probably all that you'd ever need for the scale that this problem would usually be presented with • Approach 4 uses the sharding idea that was found for scaling up writes without adding distributed transactions • Approach 5 through 8 are completely unnecessary but demonstrate ideas and approaches for handling distributed transactions that I wish was more accessible in the materials that currently exist for system design interviews (I literally have 20 books on system design, and I'm still not happy with what I've been able to find) • The biggest piece of content that's missing from the infographic of 8 approaches is currently the pros & cons of each approach. I'd actually like to do a much more cleaned up and thorough recording of this video at some point, and it will be driven off the infographic of 8 different approaches attached above in this comment that's currently a draft.

I used this in an interview for L5 at square, and they told me I failed because my SD was too advanced for the requirements and they didnt think the db datastructure would work well. I guess it really depends on how the interviewer is feeling on the day.

Thanks for the explanation! This was great!

Thank you for the video! My brain is stuck on one thing :) I see you would want to use a string for the price so you don't have to worry about overflow depending on the currency, but I think what you lose may be more than you gain. Assuming your database supported a reasonable range of costs (64bit integers would for sure, 32 most likely would unless it's a hotel on the moon :)). It would make more sense to store a common currency (e.g. dollars) and do any conversions per customer? You could store cents instead of dollars if you're concerned with floating point errors. Using a string would mean you wouldn't be able to do very useful things like ask the database "Give me all rooms which are available and cost < $40". If you store a string then you would be doing a lexicographic comparison and "40" > "200". If you stored a string you would have to pull all available rooms into memory, convert the price to a string and then filter. This would be way more expensive in time, processing power, and bandwidth than pushing the query down to the database. Storing a string makes more sense to me on the payment side. Depending on the conversion you may have to support fractional cents. But, you also aren't as likely to do as many queries for ranges for payments than you are to query against room cost. I'm curious what others think and thank you again!

Really good content, glad you went into the details that a lot of system design seem to skip!

Super high quality content! Thanks for sharing. Would be great to see a video on Zookeeper and how exactly it helps with distributed TXs.

Thank you, this was a great design.

Thanks for the great tutorial! I think DynamoDB supports ACID with Transactions operations which can perfectly handle the all-or-nothing.

subscribed, thanks for the video!

Extremely good content. I think an improvement area could be deep diving into actual reservation with some state management - like reserved, blocked, booked, canceled etc.

Thanks, buddy, this helped me a lot with my first ever system design interview. I got an L5 top band offer from Square. Let me know if and how I can buy you a beer!

Great channel name

Quite informative, I willl suggest some minor timestamps for these long form of videos.

first, you don't need to update both inventory and reservation. inventory can be inferred from the reservations. secondly, it's okay for inventory to be out of sync sometimes - the user might see something as available but the booking can fail. that happens occasionally on real websites and it's acceptable.

Why have a separate inventory and reservations DB?

For Inventory DB, why not use some unique Id as PK and set it as UUID? Why date in inventory DB, is it not better to have a from and to fields in reservation DB? Isn't it better to check if room is available by checking the dates you want reservation for with the reservations inside Reservation DB? Btw, great video, learned some new things, and to what details I need to pay attention. Thank you!

Just one consideration: page view != reads as much as reservations != writes. A reservation would include a few reads along with some upserts.

(Should 'roomsCount' and 'roomsRemaining' be in another table other than Inventory? Otherwise, if a room is reserved, all the room records in Inventory need udpate those 2 attributes?) - I see now. You mean we just select a random available room, right? But we can also have 2 tables, one is for general room available information, and one is for each room's available information?

What about search for listings DB? Don't we need geo based db like GoeHashId as one of the fields or do quad tree based search ?

thanks for the video. why is the primary key 4 attributes? i'm a newish (3yrs) full stack enginner but have lent a little more into frontend and have barely touched DB's, any resources you could suggest? Thanks!

Why are the inventory table and reservations table on different databases? If they were on the same database could we avoid the need to do 2PC? Is it because of the sizes of the tables?

For the inventoryDb, how many different date entries does a single room have? Does it have one row for each date for the next two years?

Instead of trying to do atomic transactions across different database nodes, could we instead shard the data by a shared id? For example sharding by roomTypeId. That way the Inventory and Reservations can be stored on the same database node and we wouldn't need to use ZooKeeper to coordinate transactions across nodes for booking rooms.

In some system design videos, I see multiple services talking to a common DB which is usually a big NO NO going by microservices best practices. Any opinions on that?

How would 2PC work across two dynamoDB tables? AFAIK there is no concept of making a request to lock a key (i.e. prepare phase) in the DDB API.

What are the resources without links? Grokking and Alex Xu?

Why is dealing with floats for money a bad idea?

20:54 I don't understand how paxos or Raft consensus algorithms fit into the picture here? In this scenario we have to use 2 phase commit because we are writing different data to 2 entirely different databases, it's not like consensus algorithm which is used for transaction writes among equivalent nodes for the same database, in those cases, paxos/raft achieve even better reliability but functionally equivalent to 2PC, but not in this heterogenous distributed transaction case, right? Consensus algorithm relies on tolerance of partial failure, which is not the case here --- all participants have to succeed or fail together. Also after you mentioned outsourcing to those consensus algorithms, you then mentioned "outsourcing to Zookeeper", my understanding though now you're just coming back to 2PC but just saying to make Zookeeper to do the coordinator job for you, so it doesn't have anything to do with paxos/raft any more, is that right?

There is 86400 seconds in one day, not 100k. I guess that was an approx, rounding up? (a lot)

I really recommend digging in more to how the payment processor side works. I think if you were to dig into that, you'd learn some valuable things that you could then apply to this design and make it better, both technically simpler and more able to meet various real world business needs. One thing this sort of solution can't address is that when those coordinated writes roll back, you are going to lose a lot of information about end-user intention. They requested a room reservation, but it was declined. You lose that information if the solution is to erase the room reservation with transactional semantics. Having a way to see those failed room reservations is probably valuable information both for customer service, as well as to identify and understand customer demand for limited resources.

miro?

Another issue with this design is that you have multiple deployment units, services, that are reading and writing to the same underlying physical storage. That is going to make data migrations incredibly annoying and error prone to roll out.

Please stick to usual naming convention when explaining technical terms, instead of coming up with your own. This pattern you are describing is pretty much universally known as master/slave. I was like, what is "leader/follower"? Until I got it what you meant.