Transactions and ACID

1What a Transaction Actually Is

A transaction is a group of one or more statements that the database executes as a single, indivisible unit: either every statement's effects become permanent, or none of them do. It's the mechanism that lets you say "move $100 from account A to account B" as one operation instead of two independent writes that a crash could catch halfway through.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

COMMIT;

If the process crashes after the first UPDATE but before COMMIT, the database guarantees that on restart, neither update happened — money is not destroyed. If both statements succeed and COMMIT returns, both changes are permanent even if the server loses power one millisecond later.

2ACID, One Letter at a Time

A — Atomicity. All statements in the transaction succeed, or all are rolled back. There is no partial state visible to anyone, ever — not even for a moment.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'ZZZ';  -- 'ZZZ' doesn't exist, 0 rows affected, no error by itself
-- an application bug might COMMIT here anyway -- atomicity doesn't save you from a bug that fails to notice 0 rows changed
ROLLBACK;  -- correct handling: check rowcount, or add a CHECK/trigger, and abort

Atomicity protects against crashes and explicit aborts. It does not protect against application logic that commits nonsense — see Common Pitfalls.

C — Consistency. Every transaction moves the database from one valid state to another valid state, where "valid" means every constraint (CHECK, NOT NULL, FOREIGN KEY, UNIQUE) holds. This is largely a restatement of "constraints are enforced," and it's the one ACID letter that's more a consequence of the other three than a mechanism of its own.

CREATE TABLE accounts (
  id      TEXT PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';  -- if this makes balance negative...
COMMIT;  -- ...the CHECK constraint fails here, the whole transaction rolls back automatically

I — Isolation. Concurrent transactions should not see each other's uncommitted or partial work. This is the deepest and most configurable of the four — covered fully in Section 3.

D — Durability. Once a transaction commits, its effects survive a crash, power loss, or restart — because the change was written to durable storage (the write-ahead log) before COMMIT returned, not just cached in memory.

3Isolation Levels and the Anomalies They Prevent

Running transactions concurrently is where correctness gets hard. The SQL standard defines four isolation levels, each permitting fewer anomalies than the last, at the cost of more blocking or more rollback-and-retry:

-- Transaction A                          -- Transaction B
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id='A'; -- reads 500
                                            BEGIN;
                                            UPDATE accounts SET balance = 300 WHERE id='A';
                                            COMMIT;
SELECT balance FROM accounts WHERE id='A'; -- reads 300 -- non-repeatable read
COMMIT;

Under REPEATABLE READ, A's second SELECT in that example would still return 500 — A's snapshot is frozen at the start of its transaction (see Section 4 for the mechanism). Most applications default to READ COMMITTED (Postgres's and Oracle's default) because it's fast and "good enough" for most logic; reach for REPEATABLE READ or SERIALIZABLE specifically when a piece of business logic reads-then-writes based on data that must not shift underneath it (see Section 5's pitfall).

4Locking: Pessimistic Control Alongside MVCC

Isolation levels describe what you can see. Locks describe what you can do concurrently while writing.

-- Row-level lock: blocks any other transaction from UPDATE/DELETE-ing
-- this specific row (or acquiring the same lock on it) until COMMIT.
BEGIN;
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE;
-- ... application logic decides the new balance based on the current one ...
UPDATE accounts SET balance = 450 WHERE id = 'A';
COMMIT;

SELECT ... FOR UPDATE is the standard tool for "read a value, then write a new value derived from it" without another transaction sneaking in a conflicting write between your read and your write — the classic lost update anomaly. Without it, two transactions can both read balance = 500, both independently decide to subtract 100, and both write 400 — one of the two withdrawals is silently lost.

5Deadlocks

A deadlock happens when transaction A holds a lock B wants, while B holds a lock A wants — neither can proceed.

-- Transaction A                    -- Transaction B
BEGIN;                              BEGIN;
UPDATE accounts SET ... WHERE id='X';
                                     UPDATE accounts SET ... WHERE id='Y';
UPDATE accounts SET ... WHERE id='Y'; -- blocks, waiting on B
                                     UPDATE accounts SET ... WHERE id='X'; -- blocks, waiting on A -> deadlock

The database detects the cycle (Postgres checks periodically, roughly every second by default) and kills one transaction with an error, letting the other proceed. The surviving application code must catch that error and retry. The fix at the application level is almost always lock ordering: always acquire locks on rows in the same, consistent order (e.g., always by ascending id) across every code path, so a cycle can never form.

6Transaction Control: SAVEPOINT

BEGIN;
INSERT INTO orders (id, status) VALUES (1, 'pending');

SAVEPOINT before_risky_step;
UPDATE inventory SET stock = stock - 1000 WHERE product_id = 42; -- CHECK constraint fails: stock can't go negative
ROLLBACK TO SAVEPOINT before_risky_step;  -- undoes only the UPDATE, keeps the INSERT

UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;    -- retry with a sane quantity
COMMIT;

A SAVEPOINT is a named point inside a transaction you can roll back to without abandoning the whole transaction — useful for "try this, and if it fails, fall back to a safer path" without losing everything already accumulated in the transaction.

Under the HoodHow it works internally▾

MVCC: giving every transaction its own consistent snapshot

