Staff Prep 06: Transactions & Concurrency — Isolation, Deadlocks & Race Conditions

-- Set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED;     -- Postgres default
BEGIN ISOLATION LEVEL REPEATABLE READ;    -- Prevents non-repeatable reads and phantoms
BEGIN ISOLATION LEVEL SERIALIZABLE;       -- Prevents write skew; slowest

-- Or at session level
SET default_transaction_isolation = 'repeatable read';

-- Doctor A and Doctor B are both on call
-- Both want to go off-call simultaneously

-- Transaction A (Doctor A goes off-call):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;  -- returns 2
-- Thinks: "2 doctors on call, safe to remove myself"
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;

-- Transaction B (Doctor B goes off-call), concurrent with A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;  -- also returns 2 (snapshot)
-- Thinks: "2 doctors on call, safe to remove myself"
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;

-- Result: 0 doctors on call. Both transactions "succeeded". Invariant violated.

-- Fix: use SERIALIZABLE isolation (detects the conflict, one transaction will retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true;
-- Postgres tracks the read set; if another serializable transaction modifies it,
-- one will get: ERROR: could not serialize access due to read/write dependencies

-- Race condition without locking
BEGIN;
SELECT balance FROM accounts WHERE id = 42;  -- reads 100
-- Another transaction runs: UPDATE accounts SET balance = 50 WHERE id = 42
UPDATE accounts SET balance = balance - 30 WHERE id = 42;  -- sets to 70, not 20!
COMMIT;

-- Safe: lock the row at read time
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;  -- acquires lock, reads 100
-- Other transaction's UPDATE will BLOCK here until we COMMIT
UPDATE accounts SET balance = balance - 30 WHERE id = 42;  -- correctly sets to 70
COMMIT;

-- FOR UPDATE SKIP LOCKED: skip rows already locked by other transactions
-- Useful for job queues where workers pick up work without blocking each other
SELECT * FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Each worker gets a different job row, no lock contention

-- Deadlock scenario
-- Transaction A locks account 1, then tries to lock account 2
-- Transaction B locks account 2, then tries to lock account 1

-- Transaction A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- ... some work ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for B to release lock 2

-- Transaction B (concurrent):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- locks row 2
-- ... some work ...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- waits for A to release lock 1
-- DEADLOCK DETECTED -- Postgres kills one of these transactions

-- Prevention: always acquire locks in a consistent order
-- If all transactions lock accounts in ascending id order, deadlocks are impossible
BEGIN;
-- Always lock lower id first
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);

-- Session-level advisory lock (held until released or session ends)
SELECT pg_try_advisory_lock(12345);  -- returns true if acquired, false if already held

-- Safe pattern: try to acquire, do work, release
DO $$
BEGIN
    IF pg_try_advisory_lock(42) THEN
        -- Do exclusive work here
        RAISE NOTICE 'Lock acquired, running exclusive job';
        PERFORM pg_advisory_unlock(42);
    ELSE
        RAISE NOTICE 'Another worker has the lock, skipping';
    END IF;
END;
$$;

-- Transaction-level advisory lock (released automatically at COMMIT/ROLLBACK)
SELECT pg_try_advisory_xact_lock(42);
-- Safer: no need to remember to release

-- Optimistic concurrency with a version column
CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    content     TEXT,
    version     INT NOT NULL DEFAULT 1,
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Read the document and its version
SELECT id, content, version FROM documents WHERE id = 5;
-- User edits content for 30 seconds...

-- Write: only update if version has not changed
UPDATE documents
SET content = 'new content', version = version + 1
WHERE id = 5 AND version = 1;  -- optimistic check

-- If 0 rows updated: someone else modified it, retry or show conflict to user
GET DIAGNOSTICS rows_updated = ROW_COUNT;
IF rows_updated = 0 THEN
    RAISE EXCEPTION 'Conflict: document was modified by another user';
END IF;