Transactions & Isolation

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If anything went wrong, roll back the whole thing:
-- ROLLBACK;

COMMIT;

-- Set the level for the current transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- ... your work ...
COMMIT;

-- Or set the default for the session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;

-- Lock the row so nobody else can update it until we COMMIT
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- Now we can safely compute and write
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

-- Variants:
--   FOR UPDATE          - exclusive, blocks readers asking FOR UPDATE
--   FOR NO KEY UPDATE   - lighter, allows FK references
--   FOR SHARE           - shared, blocks FOR UPDATE
--   SKIP LOCKED         - useful for job queues (skip rows others are processing)
--   NOWAIT              - fail immediately instead of waiting

BEGIN;

INSERT INTO orders(customer_id, total) VALUES (42, 100);

SAVEPOINT before_items;

INSERT INTO order_items(order_id, sku) VALUES (currval('orders_id_seq'), 'BAD-SKU');
-- Oops, FK violation. Roll back just the items, keep the order.
ROLLBACK TO SAVEPOINT before_items;

INSERT INTO order_items(order_id, sku) VALUES (currval('orders_id_seq'), 'GOOD-SKU');

COMMIT;

-- Autocommit (default): each statement is standalone
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- auto-committed
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- if this fails, the debit is already permanent

-- Explicit transaction: both succeed or both fail
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Disable autocommit for the session (psql behavior)
SET autocommit = off;

-- Phase 1: Prepare
-- Coordinator asks all participants: "Can you commit?"
-- Each participant writes a prepare record to its own WAL and replies YES/NO.

PREPARE TRANSACTION 'txn-uuid-123';

-- Phase 2: Commit or Abort
-- If all participants said YES, coordinator sends COMMIT.
-- If any said NO, coordinator sends ROLLBACK.

COMMIT PREPARED 'txn-uuid-123';
-- or: ROLLBACK PREPARED 'txn-uuid-123';

-- Session-level: held until explicitly released or session ends
SELECT pg_advisory_lock(42);        -- blocks until acquired
SELECT pg_advisory_unlock(42);      -- release it

-- Try-lock: returns true/false immediately instead of blocking
SELECT pg_try_advisory_lock(42);

-- Transaction-level: auto-released at COMMIT/ROLLBACK
SELECT pg_advisory_xact_lock(42);

-- Common pattern: lock on a hash of a business key
SELECT pg_advisory_xact_lock(hashtextext('report:nightly'));

-- Use SKIP LOCKED + advisory locks for robust job queues
UPDATE jobs
SET worker_pid = pg_backend_pid(), started_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;

BEGIN TRANSACTION READ ONLY ISOLATION LEVEL REPEATABLE READ;
-- Run complex reporting queries here with a stable, consistent snapshot
COMMIT;

-- Postgres: set default for a long-running analytics connection
SET default_transaction_read_only = on;

-- Postgres: find long-running transactions
SELECT pid, usename, application_name, state, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

-- Postgres: find idle-in-transaction connections (often worse than active!)
SELECT pid, usename, now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_duration DESC;

-- Postgres: what is blocking what?
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- WRONG: read, check, then update (lost update / oversell)
SELECT stock FROM products WHERE id = 99;  -- reads 100
-- app checks stock > 0
UPDATE products SET stock = stock - 1 WHERE id = 99;  -- race condition!

-- CORRECT: atomic decrement with a condition
UPDATE products
SET stock = stock - 1
WHERE id = 99 AND stock > 0
RETURNING stock;
-- If 0 rows returned, the item is sold out. No race, no oversell.

-- CORRECT (with reservation): pessimistic lock during checkout flow
BEGIN;

-- Reserve the item for this user for 15 minutes
UPDATE products
SET reserved = reserved + 1, stock = stock - 1
WHERE id = 99 AND stock > 0
RETURNING *;

INSERT INTO reservations (product_id, user_id, expires_at)
VALUES (99, $user_id, now() + interval '15 minutes');

COMMIT;

-- A cron job releases expired reservations back to stock
UPDATE products
SET stock = stock + r.amount, reserved = reserved - r.amount
FROM (
  SELECT product_id, count(*) AS amount
  FROM reservations
  WHERE expires_at < now()
  GROUP BY product_id
) r
WHERE products.id = r.product_id;

import time
import random

def run_with_retry(conn, operation, max_attempts=5):
    for attempt in range(1, max_attempts + 1):
        try:
            with conn.begin():          class=class="str">"com"># BEGIN / COMMIT block
                return operation(conn)   class=class="str">"com"># your business logic
        except Exception as e:
            if class="str">"40001" not in str(e) and class="str">"40P01" not in str(e):
                raise                  class=class="str">"com"># not a retryable error
            if attempt == max_attempts:
                raise
            class=class="str">"com"># Exponential backoff + jitter
            sleep = (2 ** attempt) * 0.01 + random.uniform(0, 0.05)
            time.sleep(sleep)

class=class="str">"com"># Usage
run_with_retry(pool, lambda c: c.execute(
    class="str">"UPDATE inventory SET count = count - 1 WHERE sku = %s", (sku,)
))

-- Write skew example: doctor on-call constraint (at least one must be on call)
-- Transaction A and B both start under Snapshot Isolation.
-- T1 reads: Alice=true, Bob=true
-- T2 reads: Alice=true, Bob=true
-- T1: set Alice=false (valid because Bob is still true in T1's snapshot)
-- T2: set Bob=false   (valid because Alice is still true in T2's snapshot)
-- Both COMMIT. Result: nobody is on call. Invariant violated.

-- Fix 1: use SERIALIZABLE (detects the dependency cycle and aborts one)
-- Fix 2: restructure with an explicit lock on a guard row
SELECT * FROM oncall_guard WHERE hospital_id = 1 FOR UPDATE;
-- Now only one transaction at a time can modify on-call status.