Locking and concurrency control

MVCC lets readers skip locks. Writers still need them. Two transactions must not change the same row at the same time, and a lock is how the database makes the second one wait.

Row locks and FOR UPDATE

UPDATE and DELETE lock the rows they change until the transaction ends. A second writer to the same row waits.

SELECT ... FOR UPDATE takes the same lock on rows you only read. That lets you read, decide and write without anyone changing the rows in between. FOR SHARE blocks writers but lets other readers share the lock.

Skipping locked rows: a job queue

SKIP LOCKED changes what happens when a row is already locked. Instead of waiting, the query moves on to the next row. This is the standard way to build a job queue on Postgres. Each worker claims a different job.

-- Claim one job. Concurrent workers each get a different row.
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- ... process the job ...
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

Two workers run this at once. The first locks the oldest queued job. The second skips that row, because it is locked, and claims the next one. Neither waits, and neither processes the same job.

Deadlocks

A deadlock happens when T1 locks row A and waits for row B, while T2 locks row B and waits for row A. Neither can move. Postgres checks for this cycle after deadlock_timeout, which is 1 second by default. It then aborts one transaction with deadlock detected.

The usual fix is to lock rows in a consistent order, such as ascending ID. If both transactions lock A before B, one simply waits for the other, and no cycle forms.

Optimistic concurrency with a version column

Holding a lock while a user thinks about an edit form is a bad idea. The lock would stay for minutes. Instead, add a version column and make the write conditional:

-- Read: SELECT id, title, body, version FROM docs WHERE id = 42;  -- version = 7
UPDATE docs
SET body = $1, version = version + 1
WHERE id = 42 AND version = 7;
-- 1 row updated: saved. 0 rows: someone else saved first, so reload and retry.

No lock is held between the read and the write. If another save happened in between, the version has moved on and the WHERE matches nothing. The same idea appears in HTTP as ETag and If-Match headers.

Advisory locks

pg_advisory_xact_lock(key) locks an arbitrary number, not a row. Use it when there is no single row to lock, such as "only one process may run the billing job for account 42":

SELECT pg_advisory_xact_lock(hashtext('billing:42'));

The lock is released at commit or rollback. Be aware that hashtext returns a 32-bit value, so two different names can map to the same lock.

Common mistakes

• Locking rows in random order. Two transactions that lock the same rows in different orders can deadlock.
• Holding a row lock across user think time. Use a version column instead.
• Using SKIP LOCKED for a report. It silently skips locked rows, so the result can be incomplete.
• Assuming advisory locks are unique per name. Hash collisions can make unrelated work wait for each other.

Check yourself

1. Two workers run FOR UPDATE SKIP LOCKED on the same queue at the same moment. What does each get? Two different unlocked jobs. A locked row is skipped, so neither worker waits for the other.
2. An edit form saved version 7, but the row is now version 8. What does the UPDATE do? It matches zero rows. The app reloads and retries, so one save does not silently overwrite another.

Key takeaways

• Writers still need locks, even with MVCC. A row lock lasts until commit or rollback.
• FOR UPDATE lets you read, decide and write safely. SKIP LOCKED turns it into a queue.
• Lock rows in a consistent order to avoid deadlocks. Postgres detects them after one second by default.
• For human-speed edits, use a version column instead of holding a lock.

Further learning

The PostgreSQL manual's transaction isolation chapter explains which lock modes each isolation level uses.

PostgreSQL docs: Transaction Isolation13.2. Transaction Isolation # 13.2.1. Read Committed Isolation Level 13.2.2. Repeatable Read Isolation Level 13.2.3. Serializable Isolation Level The SQL standard …PostgreSQL Documentation

Next, read concurrency patterns for the code that makes most of these bugs impossible.