Database Locking Explained
Locking is the mechanism that lets a database serve thousands of concurrent users writing to the same tables without their changes clobbering each other. It is mostly invisible - until two transactions stall waiting on each other, throughput collapses, or you see a deadlock error in the logs. At that point, locking stops being a background detail and becomes the thing you must understand. The good news is that the core ideas are small and they explain most concurrency problems.
Why locks exist
Imagine two transactions reading an account balance of 100, each adding 50, and each writing back 150. Without coordination, one update silently overwrites the other and 50 dollars vanish. This is a lost update, and locks are the classic defense. A lock ensures that while one transaction is working with a piece of data, others must wait or work with a consistent view, so operations that look concurrent produce correct results.
Shared and exclusive locks
The two fundamental lock modes are shared and exclusive. A shared lock is for reading: many transactions can hold a shared lock on the same data at once. An exclusive lock is for writing: only one transaction can hold it, and it excludes all others, readers and writers alike. The rule is simple - reads can coexist, but a write must be alone.
| Requesting | Existing: none | Existing: shared | Existing: exclusive |
|---|---|---|---|
| Shared (read) | Granted | Granted | Wait |
| Exclusive (write) | Granted | Wait | Wait |
Lock granularity
Locks can be taken at different levels: a single row, a page of rows, or an entire table. Fine-grained row locks allow high concurrency because transactions touching different rows never block each other, but they cost more to track. Coarse table locks are cheap to manage but serialize access to the whole table. Databases sometimes escalate many row locks into a single table lock to save memory - a common and surprising cause of sudden contention.
| Granularity | Concurrency | Overhead |
|---|---|---|
| Row | High - only conflicting rows block | Higher bookkeeping |
| Page | Medium | Medium |
| Table | Low - whole table serialized | Minimal |
Deadlocks: the classic trap
A deadlock occurs when two transactions each hold a lock the other needs. Transaction A locks row 1 and wants row 2; transaction B locks row 2 and wants row 1. Neither can proceed and neither will release. Databases detect this and resolve it by killing one transaction - the victim - which then must retry. Deadlocks are not bugs in the database; they are a consequence of the order in which your application acquires locks.
-- Transaction A -- Transaction B
BEGIN; BEGIN;
UPDATE acct WHERE id=1; UPDATE acct WHERE id=2;
-- now wants id=2 -- now wants id=1
UPDATE acct WHERE id=2; UPDATE acct WHERE id=1;
-- both block forever -> the DB kills one as the victimAvoiding deadlocks
- Acquire locks in a consistent order everywhere - if every transaction locks by ascending id, the cycle above cannot form.
- Keep transactions short so locks are held for as little time as possible.
- Touch the fewest rows needed, and avoid user think-time inside an open transaction.
- Expect deadlocks and retry the victim transaction, rather than assuming they never happen.
Optimistic vs pessimistic locking
Pessimistic locking takes locks up front, assuming conflicts are likely. Optimistic locking assumes conflicts are rare: it does not lock, but checks at commit time whether the data changed - often via a version column - and retries if it did. Optimistic control scales better under low contention and pairs well with stateless services that do not hold long-lived transactions.
Locks and isolation levels
How aggressively a database locks is governed by its isolation level. Higher isolation prevents more anomalies but takes more or longer locks, reducing concurrency. Lower isolation allows more concurrency but permits phenomena like non-repeatable reads. Choosing an isolation level is really choosing where you want to sit on the correctness-versus-throughput curve.
Conclusion
Locking is how databases turn chaotic concurrent access into correct, serializable-looking results. Shared and exclusive modes decide who can read and write together, granularity decides how much gets locked, and deadlocks emerge from inconsistent acquisition order. Keep transactions short, acquire locks in a consistent order, choose the right isolation level, and be ready to retry - and concurrency stops being a source of mysterious stalls and becomes something you control.