transactions & locking
Locking & Deadlocks
Recognize how the DB acquires locks (row, range, table) and avoid deadlocks via consistent lock order and short transactions.
A lock is how the database stops two transactions from clobbering the same row. It also explains why transactions wait on each other, and why they occasionally kill each other.
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
Granularity — how much gets locked. A row lock covers one row and lets everything else run. A gap or
range lock covers a span of index values, so nobody can insert into a range you are reading — this is how
higher isolation levels stop phantoms. A table lock covers everything, and is cheap to take and brutal to
wait on.
2
Mode — shared versus exclusive. Shared locks coexist: many readers, no problem. An exclusive lock
conflicts with everything, so one writer blocks all other writers and, in a pure locking model, readers
too. Under MVCC, readers do not take these locks at all, which is why most waiting you see is
writer-on-writer.
3
A deadlock is a cycle. Not a slow query — a genuine standstill:
4
T1: UPDATE account 1 (holds 1)
T2: UPDATE account 2 (holds 2)
T1: UPDATE account 2 -> waits for T2
T2: UPDATE account 1 -> waits for T15
Neither will ever proceed. The database's detector finds the cycle, picks a victim, and rolls it back with
a deadlock error. The survivor continues.
6
This means a deadlock error is not a bug report — it is the database telling your application to retry.
Any code that can deadlock needs a retry loop around the transaction.
7
The fix is consistent ordering. Look at the cycle again: T1 took 1 then 2, T2 took 2 then 1. If both
had taken the lower account id first, T2 would simply have waited for account 1 and then proceeded. No
cycle can form when every transaction takes locks in the same order.
8
So sort your ids before you update them. It is a one-line change and it eliminates the whole class:
9
for (Long id : ids.stream().sorted().toList()) { ... }10
Everything else is about holding locks briefly. Keep transactions short. Never hold a lock across a
network call or anything waiting on a person. Use the lowest isolation level that is correct, since higher
levels take more locks.
11
Taking a lock deliberately. SELECT ... FOR UPDATE locks the rows you read, so a read-then-write
decision cannot be undercut:
SELECT ... FOR UPDATE
12
SELECT stock FROM items WHERE id = 7 FOR UPDATE; -- others now wait
UPDATE items SET stock = stock - 1 WHERE id = 7;13
That is pessimistic locking: assume a conflict, take the lock. The alternative is optimistic — keep a
version column, and on update require the version to be unchanged. If zero rows update, somebody beat you,
so re-read and retry.
14
Pessimistic suits high contention on few rows. Optimistic suits rare conflicts, and avoids holding any lock
at all.
15
What the interviewer is probing.1. "What causes a deadlock?" Probing: the condition. Stalls: "Too much load." Moves up:
two transactions acquiring the same locks in opposite order, each waiting on the other.
16
2. "How do you prevent them?" Probing: the discipline. Stalls: "Shorter transactions."
Moves up: acquire locks in a consistent order everywhere, keep transactions short, and use the
smallest lock scope that works.
17
3. "What does the database do when it detects one?" Probing: the resolution. Stalls: "It
hangs." Moves up: it kills one transaction as the victim, so the application must catch that error
and retry.
18
4. "What is the difference between optimistic and pessimistic locking?" Probing: the two
models. Stalls: "The same." Moves up: pessimistic locks the row up front; optimistic checks a
version at write time and fails if it changed — better when conflicts are rare.