Locking and Concurrency in PostgreSQL

Locks are how PostgreSQL keeps concurrent transactions from stepping on each other's changes to the same row.

Row-Level Locking: SELECT ... FOR UPDATE

Locks the selected rows so no other transaction can modify them until yours commits or rolls back — essential when you read a value, then write back a change based on it (like decrementing stock).

BEGIN;

SELECT quantity FROM inventory WHERE product_id = 1 FOR UPDATE;
-- other transactions trying to UPDATE or lock this row now WAIT

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;

COMMIT;
-- the lock is released; waiting transactions can now proceed

Without FOR UPDATE, two concurrent transactions could both read the same starting quantity, both subtract one, and the final result would be wrong — a classic lost update.

FOR UPDATE SKIP LOCKED

Instead of waiting for a locked row, skip it and grab a different one — the standard pattern for building a job queue where multiple workers pull tasks concurrently without blocking each other.

SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Deadlocks

A deadlock happens when two transactions each hold a lock the other one needs:

• Transaction A locks row 1, then wants row 2.
• Transaction B locks row 2, then wants row 1.
• Neither can proceed.

PostgreSQL automatically detects this situation and terminates one of the two transactions with a deadlock detected error — your application should catch this and retry.

Prevention tip: always lock rows in the same order across your codebase (e.g. always by ascending id) to make deadlocks far less likely in the first place.

Inspecting Current Locks

SELECT * FROM pg_locks;

SELECT pid, query, state FROM pg_stat_activity WHERE state = 'active';

Useful when a query appears to hang — it's often blocked waiting on a lock held by another session.

Common Errors & Troubleshooting

• A query appears to "hang" with no error — it's very likely waiting on a lock held by another session; check pg_stat_activity and pg_locks (shown above) to find and, if necessary, terminate the blocking session with SELECT pg_terminate_backend(pid);.
• ERROR: deadlock detected — PostgreSQL already resolved it by canceling one transaction — catch this specific error in your application and simply retry that transaction.
• Using FOR UPDATE without SKIP LOCKED in a multi-worker queue — every worker ends up waiting in line for the same locked rows instead of picking up different ones; add SKIP LOCKED so workers grab whatever's free instead of queueing behind each other.

Key Takeaways

• SELECT ... FOR UPDATE locks rows against concurrent modification, protecting classic read-then-write logic from lost updates.
• FOR UPDATE SKIP LOCKED is the standard building block for a concurrency-safe job/task queue.
• PostgreSQL detects deadlocks automatically and cancels one side — design your app to retry on that error.