Locks

Locks serialize access to rows, pages, or tables so concurrent transactions do not corrupt shared state. Engines combine MVCC reads with explicit or implicit write locks.

| Mechanism | Behavior | |-----------|----------| | Row-level write lock | Blocks conflicting updates on same row | | `SELECT ... FOR UPDATE` | Pessimistic: lock rows before update | | `SELECT ... FOR SHARE` | Shared lock — blocks writers, allows readers | | Predicate / gap lock | Protects ranges — phantom prevention (InnoDB) | | Table lock | Coarse — migrations, bulk DDL | | Advisory lock | App-defined mutex (`pg_advisory_lock`) | | Optimistic concurrency | Version column — retry on `UPDATE ... WHERE version = ?` |

					BEGIN;
SELECT * FROM seats WHERE flight_id = 42 AND seat_no = '12A'
  FOR UPDATE;
-- app checks status, then updates
UPDATE seats SET status = 'sold' WHERE id = 991;
COMMIT;
				

Deadlocks happen when two txs wait on each other's locks — the engine aborts one victim; apps should retry with backoff. Lock ordering (always lock parent then child) reduces cycles.

On interviews: compare pessimistic row locks vs optimistic versioning for a seat booking or inventory counter.

Common pitfalls: `FOR UPDATE` on wide scans locking thousands of rows; missing index turning row locks into gap/table locks; long transactions holding locks; advisory locks without timeout and fencing.

The trade-off is balancing exclusive access for correctness against throughput and deadlock risk — lock the smallest granule for the shortest time that preserves the invariant.

Checklist:

• Name what resource is protected and at what granularity.
• Choose pessimistic vs optimistic for the contention pattern.
• Define lock order to avoid deadlocks.
• Plan retry on deadlock or serialization failure.
• Measure lock wait time in production metrics.