Almost any application is prone to race conditions because values are read from the database and later updated without taking care of concurrent requests. The traditional way to solve this problem is to use a semaphore lock that prevents concurrent access for some critical code parts with files, Redis, DynamoDB or other locking systems. Instead of using complicated and error-prone locking systems, an application can cooperate with the database to indicate that a row should be loaded with an exclusive lock for the current request as the data will be updated.
START TRANSACTION; -- Trying to acquire lock by transaction on the row. Anyone -- also querying the row with FOR UPDATE will have to wait -- until the current transaction COMMITs. SELECT balance FROM account WHERE account_id = 7 FOR UPDATE;
The procedure to update rows in a database is always the same and follows a simple workflow: A request is validated, the indicated row is loaded into memory, merged with the new data, and finally saved to the database again. However, race conditions will appear at some point as servers can execute multiple requests concurrently. With preemptive multithreading, a process can be paused at any time to run another one, so the database may already have been changed until the process finally continues to update the row. But the executed validations and assumptions are no longer valid because of the new row's data by that time. The operating system is not the only cause of this context change. A language's runtime environment will change the actively executed code as soon as e.g. a blocking I/O operation is executed.
The standard solution against race conditions is to add optimistic locking to the application. When a developer thinks about race conditions in some update logic e.g. a file in the filesystem is created to indicate that no other process can do the same operation. While for distributed applications running on multiple servers or containers a distributed data store like Redis or DynamoDB is used. This approach sounds sane first, but has many problems in reality:
A different locking system than the database is not needed as the database is already doing that. Whether a transaction is manually started or one is started invisible for every query, the database locks any row when doing data modifications until the transaction is finished to prevent race conditions. The database's crash safety and data correctness depend on this behavior so it cannot be disabled and is safe to depend on it. Instead of acquiring locks in a locking system, rows can be loaded with an exclusive lock from the database within a transaction with the special `FOR UPDATE` extension. Only one transaction will be able to get the exclusive lock. By simply cooperating with the database's lock manager and loading the rows differently, the earlier-stated problems are solved:
SQL for Devs Founder
Be notified on future content. Never spam.