Session A holds a long-running transaction with a SELECT on orders. Session B then runs ALTER TABLE orders ADD COLUMN note TEXT;, which needs ACCESS EXCLUSIVE and queues, waiting for A to finish. Session C then tries a simple SELECT * FROM orders;. What happens to C?
AC's SELECT runs immediately and normally — only B is affected, since C never requested a conflicting lock itself
BC's SELECT fails immediately with an error, since two different lock levels can't coexist in a queue
CPostgreSQL automatically reorders the queue to let C's compatible request through first
DC's SELECT also has to wait — even though a plain SELECT only needs ACCESS SHARE (which doesn't conflict with A's lock), PostgreSQL's lock queue is generally first-in-first-out: once B's ACCESS EXCLUSIVE request is queued waiting for A, C's later ACCESS SHARE request queues up behind B rather than jumping ahead of it, even though C's request wouldn't have conflicted with A's lock on its own
Transaction A does UPDATE accounts SET balance = balance - 10 WHERE id = 1; then, moments later, UPDATE accounts SET balance = balance + 10 WHERE id = 2;. Transaction B, running concurrently, does the reverse: UPDATE ... WHERE id = 2; first, then UPDATE ... WHERE id = 1;. What happens if both reach their second statement at the same time?
ANothing goes wrong — PostgreSQL always resolves concurrent updates without any error
BBoth statements succeed, but the final balances are wrong due to a lost update
CA deadlock: A is holding row 1's lock and waiting for row 2 (held by B); B is holding row 2's lock and waiting for row 1 (held by A) — neither can proceed, so PostgreSQL's deadlock detector picks one transaction as the victim, aborts it with a deadlock error, and lets the other proceed. The standard fix is ensuring every transaction that touches both rows always acquires them in the same order (e.g. always lower id first), which makes this specific circular-wait pattern impossible
DPostgreSQL prevents this scenario entirely by automatically sorting all UPDATE targets by primary key before execution
A session appears frozen, waiting on a lock. Which query helps identify exactly which other session(s) are responsible for blocking it?
ASELECT * FROM pg_stat_activity WHERE state = 'active'; — this alone is sufficient to see who's blocking whom
Bpg_locks alone, with no join to any other view, immediately shows which specific query is blocking another
CThere is no built-in way to determine this; it requires an external monitoring tool
DSELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE pid = ANY(pg_blocking_pids(<stuck_pid>)); — more directly, SELECT pg_blocking_pids(<stuck_pid>); returns an array of the process ids currently blocking that specific session, which can then be cross-referenced against pg_stat_activity to see what those blocking sessions are actually running