The design of any booking system like Booking.com, Airbnb.com, or even Uber involves “Booking” an entity - a product or service. What happens, though, when two clients try to book the same hotel room or a cab at the same time? How is that conflict resolved?
In this article, we will take a look at race conditions in DB and how we can handle them.
ACID property - Isolation
Database isolation refers to the level of isolation between concurrent transactions in a database. Isolation levels control the visibility and accessibility of data to concurrent transactions and can affect the occurrence of race conditions in a database. If your isolation level is not “serializable” - there is a possibility of race conditions.
Example of Race Condition
Race conditions can occur in a booking database when multiple users try to access and update shared data concurrently. Consider a simple booking database for a hotel, with the following tables:
CREATE TABLE Room (
id INT PRIMARY KEY,
room_number INT,
available BOOLEAN
);
CREATE TABLE Booking (
id INT PRIMARY KEY,
room_id INT,
start_date DATE,
end_date DATE
);
Two users, Alice and Bob, try to book the same room for overlapping dates at the same time.
Alice:
UPDATE Room
SET available = FALSE
WHERE id = 123;
INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-01', '2022-01-07');
Bob:
UPDATE Room
SET available = FALSE
WHERE id = 123;
INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-03', '2022-01-10');
Both of these statements update the availability of the room with ID 123 to "FALSE", and insert a booking into the "Booking" table for that room. However, a race condition can occur if these statements are executed concurrently.
For example, suppose the initial availability of the room with ID 123 is "TRUE". Alice and Bob concurrently read info from the database saying that the room is available. Both of them reserve this room.
The final result (the room being booked for both Alice and Bob) is different from what would be expected if the statements were executed sequentially (either Alice's booking or Bob's booking being rejected).
To solve race conditions in a booking database, various concurrency control techniques can be used, such as pessimistic concurrency control and optimistic concurrency control. These techniques allow the database to manage concurrent access to shared data in a controlled and consistent manner and can help to prevent race conditions from occurring.
Notice that basically, we have only 2 steps while booking a room, read the data and update it.
So we can solve race conditions either on a read step or on an update step.
Pessimistic concurrency control
Pessimistic concurrency control is a technique used to prevent race conditions in a database by locking the data that is being accessed or updated. This ensures that only one user can access the data at a time, and other users have to wait until the lock is released before they can access it.
In SQL, pessimistic concurrency control can be implemented using the "SELECT ... FOR UPDATE" statement. This statement allows a user to lock the rows of a table that are being accessed and prevents other users from updating or locking those rows until the lock is released.
To implement pessimistic concurrency control for the "Booking" table, a user can execute the following SQL statement:
SELECT * FROM Room WHERE id = 123 FOR UPDATE;
This statement will lock the row with the ID 123 in the "Book" table, and prevent other users from accessing or updating that row until the lock is released.
To release the lock, the user can commit or roll back the transaction:
COMMIT; -- releases the lock
ROLLBACK; -- releases the lock and discards any changes made to the data
Optimistic concurrency control
Optimistic concurrency control, on the other hand, allows multiple users to access and update the data concurrently, but checks for conflicts before committing the changes. If a conflict is detected, the user is notified and the changes are not applied.
One way to implement optimistic concurrency control in a booking system is to use a "version" column in the "Room" table. This column can be used to store a "version number" for each booking, which is incremented each time the booking is updated.
ALTER TABLE Room
ADD version INT DEFAULT 1;
Then we need to update SQL statements for booking a room. Now Alice's statement will look like this:
UPDATE Room
SET available = FALSE, version = version + 1
WHERE id = 123 AND version = 1;
INSERT INTO Booking (room_id, start_date, end_date, version)
VALUES (123, '2022-01-01', '2022-01-07', 1);
And Bob’s will look like this:
UPDATE Room
SET available = FALSE, version = version + 1
WHERE id = 123 AND version = 1;
INSERT INTO Booking (room_id, start_date, end_date)
VALUES (123, '2022-01-03', '2022-01-10');
If both of these statements are executed concurrently, the first UPDATE statement to be executed will increment the "version" of the room with ID 123 to 2, and the second UPDATE statement will fail, as the "version" in the WHERE clause is 1 (so zero rows will be updated with the second transaction).
This will prevent the race condition from occurring, as only one booking will be inserted into the "Booking" table, and the availability of the room will not be incorrectly updated.
Conclusion
Optimistic locking is a useful technique that allows multiple users to access and update data concurrently, while still ensuring that conflicts are detected and resolved. It can be used even when using less-strict isolation levels, such as Read Committed, or when reads and writes are executed in separate transactions. However, it has the drawback of triggering rollbacks when conflicts are detected, which can cause the current transaction to lose all of its previous work. This can be costly for the database system, as it has to revert all pending changes, including both table rows and index records. In situations where conflicts are frequent, pessimistic locking may be a better choice, as it reduces the risk of rollbacks by preventing conflicting updates from being applied.
Bonus
With such a simple example we can even do better, instead of creating an additional field version we can just provide new conditions to our update statement:
UPDATE Room
SET available = FALSE WHERE id = 123 AND available = TRUE;
If two transactions will be executed in parallel, only the first one will be succeeded. It works similarly to the optimistic concurrency control with the version field for that example, but if you have a more complicated DB schema, for example, if you have int quantity instead of boolean available and you cannot use it in WHERE clause, you can also use DB constraints.