Locking & Deadlocks

Preview — 3 of 10 questions

What does SELECT ... FOR UPDATE do in PostgreSQL?

javascript

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- acquires exclusive row lock
-- Safe to update: no other transaction can change this row until COMMIT
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- releases the lock

AIt executes the SELECT as an UPDATE statement and returns the new row values

BIt acquires an exclusive row-level lock on the selected rows, preventing other transactions from modifying or locking them until the current transaction commits or rolls back

CIt creates an immutable snapshot of the rows for use in the current transaction without locking them

DIt marks the rows as read-only for the duration of the current session

What is the optimistic locking pattern using a version column?

javascript

-- Read: captures version = 5
SELECT id, balance, version FROM accounts WHERE id = 1;

-- Write: fails safely if version changed since the read
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;
-- Check affected rows: if 0, a conflict occurred — re-read and retry

AIssuing LOCK TABLE orders IN EXCLUSIVE MODE before every UPDATE statement

BWrapping every read-modify-write cycle in a SERIALIZABLE transaction

CUsing a updated_at TIMESTAMP column and retrying if the timestamp is more recent than expected

DAdding a version INT column; on UPDATE, including WHERE version = <read_version> in the clause and incrementing the version — if 0 rows are affected, a conflict was detected and the operation must be retried

What is the primary use case for SELECT ... FOR UPDATE SKIP LOCKED in PostgreSQL?

javascript

-- Worker claiming exactly one pending job atomically
BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- If a row was returned, mark it as in-progress
UPDATE jobs SET status = 'processing' WHERE id = $claimed_id;
COMMIT;

ASkipping rows that are locked by the current transaction to avoid self-deadlock

BPermanently skipping locked rows for the duration of the current session

CImplementing a high-throughput job queue where multiple workers concurrently claim and process tasks without blocking each other

DReading rows without acquiring any locks, bypassing isolation guarantees

Answer all 10 questions (7 more), see explanations for every answer, and track your score.