The previous chapter ended with a worked example showing exactly how two transactions running at the same time can accidentally cause a lost update, silently erasing one transaction's work without any error message ever appearing. That example was not an unusual edge case; it represents an entire category of problems that emerge naturally whenever a database allows multiple transactions to execute concurrently. Concurrency control is the set of techniques a database management system uses to prevent exactly these kinds of problems, while still allowing transactions to run alongside each other for the sake of performance.
This balancing act is genuinely difficult. Running transactions strictly one at a time, in a serial schedule, would completely eliminate concurrency problems, but it would also be unacceptably slow for any real-world system serving many simultaneous users. Concurrency control mechanisms aim to allow as much genuine parallelism as possible while still guaranteeing that the final result of any concurrent schedule is exactly as correct as some equivalent serial schedule would have produced.
In this tutorial, you will learn about the specific concurrency problems that can occur when transactions overlap, how lock-based protocols, particularly two-phase locking, prevent these problems, and how timestamp-based protocols offer a completely different, lock-free approach to achieving the same goal. Worked examples are included throughout to make each technique concrete.
Before studying the solutions, it helps to have a clear catalog of exactly what can go wrong. Several distinct problems can arise when transactions are interleaved without proper coordination.
| Problem | Description |
|---|---|
| Lost Update | Two transactions read the same data and then write updated values, with one transaction's update silently overwriting the other's, as demonstrated in the previous chapter. |
| Dirty Read | A transaction reads data that has been modified by another transaction that has not yet committed, and that other transaction later rolls back, leaving the first transaction working with data that never actually existed. |
| Unrepeatable Read | A transaction reads the same piece of data twice during its execution, and gets two different values, because another transaction modified and committed a change to that data in between the two reads. |
| Phantom Read | A transaction re-executes a query that returns a set of rows matching a condition, and finds a different set of rows the second time, because another transaction inserted or deleted rows matching that condition in between. |
T1: Write(A = 500) (T1 has not yet committed) T2: Read(A) T2 reads A = 500 T1: Rollback T1 is aborted, A reverts to its original value T2 has now read a value of A that never actually existed in the committed database, since T1's change was undone after T2 already read it.
Each of these four problems stems from the same underlying issue: transactions accessing shared data without any coordination about when it is safe to read or write that data relative to other transactions working with the same data at the same time.
The most widely used approach to concurrency control relies on locks, a mechanism where a transaction must formally request permission before reading or writing a particular piece of data, and the database management system grants or denies that request based on what locks other transactions currently hold on that same data.
| Lock Type | Purpose | Compatibility |
|---|---|---|
| Shared Lock (S) | Requested before reading a data item. | Multiple transactions can hold a shared lock on the same data item simultaneously, since reading does not change the data. |
| Exclusive Lock (X) | Requested before writing a data item. | Only one transaction can hold an exclusive lock on a data item at a time, and no other transaction can hold any lock, shared or exclusive, on that same item simultaneously. |
T1: Lock-X(A) T1: Read(A) A = 1000 T1: A = A - 500 T1: Write(A) A = 500 T1: Unlock(A) T1: Commit T2: Lock-X(A) (must wait until T1 releases its lock) T2: Read(A) A = 500 (correctly sees T1's committed update) T2: A = A - 200 T2: Write(A) A = 300 T2: Unlock(A) T2: Commit
By forcing T2 to wait until T1 releases its exclusive lock on A, the lock-based protocol guarantees that T2 always works with the correct, up-to-date value of A, completely eliminating the lost update problem demonstrated in the previous chapter.
Simply using locks is not enough by itself to guarantee correctness; the timing of when locks are acquired and released matters enormously. Two-phase locking is a widely used protocol that divides every transaction's lifetime into exactly two distinct phases regarding its locking behavior.
| Phase | Description |
|---|---|
| Growing Phase | The transaction can acquire new locks, but cannot release any locks it already holds. |
| Shrinking Phase | The transaction can release locks, but cannot acquire any new locks from this point onward. |
Growing Phase: Lock-X(A) Lock-S(B) Lock-X(C) (Point of maximum lock ownership reached) Shrinking Phase: Unlock(A) Unlock(B) Unlock(C)
The rule is strict: once a transaction releases even a single lock, it is no longer permitted to acquire any new locks for the remainder of its execution. This clean separation between the growing and shrinking phases is what gives two-phase locking its name, and it can be mathematically proven that any schedule following this protocol is guaranteed to be conflict serializable, meaning it produces the same result as some valid serial ordering of the transactions involved.
A common refinement, called strict two-phase locking, requires that all exclusive locks held by a transaction be released only after that transaction has either committed or aborted, rather than being released earlier during a separate shrinking phase. This stricter variant additionally prevents dirty reads, since no other transaction can ever read data that has been modified by a transaction that has not yet reached its final commit or abort decision, making strict 2PL the most commonly implemented locking approach in real database systems.
An entirely different approach to concurrency control avoids locks altogether, instead assigning every transaction a unique timestamp at the moment it begins, typically based on the system clock or a simple incrementing counter. This timestamp determines a fixed, unambiguous ordering among transactions, and the database enforces that all conflicting operations occur in an order consistent with these timestamps.
Every data item maintains two additional pieces of information alongside its actual value: a read-timestamp, recording the largest timestamp of any transaction that has successfully read it, and a write-timestamp, recording the largest timestamp of any transaction that has successfully written it.
| Operation | Rule |
|---|---|
| Read by transaction Ti | If Ti's timestamp is earlier than the data item's write-timestamp, Ti is attempting to read a value that has already been overwritten by a "future" transaction, so Ti is rejected and rolled back. |
| Write by transaction Ti | If Ti's timestamp is earlier than the data item's read-timestamp or write-timestamp, Ti is attempting to write a value that a "future" transaction has already read or overwritten, so Ti is rejected and rolled back. |
T1 has timestamp 10
T2 has timestamp 20
Data item A currently has read-timestamp 0 and write-timestamp 0.
T2: Write(A) T2's timestamp (20) ≥ A's read and write timestamps (0, 0), allowed.
A's write-timestamp is updated to 20.
T1: Write(A) T1's timestamp (10) < A's write-timestamp (20), rejected.
T1 is rolled back and restarted with a new, later timestamp.
In this example, T1 attempted to write a value into A after T2, a transaction with a later timestamp, had already written to it. Allowing T1's write to succeed would effectively make it appear as though T1 executed before T2, contradicting their actual timestamp order, so the protocol correctly rejects T1's operation and forces it to restart.
Timestamp-based protocols have the notable advantage of being deadlock-free by design, since transactions are never made to wait for locks; instead, a transaction that would violate the required ordering is simply rolled back and restarted immediately. This comes at the cost of potentially more rollbacks compared to a well-tuned locking protocol, particularly under heavy contention for the same data items.
| Aspect | Lock-Based Protocols | Timestamp-Based Protocols |
|---|---|---|
| Coordination Mechanism | Transactions request and wait for locks before accessing data. | Transactions are ordered using assigned timestamps, with no waiting for locks. |
| Deadlock Possibility | Possible, since transactions can end up waiting for each other's locks in a cycle. | Not possible, since transactions are rolled back instead of being made to wait. |
| Rollback Frequency | Generally lower, since transactions wait rather than restart. | Can be higher under heavy contention, since conflicting transactions are restarted rather than delayed. |
| Mistake | Correct Understanding |
|---|---|
| Assuming multiple transactions can never hold a lock on the same data at once. | Multiple transactions can simultaneously hold shared locks on the same data item for reading; only exclusive locks require sole ownership. |
| Confusing two-phase locking with strict two-phase locking. | Basic 2PL allows locks to be released gradually during a shrinking phase, while strict 2PL holds all exclusive locks until the transaction commits or aborts. |
| Believing timestamp-based protocols use locks internally. | Timestamp-based protocols avoid locks entirely, instead relying on comparing timestamps to reject and roll back conflicting operations. |
| Assuming 2PL completely eliminates the possibility of deadlock. | Two-phase locking guarantees serializability but does not by itself prevent deadlock, which is why deadlock handling is studied as a separate topic. |
Concurrency control provides the mechanisms that allow multiple transactions to execute simultaneously without falling into the lost updates, dirty reads, unrepeatable reads, and phantom reads that can otherwise corrupt a database silently. Through lock-based protocols like two-phase locking, and the entirely different approach of timestamp-based ordering, this chapter demonstrated two proven strategies databases use to guarantee correctness even under heavy concurrent load.
In this tutorial, you learned about the four major concurrency problems with a worked dirty read example, how shared and exclusive locks work together with the two-phase locking protocol to prevent lost updates, and how timestamp-based protocols achieve the same correctness guarantee without using locks at all. With this foundation, you are ready to move on to serializability, which provides the formal mathematical framework used to precisely define and verify exactly what makes a concurrent schedule "correct" in the first place.