Postgres (and most modern relational engines) implement isolation using Multi-Version Concurrency Control (MVCC) instead of locking every row a transaction merely reads. Every row version carries two hidden system columns: xmin (the id of the transaction that created this version) and xmax (the id of the transaction that deleted/superseded it, or empty if it's still current).

An UPDATE never overwrites a row in place — it writes a new row version with a new xmin, and sets the old version's xmax to the updating transaction's id. The old version isn't physically removed yet; it just becomes invisible to new transactions once nobody's snapshot can see it anymore.

sqlCopy

UPDATE accounts SET balance = 400 WHERE id = 'A';
-- old row: (id='A', balance=500, xmin=100, xmax=105)  <- now "dead" but still on disk
-- new row: (id='A', balance=400, xmin=105, xmax=null) <- current version

Each transaction, when it starts (or at each statement, depending on isolation level), takes a snapshot: a record of which transaction ids were already committed. A row version is visible to a transaction if its xmin was committed before the snapshot was taken and its xmax was not. This is the entire mechanism behind "readers never block writers and writers never block readers" in Postgres — a reader simply ignores row versions that shouldn't exist in its snapshot, without ever needing to acquire a lock against the writer.

This is also exactly why REPEATABLE READ can prevent non-repeatable reads without any extra locking: the transaction just keeps using its original snapshot for every subsequent statement, so a concurrent commit is invisible to it until it commits and starts a new transaction.

Why old row versions need VACUUM

Because UPDATE/DELETE leave dead row versions behind instead of erasing them, a table under heavy write load accumulates bloat — dead tuples nobody's snapshot can see anymore but that still occupy disk pages. Postgres's VACUUM process (usually running automatically as autovacuum) scans for tuples no longer visible to any active snapshot and reclaims that space for reuse. A long-running transaction (even a read-only one left open by mistake) prevents vacuum from reclaiming anything newer than its snapshot, which is the single most common cause of catastrophic table bloat in production Postgres.

Serializable isolation: detecting conflicts instead of preventing them upfront

SERIALIZABLE in Postgres is implemented as Serializable Snapshot Isolation (SSI): transactions run exactly like REPEATABLE READ (their own MVCC snapshot, no extra blocking) but the engine additionally tracks read/write dependencies between concurrent transactions. If it detects a pattern that could not have arisen from any serial (one-at-a-time) execution order, it aborts one of the transactions with a serialization failure at commit time, and the application is expected to retry. This is why serializable transactions must always be wrapped in retry logic — an abort under SERIALIZABLE is an expected, not exceptional, outcome under contention.

Durability: the write-ahead log

Before any data page is modified on disk, the change is first appended to the write-ahead log (WAL) — a sequential, append-only file. COMMIT does not return to the client until the WAL record for that transaction has been flushed (fsync) to durable storage. If the server crashes immediately after, redo from the WAL replays every committed-but-not-yet-applied change on restart, which is how a committed transaction survives a crash even though its actual data pages might not have been written to disk yet. This same log is what enables replication (streaming WAL to replicas) and point-in-time recovery.

⚠️ Common Pitfalls

1. Assuming atomicity means "logically correct" — atomicity guarantees all-or-nothing, not that the logic itself is right. A transaction that commits an UPDATE affecting 0 rows because of a typo'd WHERE clause is still perfectly atomic and perfectly wrong.
2. Read-modify-write without FOR UPDATE or an atomic expression — reading a balance, computing a new value in application code, then writing it back is a classic lost-update race. Prefer UPDATE accounts SET balance = balance - 100 WHERE id='A' (atomic, single statement) over separate read-then-write, or lock the row explicitly with FOR UPDATE when the logic can't be a single statement.
3. Leaving a transaction open ("idle in transaction") — an open transaction, even one doing nothing, pins an MVCC snapshot, which blocks VACUUM from cleaning up dead tuples the whole table over, causing table bloat. Always commit or roll back promptly; set idle_in_transaction_session_timeout as a safety net.
4. Choosing SERIALIZABLE without retry logic — a serialization failure at commit time is a normal, expected error under this isolation level, not a bug. Code that doesn't catch and retry 40001 will simply lose transactions under load.
5. Assuming REPEATABLE READ prevents lost updates on its own — it prevents your reads from changing mid-transaction, but a naive read-then-write can still race with another REPEATABLE READ transaction; Postgres detects the conflict and aborts one of them at commit time (serialization failure), it does not silently merge the writes.
6. Inconsistent lock ordering across code paths — if one code path updates account X then Y, and another updates Y then X, a deadlock is a matter of when, not if, under concurrent load. Always acquire locks (including implicit ones from UPDATE/FOR UPDATE) in the same global order.
7. Treating BEGIN/COMMIT as free — every transaction, even a trivial one, costs a WAL flush on commit. Batching many small logical writes into one transaction is usually faster than committing each individually, up to the point where the transaction gets so large it holds locks too long.

📋 Quick Reference

ACID:
  Atomicity   -> all statements commit, or none do
  Consistency -> constraints hold before and after every commit
  Isolation   -> concurrent transactions don't see each other's half-finished work
  Durability  -> a committed transaction survives a crash (via WAL)

Anomalies prevented per isolation level:
  Read Uncommitted -> none
  Read Committed   -> dirty reads
  Repeatable Read  -> dirty reads + non-repeatable reads (+ phantoms, in Postgres)
  Serializable     -> all of the above, via conflict detection + abort-and-retry

Mechanism:
  MVCC     -> each transaction gets a snapshot; readers never block writers
  FOR UPDATE -> pessimistic row lock, prevents lost updates on read-modify-write
  WAL      -> durability; commit waits for the log to be flushed, not the data pages
  VACUUM   -> reclaims dead row versions once no snapshot can see them anymore

Deadlock: cycle of transactions waiting on each other's locks -> engine kills one -> app must retry.
Fix: consistent lock ordering across every code path.

🎤 Interview Q&A

🛠️ Practice exercises

Each exercise is a file in this topic folder. Code exercises are self-checking: run them and make the checks pass.

✅ Check yourself

Source: dbfund_transactions_and_acid_theory.md · generated by tools/build_lessons.py