Understanding Database Transaction Isolation and Locking Protocols

I've always only had a superficial understanding of database transaction isolation mechanisms, so I decided to study the principles and organize my notes.

Concurrency problems in database operations

When multiple transactions access the database simultaneously, five types of issues can occur: three read anomalies (dirty read, non‑repeatable read, phantom read) and two write anomalies (first kind of lost update, second kind of lost update).

Dirty read

A transaction reads data modified by another transaction that has not yet committed; if the other transaction rolls back, the read data is invalid.

Non‑repeatable read

A transaction reads a row, another transaction modifies and commits that row, and the first transaction reads the same row again and sees different data.

Phantom read

A transaction reads a set of rows, another transaction inserts a new row that matches the query condition, and the first transaction reads again and sees an additional row.

Lost updates

First kind: a transaction overwrites data that has already been committed by another transaction when it rolls back. Second kind: a transaction overwrites data committed by another transaction at commit time.

Locking protocols

To prevent these problems, databases use lock protocols that define when an operation may proceed.

Level‑1 lock protocol : A transaction must acquire an exclusive (X) lock before modifying a data item and holds it until the transaction ends (commit or rollback). It prevents lost updates but does not protect against dirty reads or non‑repeatable reads.

Level‑2 lock protocol : Adds a shared (S) lock that a transaction must acquire before reading a data item; the S lock is released after the read. This prevents dirty reads and lost updates but still allows non‑repeatable reads.

Level‑3 lock protocol : The transaction holds the S lock on read until the transaction ends, in addition to the X lock for writes. This prevents dirty reads, lost updates, and non‑repeatable reads.

Transaction isolation levels

The three‑level lock protocols correspond to the four standard isolation levels used by most DBMSs:

Read Uncommitted

Read Committed

Repeatable Read

Serializable

These levels progressively restrict concurrency, with performance decreasing as isolation strengthens. MySQL’s default is Repeatable Read .

Read Uncommitted

Only the level‑1 lock protocol is applied; transactions can read uncommitted changes, leading to dirty reads.

Read Committed

Level‑2 locking is used; a transaction can only read data that has been committed. MySQL implements this with MVCC, returning a snapshot of the data as of the start of the read.

Repeatable Read

Based on level‑2 locking with additional MVCC guarantees; a transaction sees a consistent snapshot for the duration of the transaction, preventing dirty and non‑repeatable reads, though phantom reads can still occur. InnoDB uses gap locks to mitigate phantom reads.

Serializable

Implements the level‑3 lock protocol; all transactions are effectively serialized, eliminating all concurrency anomalies at the cost of higher waiting, blocking, and possible deadlocks.

Two‑phase locking (2PL)

All transactions must acquire all required locks during an expanding phase before any lock is released, and then release locks only during a shrinking phase. This schedule guarantees serializability.

Under any isolation level, a transaction cannot delete or modify data that another uncommitted transaction has affected, because exclusive locks block such operations, preventing lost updates.