Transactions & Concurrency

-- Explicit transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback on error
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (42, 99.99);
  -- Something goes wrong...
ROLLBACK;  -- nothing was committed

-- Savepoints — partial rollback within a transaction
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (42, 99.99);
  SAVEPOINT before_items;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 999);
  -- Oops, product 999 doesn't exist
  ROLLBACK TO SAVEPOINT before_items;
  -- Order still exists, just no items
  INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
COMMIT;

-- Per-transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE id = 1;
  -- ... logic ...
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- If a serialization conflict is detected, PostgreSQL aborts with:
-- ERROR: could not serialize access due to concurrent update
-- Application must retry the transaction

-- Per-session default
SET default_transaction_isolation = 'repeatable read';

-- Check current level
SHOW transaction_isolation;

-- Rule: at least one doctor must be on-call at all times
-- Two doctors are on-call. Both try to go off-call simultaneously.

-- Transaction 1 (Dr. Alice):
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM doctors WHERE on_call = true;  -- returns 2
-- "There are 2 on-call, I can safely go off-call"
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;  -- succeeds

-- Transaction 2 (Dr. Bob) — runs concurrently:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM doctors WHERE on_call = true;  -- returns 2 (snapshot!)
-- "There are 2 on-call, I can safely go off-call"
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;  -- succeeds

-- Result: ZERO doctors on-call! Constraint violated.
-- Fix: use SERIALIZABLE isolation — one transaction would be aborted.

-- Pessimistic locking: lock the row before modifying
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is now locked — other transactions wait
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- NOWAIT: fail immediately if row is locked (don't wait)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row — handle in application

-- SKIP LOCKED: skip locked rows (queue worker pattern)
SELECT * FROM tasks
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Multiple workers can process different tasks concurrently
-- Each worker skips rows already being processed by others

-- Transaction 1:
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;  -- locks row 1
UPDATE accounts SET balance = balance + 10 WHERE id = 2;  -- waits for row 2

-- Transaction 2 (concurrent):
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;  -- locks row 2
UPDATE accounts SET balance = balance + 10 WHERE id = 1;  -- waits for row 1
-- DEADLOCK! PostgreSQL aborts one transaction.

-- Prevention: always lock rows in consistent order (e.g., by ID ascending)

-- Session-level advisory lock (held until session ends or explicit unlock)
SELECT pg_advisory_lock(12345);  -- blocks if another session holds it
-- ... do exclusive work ...
SELECT pg_advisory_unlock(12345);

-- Transaction-level advisory lock (released at COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('process-payments'));
-- ... exclusive payment processing ...
COMMIT;  -- lock automatically released

-- Try lock (non-blocking — returns true/false)
SELECT pg_try_advisory_lock(12345);  -- returns false if already held

-- Use case: prevent duplicate cron job execution
SELECT pg_try_advisory_lock(hashtext('daily-report-job'));
-- If false, another instance is already running — exit gracefully

-- Table with version column
CREATE TABLE products (
  id bigint PRIMARY KEY,
  name text NOT NULL,
  price numeric(10,2) NOT NULL,
  version integer NOT NULL DEFAULT 1
);

-- Application reads product
SELECT id, name, price, version FROM products WHERE id = 42;
-- Returns: version = 5

-- Application computes new price, then updates with version check
UPDATE products
SET price = 29.99, version = version + 1
WHERE id = 42 AND version = 5;

-- If another transaction modified the row, version != 5
-- UPDATE affects 0 rows — application detects this and retries

-- Alternative: use xmin (system column) as version
SELECT id, name, price, xmin FROM products WHERE id = 42;
-- xmin changes on every update — no need for explicit version column
UPDATE products SET price = 29.99
WHERE id = 42 AND xmin = '12345';