Skip to content

Two-Phase Locking (2PL)#

Two-phase locking is the classical algorithm that achieves serializable isolation in DBs. A transaction has two phases: growing (only acquires locks) and shrinking (only releases locks).

flowchart TB
  Start[BEGIN] --> Grow[Growing phase<br/>acquire locks as needed]
  Grow --> Peak[Lock peak]
  Peak --> Shrink[Shrinking phase<br/>release locks, cannot acquire more]
  Shrink --> End[COMMIT or ROLLBACK]

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class Start,Grow,Peak,Shrink,End service;

Strict 2PL (most common in practice) holds all exclusive locks until commit/rollback. Used by MySQL InnoDB (under the hood), SQL Server (default), and many embedded DBs.

Two-phase locking (2PL) is the original algorithm for serializable transactions. Even systems that use MVCC for most reads fall back to 2PL for SELECT FOR UPDATE and constraint enforcement.

The protocol#

A transaction obeys 2PL if:

  1. Growing phase: only acquires locks.
  2. Shrinking phase: only releases locks. Once you release one, you can't acquire any.

The serializability theorem says: any schedule produced by 2PL transactions is equivalent to some serial schedule.

Three variants#

Variant Locks held until
Basic 2PL Released anywhere in shrinking phase
Strict 2PL (S2PL) All exclusive locks held until commit/abort
Rigorous 2PL All locks (shared + exclusive) held until commit/abort

Strict 2PL prevents cascading aborts (a reader can't see uncommitted data, so it can't depend on aborted writes). Rigorous 2PL additionally simplifies recovery and is what most production DBs use.

Lock modes#

flowchart LR
  S[Shared S<br/>multiple readers]
  X[Exclusive X<br/>one writer]
  U[Update U<br/>reader intending to write]

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class S,X,U service;

Compatibility matrix:

S X U
S yes no yes
X no no no
U yes no no

The Update mode (SQL Server) prevents the classic upgrade-deadlock where two readers both want to upgrade S→X.

Intention locks (multi-granularity)#

Locking at row level alone misses the case where someone wants to drop the table while you hold a row lock. Intention locks declare intent at higher granularity:

Table:  IS (intent shared) or IX (intent exclusive)
Page:   IS / IX
Row:    S / X

A transaction wanting an S on a row takes IS on its page and table first.

Deadlock detection#

flowchart LR
  T1[T1 holds R1, wants R2] --> T2[T2 holds R2, wants R1]
  T2 --> T1

Most DBs maintain a waits-for graph; a cycle = deadlock. The engine picks a victim (usually the youngest or least-progress txn), aborts it, and the other proceeds.

Alternatives: - Timeout (innodb_lock_wait_timeout): wait N seconds, abort. - Wound-wait / wait-die scheduling: avoid deadlocks structurally using timestamps.

2PL vs MVCC#

Property 2PL MVCC
Readers block writers Yes (S vs X) No
Writers block readers Yes No
Writers block writers Yes Yes
Phantom reads Range locks prevent Snapshot isolation requires extra (predicate) locks
Memory footprint Lock table Multiple row versions

Most modern DBs use MVCC by default; 2PL kicks in for SELECT FOR UPDATE, foreign-key checks, and serializable level.

Used by#

DB 2PL use
MySQL InnoDB Strict 2PL for X locks; MVCC for snapshot reads
SQL Server 2PL by default (read committed snapshot isolation optional)
DB2 2PL
Sybase / Spanner Combinations
PostgreSQL MVCC primary; 2PL via FOR UPDATE and serializable level uses SSI

Locks and indexes#

Real DBs lock at multiple granularities:

  • Table lock (DDL).
  • Page lock (deprecated in most DBs).
  • Row lock (the usual).
  • Key/gap locks (MySQL): lock the index range to prevent phantoms.

Pitfalls#

  • Lock escalation: too many row locks → upgrade to table lock; throughput collapses (SQL Server).
  • Hot rows: high-contention counter row serializes the entire workload.
  • Long-held locks: a transaction that does external I/O while holding locks. Always do I/O outside the transaction.

Where 2PL connects#

flowchart TB
  PL((Two-phase<br/>locking))
  PO[Pessimistic / optimistic<br/>broader trade-off]
  MVCC[MVCC isolation<br/>lock-free alternative]
  TD[Threading / deadlocks<br/>OS-level analog]
  ACID[ACID properties<br/>implements I]
  PO --> PL
  MVCC -. alternative .- PL
  TD -. analog .- PL
  ACID --> PL

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class PL service;
    class PO,MVCC,TD,ACID datastore;

Glossary & fundamentals#

Tag Concept What it is Page
LLD Pessimistic vs optimistic locking the broader trade-off pessimistic-optimistic-locking
HLD MVCC isolation levels the alternative concurrency model mvcc-isolation-levels
LLD Threading and deadlocks analog at OS thread level threading-and-deadlocks
HLD ACID properties I requires either 2PL or MVCC acid-properties

Quick reference#

Protocol#

  • Growing: only acquire
  • Shrinking: only release; once you release one, no more acquires

Variants#

Variant Held until
Basic 2PL Anywhere in shrinking
Strict (S2PL) All X until commit/abort
Rigorous All locks until commit/abort

Lock modes#

  • S - Shared (read)
  • X - Exclusive (write)
  • U - Update (SQL Server; read planning to write)

Plus intention locks (IS, IX) for multi-granularity.

Deadlock handling#

  • Waits-for graph + victim selection
  • Timeout (innodb_lock_wait_timeout)
  • Wound-wait / wait-die schedulers

2PL vs MVCC#

  • 2PL: readers and writers block each other
  • MVCC: writers don't block readers; need predicate locks for phantoms

Used by#

  • MySQL InnoDB (X via 2PL, reads via MVCC)
  • SQL Server (default 2PL; RCSI optional)
  • DB2

Pitfalls#

  • Hot rows serialise workload
  • Lock escalation (SQL Server) → table lock
  • I/O inside transaction = long-held locks

Refs#

  • Gray & Reuter
  • MySQL InnoDB locking docs
  • DDIA - Transactions

FAQ#

What is two-phase locking?#

Two-phase locking is a concurrency protocol where a transaction first only acquires locks (growing phase) then only releases them (shrinking phase). It guarantees serializability.

What is strict two-phase locking?#

Strict 2PL holds all exclusive locks until commit or rollback. It avoids cascading aborts and is what InnoDB and SQL Server use under the hood for write workloads.

How does 2PL differ from MVCC?#

2PL blocks readers and writers on the same row. MVCC keeps multiple versions so readers never block writers. Most modern DBs use MVCC for reads and 2PL for explicit locks.

Can two-phase locking cause deadlocks?#

Yes. Acquiring locks in different orders is a classic 2PL deadlock. Databases detect cycles in the wait-for graph and abort one transaction so the rest can progress.

Why do some queries still use SELECT FOR UPDATE on an MVCC database?#

When business rules require strict ordering, like reserving the last unit of inventory, a pessimistic 2PL lock prevents lost updates that MVCC alone would let through.

Further reading#