The SQL standard defines four isolation levels, and every database course teaches them as a table: dirty reads permitted here, non repeatable reads there, range read anomalies one row down. Production code meets the same levels as three concrete bugs instead: the financial report that changes its own totals while it runs, the duplicate account born from an existence check that passed twice, and the balance that quietly loses an update nobody committed wrong. This article walks the levels through code an application actually runs, shows where each anomaly appears in real sessions, and builds the locking and retry discipline that keeps an application correct whichever level the server ships as its default.
The four standard levels and what modern engines actually deliver
The standard ladder has four rungs. READ UNCOMMITTED permits reading rows another transaction has written but not committed, the dirty read, which means reading data that may never exist. READ COMMITTED removes the dirty read but lets a row change between two reads inside one transaction, the non repeatable read. REPEATABLE READ pins the values of rows already read but permits new matching rows to appear on a re query, the range read anomaly. SERIALIZABLE promises the result of some serial execution, with no anomalies at all.
Modern engines quietly raise the floor. PostgreSQL and InnoDB do not permit dirty reads under any setting, and a request for READ UNCOMMITTED is satisfied as READ COMMITTED. The practical ladder has three rungs, and each engine positions them differently: PostgreSQL defaults to READ COMMITTED, adds a snapshot based REPEATABLE READ above it, and offers true SERIALIZABLE built on Serializable Snapshot Isolation. InnoDB defaults to REPEATABLE READ and enforces it with next-key locks strong enough that many range read surprises cannot occur even though the standard permits them. An application written against the standard table therefore behaves differently per engine, and the only portable ground truth is what the code itself guarantees, which is what the rest of this article builds.
READ COMMITTED and the report that moved under its own cursor
Under READ COMMITTED every statement takes a fresh snapshot. Each query alone is consistent, but two statements inside one transaction see different worlds, and the gap is where reports go wrong. A billing summary demonstrates it in three statements:
BEGIN;
SELECT sum(amount) FROM payments WHERE created_at < now();
SELECT count(*) FROM invoices WHERE status = 'open';
SELECT sum(balance) FROM accounts;
COMMIT;While this transaction runs, payments keep arriving. The first sum sees the ledger as it was at that moment, the third sees it seconds later, and a report that reconciles those three numbers against a fourth will not reconcile. No row was read twice, so no non repeatable read occurred, yet the report contradicts itself because its statements sampled a moving world.
The repair is one line and it is chosen per transaction, not per database:
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;Now the snapshot is taken once and every statement inside the transaction reads the same frozen world. The lesson generalizes into the first working rule: read consistency is a property the application requests for the transactions that need it. The default serves short operational statements well, and reporting transactions opt into the stronger level explicitly, which costs concurrency discipline in exchange for numbers that hold still.
The check then insert race behind duplicate registrations
The range read anomaly lives in production as the check then insert race. The code is always some variation of the same three lines: look for the username, and if the count is zero, create the account:
SELECT count(*) FROM users WHERE email = Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript. ';
-- returns 0
INSERT INTO users (email, name)
VALUES (Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript. ', 'A. Buyer');Two requests arrive inside the same millisecond, both counts return zero, both inserts succeed, and the invariant one account per email is gone. Nothing in the READ COMMITTED table names this a violation, because each transaction read committed data and wrote a valid row. The anomaly only exists at the level of the intended business rule, which is why the engine cannot be blamed and the schema must be trusted instead.
The reliable fix ranks three options. The first and strongest is the unique constraint, which turns the race into an error one request wins and the other receives:
ALTER TABLE users ADD CONSTRAINT users_email_uniq UNIQUE (email);and the application treats the duplicate key error, SQLSTATE 23505, as a normal control flow path, not an exception that reaches the logs as a surprise. The second is the advisory lock, a session scoped queue key that serializes contenders without touching the table:
SELECT pg_advisory_xact_lock(hashtext(Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript. '));The third is SERIALIZABLE plus retry, which the next section covers. A production schema should always carry the constraint regardless, because every application level lock is a convention, and conventions are what the next deployment forgets.
Lost updates that no level in the table names
The lost update is the anomaly the standard table does not assign to any level, because modern engines make the pure form hard to produce with plain SQL. The application version is everywhere. The pattern: read a balance into memory, compute the new value in application code, write it back unconditionally:
SELECT balance FROM accounts WHERE id = 7; -- reads 100
-- application adds interest, decides 110
UPDATE accounts SET balance = 110 WHERE id = 7;Two sessions run this against the same row from the same starting value, one computing 110 and one computing 90, and the last write wins with no error. The data written by the first session is silently discarded, which is the anomaly the textbooks lost.
Three safe patterns close it. Arithmetic inside the statement lets the engine add instead of the application:
UPDATE accounts SET balance = balance + 10 WHERE id = 7;The compare and swap guard writes only from the expected prior state and checks the affected row count:
UPDATE accounts SET balance = 110
WHERE id = 7 AND balance = 100;The pessimistic lock takes the row first and holds it:
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;The choice is workload shaped. Counters and balances take arithmetic, optimistic flows take the guard, and multi row consistency inside one transaction takes the lock. One boundary rule belongs with all three: transactions must not span the user's think time. A transaction held open while it waits for an external service or a human decision accumulates locks and replication lag for nothing, and every pattern above assumes the transaction is short, scoped, and free of network calls it does not own.
Write skew as the anomaly snapshot isolation does not catch
Between the lost update and the serialization anomaly sits a subtler bug that snapshot based REPEATABLE READ permits and SERIALIZABLE refuses. Two transactions each read a set of rows, each makes a decision based on what they saw, and each writes to a row the other only read. The canonical case is the on call schedule: two doctors are on call, both ask to drop their shift after checking that the other remains, both checks pass against the shared snapshot, both updates commit, and the night has nobody on call. No row was written twice, no constraint exists to enforce the rule, and the invariant was cross row from the beginning.
The same shape appears as the double booking of the last inventory item across two warehouses, the simultaneous approval of two budget lines that jointly exceed a cap, and any rule the application states as at least one or at most one across rows. Snapshot isolation permits all of them, because each transaction wrote a disjoint set of rows, and only SERIALIZABLE sees the read write dependency that connects the two decisions. A team that wants such rules enforced by the engine rather than by hope chooses SERIALIZABLE for exactly those transactions, which is the narrow, honest answer to when the top level is worth its retry cost. The weaker alternative is to materialize the rule as a single row both sides must update, the counter pattern from the lost update section, which converts a cross row invariant into a row level race the engine can lock.
SERIALIZABLE in practice as a retry discipline
SERIALIZABLE in PostgreSQL is not a bigger lock, it is a different execution model. SSI watches for read and write dependencies between transactions, and when two concurrent transactions could only be serialized in an order the engine cannot prove, one of them is aborted with the error the server states plainly: could not serialize access due to read write dependencies among transactions, SQLSTATE 40001. The application catches the error and retries, and the retry must be written as ordinary control flow, because under contention it is not exceptional at all.
A correct retry loop is bounded, it sleeps with jitter so contending sessions do not synchronize their retries, and above all it is idempotent. Retrying an INSERT that already committed creates the duplicate the retry was supposed to prevent, so the retried unit of work must carry a natural key or a client request id that makes the second attempt a no operation. The classic shape:
attempts = 0
begin
while attempts < 3:
try:
run_transaction()
break
except serialization_failure:
attempts += 1
sleep(random_jitter())
else:
queue_for_manual_review()The retry loop also serves deadlock errors, which arrive as SQLSTATE 40P01 in PostgreSQL and 40001 in InnoDB, and one loop handles both engines. What serializable execution cannot absorb is a hot row: a counter every request updates will abort a steady share of transactions no matter the retry budget, and that workload belongs behind arithmetic updates or a dedicated sequence, not behind optimistic retries.
The per transaction decision rules that survive production
No single level is correct for a whole application, because workloads mix reporting, registration and ledger updates in one service. The durable rules are per transaction, and the working set is short:
- Keep READ COMMITTED as the default and raise the level per transaction that needs a stable view;
- Enforce every business uniqueness rule with a constraint and treat the duplicate key error as normal flow;
- Write balances and counters as arithmetic updates, never as values computed in application memory;
- Take one snapshot explicitly for any report that reconciles several statements;
- Add a bounded retry loop with jitter wherever SERIALIZABLE or deadlocks are reachable;
- Keep every transaction short, with no external calls and no user think time inside its bounds.
Isolation levels are an interface between the engine's guarantees and the application's assumptions. The team that treats them as a table to memorize writes code that works on the demo dataset, and the team that treats them as per transaction decisions, backed by constraints, guarded updates and honest retries, writes code that keeps its invariants on the weekend when the two requests arrive in the same millisecond.
Savepoints and the partial rollback that keeps long transactions short
The discipline of short transactions collides with a real application need: a unit of work that attempts several operations and must undo only the last one when it fails, without discarding the work before it. The savepoint is the mechanism, a named marker inside the transaction that a rollback can target:
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (42, 120.00);
SAVEPOINT item_1;
INSERT INTO order_items (order_id, sku, qty)
VALUES (currval('orders_id_seq'), 'sku-9', 1);
-- a stock violation aborts the statement
ROLLBACK TO SAVEPOINT item_1;
-- the order survives, the failed item is retried or skipped
COMMIT;The pattern powers batch imports, where one bad row out of ten thousand must not discard the batch, and interactive flows, where a user's rejected action must not roll back their session's earlier confirmed actions. The savepoint keeps the transaction short relative to the unit of work, because the alternative, one transaction per row, trades the consistency of the unit for the failure tolerance of the parts, and the design question is always which granularity the business rule actually requires.