What is it? #
A lock is PostgreSQL reserving something so that two transactions cannot interfere with each other.
The rule that surprises people coming from other databases: readers never block writers, and writers never block readers. Because of MVCC, a SELECT reads an older version of a row rather than waiting for an in-progress update. Locking only becomes visible when two transactions both want to write the same row, or when someone takes a heavy table-level lock.
SELECT ... FOR UPDATE is the tool for the read-then-write pattern from the previous lesson: it locks the rows you read so nobody else can change them until you finish.
A deadlock is two transactions each waiting for a lock the other holds. PostgreSQL detects this automatically and kills one of them.
Think of it like this #
A library where every book has an unlimited number of copies for reading, but only one editable master.
Anyone can read at any time, even while an edit is in progress — readers get the last published version and never wait. That is MVCC.
To edit, you must take the master copy. If someone else has it, you wait. That is a row lock.
A deadlock is two editors who each hold one book and each need the other's: neither can proceed, and neither will give up. The librarian notices, takes one book back and tells that person to start over.
Simple example #
Two support staff open the same order to change its status at the same moment.
Without locking, the second write silently overwrites the first — the "lost update" problem. With SELECT ... FOR UPDATE, the second transaction waits until the first finishes, then works from the updated value.
Code #
-- ---------- Row locks: SELECT ... FOR UPDATE ----------
BEGIN;
SELECT seats_left FROM flights WHERE id = 7 FOR UPDATE;
-- This row is now locked. Any OTHER transaction running the same statement
-- WAITS here until this transaction commits or rolls back.
UPDATE flights SET seats_left = seats_left - 1 WHERE id = 7;
COMMIT; -- lock released-- ---------- The four row-lock strengths ----------
SELECT ... FOR UPDATE; -- strongest. Blocks other FOR UPDATE, UPDATE, DELETE.
-- Use when you intend to modify the row.
SELECT ... FOR NO KEY UPDATE; -- like FOR UPDATE but allows concurrent
-- foreign-key references to this row.
SELECT ... FOR SHARE; -- several transactions may hold it at once,
-- but nobody can UPDATE or DELETE the row.
-- Use for "this must stay as it is while I work".
SELECT ... FOR KEY SHARE; -- weakest. Only blocks changes to the key columns.
-- This is what a foreign key check takes.-- ---------- Not waiting: NOWAIT and SKIP LOCKED ----------
-- Fail immediately instead of waiting:
SELECT * FROM flights WHERE id = 7 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "flights"
-- Skip rows somebody else has locked — the classic job-queue pattern:
BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED; -- each worker gets a DIFFERENT job, no waiting
UPDATE jobs SET status = 'running' WHERE id = :id;
COMMIT;
-- SKIP LOCKED is how you build a safe multi-worker queue in PostgreSQL
-- without any external queueing system.-- ---------- The lost update problem ----------
-- WRONG: read, compute in the application, write back
-- Session A Session B
BEGIN; BEGIN;
SELECT stock FROM products SELECT stock FROM products
WHERE id=1; -- 10 WHERE id=1; -- 10
-- app computes 10-1 = 9 -- app computes 10-1 = 9
UPDATE products SET stock=9 UPDATE products SET stock=9
WHERE id=1; WHERE id=1;
COMMIT; COMMIT;
-- Two items sold, stock went from 10 to 9. One sale was LOST.
-- RIGHT (option 1): arithmetic in the database, no gap at all
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
-- RIGHT (option 2): lock while you decide
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE; -- B now waits here
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;---------- Table locks: mostly taken automatically ----------
Weakest Strongest
ACCESS SHARE ... ROW EXCLUSIVE ... SHARE ... ACCESS EXCLUSIVE
ACCESS SHARE taken by SELECT. Conflicts only with ACCESS EXCLUSIVE.
ROW EXCLUSIVE taken by INSERT / UPDATE / DELETE. These do NOT conflict
with each other, which is why concurrent writes to
DIFFERENT rows work fine.
SHARE taken by CREATE INDEX (without CONCURRENTLY). Blocks writes.
ACCESS EXCLUSIVE taken by ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL,
REFRESH MATERIALIZED VIEW (non-concurrent).
BLOCKS EVERYTHING, INCLUDING PLAIN SELECTS.-- ---------- The lock queue trap ----------
-- !! This is one of the most common accidental production outages.
-- A long-running SELECT is holding ACCESS SHARE on "orders".
-- You run:
ALTER TABLE orders ADD COLUMN note text; -- needs ACCESS EXCLUSIVE, so it WAITS
-- Now EVERY new query on "orders" queues BEHIND your waiting ALTER,
-- even simple SELECTs that would not have conflicted with the original query.
-- The table appears completely frozen.
-- PREVENTION: never let a DDL statement wait indefinitely on a busy table.
BEGIN;
SET LOCAL lock_timeout = '3s'; -- give up after 3 seconds
ALTER TABLE orders ADD COLUMN note text;
COMMIT;
-- If it cannot get the lock quickly, it fails harmlessly instead of
-- blocking the entire table. Retry at a quieter moment.-- ---------- Deadlocks ----------
-- Session A Session B
BEGIN; BEGIN;
UPDATE accounts SET ... WHERE id=1; UPDATE accounts SET ... WHERE id=2;
-- holds lock on row 1 -- holds lock on row 2
UPDATE accounts SET ... WHERE id=2; UPDATE accounts SET ... WHERE id=1;
-- waits for B -- waits for A -> DEADLOCK
-- PostgreSQL detects it (after deadlock_timeout, default 1s) and kills one:
-- ERROR: deadlock detected
-- DETAIL: Process 123 waits for ShareLock on transaction 456...
-- HINT: See server log for query details.
-- PREVENTION: always lock rows in a CONSISTENT ORDER.
-- If every transaction touches accounts in ascending id order,
-- this deadlock becomes impossible.
UPDATE accounts SET ... WHERE id IN (1,2) ORDER BY id; -- or lock explicitly:
SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;-- ---------- Finding what is blocking what ----------
SELECT blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
left(blocked.query,50) AS blocked_query,
blocking.pid AS blocking_pid,
blocking.usename AS blocking_user,
left(blocking.query,50)AS blocking_query,
now() - blocked.query_start AS blocked_for
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;
-- pg_blocking_pids() is the shortcut worth remembering: given a blocked
-- process id, it returns the process ids that are blocking it.-- ---------- Resolving a block ----------
-- !! Check whose query it is before stopping it. Ask if it is not yours.
SELECT pg_cancel_backend(12345); -- polite: cancels the QUERY, keeps the connection
SELECT pg_terminate_backend(12345); -- forceful: drops the whole CONNECTION,
-- rolling back its open transaction
-- Prefer cancel. Use terminate only when cancel does not work.
-- Neither loses committed data.-- ---------- Useful timeouts (set per session, or in postgresql.conf) ----------
SET lock_timeout = '5s'; -- give up waiting for a lock
SET statement_timeout = '30s'; -- cap any single statement
SET idle_in_transaction_session_timeout = '60s';-- kill abandoned open transactions
SET deadlock_timeout = '1s'; -- how long before checking for deadlock
-- idle_in_transaction_session_timeout is especially worth setting in production:
-- it automatically cleans up the "forgotten open transaction" problem
-- that blocks VACUUM across the whole database.How it works #
Because of MVCC, a SELECT never waits for a writer and never makes a writer wait. It reads the row version that was committed when its snapshot was taken. This is why PostgreSQL handles read-heavy workloads well and why most applications rarely think about locks at all.
Row locks appear when two transactions want to write the same row. The second one waits until the first commits or rolls back. SELECT ... FOR UPDATE takes that lock during the read, which closes the gap between reading a value and acting on it — the lost-update problem.
The lock strengths matter mainly when foreign keys are involved. FOR UPDATE is strong enough to block another transaction merely referencing the row through a foreign key, which occasionally causes surprising contention. FOR NO KEY UPDATE exists for that case, and it is what a plain UPDATE of a non-key column takes.
SKIP LOCKED deserves particular attention. It returns only rows nobody else has locked, which makes a correct multi-worker job queue possible in plain SQL: each worker claims a different row, none of them wait, and no job is handed out twice. Many teams reach for an external queue before discovering PostgreSQL does this well.
Table locks are mostly taken for you. SELECT takes the weakest (ACCESS SHARE); INSERT, UPDATE and DELETE take ROW EXCLUSIVE, and crucially those do not conflict with each other — that is why many concurrent writers to different rows work fine. ALTER TABLE, TRUNCATE, DROP and VACUUM FULL take ACCESS EXCLUSIVE, which conflicts with everything including plain reads.
The lock queue is the most important operational detail in this lesson. PostgreSQL grants locks roughly in order of request. If a long SELECT holds ACCESS SHARE and your ALTER TABLE requests ACCESS EXCLUSIVE, the ALTER waits — and every subsequent query queues behind it, even harmless SELECTs that would not have conflicted with the original query at all. One ALTER TABLE behind one slow query can freeze an entire table. Setting lock_timeout before DDL turns a potential outage into a harmless failed statement you can retry.
A deadlock is a cycle of waiting. PostgreSQL checks for one after deadlock_timeout and aborts one transaction to break it. Deadlocks are a normal consequence of concurrency, not corruption. They become rare when transactions acquire locks in a consistent order, because a cycle then cannot form.
Real-world use #
Most application code never manages locks explicitly, and that is correct. Explicit locking is for the read-then-write pattern: check stock then reserve, read a balance then debit, claim a job from a queue.
When you do need it, prefer the simplest thing that removes the gap. A single UPDATE ... WHERE stock > 0 with a CHECK constraint needs no locking at all and cannot race. Reach for FOR UPDATE when the decision genuinely cannot be expressed in one statement.
For background job processing, FOR UPDATE SKIP LOCKED with LIMIT 1 is the standard pattern and scales to many workers safely.
Treat every schema change on a busy production table as a locking operation. Set lock_timeout, run migrations during quieter periods, and use CREATE INDEX CONCURRENTLY rather than plain CREATE INDEX. Also check for long-running queries before starting: a migration that would take milliseconds can still block a table for minutes if it has to wait behind a report.
Set idle_in_transaction_session_timeout in production. Application bugs that leave transactions open are common, and the consequences — held locks and blocked VACUUM across the whole database — are disproportionate. A timeout converts a silent, escalating problem into a visible error in the application logs.
When something appears frozen, the pg_blocking_pids() query above answers "what is blocking what" immediately. Work from the root blocker outwards rather than terminating processes at random, and prefer pg_cancel_backend over pg_terminate_backend.
Common mistakes #
- Running ALTER TABLE on a busy table without lock_timeout, so every later query queues behind it.
- Reading a value into the application, computing, and writing back — losing concurrent updates.
- Acquiring locks in inconsistent order across transactions, creating avoidable deadlocks.
- Treating a deadlock as corruption instead of a normal concurrency event that should be retried.
- Leaving idle_in_transaction_session_timeout unset, so an abandoned transaction blocks VACUUM indefinitely.
Practice #
Open two psql sessions. In session A, BEGIN and run SELECT ... FOR UPDATE on one row; in session B, try to update that same row and watch it wait. Commit in A and see B proceed. Then deliberately create a deadlock by updating two rows in opposite orders in each session, and read the error PostgreSQL produces. Finally, build a small jobs table and use FOR UPDATE SKIP LOCKED from two sessions to confirm each one claims a different job.