SQL Isolation Levels

01

Start with a history, not a label

A transaction reads a state, makes a decision, and attempts to publish a new state. Isolation asks whether concurrent decisions can be ordered as if they happened one after another.

The final value may be 7, silently erasing A's update. Or the second writer may wait, restart, or fail. All are implementation choices. The application contract must say which outcome is acceptable.

02

Name the anomaly before choosing the remedy

The classic SQL phenomena are useful vocabulary, but production failures are easier to reason about as dependency cycles: what did each transaction read, and which facts did it invalidate?

-- Fragile read-modify-write
SELECT balance FROM account WHERE id = 42;
-- application computes a new value
UPDATE account SET balance = :new_balance WHERE id = 42;

-- Prefer one atomic statement when the rule is local to the row
UPDATE account
SET balance = balance - :amount
WHERE id = 42
  AND balance >= :amount;

The second statement turns the predicate and mutation into one database operation. It narrows the isolation problem instead of asking a broad isolation level to repair an avoidable race.

03

The isolation spectrum is not one-dimensional

SQL names describe intent, but engines reach that intent through different mechanisms. The most important distinction is often not the name; it is whether the snapshot is refreshed per statement or held for the transaction, and whether dangerous histories are blocked or detected.

Statement snapshot versus transaction snapshot

“Serializable” is a semantic guarantee. “Snapshot” is a visibility mechanism. One does not imply the other.

04

MVCC removes read/write blocking, not coordination

Multi-Version Concurrency Control lets readers find a visible committed version while writers create a new one. This is excellent for concurrency, but old versions cannot enforce a future business invariant.

Locks and MVCC solve different parts of the problem:

• MVCC visibility chooses which committed version a reader can see.
• Row or key locks coordinate writers that touch the same protected object.
• Predicate protection covers facts such as “no row exists in this range.”
• Serialization detection observes dependency patterns and aborts a transaction before an impossible history commits.

BEGIN;
SELECT balance
FROM account
WHERE id = 42
FOR UPDATE;

-- The row now represents the coordination point.
UPDATE account SET balance = balance - 50 WHERE id = 42;
COMMIT;

SELECT FOR UPDATE expresses an intention to modify the selected rows. It cannot lock a row that does not exist, and it does not automatically protect an arbitrary aggregate or predicate.

06

Same SQL name, different operational contract

Portable SQL syntax does not make concurrency behavior portable. Defaults, snapshots, lock timing, retry errors, and even the meaning behind a level name vary.

Why Oracle “serializable” needs qualification

Oracle's level historically called SERIALIZABLE gives a transaction-consistent snapshot and rejects some conflicting updates with ORA-08177. Snapshot isolation is stronger than ordinary Read Committed, but a stable snapshot alone is not the general definition of serializability. The distinction matters for write skew and predicate-based invariants.

Why distributed SQL makes retries visible

A distributed database coordinates keys across nodes and clocks. Waiting forever would preserve correctness but destroy availability and latency. Optimistic conflict detection, transaction restarts, and explicit retry errors are therefore not defects; they are part of the isolation API.

07

Design patterns that make isolation explicit

UPDATE inventory
SET quantity = quantity - :requested
WHERE sku = :sku
  AND quantity >= :requested;

UPDATE document
SET body = :body, version = version + 1
WHERE id = :id
  AND version = :version_read;

SELECT id FROM shift
WHERE id = :shift_id
FOR UPDATE;

for attempt in retry_budget:
    try:
        begin_serializable()
        apply_business_transaction()
        commit()
        break
    except SerializationFailure:
        rollback()
        backoff(attempt)

The retry boundary is the transaction boundary

Do not retry only the failed statement: its inputs may have come from a now-invalid snapshot. Re-run every read and decision in the transaction. Keep emails, payments, and messages idempotent or publish them through an outbox committed with the data.

08

A decision guide for real applications

Test the history, not just the happy path

• Open two independent sessions and pause them at controlled points.
• Record values read, locks waited on, errors returned, and final committed state.
• Test the configured default and every level the application explicitly requests.
• Repeat after database upgrades and driver or connection-pool changes.
• Monitor serialization failures and deadlocks as expected concurrency signals, not generic server faults.