Engineering Atlas › System Design Fundamentals

ACID guarantees in transactions

14 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

What each letter actually promises

Atomicity is about failure, not concurrency. If a transaction aborts, through ROLLBACK, a constraint violation, a deadlock or a crash, every change it made disappears. It says nothing about what others see while it runs.

Consistency is the odd one out. The database guarantees that declared constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL) hold at commit. Undeclared invariants, such as "every shift has a doctor on call", hold only if every transaction is written correctly and the isolation level stops concurrent transactions interleaving badly. Read the C as "A and I let your code keep invariants".

Isolation is about concurrency. The ideal is serializability: the outcome equals some one-at-a-time ordering of the committed transactions. Durability (a commit survives a crash) is covered in How Databases Guarantee Durability.

The anomalies, named

Isolation levels are defined by which of these they allow:

The SQL standard defines its four levels using only the first three. The 1995 paper "A Critique of ANSI SQL Isolation Levels" showed this misses the last two and named Snapshot Isolation. Products then diverged:

DatabaseDefault"Repeatable Read" means"Serializable" means
PostgreSQLRead CommittedSnapshot IsolationSSI, truly serializable
MySQL InnoDBRepeatable ReadSnapshot reads, locking writesLocking reads (2PL style)
OracleRead CommittedNot offeredSnapshot Isolation
SQL ServerRead Committed (locking)Shared locks heldRange locks, 2PL

So Serializable on Oracle does not stop write skew: it fails only when two transactions write the same row, with ORA-08177 (Oracle docs). Repeatable Read on MySQL is not Repeatable Read on Postgres. SQL Server also offers a SNAPSHOT level and a READ_COMMITTED_SNAPSHOT option that makes Read Committed use row versions; that option is off by default on SQL Server but on by default in Azure SQL Database (Microsoft docs), so the same code can behave differently after a cloud migration. Postgres accepts READ UNCOMMITTED but runs it as Read Committed. Kleppmann's Hermitage project tests these differences.

Two ways to isolate

Locking (two-phase locking, 2PL): a transaction takes shared locks to read and exclusive locks to write, and releases nothing until it commits. Range locks stop phantoms. It is serializable, but readers and writers block each other and deadlocks are routine, so a detector kills a victim.

A third option, covered in Kleppmann's Designing Data-Intensive Applications (chapter 7), is actual serial execution: run every transaction on one thread, one at a time. VoltDB and Redis scripts work this way. It is trivially serializable, but only works when each transaction is short and the data fits one core's throughput.

