Transaction isolation architecture, in depth: what each engine's levels really mean, reproducible anomalies and retry-safe code

Isolation is a list of forbidden anomalies

The SQL standard defines its four levels by three phenomena: dirty reads, non-repeatable reads and phantoms. Berenson and colleagues showed in 1995 that this is incomplete, because a level that forbids all three can still produce non-serializable results. A practical list has seven entries:

Dirty writes are forbidden by every real engine at every level. The rest are what you choose between. Write skew is the one to remember, because snapshot isolation allows it and snapshot isolation is what many engines give you when you ask for something that sounds strong.

Three mechanisms

Two-phase locking. Transactions acquire locks as they go and release them only at the end. Reads take shared locks, writes take exclusive locks, and serializable locking also locks ranges so that inserts into a scanned range wait. It is serializable when done fully, and its costs are blocking and deadlocks: readers wait for writers and vice versa, and cycles in the wait-for graph must be broken by killing a victim, as described in deadlock detection.

Snapshot isolation. Each transaction reads from a consistent snapshot taken at its start, using multi-version storage, so reads never block and are never blocked. Writers still lock the rows they change, and if two concurrent transactions update the same row, the second to commit, or to try to update, fails. That first-committer-wins rule prevents lost updates on a single row, but not write skew, because two transactions writing different rows never conflict.

Serializable snapshot isolation. SSI, from Cahill, Rohm and Fekete, keeps snapshot reads and adds bookkeeping: it records what each transaction read, and notices when a transaction both has a read-write dependency on one concurrent transaction and is the source of another. That structure, a pivot, is necessary for any serialization anomaly under snapshot isolation, so aborting one transaction when it appears is enough to guarantee serializability. It never makes readers wait, and it sometimes aborts transactions that would have been fine.

What the names mean in each engine

Three traps stand out. PostgreSQL's REPEATABLE READ is stronger than the standard asks, because it forbids phantoms, but it is still snapshot isolation and allows write skew. MySQL's REPEATABLE READ mixes two views in one transaction: plain SELECTs see the snapshot from the first read, while UPDATE, DELETE and locking reads act on the latest committed rows. And Oracle's SERIALIZABLE, which reports failures as ORA-08177, is snapshot isolation, so write skew is possible there too. Distributed SQL databases add their own mappings; read the vendor's isolation page before assuming anything, and test.

Reproduce a lost update

Open two psql sessions against a PostgreSQL database at the default READ COMMITTED level. The application pattern here, read a value, compute in the application, write it back, is extremely common:

CREATE TABLE account (id int PRIMARY KEY, balance int);
INSERT INTO account VALUES (1, 100);

-- Session A                                -- Session B
BEGIN;                                      BEGIN;
SELECT balance FROM account WHERE id = 1;   SELECT balance FROM account WHERE id = 1;
-- app computes 100 + 10                    -- app computes 100 + 20
UPDATE account SET balance = 110 WHERE id = 1;
                                            UPDATE account SET balance = 120 WHERE id = 1;
                                            -- blocks until A commits
COMMIT;
                                            -- proceeds, overwrites 110
                                            COMMIT;
SELECT balance FROM account WHERE id = 1;   -- 120: the +10 is lost

Rerun it with BEGIN ISOLATION LEVEL REPEATABLE READ in both sessions. Session B's UPDATE now fails when A commits, with could not serialize access due to concurrent update and SQLSTATE 40001, and the application must retry B from the beginning. Under MySQL InnoDB's default REPEATABLE READ the same script loses the update, because B's UPDATE writes an absolute value computed from a stale snapshot read. Three fixes work everywhere: write the update relative to the current row (SET balance = balance + 20), lock the row when reading it (SELECT ... FOR UPDATE), or add a version column and make the UPDATE conditional on it.

Reproduce write skew

The classic example: a hospital requires at least one doctor on call. Alice and Bob are both on call and both feel unwell. Each transaction checks that someone else is on call, then takes itself off:

CREATE TABLE oncall (doctor text PRIMARY KEY, on_call boolean);
INSERT INTO oncall VALUES ('alice', true), ('bob', true);

-- Session A (Alice)                         -- Session B (Bob)
BEGIN ISOLATION LEVEL REPEATABLE READ;       BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall WHERE on_call;   SELECT count(*) FROM oncall WHERE on_call;
-- 2, so it is safe to leave                 -- 2, so it is safe to leave
UPDATE oncall SET on_call = false            UPDATE oncall SET on_call = false
 WHERE doctor = 'alice';                      WHERE doctor = 'bob';
COMMIT;                                      COMMIT;
-- both succeed: nobody is on call

