Transactions Primer

ACID — Atomicity, Consistency, Isolation, Durability — was coined by Härder and Reuter in 1983 to describe what a transaction is supposed to be. The word "transaction" means almost nothing without it.

A — Atomicity

Every statement between BEGIN and COMMIT either all takes effect, or none does. There is no "half a transaction." If the second UPDATE fails, the first one is rolled back — even if some of it has already been written to the WAL or the data page.

Implementation: the engine writes undo information before changing anything (Postgres keeps old row versions; MySQL/InnoDB writes to a separate undo log). On ROLLBACK, undo is replayed in reverse. On crash recovery, the recovery code finishes any in-flight transactions by either re-applying the WAL (REDO) or applying the undo (UNDO) — Mohan's ARIES algorithm, 1992, is the canonical reference, and most modern engines are variants of it.

C — Consistency

The transaction moves the database from one valid state to another, where "valid" is whatever your application says it is. The database enforces only what you've declared: CHECKconstraints, foreign keys, UNIQUE, NOT NULL. Application invariants ("every order has at least one line item", "total debits equal total credits") are your responsibility — the C in ACID is the weakest letter, and the one most people overestimate.

Critically, the C in ACID is not the C in CAP. ACID-C is about constraint satisfaction; CAP-C is about every replica seeing the same value at the same time. Same letter, totally different idea.

I — Isolation

Concurrent transactions behave as if they ran one at a time. This is the most expensive promise to keep, so SQL gives you a dial: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Section 02 walks through what each level actually prevents and, more importantly, what it still allows.

The default is almost never SERIALIZABLE. Postgres ships with READ COMMITTED; MySQL InnoDB ships with REPEATABLE READ; Oracle ships with READ COMMITTED. If your code has not explicitly asked for SERIALIZABLE, it is running on something weaker than the textbook definition of "isolated."

D — Durability

