ACID Transactions & Isolation Levels Explained: Dirty Reads to Serializable

Most backend bugs involving money or inventory are not logic errors. They are concurrency errors: two requests run the same correct code at the same time and the result is wrong. Isolation levels are the database's tool for this, and they are consistently misunderstood, partly because the SQL standard's definitions are vague and every database implements them differently. This guide reproduces each anomaly as two concurrent SQL sessions so you can actually run them, then maps exactly what Postgres and MySQL do.

ACID Properties, Precisely

Interviewers ask "what is ACID" at every level, from campus placements to senior rounds. The difference between a fresher answer and a strong one is precision about how each property is achieved.

Two nuances worth stating in an interview. First, the "C" in ACID is not the "C" in the CAP theorem; CAP consistency means linearizability across replicas (see CAP theorem for interviews). Second, durability has knobs: Postgres synchronous_commit = off or MySQL innodb_flush_log_at_trx_commit = 2 trade a small window of possible data loss for throughput.

A transaction in code

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;   -- debit Asha
UPDATE accounts SET balance = balance + 500 WHERE id = 2;   -- credit Ravi
INSERT INTO ledger(from_id, to_id, amount) VALUES (1, 2, 500);
COMMIT;   -- all three become visible together, or none do

The Concurrency Anomalies, Reproduced in SQL

For each anomaly below, two sessions (T1 and T2) run interleaved. Time flows downward. Assume this table:

CREATE TABLE accounts (id INT PRIMARY KEY, owner TEXT, balance INT);
INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ravi', 1000);

1. Dirty read

A transaction reads data written by another transaction that has not yet committed. If that other transaction rolls back, you acted on data that never existed.

Postgres never allows dirty reads, even if you ask for READ UNCOMMITTED (it behaves as Read Committed). MySQL does allow them at READ UNCOMMITTED.

2. Non-repeatable read

Reading the same row twice in one transaction gives different values because another transaction committed an update in between.

Under Read Committed, each statement gets a fresh snapshot. Under Repeatable Read, the whole transaction uses one snapshot taken at its first query. A report that sums balances in several queries can produce totals that never existed at any single point in time under Read Committed.

3. Lost update

Two transactions read the same value, compute a new value in the application, and write it back. The second write overwrites the first. This is the most common real-world bug of the lot.

What happens per database and level:

• Read Committed (Postgres, MySQL): the lost update happens.
• Postgres Repeatable Read: T2's UPDATE fails with ERROR: could not serialize access due to concurrent update. The application must retry. Lost update prevented.
• MySQL Repeatable Read: the lost update still happens. InnoDB's UPDATE reads the latest committed version (a "current read"), not the snapshot, and does not raise an error.

The fixes: write it as an atomic statement (UPDATE accounts SET balance = balance - 200 WHERE id = 1 AND balance >= 200), lock the row when reading (SELECT ... FOR UPDATE), or use optimistic concurrency with a version column. All three are covered in the locking section below.

4. Phantom read

Re-running the same range query returns a different set of rows because another transaction inserted or deleted matching rows.

Row-level locks cannot prevent phantoms by themselves, because the phantom row did not exist when you locked. Preventing them needs either a snapshot (for reads) or range/predicate locks (InnoDB's gap and next-key locks; Postgres SSI's predicate tracking).

5. Write skew

The subtle one. Two transactions read overlapping data, each checks an invariant, and each writes to a different row. Neither write conflicts with the other, but together they violate the invariant.

Classic example: a hospital requires at least one doctor on call per shift.

CREATE TABLE doctors (name TEXT PRIMARY KEY, on_call BOOLEAN, shift INT);
INSERT INTO doctors VALUES ('Alice', true, 1), ('Bob', true, 1);

