ACID Transactions and Isolation Levels

A transaction groups several reads and writes into one logical unit that either takes effect completely or not at all. Databases provide transactions so that application code does not have to handle partial failures and interleaving of concurrent requests. The guarantees are summarized as ACID, and the strength of the isolation guarantee is configurable.

ACID

Consistency in ACID is different from consistency in the CAP theorem or in replication. In ACID it depends on the application defining correct invariants; the database enforces only those it can express as constraints.

Atomicity example: transferring 100 from account A to B is two updates. If the process crashes after debiting A, atomicity ensures the debit is rolled back.

Concurrency anomalies

When transactions run concurrently, these anomalies can occur.

• Dirty read. T1 reads data written by T2 before T2 commits. If T2 rolls back, T1 used data that never existed.
• Dirty write. T1 overwrites a value written by uncommitted T2.
• Non-repeatable read (fuzzy read). T1 reads a row twice and gets different values because T2 updated and committed between the reads.
• Phantom read. T1 runs a query with a predicate twice (WHERE status = 'open') and gets different sets of rows because T2 inserted or deleted matching rows.
• Lost update. T1 and T2 both read a value, compute a new value, and write. One write overwrites the other.
• Write skew. T1 and T2 read overlapping data, then each writes to different rows based on what it read, and together they violate an invariant. Example: the invariant is that at least one doctor stays on call. Two doctors are on call. Each transaction checks that the count is at least 2 (so that one remains after leaving), then removes its own doctor. Both checks pass against the same snapshot, both writes succeed, and zero doctors remain.

sequenceDiagram
    participant T1 as Transaction 1
    participant DB as Database
    participant T2 as Transaction 2
    T1->>DB: read on_call count (2)
    T2->>DB: read on_call count (2)
    T1->>DB: set alice off call
    T2->>DB: set bob off call
    T1->>DB: commit
    T2->>DB: commit
    Note over DB: invariant "at least 1 on call" violated

Write skew is not prevented by snapshot isolation because the two transactions write different rows, so no write-write conflict is detected.

SQL isolation levels

The SQL standard defines levels by which anomalies they permit.

The standard's definitions are ambiguous and implementations differ:

• Read committed is the default in PostgreSQL and Oracle. Each statement sees data committed before that statement began.
• Repeatable read is the default in MySQL InnoDB. In PostgreSQL it is implemented as snapshot isolation: the transaction sees a snapshot from its start, which also prevents phantoms for reads, but write skew remains possible. In InnoDB, plain reads use a snapshot, and locking reads (SELECT ... FOR UPDATE) and writes use next-key locks (a row lock plus a lock on the gap before it), which block inserts into the scanned range.
• Serializable guarantees that the outcome equals some serial execution of the transactions. PostgreSQL implements it with serializable snapshot isolation (SSI), which detects dangerous dependency patterns and aborts one transaction. Applications must retry aborted transactions.

The exact anomalies permitted at each level depend on the product. Consult the documentation of the specific database.

Implementing isolation

Locking (two-phase locking)

In two-phase locking (2PL), a transaction acquires shared locks to read and exclusive locks to write, and holds all locks until commit or abort. Readers block writers and writers block readers. Serializability is achieved by locking the rows read and, for predicates, index ranges (predicate or next-key locks). The costs are lower concurrency and deadlocks, which the database detects and resolves by aborting one transaction.

Multi-version concurrency control (MVCC)

MVCC keeps multiple versions of each row. Each version is tagged with the transaction that created it (and, in some designs, the one that deleted it). A reading transaction uses a snapshot, the set of transactions committed at a point in time, and sees only versions visible in it.

flowchart LR
    W["Writer tx 20 updates row"] --> V2["row version 2, created by tx 20"]
    V1["row version 1, created by tx 5"] -.->|"replaced"| V2
    R["Reader with snapshot before tx 20"] --> V1
    R2["Reader with snapshot after tx 20 commit"] --> V2

• Readers do not block writers, and writers do not block readers.
• Two writers to the same row still conflict. The second waits for the first to finish. Under snapshot isolation it then aborts if the first committed (PostgreSQL at repeatable read behaves this way; the general rule is called first-committer-wins).
• Old versions must be cleaned up (VACUUM in PostgreSQL, purge in InnoDB). Long-running transactions keep old versions alive and cause bloat.

PostgreSQL, MySQL InnoDB, Oracle, and SQL Server (with snapshot options) use MVCC.

Preventing lost updates and write skew

• Atomic operations: UPDATE accounts SET balance = balance - 100 WHERE id = 1 avoids read-modify-write in the application.
• Explicit locks: SELECT ... FOR UPDATE locks the selected rows until commit. It can also protect rows that a write-skew scenario reads.
• Unique or exclusion constraints turn some invariants into database-enforced constraints.
• Serializable isolation with retries.

Pessimistic vs optimistic concurrency control

Optimistic update with a version column:

UPDATE items SET stock = 9, version = 8
WHERE id = 42 AND version = 7;
-- 0 rows affected means another writer changed the row; re-read and retry

Transactions across nodes

ACID transactions inside one node are cheap relative to transactions spanning nodes. Across shards or services, options are two-phase commit or, between microservices, the saga pattern. Many NoSQL systems limit atomicity to a single record or partition (see SQL vs NoSQL).

Interview questions

• What does snapshot isolation prevent, and what does it not? It prevents dirty reads, non-repeatable reads, and (in implementations that detect write-write conflicts, such as PostgreSQL) lost updates on the same row. It permits write skew, because transactions writing different rows do not conflict. MySQL InnoDB's repeatable read does not abort on lost updates, so explicit locks or atomic updates are needed there.
• Why do long-running transactions hurt an MVCC database? Old row versions cannot be removed while any active snapshot may still need them, so storage grows and scans slow down until the transaction ends.
• When would you use SELECT ... FOR UPDATE? When a transaction reads a row and then writes based on it, and concurrent modification must be blocked, for example reserving a limited stock item. It serializes access to those rows.
• How is durability achieved without writing every change to the data files immediately? The database appends changes to a write-ahead log and flushes it to disk before acknowledging the commit. Data pages are written later and rebuilt from the log after a crash.
• How does optimistic concurrency control detect a conflict? Each row carries a version; the update includes the version read earlier in its WHERE clause. If another transaction changed the row, zero rows match and the writer retries.