Transactions, ACID, and Isolation Levels

A transaction is a unit of work that must execute as an indivisible whole. The canonical example is a bank transfer: debit one account, credit another. If the system crashes between the two steps, money must not vanish. ACID is the set of guarantees that makes this safe, and isolation levels are the tunable knob that trades some of that safety for concurrency. This is one of the densest and most-tested topics in database interviews.

The four ACID properties

• Atomicity: all operations in a transaction succeed, or none do. A partial transfer is impossible; the database rolls back on failure. Atomicity is enforced by the undo log (or rollback segment), which records how to reverse each change.
• Consistency: a transaction moves the database from one valid state to another, respecting all constraints (foreign keys, unique constraints, checks). Consistency is a property the application and constraints uphold; the database guarantees that a committed transaction never leaves broken invariants.
• Isolation: concurrent transactions do not interfere; the result is as if they ran in some serial order. Isolation is the property we relax for performance.
• Durability: once committed, changes survive crashes and power loss. Durability is enforced by the write-ahead log (WAL): changes are written to a sequential log and flushed to disk before the commit returns, so recovery can replay them even if the data pages were still in memory.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both persist, or ROLLBACK undoes both

The read phenomena (anomalies)

Isolation levels are defined by which of four anomalies they permit. Understanding each precisely is essential:

• Dirty read: a transaction reads data written by another transaction that has not yet committed. If that other transaction rolls back, you read a value that never officially existed.
• Non-repeatable read: a transaction reads a row, another transaction updates and commits it, and the first transaction re-reads the row and sees a different value. The same query, run twice, returns different data.
• Phantom read: a transaction runs a range query, another transaction inserts a new row matching that range and commits, and re-running the query returns extra rows — "phantoms" that appeared.
• Lost update: two transactions read the same value, both modify it, and the second write silently overwrites the first.

The following widget lets you pick an anomaly and an isolation level and watch, on a two-transaction timeline, whether the anomaly can occur.

The four standard isolation levels

The SQL standard defines four levels, each forbidding more anomalies at the cost of concurrency:

| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | |-----------------|-----------|---------------------|--------------| | READ UNCOMMITTED | Possible | Possible | Possible | | READ COMMITTED | Prevented | Possible | Possible | | REPEATABLE READ | Prevented | Prevented | Possible* | | SERIALIZABLE | Prevented | Prevented | Prevented |

* In the ANSI standard, REPEATABLE READ allows phantoms. Note that real databases vary: PostgreSQL's REPEATABLE READ (snapshot isolation) actually prevents phantoms, while MySQL/InnoDB's REPEATABLE READ uses next-key locks to block most phantoms. Always name the specific engine when discussing behavior.

How each level works

• READ UNCOMMITTED: essentially no read isolation; you can see uncommitted data. Rarely used intentionally.
• READ COMMITTED: you only see committed data, but each statement gets a fresh view — hence non-repeatable reads. This is the default in PostgreSQL and Oracle and a sensible default for most OLTP workloads.
• REPEATABLE READ: the transaction sees a consistent snapshot as of its start; repeated reads return the same values. This is the default in MySQL/InnoDB.
• SERIALIZABLE: the strongest level; transactions behave as if executed one at a time. It prevents all anomalies but has the highest contention and abort rate.

The consistency-vs-performance trade-off

Higher isolation means more locking (or more aborts under optimistic schemes) and lower throughput. The engineering skill is choosing the lowest level that is still correct for your workload. Most systems run at READ COMMITTED and handle the rare lost-update risk with explicit techniques rather than paying for SERIALIZABLE everywhere.

Preventing lost updates explicitly

Because READ COMMITTED and even REPEATABLE READ can permit lost updates in a read-modify-write pattern, two standard tools exist:

• Pessimistic locking: SELECT ... FOR UPDATE locks the row so no one else can modify it until you commit.
• Optimistic concurrency control: add a version column, and on update assert WHERE version = <read_value>; if zero rows update, someone else changed it and you retry.

-- Optimistic: fails safely if another writer intervened
UPDATE accounts
SET balance = 500, version = version + 1
WHERE id = 1 AND version = 7;

What interviewers actually probe

1. Define each ACID letter and name the mechanism — atomicity/undo log, durability/WAL. Naming the machinery separates strong candidates.
2. Distinguish non-repeatable read from phantom read. Non-repeatable is an updated existing row; phantom is a newly inserted row in a range. This is the single most common isolation question.
3. Know real-world defaults. PostgreSQL/Oracle default to READ COMMITTED; MySQL/InnoDB to REPEATABLE READ.
4. Explain how to prevent lost updates with SELECT ... FOR UPDATE or a version column.
5. Articulate the trade-off: stronger isolation buys correctness with reduced concurrency and higher abort rates.

The framing to remember: ACID is the contract, isolation levels are the dial, and the anomalies are the units in which you measure how far you have turned that dial toward performance.