SQL Transactions & ACID

Transactions — Basics

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;      -- or ROLLBACK to undo everything since BEGIN

-- Savepoints — partial rollback within one transaction
BEGIN;
INSERT INTO orders VALUES (...);
SAVEPOINT before_payment;
INSERT INTO payments VALUES (...);   -- fails
ROLLBACK TO before_payment;           -- undo just the payment insert, keep the order insert
COMMIT;

ACID, Precisely

• Atomicity — the transaction fully commits or fully rolls back; no partial state ever persists.
• Consistency — constraints (FK, UNIQUE, CHECK) are enforced at commit; the DB only ever moves between valid states.
• Isolation — concurrent transactions don't see each other's uncommitted, in-progress changes (degree depends on isolation level, below).
• Durability — once committed, data survives a crash — written to the write-ahead log (WAL) before the commit is acknowledged.

Isolation Levels & the Three Phenomena

• Dirty read: reading another transaction's uncommitted change (which might still roll back).
• Non-repeatable read: re-reading the same row within one transaction gives a different value, because another transaction committed a change in between.
• Phantom read: re-running the same query gives a different set of rows, because another transaction inserted/deleted matching rows in between.

PostgreSQL's default is READ COMMITTED — good enough for most application logic; upgrade to SERIALIZABLE specifically for critical financial correctness where even non-repeatable reads are unacceptable (see [[Payment System]]'s CP-over-AP framing — this is the SQL-level mechanism behind that same design choice).

Locking

-- Pessimistic: lock rows for modification upfront
BEGIN;
SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;

SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE NOWAIT;        -- fail immediately instead of waiting
SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;   -- queue-worker pattern

SKIP LOCKED is the concrete SQL-level mechanism behind a job-queue worker safely claiming one row without colliding with another concurrent worker — the same "atomic claim" reasoning as [[Distributed Job Scheduler]]'s leased-claim logic, implemented via row locks instead of an application-level compare-and-set.

Deadlock

T1: locks row A, wants row B
T2: locks row B, wants row A
→ deadlock — the DB detects the cycle and kills one transaction

Prevention: always acquire locks across multiple rows in a consistent order (e.g., always lock the lower ID first) — this alone eliminates the cyclic-wait condition that causes deadlocks.

Optimistic vs. Pessimistic Locking

Pessimistic (SELECT FOR UPDATE) locks upfront — good for high-contention rows where conflicts are likely. Optimistic locking avoids locks entirely — read a version number, and on write, check the version hasn't changed:

SELECT id, balance, version FROM accounts WHERE id = 1;   -- version = 3
UPDATE accounts SET balance = 400, version = 4 WHERE id = 1 AND version = 3;
-- 0 rows updated → someone else modified it concurrently → retry

MVCC (Multi-Version Concurrency Control)

PostgreSQL uses MVCC: each transaction sees a consistent snapshot of the database as of its start time; old row versions are retained until no active transaction still needs them (cleaned up by autovacuum). This is what gives readers-never-block-writers and writers-never-block-readers for normal SELECT — no read locks needed at all for standard reads.

Two-Phase Commit (Distributed Transactions)

Phase 1 (Prepare): coordinator asks every participant "can you commit?"
Phase 2 (Commit):  if all said yes → coordinator sends COMMIT to all; else ROLLBACK to all

PREPARE TRANSACTION 'tx_id_123';   -- phase 1
COMMIT PREPARED 'tx_id_123';       -- phase 2

This is the classical mechanism for coordinating a single atomic transaction across multiple independent databases — worth naming, alongside acknowledging its real cost (the coordinator itself becomes a temporary single point of failure/blocking during the window between phases), when a design genuinely needs cross-database atomicity rather than the [[Payment System]] page's Saga-pattern alternative for cross-service (not cross-database) consistency.

Interview Drills

1. Explain ACID in four sentences. — Atomicity: a transaction commits fully or not at all. Consistency: constraints always hold; the DB only moves between valid states. Isolation: concurrent transactions don't observe each other's uncommitted changes. Durability: once committed, data survives a crash via the write-ahead log.
2. What's the difference between a non-repeatable read and a phantom read? — A non-repeatable read is the same row changing value between two reads in one transaction (another transaction updated it); a phantom read is the set of rows matching a query changing between two identical queries (another transaction inserted/deleted rows) — same root cause (concurrent commits), different symptom (value vs row-set).
3. When would you reach for SELECT ... FOR UPDATE SKIP LOCKED specifically? — A multi-worker queue-processing pattern — each worker needs to atomically claim one pending row without waiting on rows already claimed by other concurrent workers; SKIP LOCKED lets a worker instantly move past locked rows to find an actually-available one, rather than blocking.

Cross-links

[[SQL Indexes & Query Performance]] · [[Payment System]] · [[Distributed Job Scheduler]] · [[CAP Theorem & Consistency Models]]