row-level locks: SELECT FOR UPDATE

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- locks this row until COMMIT/ROLLBACK
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
FOR UPDATE locks the selected rows against concurrent modification (and against other FOR UPDATE/FOR NO KEY UPDATE locks) until the transaction ends. Without it, a classic race is possible: two transactions both read balance = 100, both compute 100 - 30, both write 70 — one withdrawal is silently lost. FOR UPDATE forces the second transaction to wait for the first to commit, then see the updated value.
-- FOR UPDATE SKIP LOCKED: a job queue's core primitive
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- multiple workers can run this concurrently, and each gets a DIFFERENT job — locked rows are simply skipped, not waited on

table-level locks

Most DDL (ALTER TABLE, etc.) takes a table-level lock for at least part of its duration — how strong a lock, and for how long, is the whole subject of zero-downtime migration practice. LOCK TABLE exists for explicit manual control but is rarely needed in application code; it's mostly a tool for migrations and maintenance scripts.

deadlocks

A deadlock happens when transaction A holds a lock transaction B is waiting for, while B holds a lock A is waiting for — neither can proceed. PostgreSQL detects this automatically (checking periodically, roughly every second by default) and resolves it by aborting one of the two transactions with a deadlock_detected error, letting the other proceed.
-- classic deadlock shape: two transactions lock the same two rows in opposite order
-- Transaction A:                    Transaction B:
-- UPDATE accounts SET ... WHERE id=1;   UPDATE accounts SET ... WHERE id=2;
-- UPDATE accounts SET ... WHERE id=2;   UPDATE accounts SET ... WHERE id=1;
-- A waits for B's lock on id=2, B waits for A's lock on id=1: deadlock
The practical fix is almost always: always acquire locks (including implicit ones from UPDATE/SELECT FOR UPDATE) on multiple rows in a consistent order across every code path — e.g., always by ascending id. Application code should also be written to catch a deadlock error and retry the transaction; it's an expected, recoverable event under real concurrency, not a bug to eliminate entirely.

advisory locks

SELECT pg_advisory_lock(12345);     -- blocks until acquired
-- ... do work that needs app-level mutual exclusion, e.g. "only one instance runs this cron job" ...
SELECT pg_advisory_unlock(12345);

SELECT pg_try_advisory_lock(12345); -- non-blocking, returns true/false immediately
Advisory locks are application-defined — the integer key means whatever you decide it means, and PostgreSQL doesn't tie it to any specific row or table. Useful for coordinating something that isn't really a row-level concern at all, like ensuring only one instance of a scheduled job runs at a time across multiple app servers.

where to go from here

Transactions & Isolation Levels — the MVCC model locking operates alongside.
Migrations & Schema Evolution — the locks DDL statements take.