Row-Level Locks
While MVCC eliminates the need for locks between readers and writers, PostgreSQL still needs locks to serialize concurrent writers targeting the same row. The four row-level lock modes, from weakest to strongest, are FOR UPDATE (locks the row against other writers and against other FOR UPDATE/FOR NO KEY UPDATE lockers), FOR NO KEY UPDATE (used automatically by plain UPDATEs that don't touch a key column, allowing it to coexist with FOR KEY SHARE), FOR SHARE, and FOR KEY SHARE (the weakest, used for foreign key checks). These are acquired implicitly by UPDATE/DELETE or explicitly via SELECT ... FOR UPDATE, and a second transaction trying to acquire a conflicting lock on the same row simply waits until the first transaction commits or rolls back.
Cricket analogy: SELECT FOR UPDATE is like a batter 'calling' for a run before committing to it — once called, the non-striker must wait and coordinate rather than run independently, preventing a mix-up (run-out) from two players acting on stale information simultaneously.
Table-Level Locks and Lock Queuing
PostgreSQL also has eight table-level lock modes ranging from ACCESS SHARE (acquired by plain SELECT) up to ACCESS EXCLUSIVE (acquired by DROP TABLE, TRUNCATE, and most forms of ALTER TABLE), with a well-defined conflict matrix determining which modes can coexist. Locks are granted strictly in request order per lock queue: if transaction A holds a ROW EXCLUSIVE lock and transaction B is waiting for an ACCESS EXCLUSIVE lock (say, for an ALTER TABLE), then transaction C's later request for even a harmless ACCESS SHARE lock queues behind B rather than jumping ahead — meaning one blocked DDL statement can cascade into blocking an entire table's traffic.
Cricket analogy: ACCESS EXCLUSIVE is like a full pitch inspection halting all play — no batting, bowling, or fielding drills can proceed until the groundskeeper's inspection (the exclusive lock holder) finishes, even for teams that just wanted light warm-up.
-- Explicit row lock for a read-modify-write pattern
BEGIN;
SELECT quantity FROM inventory WHERE sku = 'SKU-123' FOR UPDATE;
-- application logic decides new quantity
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'SKU-123';
COMMIT;
-- Inspect current lock waits
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;
-- ALTER TABLE takes ACCESS EXCLUSIVE by default; use CONCURRENTLY-friendly
-- alternatives where possible to avoid blocking all readers/writers
ALTER TABLE inventory ADD COLUMN warehouse_id integer; -- brief ACCESS EXCLUSIVERunning a long ALTER TABLE on a busy production table can effectively stall the whole application: the ALTER waits to acquire ACCESS EXCLUSIVE behind existing readers, and every new query issued afterward queues behind the ALTER, even simple SELECTs. Schedule blocking DDL during low-traffic windows or use tools that perform the change in small, lock-friendly steps.
SELECT ... FOR UPDATE NOWAIT and SELECT ... FOR UPDATE SKIP LOCKED let an application fail fast or skip already-locked rows instead of waiting indefinitely — SKIP LOCKED is a common pattern for building a job queue table safely.
- Row-level locks (FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE) serialize writers to the same row without blocking MVCC readers.
- Table-level locks range from ACCESS SHARE (SELECT) to ACCESS EXCLUSIVE (DROP/TRUNCATE/most ALTER TABLE).
- Lock requests queue in strict order; a waiting exclusive lock can block subsequently-requested lighter locks from jumping the queue.
- pg_locks shows current lock state; filtering on granted = false surfaces waiters.
- SKIP LOCKED and NOWAIT give applications non-blocking alternatives to indefinite waiting.
- Long-running DDL on hot tables can cascade into stalling all traffic due to queue ordering.
- Explicit locking with SELECT ... FOR UPDATE is the standard pattern for safe read-modify-write sequences.
Practice what you learned
1. Which row lock mode is acquired by a plain UPDATE that doesn't modify a key column?
2. Which table-level lock mode is acquired by DROP TABLE and most forms of ALTER TABLE?
3. How are conflicting lock requests on the same object generally granted in PostgreSQL?
4. What does SELECT ... FOR UPDATE SKIP LOCKED do?
5. Which system view is used to see which sessions are waiting on a lock?
Was this page helpful?
You May Also Like
Deadlocks and How to Avoid Them
How PostgreSQL detects circular lock-wait dependencies, what it does when it finds one, and practical patterns to prevent deadlocks in application code.
MVCC Explained
How PostgreSQL's Multi-Version Concurrency Control lets readers and writers avoid blocking each other by keeping multiple row versions and using visibility rules.
ACID and Isolation Levels
How PostgreSQL guarantees Atomicity, Consistency, Isolation, and Durability, and how the four SQL isolation levels trade off correctness against concurrency.