Once COMMIT returns success, the change survives any crash short of physical media loss. Implementation: write the change to the write-ahead log, call fsync, then return. fsync is the load-bearing syscall — it forces the page out of kernel page cache, through the journal, onto the platter (and on most SSDs, out of the drive's volatile cache too via FUA / cache-flush commands).

Real systems weaken D constantly for throughput. Postgres has synchronous_commit = off — COMMIT returns without waiting for fsync, gaining ~10× throughput at the cost of losing up to 600 ms of committed transactions on a crash. MySQL has innodb_flush_log_at_trx_commit = 0|1|2 with the same trade. Most cloud-managed databases default to the safer setting; most self-managed ones get tuned down by an exhausted DBA at some point.

All four are dials, not booleans

Knob                                Weakens   Why you'd turn it
─────────────────────────────────── ───────── ─────────────────────────────
fsync = off (Postgres)              A + D     Benchmarks. Never in prod.
synchronous_commit = off            D         Bulk writes; tolerate small loss.
innodb_flush_log_at_trx_commit = 0  D         Same idea, MySQL.
READ UNCOMMITTED                    I         Reporting queries on hot tables.
NOLOCK hint (SQL Server)            I         Same.
async replication                   D (multi) Acked-but-unreplicated → can lose.
no FK / CHECK constraints           C         Application enforces, supposedly.

Isolation is the dial that controls how much overlap two transactions can have before they affect each other. Stronger = fewer surprises, more blocking and aborts. Weaker = better throughput, more "wait, how is the inventory at -1?" in your incident channel.

The four standard levels

Level             Dirty   Non-repeatable  Phantom  Lost      Write
                  read    read            read     update    skew
───────────────── ─────── ─────────────── ──────── ───────── ─────────
READ UNCOMMITTED  allow   allow           allow    allow     allow
READ COMMITTED    block   allow           allow    allow*    allow
REPEATABLE READ   block   block           varies   block     allow
SERIALIZABLE      block   block           block    block     block

That looks like five distinct hazards, so let's define them. Each is a concrete bug pattern, not jargon.

The five anomalies, defined by example

1. Dirty read. T1 reads a row that T2 has changed but not committed. T2 rolls back; T1 saw a value that never existed. Blocked at READ COMMITTED and above.
2. Non-repeatable read. T1 reads row 7, then reads row 7 again later in the same transaction. T2 changed it in between. T1 gets two different values for "the same" row. Blocked at REPEATABLE READ and above.
3. Phantom read. T1 runs SELECT count(*) WHERE x>5 twice. T2 inserts a matching row in between. T1 sees different counts. Blocked at SERIALIZABLE in the standard; Postgres's REPEATABLE READ (which is actually snapshot isolation) blocks it too.
4. Lost update. T1 reads X=1, T2 reads X=1, both write X=2. One of them is silently overwritten. This is the classic read-modify-write race in application code. Prevented by either taking the row lock (SELECT ... FOR UPDATE) or doing the work in a single atomic UPDATE statement.
5. Write skew. T1 reads X and Y, decides to update X based on Y's value. T2 reads X and Y, decides to update Y based on X's value. Neither overwrites the other's write — but the joint state now violates the invariant they were both checking. Only SERIALIZABLE catches this.

What real databases actually do

The SQL standard is a guideline; engines diverge:

• Postgres. Default is READ COMMITTED. Its REPEATABLE READ is actually snapshot isolation — it takes a snapshot at the first statement, you see that snapshot for the whole transaction. Its SERIALIZABLE is Serializable Snapshot Isolation (SSI): runs at REPEATABLE READ plus a predicate-lock tracker that aborts one of any pair of transactions that form a serialization cycle. Optimistic — costs only the abort.
• MySQL InnoDB. Default is REPEATABLE READ. Uses MVCC for reads, but also uses gap locks under REPEATABLE READ to prevent phantoms. This is famously deadlock-prone for INSERT-heavy workloads.
• Oracle. Default is READ COMMITTED. ItsSERIALIZABLE is also snapshot isolation — meaning it allows write skew despite the name. (Pre-SSI Postgres was the same.)
• SQL Server. Default is READ COMMITTEDwith pessimistic locking; can be switched to READ_COMMITTED_SNAPSHOT for MVCC behavior.

The right tool for lost-update

The most common transactional bug pattern in application code:

-- BAD: lost update under READ COMMITTED
BEGIN;
  SELECT balance FROM accounts WHERE id = 42;  -- returns 100
  -- application computes balance - 30 = 70
  UPDATE accounts SET balance = 70 WHERE id = 42;
COMMIT;

-- GOOD (option 1): atomic, no read-then-write race
BEGIN;
  UPDATE accounts SET balance = balance - 30 WHERE id = 42;
COMMIT;

-- GOOD (option 2): row lock held until commit
BEGIN;
  SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
  -- safely read; T2 blocks here
  UPDATE accounts SET balance = 70 WHERE id = 42;
COMMIT;

-- GOOD (option 3): SERIALIZABLE + retry loop
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  SELECT balance FROM accounts WHERE id = 42;
  UPDATE accounts SET balance = 70 WHERE id = 42;
COMMIT;  -- may fail with 40001; application retries

Option 1 is best when the new value is a pure function of the old. Option 2 is best when the application needs to make a decision (e.g. "refuse if balance < 30"). Option 3 is best when many invariants are checked and you don't want to hand-place locks; the cost is occasional retries.

Why nobody defaults to SERIALIZABLE

Cost. Postgres SSI is optimistic but still adds bookkeeping per read; under contention, abort-and-retry can dominate. Two-phase locking SERIALIZABLE (the original textbook implementation) is even worse — long-held locks and deadlocks. Most workloads can live with READ COMMITTED + SELECT FOR UPDATE on the critical paths and pay 2× less in CPU and contention. That's the ergonomic trade every engine made.

Pessimistic locking (the classical approach) is simple: take a shared lock to read, an exclusive lock to write. Readers block writers and vice versa. It works, but throughput collapses under contention.

MVCC says: never delete or overwrite a row in place. UPDATEmeans "create a new version, mark the old one dead". DELETE means "mark the row dead". Readers walk past dead versions they shouldn't see; writers don't block readers. The price is bloat (old versions accumulate) and a background process (vacuum) to clean them up.

xmin and xmax — the two columns you don't see

Every Postgres row has two hidden columns:

• xmin — the transaction ID that created this row version.
• xmax — the transaction ID that deleted or updated this row version (0 if still live).

That's it. Visibility is a calculation against those two numbers and your snapshot.

-- See them for yourself
SELECT xmin, xmax, * FROM users WHERE id = 42;

 xmin  | xmax | id | name  | last_seen
-------+------+----+-------+-----------
 50001 |    0 | 42 | alice | 2026-05-26
 (1 row)

-- After UPDATE last_seen by txn 50100:
 xmin  | xmax  | id | name  | last_seen
-------+-------+----+-------+-----------
 50001 | 50100 | 42 | alice | 2026-05-26  ← old version
 50100 |     0 | 42 | alice | 2026-05-27  ← new version

Snapshots and the visibility rule

When a transaction (or, in READ COMMITTED, a single statement) starts, the engine takes a snapshot: roughly (xmin, xmax, in_progress[]), capturing "everything below xmin is committed; everything at or above xmax hasn't started; in_progress are the ones running right now."

A row version is visible to your transaction if:

1. Its xmin is below your snapshot's xmin and not in the in-progress list (so it's committed before you started), AND
2. Either xmax = 0 (still live), OR xmax is at or above your snapshot's xmax / in the in-progress list / belongs to an aborted transaction (so the "deletion" doesn't count for you yet).

