How Relational Databases Prevent Data Corruption: Two-Phase Locking (2PL)

When hundreds of database transactions read and write to the same tables concurrently, simply using locks is not enough.

If transactions acquire and release locks haphazardly, the database can produce non-serializable anomalies—such as reading partial updates, lost updates, or inconsistent snapshots.

To mathematically guarantee Conflict Serializability, relational database engines enforce Two-Phase Locking (2PL).

---

⚡ The Two Phases of 2PL

A transaction must divide its lock operations into two distinct, non-overlapping phases:

Locks Held
    ▲
    │             [ Lock Point ]
    │                  /\
    │                 /  \
    │   GROWING      /    \      SHRINKING
    │   PHASE       /      \     PHASE
    │  (Acquire)   /        \   (Release)
    │             /          \
    └────────────/────────────\─────────────► Time

1. Growing Phase:
   • The transaction may acquire new locks (Shared or Exclusive).
   • The transaction CANNOT release any locks.
2. The Lock Point:
   • The exact moment the transaction acquires its final lock. The sequence of lock points across transactions dictates the global serialization order.
3. Shrinking Phase:
   • The transaction releases locks.
   • The transaction CANNOT acquire any new locks.

---

⚠️ The Limitation of Basic 2PL: Cascading Aborts

While basic 2PL guarantees serializability, it is vulnerable to Cascading Rollbacks:

T1: write(A) ──> unlocks(A) [Shrinking Phase]
                      │
T2:                   └──> locks(A) ──> read(A) ──> write(B)
                                                          │
T1 crashes and ABORTS! ───────────────────────────────────┘
(T2 read dirty uncommitted data from T1, so T2 must also abort!)

The Production Fix: Strict 2PL (S2PL)

Modern RDBMS engines (like PostgreSQL and MySQL InnoDB) enforce Strict 2PL:

• All Exclusive (write) locks must be held until the transaction COMMITS or ABORTS.
• This eliminates cascading aborts completely because uncommitted writes are never exposed to other transactions.

---

🚀 Explore Concurrency Schedules Interactively

You can test custom schedules, generate precedence graphs, and detect deadlocks interactively on Binary Dose.