The two transactions updated different rows, so first-committer-wins never fired. Change both to ISOLATION LEVEL SERIALIZABLE and one of the commits fails with could not serialize access due to read/write dependencies among transactions, SQLSTATE 40001. PostgreSQL's SSI saw that each transaction read data the other then wrote. Without SERIALIZABLE, the fix is to materialise the conflict: lock the rows the rule depends on with SELECT ... FOR UPDATE, or lock a single row that represents the shift, so the two transactions collide on something.

The retry loop is part of the contract

Above READ COMMITTED, and at any level with locking, the database will sometimes abort a transaction that did nothing wrong. That is the price of not blocking forever. The application must catch those errors and rerun the whole transaction, including its reads, because the decision it made was based on data that is now stale. Retrying only the failed statement is a bug.

import random, time
import psycopg

RETRYABLE = {"40001", "40P01"}   # serialization_failure, deadlock_detected

def run_serializable(conn, work, max_attempts=5):
    for attempt in range(1, max_attempts + 1):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                return work(conn)          # all reads and writes happen inside
        except psycopg.Error as e:
            if e.sqlstate not in RETRYABLE or attempt == max_attempts:
                raise
            time.sleep(min(0.5, 0.01 * 2 ** attempt) * random.random())

def go_off_call(conn, doctor):
    n = conn.execute("SELECT count(*) FROM oncall WHERE on_call").fetchone()[0]
    if n < 2:
        raise ValueError("cannot leave: last doctor on call")
    conn.execute("UPDATE oncall SET on_call = false WHERE doctor = %s", (doctor,))

The work function must be safe to run more than once, so no emails, HTTP calls or other side effects inside it; collect them and perform them after the commit, or write them to an outbox table in the same transaction. On MySQL the retryable errors are deadlocks (error 1213, SQLSTATE 40001) and, if you choose, lock wait timeouts (error 1205); on SQL Server, deadlock victims (1205) and snapshot update conflicts (3960); on Oracle, ORA-08177. Cap attempts and alert when the retry rate climbs, because a rising rate means contention that code changes, not retries, must fix.

Choosing a level per transaction

Isolation can be set per transaction, so choose per workload instead of one global setting:

• Single-statement writes such as UPDATE ... SET x = x + 1 are safe at READ COMMITTED in every engine above, because the statement reads and writes the current row atomically.
• Read-modify-write on one row: READ COMMITTED with SELECT ... FOR UPDATE, or an optimistic version check. Both are portable.
• Rules spanning several rows, like uniqueness across a range, capacity limits or on-call rules: PostgreSQL SERIALIZABLE with a retry loop, or explicit locks on the rows the rule depends on. Do not rely on REPEATABLE READ or Oracle SERIALIZABLE here.
• Consistent reports and exports: a snapshot level, REPEATABLE READ in PostgreSQL or MySQL, so every query sees one point in time. In PostgreSQL, SERIALIZABLE READ ONLY DEFERRABLE waits for a safe snapshot and then runs without any risk of serialization failure.
• Constraints beat isolation wherever they can express the rule. A unique index or exclusion constraint is enforced at every level and cannot be forgotten by a new code path.

Failure modes and operations

• Long transactions. An open snapshot pins old row versions, which blocks vacuum in PostgreSQL and grows the undo history in InnoDB. Set statement and idle-in-transaction timeouts, and keep reporting queries on a replica.
• Retry storms. A hot row under SERIALIZABLE turns contention into abort-and-retry loops. Watch the rate of 40001 errors per endpoint and redesign hot rows, for example by splitting a counter into several rows.
• SSI memory and false positives. PostgreSQL tracks reads with SIReadLock predicate locks, visible in pg_locks. They never block, but they consume shared memory and are promoted to coarser locks when there are too many, which raises false-positive aborts. Index the columns your serializable queries filter on, so the locks stay fine-grained, and raise max_pred_locks_per_transaction if promotions are common.
• Lock waits masquerading as slowness. Under locking levels, latency spikes are often waits. Sample lock views during incidents before tuning queries.
• Silent level changes. Connection pools, ORMs and database-level settings such as SQL Server's READ_COMMITTED_SNAPSHOT change what the default means. Log the effective isolation level at connection start in each service.

What to do next

1. Find out which level each service actually runs at, from the connection settings, not from assumptions.
2. Search your code for read-then-write patterns and convert them to relative updates, FOR UPDATE, or version checks.
3. List invariants that span rows, and enforce each with a constraint, SERIALIZABLE, or explicit locks.
4. Wrap every transaction above READ COMMITTED in a retry loop keyed on SQLSTATE 40001 and deadlock codes, with no side effects inside.
5. Run the lost-update and write-skew scripts above against your own engine and version, and keep them as regression tests.
6. Alert on serialization-failure and deadlock rates, and on the age of the oldest open transaction.