Result: no locks involved in reading. Two concurrent readers don't wait on each other; a writer doesn't block a reader. Writers still take row-level exclusive locks against each other to prevent two writers updating the same row.

The cost: bloat and vacuum

Dead row versions pile up. A table that's heavily updated can have 10× as many dead rows as live ones — every sequential scan reads dead bytes, every index lookup walks past tombstones. This is bloat, and it tanks performance silently.

VACUUM is the GC: scan the table, find rows whose xmax is below the oldest currently-running snapshot ("nobody can see this row any more"), reclaim the space.autovacuum runs it automatically; on write-heavy tables you usually have to tune its triggers (autovacuum_vacuum_scale_factor,autovacuum_naptime) because the defaults assume a calmer workload than yours.

-- Find bloated tables
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS pct_dead
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- Manual cleanup (blocks briefly)
VACUUM (VERBOSE, ANALYZE) users;

-- Reclaim disk too (rewrites the table; takes an ACCESS EXCLUSIVE lock)
VACUUM FULL users;

VACUUM FREEZE and the wraparound emergency

Postgres transaction IDs are 32-bit (well, 32-bit usable). At ~2 billion transactions, they wrap. If a row's xmin is from txn 12345 and the current txn ID has wrapped past it, suddenly "the row was created in the future" — visibility breaks.

The fix is freezing: rewrite old xmin values with a special "visible to everyone forever" marker. VACUUM does this automatically when rows are old enough (vacuum_freeze_min_age). If a write-heavy database isn't freezing fast enough, Postgres eventually refuses to accept new writes to protect data integrity — this is the famous transaction-wraparound emergency, and it has taken down major sites (Sentry, Mailchimp, others). Monitor datfrozenxid and don't disable autovacuum.

MVCC vs other engines

Engine            How                                           Cost
─────────────     ──────────────────────────────────────────── ─────────────
Postgres          xmin/xmax in tuple; old versions in table     Bloat, vacuum
MySQL InnoDB      old versions in separate undo log             Undo growth
Oracle            old versions in UNDO tablespace               UNDO mgmt
SQL Server        old versions in tempdb (snapshot mode)        Tempdb growth

Same idea everywhere, different storage location for the dead bytes. Postgres's in-table approach is why "bloat" is Postgres-specific terminology — other engines have the same problem but the bytes live elsewhere.

Why snapshot isolation allows write skew

Both transactions take their snapshot at BEGIN; both see the same world. Both update different rows. There's no write-write conflict at any single row, so MVCC's visibility check doesn't catch it. The cycle is only visible if you track the predicates each transaction read — that's what Postgres SSI adds on top of snapshot isolation. SSI maintains a graph of read/write dependencies and aborts one transaction in any cycle.

Our curl went to one API service that talked to one database. Real systems aren't that lucky. The user-update transaction also has to: charge a card via a payment service, send a confirmation email via a mail service, write an audit row to an analytics database, and replicate the user row to a read replica in another region. Now "commit" means something across five participants instead of one.

2PC — the protocol that wedges

Two-Phase Commit is the classical answer:

1. Phase 1 (PREPARE). Coordinator asks every participant: "can you commit?" Each participant durably writes the change to its WAL but does not commit; it locks the affected rows; it replies "yes" or "no".
2. Phase 2 (COMMIT or ABORT). If everyone said yes, coordinator durably records "commit" and tells everyone to commit. If any said no (or timed out), coordinator says abort.

The participant's problem: between voting yes and hearing the coordinator's decision, it's in the in-doubtstate — holding locks, can't commit, can't abort. If the coordinator dies right then, the participant waits forever. Other transactions that want those rows pile up. Production grinds.

XA (the X/Open distributed-transaction standard) is 2PC wearing a tuxedo. Java EE had built-in support; Postgres has PREPARE TRANSACTION. Almost no greenfield system uses XA for cross-service transactions any more, because the wedging behavior is awful operationally and the latency of two round-trips per commit is crushing under load.

Sagas — the modern answer

A saga models a long-running cross-service transaction as a sequence of local transactions plus a compensating action for each:

Step 1: charge card           Compensation: refund card
Step 2: reserve inventory     Compensation: release inventory
Step 3: send confirmation     Compensation: send cancellation
Step 4: write audit row       Compensation: write audit-reversed row

If step 3 fails, run compensations for 2 and 1 in reverse.
Each step is locally ACID; the saga is eventually consistent.

