How To Prevent Race Conditions in Database

Race condition is a condition that occurs when the system attempts to perform two or more operations at the same time and causes problems in the system. In this article, we gonna discuss race conditions in database.

In this case, we will gonna use airline booking apps as a study case. In an airline booking app system that run on the background is like above , the system will check stock first, if stock is not zero ,then insert booking detail into the order table. After that, update stock in stock table . Then, give response to the customer

If only 1 customer at a time that’s actually no matter with the system. But, how about there is more than 1 customer at a time like the image below? ya, it will be a race condition. let's say the remaining stock is 1. But there is 3 request in a time. the system selects the remaining stock from the database in a table's stock 3 times a time .because the stock is 1, So the response from table stock will be 1. Because the request is 1, the system will insert it from the order table, then update stock in table stock.

Is that normal? In the airline business, a seat that avails is 1, then there is 3 order, and it will be chaos. Of course, that’s not normal. So, How to prevent it? Yes, We can use database transactions and locking.

The schema of using database transactions and locking is when there is much of request on a time, 1 request will be processed in the locking statement, then another request will wait first after there is statement rollback or commit

DB::Transaction
Select Stock (with locking)
if error 
  DB::Rollback
Insert Order 
if error 
  DB::RollBack
Update Stock 
if error 
  DB:: Rollback
DB::Commit

To implement a locking table, a user can execute the following SQL statement “For Update”. 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 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

Implementation of db transaction and locking will be discussed here