Transactions / Isolation

-- 1) Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If anything fails, ROLLBACK undoes everything.

-- 2) Isolation levels
-- READ COMMITTED   (default) — each statement sees a fresh snapshot
-- REPEATABLE READ              — entire tx sees the snapshot from its first read
-- SERIALIZABLE                 — full serial schedule; may throw serialization_failure

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

-- READ ONLY — hints the planner, may use a snapshot more efficiently
BEGIN READ ONLY;
SELECT count(*) FROM logs WHERE created_at >= now() - interval '1 hour';
COMMIT;

-- 3) Savepoints — partial rollback inside a transaction
BEGIN;
INSERT INTO users (email) VALUES ('a@x.com') RETURNING id;

SAVEPOINT sp_after_user;
INSERT INTO posts (user_id, title) VALUES (1, 'hi');
INSERT INTO posts (user_id, title) VALUES (1, 'oops');

ROLLBACK TO SAVEPOINT sp_after_user;    -- undo the two posts, keep the user
COMMIT;

-- 4) SELECT FOR UPDATE — explicit row locks
BEGIN;
SELECT * FROM orders WHERE id = 100 FOR UPDATE;
-- Other tx that try to UPDATE / DELETE this row wait until we COMMIT.
UPDATE orders SET status = 'paid' WHERE id = 100;
COMMIT;

-- 5) FOR UPDATE SKIP LOCKED — concurrent work queues
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Process the job …
UPDATE jobs SET status = 'done' WHERE id = ?;
COMMIT;

-- 6) NOWAIT — fail fast if the row is locked
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- Throws: ERROR:  could not obtain lock on row …

-- 7) FOR SHARE — read-lock; blocks writers, allows other readers
SELECT balance FROM accounts WHERE id = 1 FOR SHARE;

-- 8) Deadlock detection + retry
-- Postgres detects circular waits and aborts one tx:
--   ERROR:  deadlock detected
--   HINT:   See server log for query details.
-- App must retry with backoff.

-- 9) Serializable + retry pattern
-- Catch serialization_failure (40001) and retry from the top of the tx.
-- In Node:
async function withRetry(fn, attempts = 3) {
    for (let i = 0; i < attempts; i++) {
        try {
            return await fn();
        } catch (e) {
            if (e.code === '40001' && i < attempts - 1) continue;
            throw e;
        }
    }
}

-- 10) Advisory locks — application-level locks (no row involved)
SELECT pg_advisory_lock(42);       -- session lock
-- ... do work that should only run one-at-a-time across the cluster ...
SELECT pg_advisory_unlock(42);

SELECT pg_try_advisory_lock(42);   -- non-blocking; returns true/false
SELECT pg_advisory_xact_lock(42);  -- released at COMMIT/ROLLBACK automatically

-- 11) Transaction-control via SQL inside scripts
BEGIN;
-- some DDL + DML
DO $$
BEGIN
    IF (SELECT count(*) FROM users) > 1000 THEN
        RAISE NOTICE 'large users table — skip expensive op';
    ELSE
        PERFORM update_user_stats();
    END IF;
END $$;
COMMIT;

-- 12) WAL + durability
-- COMMIT flushes the write-ahead log (WAL) by default — durable on disk.
-- Trade durability for speed:
SET LOCAL synchronous_commit = off;   -- commits return faster, but may lose recent on crash

-- 13) Lock monitoring
SELECT pid, mode, locktype, relation::regclass, granted
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE NOT granted;

-- Find blocking sessions
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocker.pid AS blocker_pid,
       blocker.query AS blocker_query
FROM   pg_stat_activity blocked
JOIN   pg_stat_activity blocker
  ON   blocked.wait_event_type = 'Lock'
 WHERE blocked.wait_event = 'transactionid';

-- 14) DDL inside transactions
-- Postgres supports transactional DDL — CREATE/ALTER/DROP can be rolled back.
BEGIN;
CREATE TABLE staging AS SELECT * FROM users WHERE created_at > now() - interval '1 day';
-- ...
ROLLBACK;     -- staging never existed

-- 15) Best practices
--   • Keep transactions SHORT — long transactions hold locks + bloat WAL
--   • Don't do network I/O inside a tx (no API calls, no sleeps)
--   • Use SAVEPOINTs sparingly — they add overhead
--   • Always handle deadlocks / serialization_failure with retry
--   • For job queues: FOR UPDATE SKIP LOCKED is the idiomatic pattern
--   • Set statement_timeout to bound runaway queries:
--       SET statement_timeout = '5s';
--   • Pool connections (PgBouncer) — each tx holds a connection
--   • Read-only replicas for reporting; primary for writes

-- 16) Anti-patterns
--   • Opening a transaction at the start of an HTTP request and committing at the end (long-running)
--   • Using SERIALIZABLE without a retry loop — random failures in production
--   • Forgetting that ROLLBACK aborts the entire tx including DDL — use savepoints to limit

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Or: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;