# Transactions and ACID

## 1. What 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.

```sql
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.

## 2. ACID, 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.

```sql
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.

```sql
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.

## 3. Isolation 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:

| Anomaly | Definition |
|---|---|
| **Dirty read** | Transaction A reads a row that transaction B has modified but not yet committed. If B rolls back, A read data that never officially existed. |
| **Non-repeatable read** | Transaction A reads the same row twice, and gets different values, because B committed an UPDATE to that row in between A's two reads. |
| **Phantom read** | Transaction A runs the same range query twice, and the second run returns rows the first didn't, because B committed an INSERT (or DELETE) matching the range in between. |

| Isolation Level | Dirty Read | Non-repeatable Read | Phantom Read |
|---|---|---|---|
| **Read Uncommitted** | Possible | Possible | Possible |
| **Read Committed** | Prevented | Possible | Possible |
| **Repeatable Read** | Prevented | Prevented | Possible (standard) / Prevented (Postgres) |
| **Serializable** | Prevented | Prevented | Prevented |

```sql
-- 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).

## 4. Locking: Pessimistic Control Alongside MVCC

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

```sql
-- 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.

## 5. Deadlocks

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

```sql
-- 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.

## 6. Transaction Control: SAVEPOINT

```sql
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.

## 7. Under the Hood

### 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.

```sql
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.

![Two transactions each holding their own MVCC snapshot, seeing different versions of the same logical row](diagrams/01-mvcc-snapshots.svg)

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.

## 8. 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.

## 9. 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.
```