How PostgreSQL row locking works: SELECT FOR UPDATE and advisory locks

Locking in PostgreSQL

Explicit row locking is necessary when you read a row with intent to update it — preventing another transaction from modifying it between your read and write.

SELECT FOR UPDATE

BEGIN;
-- Lock the row for exclusive update
SELECT id, balance
FROM   accounts
WHERE  id = 42
FOR    UPDATE;

-- Now safely update without race condition
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

SKIP LOCKED — for job queues

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

SKIP LOCKED skips rows already locked by another transaction, enabling multiple workers to pull from a queue concurrently without blocking.

Advisory locks

SELECT pg_advisory_lock(12345);   -- application-level lock
-- do work
SELECT pg_advisory_unlock(12345);

Advisory locks are application-level named locks not tied to any row or table. Use them for distributed mutual exclusion (e.g. prevent two app servers from running the same cron job simultaneously). Set lock_timeout = '5s' to avoid indefinite blocking.