Concurrency Control in Databases

In any system where data is a shared resource—from a banking app processing transfers to an e-commerce site managing inventory—multiple operations can attempt to read and write the same data at the same time. Without proper management, this leads to corrupted information, incorrect balances, and a complete loss of data integrity. Concurrency control is the set of mechanisms a database management system uses to coordinate simultaneous transactions, ensuring correct results and preserving data consistency even under heavy load. Understanding these mechanisms is fundamental to designing reliable, high-performance applications.

The Core Problem: Transaction Interference

A transaction is a logical unit of work, such as transferring funds between two accounts. For correctness, transactions must satisfy the ACID properties: Atomicity, Consistency, Isolation, and Durability. Isolation, the "I" in ACID, is the guarantee that concurrently executing transactions are separated from each other. The goal of concurrency control is to provide this isolation.

Without it, specific anomalies can occur. The classic example is the lost update: two transactions read the same balance (say $100),bothcalculateanewvalue(oneadds$50, the other subtracts $20), and write back their result. The second write overwrites the first, causing one of the updates to be "lost." Other anomalies include dirty reads (reading uncommitted data that may be rolled back) and non-repeatable reads (seeing different values when reading the same row twice in a transaction). Concurrency control protocols exist to prevent these issues.

Pessimistic Locking: Assume the Worst

Pessimistic locking operates on the principle that conflicts are likely, so it prevents them proactively by restricting access. When a transaction needs to read or modify a data item, it first acquires a lock. The most common types are shared locks (for reads) and exclusive locks (for writes). A shared lock allows other transactions to also acquire shared locks on the same item, but not exclusive locks. An exclusive lock blocks all other locks on that item.

This is analogous to a traffic light or a reservation system: you claim the resource you need before you proceed. Databases implement this using commands like SELECT ... FOR UPDATE to acquire an exclusive lock on rows. The primary advantage of pessimistic concurrency control is its simplicity and strong guarantee—once you have the lock, you know no one else can interfere. However, the downside is reduced throughput due to waiting. If Transaction A holds an exclusive lock on Row X, Transaction B must wait until A releases it, even if their operations might not ultimately conflict.

Optimistic Locking: Check at Commit

Optimistic locking takes the opposite approach: it assumes conflicts are rare. Transactions are allowed to proceed without acquiring locks, reading and modifying data freely in their private workspace. Conflict detection is deferred until commit time. This is typically implemented using a version number or timestamp on each record.

Here's how it works: When a transaction reads a row, it also reads its current version number. When it later tries to update that row, it includes the original version in the WHERE clause of its update statement (e.g., UPDATE accounts SET balance = 150, version = 2 WHERE id = 1 AND version = 1). If the update succeeds (i.e., one row is affected), it means no other transaction changed the row in the interim. If it fails (zero rows affected), a conflict has occurred, and the transaction is rolled back, often prompting the application to retry the operation from the beginning.

Optimistic locking excels in high-read, low-conflict environments because it avoids the overhead of locking and waiting. However, it shifts the burden of handling rollbacks to the application layer and performs poorly when conflicts are frequent.

Isolation Levels: Trading Consistency for Performance

Not all applications require the same degree of isolation. Databases offer configurable isolation levels that allow you to relax strict serializability for better performance, while accepting certain anomalies. The SQL standard defines four primary levels, from weakest to strongest:

1. Read Uncommitted: Transactions can read data written by other uncommitted transactions (dirty reads). This offers the highest performance but the lowest consistency.
2. Read Committed: A transaction can only read data that has been committed by other transactions. This prevents dirty reads but allows non-repeatable reads and phantom reads (seeing new rows that match a prior query).
3. Repeatable Read: Guarantees that any row read during a transaction will look the same if read again. It prevents dirty reads and non-repeatable reads but may still allow phantom reads.
4. Serializable: The strongest level. It guarantees that the outcome of executing concurrent transactions is equivalent to some serial (one-after-another) order. It prevents all anomalies but has the highest performance cost due to extensive locking or other mechanisms.

Choosing the right level is a critical design decision. For a reporting dashboard, READ COMMITTED might suffice. For a financial system calculating interest, SERIALIZABLE may be necessary.

Multi-Version Concurrency Control (MVCC) and Deadlocks

Two advanced concepts are pivotal to modern database implementation. Multi-version concurrency control (MVCC) is a sophisticated technique used by databases like PostgreSQL and Oracle to provide snapshot isolation. Instead of overwriting data, MVCC creates a new version of a row for each update. Read operations access a snapshot of the database as it existed at the start of the transaction, seeing only committed versions. This allows readers to never block writers and writers to never block readers, significantly improving concurrency. MVCC is often the engine behind the REPEATABLE READ and SERIALIZABLE isolation levels in these systems.

Deadlock is a perilous situation in locking-based schemes where two or more transactions are stuck, each waiting for a lock held by the other. For example, Transaction A holds a lock on Row X and needs Row Y, while Transaction B holds a lock on Row Y and needs Row X. Neither can proceed. Databases employ deadlock detection algorithms that periodically check the "wait-for" graph of transactions. When a cycle is detected, the database selects a victim transaction, rolls it back, and releases its locks, allowing the other transaction(s) to continue. Applications must be designed to handle these rollbacks gracefully, typically with retry logic.

Common Pitfalls

1. Over-locking with Pessimistic Concurrency: A common mistake is holding exclusive locks for too long or locking at too coarse a granularity (e.g., locking an entire table). This cripples system throughput. The solution is to lock only what you need, release locks as soon as possible, and perform lock-heavy operations outside of peak hours or within very short transactions.

1. Ignoring Deadlock Probability: Writing application logic that consistently accesses resources (like database rows) in a different order dramatically increases deadlock risk. For instance, one function updates Table A then Table B, while another updates Table B then Table A. Establishing and adhering to a strict, global order for accessing resources is a key defensive practice to prevent deadlocks before they happen.

1. Misapplying Optimistic Locking in High-Conflict Scenarios: Using optimistic locking for a frequently updated "hot" row, like a popular item's inventory counter, will lead to a high rate of transaction rollbacks and retries. This is inefficient and frustrating for users. In such scenarios, a pessimistic approach or a specialized pattern (like using a separate counter table) is more appropriate. Always analyze the conflict rate for your workload.

1. Assuming Isolation Level Defaults are Sufficient: Most databases default to READ COMMITTED. Developers often assume this prevents all anomalies, but it does not guard against non-repeatable reads or phantom reads. You must explicitly choose an isolation level based on your transaction's consistency requirements and test for anomalies under load.

Summary

• Concurrency control is essential for maintaining data integrity when multiple transactions access a database simultaneously, preventing anomalies like lost updates and dirty reads.
• Pessimistic locking prevents conflicts by acquiring locks before accessing data, favoring consistency, while optimistic locking detects conflicts at commit time using version numbers, favoring performance in low-conflict environments.
• Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) offer a tunable trade-off between strict consistency and system performance.
• Modern databases often use Multi-version Concurrency Control (MVCC) to provide efficient snapshot isolation, allowing readers and writers to operate without blocking each other.
• Deadlocks are a risk in locking-based systems and are resolved by the database rolling back a victim transaction; application design should aim to prevent them by enforcing a consistent order of resource access.