No distributed locks, no in-doubt state, no coordinator wedging. The cost: you have to write the compensations, every step has to be idempotent (retries happen), and the overall thing is not atomic from an external observer's view — there's a window where the card is charged but the inventory isn't reserved.

Two flavors: orchestration (a central saga executor calls each step in sequence — easier to reason about, easier to debug, single point of failure) and choreography (each service publishes events, others react — looser coupling, much harder to see the whole picture, easier to deadlock in event loops). Most teams start with orchestration and don't regret it.

Raft and Paxos — the consensus core

Sagas are about cross-service workflows. Consensus protocolsare about cross-replica state. Same problem family, totally different layer.

Raft (Ongaro & Ousterhout, 2014) is the protocol of choice today; Paxos (Lamport, 1989) is the older formalism it's equivalent to. Both replicate an append-only log across N nodes:

1. One node is the leader, elected by majority vote.
2. The leader receives writes, appends them to its local log, replicates entries to followers.
3. An entry is committed once a majority (quorum) of nodes have acknowledged it durably.
4. The state machine on every replica applies committed entries in log order.
5. If the leader dies, followers detect the timeout and elect a new one.

Survives < N/2 failures: 3 nodes tolerate 1 failure, 5 tolerate 2, 7 tolerate 3. The cost is latency — every write needs a quorum round-trip. etcd, Consul, CockroachDB, TiKV, and Kafka's newer KRaft mode are all Raft. Spanner uses Paxos. ZooKeeper uses Zab (a Paxos variant).

CAP and PACELC — the unavoidable fork

CAP theorem (Brewer, 2000; Gilbert & Lynch, 2002): in the presence of a network partition, you must choose between Consistency (every replica returns the same value) and Availability (every replica responds). You cannot have both during the partition. After the partition heals, you can have both again.

In practice, almost every system mostly behaves like CP — they prefer to stop accepting writes during a partition rather than risk diverging copies. "AP" systems like Cassandra accept writes everywhere and reconcile later via last-write-wins or CRDTs; the cost is that two readers can see different values until reconciliation.

PACELC (Abadi, 2012) extends this: "If Partition, then Availability vs Consistency; Else, then Latency vs Consistency." Even without partitions, you trade — every write that wants to be visible to every reader needs to either hit the leader or wait for synchronous replication. A read replica with async replication can serve stale data.

Linearizability vs serializability

These two terms look alike, mean different things, and almost everyone conflates them:

• Serializability is about transactions: a schedule is serializable if its effect is equivalent to some serial order of the transactions. Time doesn't matter — only the order matters.
• Linearizability is about operations across a distributed system: an operation appears to take effect at a single point in time between its start and end, and that point respects real-time ordering. After op A completes, every subsequent op B sees A's effect.

You can be one without the other. Postgres SSI is serializable but a single-node Postgres isn't "linearizable" in any distributed sense — there's only one node. Google Spanner is both: Paxos for linearizability across replicas, plus SSI inside; it uses TrueTime (GPS + atomic clocks) to give every commit a timestamp that respects real-time order across the planet. This is called external consistency, and it's the gold standard.

What this means for your code

Cross-service workflows must be designed for the network you actually have, not the one you wish you had:

• Idempotency keys. Every cross-service request gets an ID; the receiver dedupes. Retries become safe.
• Compensating actions. If you can't commit everything atomically, you must be able to undo what you already did. Compensations are first-class business logic — write them at the same time as the forward path.
• Read-your-writes consistency. If a user writes then immediately reads, route the read to the leader or pin them to a session that knows their last-write timestamp. Otherwise they see stale data and blame you.
• Never hold a DB transaction across an external call.Your row locks become a network round-trip; one slow downstream service can wedge the database.

Red flags in code review

• SELECT-then-UPDATE in application code without SELECT FOR UPDATE or atomic UPDATE. Classic lost-update under READ COMMITTED. Use UPDATE accounts SET balance = balance - 30 or take the row lock explicitly.
• Setting READ UNCOMMITTED for "performance" without measuring. The throughput gain is usually imaginary; the dirty-read bugs are not. If you really need stale reads for a reporting query, name it that way in code review.
• Saga without compensating actions implemented.Forward path ships, compensations are "TODO". The first partial-failure incident corrupts data in three services simultaneously and the on-call has nothing to undo with.
• Cross-service transaction via 2PC / XA. Modern microservices should be sagas with idempotent steps. XA brings coordinator-wedging, two-round-trip latency, and ops nightmares for a guarantee you usually don't need.
• Long-running transaction holding locks across an external HTTP call. Your row locks now last as long as a third party's p99 latency. One slow downstream service can take down the database — common cause of incidents that look like "DB is slow" but are really "DB is waiting on something else".