Concurrency Control in DBMS

What is Concurrency Control?

Concurrency control is the mechanism that ensures correct results when multiple transactions execute simultaneously. Without concurrency control, concurrent transactions can interfere with each other and produce incorrect results (dirty reads, lost updates, etc.).

Lock-Based Protocols

Locking is the most common concurrency control mechanism. A transaction must acquire a lock on a data item before accessing it.

Lock Type	Also Called	Allows	Blocks
Shared Lock (S)	Read Lock	Multiple transactions to read simultaneously	Write operations
Exclusive Lock (X)	Write Lock	Only one transaction to read AND write	All other reads and writes

Lock Compatibility Matrix:

Requested →	Shared (S)	Exclusive (X)
Held: Shared (S)	✓ Compatible	✗ Incompatible
Held: Exclusive (X)	✗ Incompatible	✗ Incompatible

Two-Phase Locking (2PL)

Two-Phase Locking is a protocol that guarantees serializability. It has two phases:

Growing Phase: A transaction can acquire locks but cannot release any lock.
Shrinking Phase: A transaction can release locks but cannot acquire any new lock.

The point where a transaction transitions from growing to shrinking is called the lock point. Transactions are serialized in the order of their lock points.

Variants of 2PL:

Strict 2PL: All exclusive locks are held until the transaction commits or aborts. Prevents cascading rollbacks.
Rigorous 2PL: All locks (shared and exclusive) are held until commit/abort. Simplest to implement.

Deadlock

A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular wait. None of the transactions can proceed.

Example:

T1 holds lock on A, waiting for lock on B
T2 holds lock on B, waiting for lock on A
Neither can proceed → deadlock

Deadlock Prevention:

Wait-Die: Older transaction waits; younger transaction is killed (dies) and restarted.
Wound-Wait: Older transaction wounds (kills) younger; younger transaction waits for older.
No Wait: If a lock cannot be granted immediately, the transaction is aborted.
Timeout: A transaction is aborted if it waits longer than a specified time.

Deadlock Detection: The DBMS periodically checks for cycles in the wait-for graph. If a cycle is found, one transaction (the victim) is aborted to break the deadlock.

Isolation Levels

SQL defines four isolation levels that trade off between consistency and concurrency:

Isolation Level	Dirty Read	Unrepeatable Read	Phantom Read	Performance
READ UNCOMMITTED	Possible	Possible	Possible	Highest
READ COMMITTED	Prevented	Possible	Possible	High
REPEATABLE READ	Prevented	Prevented	Possible	Medium
SERIALIZABLE	Prevented	Prevented	Prevented	Lowest

Timestamp-Based Concurrency Control

An alternative to locking. Each transaction is assigned a unique timestamp when it starts. The DBMS uses timestamps to order transactions and resolve conflicts:

Each data item has a read timestamp (RTS) and write timestamp (WTS)
If T wants to read X: if TS(T) < WTS(X), T is too old — abort and restart T
If T wants to write X: if TS(T) < RTS(X) or TS(T) < WTS(X), abort and restart T
Advantage: No deadlocks (no waiting)
Disadvantage: More aborts and restarts

Previous Next