Transactions & Concurrency

Locking and Two-Phase Locking

Locks coordinate access to logical resources at row, key, range, page, or table granularity. Two-phase locking grows then shrinks a lock set; strict variants retain write locks through completion, while predicate protection is essential for serializable searches.
  • Shared and exclusive modes express compatibility.
  • Intention locks make hierarchy safe.
  • Granularity trades concurrency for overhead.
  • 2PL has growing and shrinking phases.
  • Rows do not represent absent rows.
  • 2PL is not two-phase commit.
Simplified compatibility
Held / requestedSX
Scompatiblewait
Xwaitwait
Lock hierarchy and intention
Phantom without predicate protection
Start: no open order for customer 7; invariant at most one open order/customer.
T1 searches and finds none. T2 searches and finds none.
T1 inserts order 91; T2 inserts order 92; both commit.
Row locks on existing rows lock nothing; a UNIQUE constraint or protected key range is required.

An index changes which keys and gaps can be locked and how many resources a query touches. A full scan, missing index, or changed plan can broaden lock footprint; correctness must rely on the engine’s isolation contract, not a guessed row-lock plan.