Martin Joo

1/ Let's model a seat reservation process in a movie theatre. When a user selects a seat they have 5 minutes to complete the payment. Until then, the seat is reserved for them and cannot be selected by other users.

2/ In this app, I'm not going to handle dates, so the Seat model has a status column. It can have the following values: - Available - Reserving represents the 5-minute period until it's not available for other users - Reserved means the user completed the payment

3/ When a seat is reserved a new Reservation model is created. This simple process looks like this:

4/ It works as expected but it has a flaw. Conflicts are possible. If two users select the same seat at the same time this condition returns false for both of them and then two reservations are created for the same seat:

5/ The easiest way to avoid conflicts like this is to use locks at the database level. A database lock is a technique that helps us concurrent requests to the database. For example, you can lock a specific row and say “no one can change this row until I’m done.”

6/ In this example this is the desired behavior: When the request comes in it needs to lock the specific seat The lock prevents other requests from changing the seat data (status) until the original request is handled

7/ In MySQL, there are two types of locks: A *shared lock* prevents the selected rows from being modified until your transaction is committed. An *exclusive lock* prevents the selected records from being modified or from being selected with another lock.

8/ We need an exclusive lock in this case. The current request should own the seat record entirely. The way exclusive locks are implemented in MySQL is the following:

9/ After executing this query seat #112 cannot be modified by other transactions until the current one commits. This is what the new method looks like:

10/ First, everything is wrapped in a transaction, and then the seat query acquires an exclusive lock. Until this transaction is done, other transactions (requests) cannot modify or acquire locks on seat #112. This means that the other request has to wait even to select the seat

11/ But until then it is updated by the first request. So this if statement results true and the second request is rejected:

12/ Thanks for reading! On the 8th of April, I'm releasing a new book - Building a database engine Check it out here: https://buff.ly/4kcvgcv