Multi-version concurrency control (MVCC): a write creates a new version of the row instead of overwriting it. A reader sees only versions committed before its snapshot, so readers and writers never block each other. Two writers to the same row still conflict: the second waits, then either proceeds on the new version (Read Committed) or aborts (Snapshot Isolation's "first committer wins").

Snapshot isolation still allows write skew, because the transactions write different rows and never collide. Postgres closes the gap with Serializable Snapshot Isolation (SSI): it tracks what serializable transactions read and aborts one with SQLSTATE 40001 when a non-serializable outcome becomes possible (Postgres docs).

The same shape elsewhere

This is the multi-step consistency and contention shape. A deploy tool that checks "is anyone else rolling out?" and then writes "I am" has write skew; a Kubernetes controller that writes back without checking resourceVersion loses updates.

Down the stack

Layer 1The contract

At Read Committed, each statement sees data committed before it began. At Snapshot Isolation (Postgres REPEATABLE READ), every statement sees the snapshot taken at the first one, and updating a row that someone else changed after that snapshot fails with could not serialize access due to concurrent update. At SERIALIZABLE, committed serializable transactions have the effect of some serial order, and any statement or commit may fail with 40001, so the client retries the whole transaction. Atomicity holds at every level. Durability holds when COMMIT returns, unless synchronous_commit = off, which risks losing up to three times wal_writer_delay (200 ms by default) of commits (Postgres docs).

Layer 2The mechanism

In Postgres every heap tuple header (23 bytes) carries t_xmin, the transaction that created it, t_xmax, the one that deleted or replaced it (0 if none), and t_ctid, a pointer to the newer version. An UPDATE sets xmax on the old tuple and inserts a new one.

Commit status lives in pg_xact, 2 bits per transaction ID. A snapshot, shown by SELECT pg_current_snapshot(), is xmin (oldest running ID), xmax (next unassigned ID) and xip (IDs in progress). An ID is visible if it is below xmax, not in xip, and committed. A tuple is visible if its xmin is visible and its xmax is unset or not visible. The practice implements this rule.

The first reader to confirm a commit sets the HEAP_XMIN_COMMITTED hint bit in t_infomask, so later readers skip pg_xact. VACUUM removes versions no snapshot can see and, because IDs are 32 bits, freezes old tuples before wraparound (forced at autovacuum_freeze_max_age, 200 million by default; see the Postgres vacuuming docs).

For SSI, Postgres takes SIREAD locks on what serializable transactions read. A write that overlaps another transaction's SIREAD lock records a read-write dependency; two consecutive edges, T1 -rw-> T2 -rw-> T3, form a "dangerous structure" and one transaction aborts. Some aborts are false positives.

InnoDB keeps the newest version in place, with DB_ROLL_PTR pointing to older versions in the undo log, and its read view is the same three-part snapshot.

Layer 3The machine

Atomicity and durability cost one log flush per commit. That fsync takes tens of microseconds on NVMe with power-loss protection and roughly 0.5 to 2 ms on network block storage; group commit shares one flush among many commits.

Isolation costs memory and I/O, not flushes. A pg_xact lookup hits a cached 8 KB page holding 32,768 transaction statuses, and setting the hint bit dirties the heap page, so the first SELECT after a bulk load can cause heavy writes. Each MVCC update writes a whole new tuple and, unless it is a heap-only tuple (HOT) on the same page, new index entries too. A long-running transaction pins its xmin, so VACUUM cannot remove newer dead versions anywhere in the database, and tables and indexes bloat. Predicate locks live in fixed shared memory (max_pred_locks_per_transaction), so SSI coarsens row locks into page and relation locks under load, raising false-positive aborts. A sequential scan always takes a relation-level predicate lock (Postgres docs), so a missing index on a serializable path turns every concurrent writer to that table into a possible conflict.

Why it's built this way

  1. Disks tear and reorder writes, so changes go to an append-only log first; replay or discard gives atomicity and durability for one sequential flush.
  2. Most workloads read far more than they write, and locks make readers wait, so databases keep old versions and let readers use a snapshot.
  3. A snapshot is taken on every statement, so it is two numbers and a short list, and commit status is a dense 2-bit array that stays in cache.
  4. Snapshot isolation alone lets write skew through, and full predicate locking is too slow, so SSI tracks reads cheaply and aborts on suspicion, pushing the last step (retry) to the application.

Diagram

sequenceDiagram
  participant A as "T100 (admin Ana)"
  participant DB as Postgres
  participant B as "T101 (admin Budi)"
  A->>DB: BEGIN ISOLATION LEVEL SERIALIZABLE
  B->>DB: BEGIN ISOLATION LEVEL SERIALIZABLE
  A->>DB: SELECT count(*) admins (=2), SIREAD lock
  B->>DB: SELECT count(*) admins (=2), SIREAD lock
  A->>DB: UPDATE Ana role=member (xmax=100)
  Note over DB: rw edge T101 -> T100
  B->>DB: UPDATE Budi role=member (xmax=101)
  Note over DB: rw edge T100 -> T101, cycle possible
  A->>DB: COMMIT
  DB-->>A: OK
  B->>DB: COMMIT
  DB-->>B: ERROR 40001, retry

Worked example

A workspace must always keep at least one admin. The app removes an admin with:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM members WHERE workspace_id = 42 AND role = 'admin';  -- 2
-- application checks count > 1
UPDATE members SET role = 'member' WHERE workspace_id = 42 AND user_id = :me;
COMMIT;

Ana and Budi click "leave admin role" within 50 ms of each other. Both see a count of 2 and update different rows, so "first committer wins" never fires. Both commit; the workspace has zero admins. That is write skew.

Three fixes, at 300 such transactions per second across 50,000 workspaces:

Under snapshot isolation, a cross-row invariant is safe only if the transactions that could break it write a common row.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Read CommittedFew aborts, the common defaultLost updates and write skew unless you lockSingle-statement writes, explicit locks
Snapshot IsolationStable reads, readers never blockWrite skew; long snapshots bloat storageReports; writes to the rows they check
Serializable (SSI)No anomaliesRetries required, false-positive abortsMoney, quotas, cross-row invariants
Explicit FOR UPDATETargeted, predictableEasy to forget; deadlocks if order variesOne known hot invariant

Pitfalls in production

Leadership lens

Check yourself

  1. Why does snapshot isolation stop lost updates on one counter row but not write skew?

    Show answer

    Two updates of the same row conflict and the second fails. Write skew writes different rows, so no conflict arises.

  2. A tuple has xmin = 90 (committed) and xmax = 95. A snapshot has xmax = 100 and xip = [95]. Is the tuple visible?

    Show answer

    Yes. The creator committed before the snapshot and the deleter was still running, so the deletion stays invisible even if 95 commits later.

  3. Why can a single SELECT on a freshly loaded table cause a burst of disk writes in Postgres?

    Show answer

    The first reader of each tuple sets hint bits after checking pg_xact, dirtying pages that must be written back.

  4. Why is "retry on 40001" not enough if the transaction sent an email before committing?

    Show answer

    The retry re-runs everything, so the email goes out twice. Send side effects after commit, ideally through an outbox.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →