ACID & Isolation Levels

Transactions are governed by the ACID properties, which guarantee that database transactions are processed reliably.

ACID Properties

Atomicity

“All or nothing.” If any part of a transaction fails, the entire transaction is rolled back as if nothing happened.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- If server crashes here, the debit is rolled back
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Consistency

Transactions transform the database from one valid state to another. All constraints, triggers, and rules are preserved.

Isolation

Concurrent transactions should appear as if they ran one after another. The database provides isolation levels that let you trade strictness for performance.

Durability

Once a transaction is committed, the changes persist even if the server crashes immediately after. This is typically achieved through write-ahead logging (WAL).

Transaction Anomalies

These are problems that occur when isolation is insufficient:

Anomaly	Description	Prevention Level
Dirty Read	Reading uncommitted data from another transaction	Read Committed
Non-Repeatable Read	Same row read twice, gets different values	Repeatable Read
Phantom Read	Same range read twice, gets different rows	Serializable
Lost Update	Two transactions overwrite each other’s changes	Serializable / Optimistic Locking

Standard SQL Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
Read Uncommitted	Possible	Possible	Possible	Best
Read Committed	Prevented	Possible	Possible	Good
Repeatable Read	Prevented	Prevented	Possible	Medium
Serializable	Prevented	Prevented	Prevented	Worst

PostgreSQL Default: Read Committed

Most databases default to Read Committed. This prevents dirty reads but allows non-repeatable reads and phantoms. It is the right balance for most applications.

Serializable

The strictest level. Transactions are executed as if they ran sequentially. This prevents all anomalies but can significantly reduce concurrency. Use it only when absolute correctness is required (e.g., financial systems).

Snapshot Isolation (MVCC)

PostgreSQL, Oracle, and MySQL (InnoDB) use Multi-Version Concurrency Control (MVCC). Instead of locking rows for readers, the database keeps multiple versions of each row. Readers see a snapshot of the data as of the start of their transaction, so reads never block writes and writes never block reads.

In MVCC:

• READ COMMITTED: A new snapshot is taken for each statement.
• REPEATABLE READ: The same snapshot is used for the entire transaction.
• SERIALIZABLE: Uses snapshot isolation plus runtime conflict detection.