Database Locks Explained: Shared, Exclusive, Update, Key-Range and Granularity
What each lock mode allows, why update locks exist, how key-range locks stop phantoms, and when row, page or table locks cost you concurrency.
On this page
Most developers meet locks as a symptom: a query that hangs, a deadlock error in the logs, a table that “locks up” during a migration. Knowing the handful of lock modes turns each of those from mystery into a diagnosis.
The problem locks solve
Two transactions touch the same account balance at the same moment. Without coordination, one overwrites the other and money appears or vanishes. A lock is the database saying “this resource is mine until I finish”. Every mode is a different promise about who else may touch it meanwhile.
The core modes and what they allow
| Mode | Holder can | Others can |
|---|---|---|
| Shared (S) | Read | Also take shared locks and read. Cannot write. |
| Exclusive (X) | Read and write | Nothing: no shared, no exclusive |
| Update (U) | Read, with intent to write | Take shared locks, but no other update or exclusive |
| Key-range | Read a range of index keys | Cannot insert into that range |
| Schema | Protect a table’s structure | Cannot change or, for some modes, use the object |
The compatibility rule underneath is simple: readers coexist with readers, writers need to be alone.
Why the update lock exists
Here is the deadlock that motivates it. Two transactions each run “read the row, then update it”.
- Both read
Transaction A and transaction B each take a shared lock on the same row. That is allowed, because shared locks are compatible.
- Both try to write
A asks to upgrade to exclusive. It must wait for B’s shared lock to go away. B asks to upgrade too and waits for A’s.
- Nobody moves
Each holds the lock the other needs. The engine detects the cycle and kills one transaction.
An update lock breaks the cycle. Whoever reads with intent to update takes a U lock instead. Only one transaction can hold it, and it can coexist with plain readers. When it is time to write, the U lock converts to exclusive as soon as the readers drain. The second transaction waits at the read step, which is harmless, rather than deadlocking at the write step.
In PostgreSQL the same intent is expressed explicitly with SELECT ... FOR UPDATE, which takes a row-level lock so that the later write cannot collide.
BEGIN;
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE; -- locks the row
UPDATE accounts SET balance = balance - 50 WHERE id = 7;
COMMIT;Make every transaction touch shared rows in the same order, and lock early with intent when you know you will write. Most production deadlocks are two code paths taking the same two rows in opposite orders.
Key-range locks: guarding the gaps
A row lock protects rows that exist. A phantom read happens when you query “all orders between 100 and 200”, get five rows, then another transaction inserts a sixth into that range and your second query returns six. No existing row changed, so no row lock was violated.
A key-range lock covers the range of keys in an index, including the empty gaps between them. Insert into the range and you wait. This is how serializable isolation prevents phantoms without locking the whole table, and it is why range locks need an index to be useful: with no index, the engine has nothing to lock the gap in, and falls back to something coarser.
Granularity: row, page, table
Engines escalate automatically: when a transaction holds too many fine-grained locks, the engine trades them for one coarser lock to save memory. That is usually invisible, until the day a batch job escalates to a table lock and every other session queues behind it.
Reading a lock problem
| Symptom | Likely cause | First move |
|---|---|---|
| One query hangs for minutes | Blocked behind a long transaction holding X | Find the blocker, shorten or kill the transaction |
| Sporadic deadlock errors | Opposite lock order, or read-then-write upgrades | Standardise order, lock with intent early |
| Everything freezes during a migration | Schema or table lock requested while readers hold shared locks | Run during low traffic, use online schema tools |
| New rows appear mid-transaction | Phantom reads under a weak isolation level | Raise isolation or use range locking |
Do
Keep transactions short, touch rows in a consistent order, lock with intent when you will write, and index the columns you filter on so locks stay narrow.
Don't
Open a transaction, call a slow external API inside it, then write. Every lock you hold stays held for the whole network round trip.
Takeaway
Five ideas cover almost everything: shared for readers, exclusive for writers, update to avoid the upgrade deadlock, key-range to stop phantoms, and granularity as a dial between concurrency and overhead. When something hangs, ask what lock mode is held, by whom, and how long the transaction has been open. The answer is nearly always one of these.
Frequently asked questions
What is the difference between a shared and an exclusive lock?
A shared lock lets many transactions read a resource at once but blocks writers. An exclusive lock lets one transaction read and modify it and blocks every other lock, shared or exclusive. Readers coexist with readers, but a writer needs the resource to itself.
Why do update locks exist?
Two transactions that both take a shared lock to read a row, then both try to upgrade to exclusive to write it, wait on each other forever. An update lock says I will probably write: only one transaction can hold it, so the upgrade can never deadlock against another reader-turned-writer.
What is a key-range lock?
A lock on a range of index keys, not just the rows that exist. It prevents other transactions from inserting a new row into a range you already queried, which is how serializable isolation blocks phantom reads without locking the whole table.
Are row locks always better than table locks?
No. Row locks maximise concurrency but cost memory and bookkeeping per lock. A statement touching most of a table is cheaper with one table lock, which is why engines escalate. Choose granularity by how much of the data a transaction really touches.
/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.