Transactional Locking to Prevent Race Conditions

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.

Usage

MySQL / PostgreSQL

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;

Detailed Explanation

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:

• The manual locking approach is not used for all code parts because it complicates the source code or someone forgot to add it.
• When an in-memory lock store crashes, many requests can acquire the same lock resulting again in race conditions.
• A tiny replication lag for replicated lock stores on many servers will also result in a lock being acquired multiple times.
• When a request crashes with an active lock, it may not be able to release the lock in all circumstances and the resource will be locked forever. Most locking systems therefore assign a TTL (Time-To-Live) value to the lock so it will be automatically released on errors. However, the resource is still locked for a long time or may get unlocked too early when the request takes longer than the TTL duration.
• Only in simple cases a single lock is required for an update operation. When multiple locks are needed it is very to easy to create deadlocks because locks are acquired in different orders or a combination of multiple concurrent requests interfere with each other. No available locking system can detect those and can only resolve this state by automatic TTL lock releases. However, the system can still be deadlocked again if a new request acquires a released lock.

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:

• The architecture does not have to be extended with another type of database to do locking in multiple server environments.
• When a request crashes, the lock and all partially applied modifications to the database are released together in an atomic operation. There will never be an inconsistency between acquired locks and database modifications.
• All locks are automatically released when the transaction commits, rolls back or the connection is terminated/lost, which results in a rollback.
• A too-long running request cannot have its locks automatically released as they are linked to the active database transaction that is doing the modification.
• Deadlock handling is a native feature of database transactions that will terminate the minimal amount of transactions to get back to a deadlock-free state.

Additional Resources

• MySQL Blog: MySQL 8.0 UUID announcements describing new functions.
• PostgreSQL documentation: The space-efficient uuid data type.
• PostgreSQL documentation: gen_random_uuid() to create random UUID v4 IDs.
• Christian Emmers' Blog: The uuid_v4() function to add UUID v4 IDs to MySQL.

Tobias Petry

SQL for Devs Founder