transactions & locking
Locking & Deadlocks
Recognize how the DB acquires locks (row, range, table) and avoid deadlocks via consistent lock order and short transactions.
To enforce isolation, a database must prevent concurrent transactions from clobbering each other, and locking is the primary mechanism. Understanding what gets locked, and when, explains both why transactions sometimes wait on each other and the dreaded deadlock, where two transactions each hold what the other needs and neither can proceed.
Two people each holding one key for a door that needs both. Polite resolution: drop your key (abort), let the other proceed, try again.
Key Concepts
1
Locks come at different granularities. A row lock affects a single row and allows high concurrency; a range or gap lock covers a span of index values to prevent phantom inserts at higher isolation levels; a table lock blocks the whole table and is coarse but cheap. Locks also have modes: shared (read) locks coexist with other shared locks, while exclusive (write) locks conflict with everything, so a writer blocks other writers and readers of the same row (in a pure locking model). A deadlock arises from a cycle: transaction A locks row 1 then waits for row 2, while transaction B locks row 2 then waits for row 1. Neither can advance, so the database's deadlock detector notices the cycle and aborts one transaction as the "victim," rolling it back so the other can continue — the application then sees a deadlock error and is expected to retry.
2
The prevention techniques are the heart of an interview answer. Always acquire locks in a consistent order across the codebase — if every transaction touches accounts in ascending id order, the A-then-B versus B-then-A cycle cannot form. Keep transactions short so locks are held briefly, do not hold locks across user think-time or network calls, and use the lowest isolation level that is correct for the task. SELECT ... FOR UPDATE deliberately takes a write lock to implement safe read-then-write logic, and choosing between pessimistic locking and optimistic concurrency (a version column checked on update) is the broader design decision behind all of this.
SELECT ... FOR UPDATE