Snapshot isolation (Postgres Repeatable Read, Oracle's "Serializable") allows this, because the two transactions wrote different rows. Postgres Serializable detects the dangerous read-write dependency cycle and aborts one with a serialization failure. The same pattern appears in "two users book the last two seats such that a group booking constraint breaks", "two withdrawals from a joint account each checking the combined balance", and "username uniqueness checked by SELECT before INSERT without a unique index".

The Four SQL Isolation Levels (and Snapshot Isolation)

The SQL standard defines four levels by which of three anomalies (dirty, non-repeatable, phantom) they forbid. That framing is from 1992 and is incomplete; the famous 1995 paper "A Critique of ANSI SQL Isolation Levels" (Berenson et al.) added lost update, write skew and snapshot isolation. The practical table is:

Serializable means: the result is the same as if transactions ran one at a time in some order. It is the only level where you can reason about each transaction as if it were alone. There are three classic ways to implement it: actual serial execution (Redis, VoltDB), two-phase locking (2PL: shared locks on reads, exclusive on writes, held until commit, plus range locks), and Serializable Snapshot Isolation (SSI: optimistic, abort on dangerous patterns).

-- Setting the level
-- Postgres
BEGIN ISOLATION LEVEL SERIALIZABLE;
SET default_transaction_isolation = 'repeatable read';  -- session default

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

Postgres vs MySQL: What Each Level Really Does

This is where most online explanations are wrong or vague, and where interviewers at product companies probe.

Oracle supports Read Committed (default) and a level it calls Serializable that is actually snapshot isolation. SQL Server defaults to lock-based Read Committed unless READ_COMMITTED_SNAPSHOT is enabled, and also offers a separate SNAPSHOT level.

The MySQL Repeatable Read surprise

This sequence surprises many people:

-- MySQL, REPEATABLE READ, both sessions
-- T1                                    -- T2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;    -- balance 1000
                                         START TRANSACTION;
                                         UPDATE accounts SET balance = 700 WHERE id = 1;
                                         COMMIT;
SELECT * FROM accounts WHERE id = 1;    -- still 1000 (snapshot read)
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- current read: 700 + 100
SELECT * FROM accounts WHERE id = 1;    -- 800 (own write now visible)
COMMIT;

The transaction "saw" 1000 but its update built on 700. The final value (800) is correct because the UPDATE is atomic, but if the application had computed 1000 + 100 and written 1100, T2's update would be lost. Postgres Repeatable Read would instead raise an error at the UPDATE.

How MVCC Works Under the Hood

Multi-Version Concurrency Control is why modern databases do not need readers to lock rows. Every write creates a new version; readers pick the version that was committed as of their snapshot.

PostgreSQL's implementation

Every row version (tuple) carries two hidden fields: xmin, the transaction ID that created it, and xmax, the transaction ID that deleted or replaced it (0 if still live). An UPDATE marks the old tuple's xmax and inserts a new tuple. A snapshot records which transaction IDs were committed when it was taken.

SELECT xmin, xmax, * FROM accounts WHERE id = 1;
-- xmin | xmax | id | owner | balance
--  901 |    0 |  1 | Asha  |    1000

-- after T(905) runs UPDATE ... balance = 700 and commits:
-- old tuple: xmin=901, xmax=905, balance=1000  (visible to snapshots before 905)
-- new tuple: xmin=905, xmax=0,   balance=700   (visible to snapshots after 905)

Visibility rule, simplified: a tuple is visible to your snapshot if xmin committed before your snapshot and xmax is either empty, aborted, or not yet committed as of your snapshot. Dead tuples pile up and are reclaimed by VACUUM; long-running transactions keep old snapshots alive and block cleanup, causing table bloat. That is why "a forgotten idle-in-transaction session bloated our table" is a common Postgres incident.

MySQL InnoDB's implementation

InnoDB updates rows in place in the clustered index and writes the previous version to the undo log. Each row has a hidden transaction ID and a roll pointer to its undo record. A read view decides whether the current version is visible; if not, InnoDB follows the undo chain to reconstruct an older version. The purge thread removes undo records once no read view needs them. Long transactions here grow the undo history list ("history list length" in SHOW ENGINE INNODB STATUS).

Locks, SELECT FOR UPDATE and Optimistic Concurrency

Row locks

• Exclusive (X) lock: taken by UPDATE, DELETE and SELECT ... FOR UPDATE. Blocks other writers and other locking readers of that row; plain MVCC reads still proceed.
• Shared (S) lock: taken by SELECT ... FOR SHARE. Others can also share-lock; nobody can write until released.
• Gap and next-key locks (InnoDB): lock the gap between index records so no one can insert into a range you scanned with a locking read. This is how InnoDB prevents phantoms at Repeatable Read, and why a locking read on an unindexed column can lock far more than you expect.

Pattern 1: atomic conditional update (best when possible)

UPDATE accounts SET balance = balance - 300
WHERE id = 1 AND balance >= 300;
-- check affected rows: 1 = success, 0 = insufficient funds

One statement, one row lock, no window between check and act. Works the same at any isolation level. Inventory decrements in flash sales use exactly this (see flash sale checkout design).

Pattern 2: pessimistic locking with SELECT FOR UPDATE

When the decision needs application logic (fraud checks, multiple rows, external rules):

conn.setAutoCommit(false);
try (PreparedStatement sel = conn.prepareStatement(
        "SELECT balance FROM accounts WHERE id = ? FOR UPDATE")) {
    sel.setLong(1, accountId);
    ResultSet rs = sel.executeQuery();
    if (!rs.next()) throw new IllegalStateException("no account");
    long balance = rs.getLong(1);
    if (balance < amount) { conn.rollback(); return false; }
    try (PreparedStatement upd = conn.prepareStatement(
            "UPDATE accounts SET balance = ? WHERE id = ?")) {
        upd.setLong(1, balance - amount);
        upd.setLong(2, accountId);
        upd.executeUpdate();
    }
    conn.commit();
    return true;
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

A concurrent transaction running the same code blocks on its FOR UPDATE until the first commits, then reads the new balance. For transfers between two accounts, lock rows in a consistent order (lower ID first) to avoid deadlocks. FOR UPDATE NOWAIT fails immediately instead of waiting, and FOR UPDATE SKIP LOCKED skips locked rows, which is the standard way to build a job queue on Postgres or MySQL 8.

Pattern 3: optimistic concurrency with a version column

def debit(conn, account_id, amount, retries=3):
    for _ in range(retries):
        with conn.cursor() as cur:
            cur.execute("SELECT balance, version FROM accounts WHERE id = %s",
                        (account_id,))
            balance, version = cur.fetchone()
            if balance < amount:
                conn.rollback()
                return False
            cur.execute(
                "UPDATE accounts SET balance = %s, version = version + 1 "
                "WHERE id = %s AND version = %s",
                (balance - amount, account_id, version))
            if cur.rowcount == 1:
                conn.commit()
                return True
            conn.rollback()          # someone else won; re-read and retry
    raise RuntimeError("too much contention")

No locks are held while the application thinks. Ideal for low-contention edits (profile updates, document saves, admin panels) and for flows that span user think-time. Under heavy contention, retries waste work and pessimistic locking wins. JPA/Hibernate implements this with @Version.

Pattern 4: Serializable with retry

import psycopg
from psycopg import errors

def run_serializable(conn, fn, attempts=5):
    # conn = psycopg.connect(dsn, autocommit=True); transaction() issues BEGIN
    for _ in range(attempts):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                return fn(conn)
        except errors.SerializationFailure:
            continue    # SQLSTATE 40001: safe to retry the whole transaction
    raise RuntimeError("could not serialize after retries")

This fixes write skew (the doctors example) without you having to identify what to lock. The cost is retries, and every transaction using it must be safe to re-run from the start.

Choosing an Isolation Level in Practice

Two additional rules from production experience. Keep transactions short: never hold a transaction open across a network call to another service or a payment gateway. And remember isolation is per database: as soon as an operation spans two services or two shards, ACID no longer covers it, and you need patterns like sagas and the transactional outbox (saga pattern and distributed transactions).

ACID and Isolation Interview Questions (with Answers)

These come up in DBMS rounds for freshers (also see DBMS interview questions) and in backend and system design rounds for experienced engineers.

Q1. Explain ACID with a bank transfer example.

Atomicity: debit and credit both happen or neither. Consistency: total money is conserved and balances never go negative (CHECK constraint). Isolation: a concurrent balance check doesn't see the debit without the credit. Durability: after commit, a power failure doesn't undo the transfer because the WAL was flushed.

Q2. How is atomicity implemented?

Through logging. Changes are recorded in a log before data pages are written. On crash recovery, the database replays committed work from the redo/WAL and undoes uncommitted work (InnoDB uses its undo log; Postgres simply never marks the aborted transaction as committed, so its tuples stay invisible).

Q3. Two users click "Book" on the last seat at the same moment. What happens and how do you prevent double booking?

Without protection, both read "available", both insert a booking. Prevent it with a unique constraint on (show_id, seat_id) so the second insert fails, or a conditional update UPDATE seats SET status='BOOKED', user_id=? WHERE id=? AND status='AVAILABLE' and check affected rows. Mention seat holds with expiry for the payment window. See BookMyShow system design.

Q4. Is Repeatable Read enough to prevent double booking?

Not if the code does SELECT-then-INSERT into a new row: under snapshot isolation both transactions see no booking and both insert different rows (write skew/phantom). A unique constraint or Serializable fixes it.

Q5. What's the difference between optimistic and pessimistic locking?

Pessimistic locks rows up front (FOR UPDATE), so conflicting transactions wait; good under high contention. Optimistic checks at write time via a version column and retries on conflict; good when conflicts are rare and you don't want to hold locks across user think-time.

Q6. What causes deadlocks and how do databases handle them?

Two transactions each hold a lock the other needs (T1 locks A then wants B; T2 locks B then wants A). Databases detect the cycle and abort one transaction with a deadlock error. Prevent it by acquiring locks in a consistent order and keeping transactions short; always retry on deadlock errors.

Q7. Why might a long-running transaction hurt a Postgres database?

Its snapshot prevents VACUUM from removing dead tuples newer than it, causing bloat and slower scans; it may also hold locks that block DDL. Set idle_in_transaction_session_timeout and monitor pg_stat_activity.

Q8. Do NoSQL databases support ACID?

Many now do within limits: MongoDB supports multi-document transactions (since 4.0, sharded since 4.2), DynamoDB has TransactWriteItems, and distributed SQL databases (Spanner, CockroachDB) offer serializable transactions across nodes. Historically many NoSQL stores offered only single-key atomicity, and that is still the cheapest path.

Frequently Asked Questions

Pranjal Jain

Ex-Microsoft SDE · IIT Kanpur · Founder of Prepflix. Helps engineers crack startup and product company interviews across India.