Databases at Scale

Transactions & Isolation Levels

A transaction bundles several operations into one all-or-nothing unit (atomicity), but isolation — what one transaction can see of another's uncommitted work — is a spectrum, not a switch, and the default on most databases is weaker than people assume.

ACID: Atomicity, Consistency, Isolation, Durability — isolation is the axis with the most real-world variance between databases
Anomalies from weak isolation: dirty reads (seeing uncommitted data), non-repeatable reads (a re-read within one transaction sees different data), phantom reads (a re-run query returns different rows)
Isolation levels from weakest to strongest: Read Uncommitted, Read Committed, Repeatable Read, Serializable — each rules out more anomalies at a concurrency cost
MVCC (multi-version concurrency control) is how most modern databases give readers a consistent snapshot without blocking writers — readers see an older version instead of waiting
A database's default isolation level is usually not Serializable — PostgreSQL defaults to Read Committed, and "Serializable" must be requested explicitly when correctness demands it

A lost update under weak isolation

Both transactions read the same starting value and overwrote each other's update

Isolation levels and what they rule out
Level	Dirty reads	Non-repeatable reads	Phantom reads	Concurrency cost
Read Uncommitted	Possible	Possible	Possible	Lowest
Read Committed	Prevented	Possible	Possible	Low
Repeatable Read	Prevented	Prevented	Possible (mostly)	Medium
Serializable	Prevented	Prevented	Prevented	Highest

Requesting isolation explicitly instead of trusting the default

try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

    int balance = readBalance(conn, accountId);
    if (balance < amount) throw new InsufficientFundsException();
    writeBalance(conn, accountId, balance - amount);

    conn.commit();
} catch (SQLException e) {
    // A serialization failure here means retry the whole transaction —
    // the database detected a conflict it could not silently resolve.
}

Serializable transactions can fail with a serialization error under contention — callers must retry