Locking & Deadlocks

Isolation levels are the setting you choose. But something has to actually enforce them.

That something is locks. And locks have a failure mode of their own — one where two transactions wait for each other forever.

Why & what

To stop T2 from reading a row T1 is halfway through changing, the database must make T2 wait. A lock is how it does that.

A lock is a claim a transaction puts on a row so others can't interfere with it. A shared (read) lock allows other readers. An exclusive (write) lock allows nobody else.

How it works

1. Shared lock — reading. T1 reads Aisha's fees and takes a shared lock. T2 wants to read the same row: allowed. Many readers can hold shared locks together, because reading changes nothing.
2. Exclusive lock — writing. T1 updates the fees and takes an exclusive lock. Now nobody else may read or write that row until T1 finishes. One writer, alone.
3. Two-phase locking (2PL) — the rule that makes this safe. Every transaction has a growing phase where it only takes locks, and a shrinking phase where it only releases them. Once you've released one lock, you may never take another. Follow that rule and the result is always equivalent to some serial order.
4. Now the trap. T1 locks row A and asks for row B. T2 locks row B and asks for row A. T1 waits for T2. T2 waits for T1. Neither will ever release, because neither can finish. That's a deadlock.
5. How the database escapes. It detects the cycle, picks a victim, kills that transaction and rolls it back. The survivor proceeds. Your application gets an error and retries — which is why real code wraps transactions in retry logic.

Pause here. Both transactions in your app update Student then College, in that order, every time. Can they deadlock on those two rows?

No. A deadlock needs the two transactions to grab the resources in opposite orders. Consistent ordering is the cheapest deadlock prevention there is.

Notice: a lock makes others wait — a deadlock makes everyone wait forever.

Common confusion

Deadlock and starvation are different failures.

• Deadlock — transactions wait for each other in a circle. Nobody ever moves.
• Starvation — one transaction could proceed, but keeps getting pushed to the back of the queue while others go first. It moves eventually, or never, but nothing is circular.

Circular wait versus bad luck in the queue.

Second: a deadlock isn't a crash or a bug in the database. It's an expected outcome of concurrent work. The database handles it by killing one side. Your job is to retry, and to reduce how often it happens — by touching tables in a consistent order and keeping transactions short.

Interview angle

1. "What is a deadlock, and how does a DBMS handle it?" — Two or more transactions waiting on each other in a cycle. The DBMS detects it, kills a victim, rolls it back, and the other finishes. Add that the app should retry.
2. "Difference between a shared and an exclusive lock?" — Shared allows other readers; exclusive allows nobody else. Reads coexist, writes don't.
3. "What is two-phase locking?" — Take all locks in a growing phase, release in a shrinking phase, never take one after releasing. It guarantees serializability — but note that 2PL prevents nothing about deadlocks.
4. "How would you reduce deadlocks in an application?" — Always access tables in the same order, keep transactions short, and use the lowest isolation level the task actually needs.

Recap

Locks enforce isolation — shared locks let readers share a row, exclusive locks give a writer sole access, 2PL orders lock taking and releasing, and a deadlock is a circular wait that the DBMS breaks by killing one transaction.