The idea: lock what you touch, release only at the end
A database runs many transactions at the same time. To keep them from seeing each other half-done, a locking database makes every transaction lock a row before it reads or writes it. Two-phase locking (2PL) adds one rule: a transaction first only takes locks (the growing phase), and once it has released a lock it may never take another (the shrinking phase). The page runs three transactions, T1, T2 and T3, on rows A, B and C of a table t(id, v).
The canvas shows each transaction's panel (its trx id, its state, its phase and the statements it ran), the lock table kept by the lock manager, and the wait-for graph.
Shared and exclusive locks
There are two lock modes. A shared (S) lock is for reading: SELECT … FOR SHARE takes one. An exclusive (X) lock is for writing: UPDATE, DELETE and SELECT … FOR UPDATE take one. Many readers can share a row, but a writer must be alone:
| held \ requested | S | X |
|---|---|---|
| S | ✓ compatible | ✗ waits |
| X | ✗ waits | ✗ waits |
A transaction that holds S on a row and then updates it upgrades its lock to X. That works only if no one else holds the row. Demo: shared locks are compatible lets T1 and T2 share A. Demo: X waits for S makes T2's UPDATE wait until T1 commits.
Each row in the lock table has a list of granted locks and a wait queue. A new request is checked against both: it waits if it conflicts with a granted lock or with a request already queued ahead of it. So a stream of readers cannot starve a writer that is waiting.
Why two phases
Locks alone are not enough. If T1 released its lock on A before locking B, T2 could change both A and B in between, and T1 would see A before T2 and B after T2. No order of running them one after another gives that result. With two phases, every transaction has a lock point: the moment it holds its last new lock. Conflicting transactions can't overlap at their lock points, so the result is the same as running them one by one in lock-point order. The schedule is conflict-serializable.
Basic 2PL may release locks early in the shrinking phase. Then another transaction can read a value that is later rolled back, and must be rolled back too (a cascading abort). Strict 2PL holds every lock until COMMIT or ROLLBACK, so this can't happen. InnoDB, SQL Server's locking mode and Db2 do it this way, and so does the page: the shrinking phase is one step, at COMMIT.
Waiting and the wait-for graph
A request that conflicts joins the row's queue, and the transaction waits: its session is blocked until the lock is granted. The wait-for graph has a node per transaction and an edge from each waiter to every transaction it waits for, labelled with the row. When a holder commits, its locks go to the waiters in queue order, and their edges disappear.
Deadlock detection and the victim
2PL does not prevent deadlocks. In Demo: classic deadlock, T1 updates A and then B, and T2 updates B and then A. Each holds the row the other wants. The graph gets T1 → T2 and T2 → T1: a cycle. No one in a cycle can ever go on, because each one waits for the next.
InnoDB checks for a cycle each time a transaction has to wait (innodb_deadlock_detect = ON, the default). When it finds one, it picks a victim, rolls it back, and sends it:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
The rollback frees the victim's locks and the others go on. The page picks the youngest transaction in the cycle (the highest trx id). InnoDB really picks the "smaller" transaction, the one that has changed or locked fewer rows, so less work is thrown away. SHOW ENGINE INNODB STATUS prints the last deadlock under LATEST DETECTED DEADLOCK.
With detection turned off, a waiter gives up after innodb_lock_wait_timeout (50 seconds by default) with ERROR 1205: Lock wait timeout exceeded. Demo: three-transaction cycle shows that a cycle can be longer: T1 → T2 → T3 → T1. T3 is the youngest, so it is rolled back, and then T2 and T1 finish.
Avoiding deadlocks
- Lock in one order. If every transaction locks A before B, nobody can hold B while waiting for A, so no cycle can form. Demo: same lock order, no deadlock runs the classic demo's work with that rule: T2 just waits.
- Keep transactions short, and don't wait for a user or a remote call while holding locks.
- Retry on 1213. A deadlock is a normal result: the application runs the whole transaction again from the start.
- Some systems prevent cycles with timestamps instead of finding them: wait-die (an older transaction may wait for a younger one, a younger one is aborted) and wound-wait (an older transaction aborts the younger one that holds its lock).
The same cycle with threads and mutexes is the dining philosophers. Databases that don't lock rows for reading use versions instead: see MVCC and isolation levels. Optimistic vs pessimistic locking compares SELECT … FOR UPDATE with a version column at the application level.
For PostgreSQL's version (8 table lock modes, row locks stored in xmax, deadlock check after deadlock_timeout) see The PostgreSQL Lock Manager.
InnoDB Isolation shows which InnoDB statements take these locks and which read a snapshot instead. InnoDB also locks the gaps between index records; see InnoDB record, gap and next-key locks. Above InnoDB's row locks, MySQL also locks table definitions with metadata locks: MySQL Metadata Locks and Online DDL.
Across several databases, each participant holds these locks until the coordinator's decision: see Two-Phase Commit.
What the page leaves out
InnoDB locks index records, not rows, and adds gap and next-key locks so that no one can insert into a range a transaction has scanned. It also has table-level intention locks (IS, IX), and a plain SELECT takes no lock at all: it reads a snapshot through MVCC. The page has no lock wait timeout, picks the youngest victim instead of the smallest, gives each transaction its trx id at its first statement, and does not show the undo of the victim's changes.