Transaction isolation levels

An isolation level is the contract between your transaction and every other transaction running at the same moment: which of their unfinished or freshly committed changes you may see, and which interleavings the database promises to rule out. The SQL standard names four levels, real databases implement them differently, and the gap between a level’s name and its guarantee is where wallets lose money and berths get sold twice.

Picture it first: the reservation chart

Think of the paper reservation chart pinned up on a railway platform. Clerks keep editing it; passengers keep reading it.

• Peek over a clerk’s shoulder while she is still pencilling in names, and you may read a booking she is about to rub out: read uncommitted.
• Read only inked entries, but look afresh each time. Two glances a minute apart can disagree: read committed.
• Photograph the chart when you arrive and read only the photo. Everything agrees, though it may be minutes old: snapshot isolation. The catch: two clerks working from their own photos can both decide “the coach has another examiner, so I can take mine off”.
• Let one clerk at a time touch the chart, or have an umpire cancel any edits that couldn’t have happened one clerk at a time: serializable.

Map it back: the chart is the database, clerks and passengers are transactions, pencil is an uncommitted write, ink is a commit, and the photo is a snapshot. Each step up costs more queueing at the chart or more edits cancelled and redone.

Why isolation exists

A database runs transactions concurrently because one at a time would waste the machine: while one waits on a disk read, a lock or the application’s next statement, hundreds of others can make progress. The price is interleavings that no transaction’s author pictured.

Isolation is the I in ACID. The ideal is serializability: the committed result equals running the same transactions one after another in some order. It composes: if each transaction is correct alone, a serializable database keeps them correct together.

To guarantee it, a database must either make transactions wait (locks held until commit, even on ranges of rows that don’t exist yet) or abort some at commit and make you retry. Hot rows become queues either way, and Oracle’s concepts guide warns that serializable mode can badly hurt throughput. So databases ship weaker levels and usually default to one: more concurrency and fewer errors, in exchange for anomalies your code must prevent.

Here is one such anomaly. A nightly job sums a user’s savings and wallet balances while a transfer moves ₹200 between them. At READ COMMITTED each statement takes a fresh snapshot, so the job reads savings before the transfer and the wallet after it, and reports ₹1,200 where ₹1,000 exists. Nothing was written wrongly; the report saw half a committed transaction.

Every isolation level answers one question: which interleavings may my transaction experience? To compare answers, you need names for them.

The anomaly catalogue

The 1995 paper by Berenson, Bernstein, Gray, Melton and the two O’Neils writes each anomaly as a short history: w1[x] means transaction 1 writes item x, r2[x] means transaction 2 reads it, c1 and a1 are commit and abort, and P is a predicate (a WHERE clause). P-numbers are the phenomena the standard meant; A-numbers were added by the paper. One paragraph each here; the linked pages go deep.

P0: dirty write

w1[x] … w2[x] … (c1 or a1). T2 overwrites an item T1 has written but not committed. If T1 rolls back, what should x become? If both write the same two rows in different orders, you get a mix: the berth row names passenger P2 while the coach chart names P1. Every mainstream database blocks this at every level by holding write locks until commit. More in dirty read.

P1: dirty read

w1[x] … r2[x] … (c1 or a1). You read a value that is not yet committed. A UPI debit of ₹2,000 is in flight; a fraud check reads the reduced balance and declines another payment, then the debit rolls back. The decision rested on money that never left. Only READ UNCOMMITTED allows it, and PostgreSQL runs that level as READ COMMITTED. See dirty read.

P2: non-repeatable (fuzzy) read

r1[x] … w2[x] … (c1 or a1). You read a row twice and another transaction’s commit in between changes the answer. A Flipkart-style checkout shows a ₹999 price, the seller changes it to ₹1,099, and the invoice step re-reads ₹1,099, so line items and total disagree. READ COMMITTED allows it. See read committed vs repeatable read.

P3: phantom

r1[P] … w2[y in P] … (c1 or a1). The set of rows matching your WHERE clause changes. A tatkal flow counts confirmed berths in coach S4, sees 71 of 72 and inserts one; a concurrent booking does the same, and a recount shows 73. A snapshot keeps the recount stable, but stopping the 73rd booking needs range locks, SERIALIZABLE or a constraint, because the troublesome row didn’t exist when you read. See phantom read.

P4: lost update

r1[x] … w2[x] … w1[x] … c1. Two read-modify-write cycles interleave and one silently overwrites the other. A wallet holds ₹500; two ₹200 top-ups both read 500 and both write 700, so ₹200 vanishes. READ COMMITTED allows it when the application computes the new value; snapshot isolation and locking REPEATABLE READ stop it, though MySQL’s REPEATABLE READ does not (see below). See the lost update problem.

A5A: read skew

r1[x] … w2[x] … w2[y] … c2 … r1[y]. You read two related items at different moments and see half of a transaction that changed both: the ₹1,200 report in Fig. 1. A fuzzy read is the special case where x and y are the same item. Reading the whole transaction from one snapshot prevents it.

A5B: write skew

r1[x] … r2[y] … w1[y] … w2[x] … (c1 and c2). Two transactions read overlapping data, each checks a rule, and each writes a different row. A hospital needs one doctor on call; Asha and Ravi both are, each sees the other on call and signs off, and both commit. Nobody overwrote anybody, yet nobody is on call. Snapshot isolation allows it; SERIALIZABLE or explicit locks stop it. See serializable isolation.

The four ANSI levels

SQL-92 defines SERIALIZABLE as equivalent to some serial execution, and each lower level by which of three phenomena it must not allow:

These are minimums: a database may forbid more, which is how PostgreSQL’s REPEATABLE READ rules out phantoms and still complies. The levels also assume locking: read locks held per read give read committed, held on rows until commit give repeatable read, and held on key ranges give serializable.

What the 1995 critique found

Berenson and his co-authors, from Microsoft, Sybase and UMass Boston, found three problems with this ladder.

The English is ambiguous. Each phenomenon can be read strictly, as an anomaly that happened (“T2 read T1’s write, then T1 aborted”), or broadly, as a risky pattern however it ends. The strict reading lets bugs through. In the paper’s history H1, T1 moves 40 from x to y, both starting at 50; T2 reads x after the debit (10) and y before the credit (50), sees a total of 60 instead of 100, and both commit. Nothing aborted or was read twice, so no strict anomaly occurred, yet T2 watched money vanish. The paper concludes the broad readings are what the standard must mean.

The ladder misses anomalies. Nothing below SERIALIZABLE forbids dirty writes, though every locking engine prevents them, so the paper says P0 should be banned at every level. Lost update, read skew and write skew are absent too, yet they are what separates real implementations.

Snapshot isolation doesn’t fit. Under the strict readings it never shows a dirty read, a fuzzy read or a phantom, so by the letter of the standard it would pass as SERIALIZABLE, yet it allows write skew. Against locking REPEATABLE READ it is neither stronger nor weaker: locking RR allows phantoms but stops write skew; snapshot isolation does the opposite. That is how PostgreSQL can call its snapshot isolation “repeatable read” and Oracle can call its own “serializable”.

The paper’s summary table, trimmed to the levels you will meet in practice:

Snapshot isolation in brief

Snapshot isolation (SI) gives each transaction a start timestamp. Every read sees data as committed at that moment, plus the transaction’s own writes. The database keeps old row versions so it can serve those reads without blocking anyone; how is the subject of MVCC.

Two concurrent writers of the same row can’t both win, or you’d have a lost update. The paper’s rule is first committer wins: a transaction aborts at commit if a transaction that committed during its lifetime wrote an item it also wrote. PostgreSQL applies this eagerly: a REPEATABLE READ transaction that updates a row changed by a concurrent transaction waits for it and, if it committed, fails with “could not serialize access due to concurrent update”.

What SI gains: readers and writers never block each other, every transaction sees a consistent picture, and lost updates and read skew are gone. What it costs: old versions to store and clean up, retries for writers of the same row, and write skew, because transactions that write different rows never conflict. Serializable Snapshot Isolation (SSI), PostgreSQL’s SERIALIZABLE since 9.1, closes that gap by also tracking who read what and aborting one transaction when a dangerous pattern of read-write dependencies appears; see serializable isolation.

What real databases actually do

Names are portable; guarantees are not. This table comes from each vendor’s current documentation.

A few notes the cells can’t hold:

• PostgreSQL before 9.1 gave you snapshot isolation when you asked for SERIALIZABLE, so old code that relied on “serializable” may have been running SI.
• SQL Server boxed installs default READ_COMMITTED_SNAPSHOT to OFF, so readers and writers block each other; Azure SQL Database and SQL database in Fabric default it to ON.
• CockroachDB advises staying on SERIALIZABLE unless you have a specific need; READ COMMITTED works only while sql.txn.read_committed_isolation.enabled is true, and is documented as stronger than PostgreSQL’s.

Kleppmann’s Hermitage suite runs the same small interleavings by hand against each database, and its findings correct the names: PostgreSQL’s REPEATABLE READ and Oracle’s SERIALIZABLE test as snapshot isolation, SQL Server’s REPEATABLE READ stops lost updates but not phantoms, and MySQL’s REPEATABLE READ fails the lost-update test and allows read skew once the transaction writes.

Setting the level

There are three scopes: the next or current transaction, the rest of the session, and a default for the server, database or role. Prefer the narrowest, because pooled connections carry session settings from one request to the next.

-- PostgreSQL: one transaction (must be set before its first query)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts WHERE user_id = 42;
COMMIT;

-- PostgreSQL: rest of this session, or a default per database or role
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ALTER DATABASE wallet SET default_transaction_isolation = 'serializable';
SHOW transaction_isolation;

-- MySQL: no keyword = next transaction only (not allowed inside one)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE wallets SET balance = balance + 200 WHERE user_id = 42;
COMMIT;

-- MySQL: this session, or every new session (needs CONNECTION_ADMIN)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
-- my.cnf: [mysqld] transaction-isolation = READ-COMMITTED

-- SQL Server: applies to the connection until changed
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT SUM(Balance) FROM dbo.Accounts WHERE UserId = 42;
COMMIT;

-- SQL Server: database options, run once by an administrator
ALTER DATABASE Payments SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE Payments SET READ_COMMITTED_SNAPSHOT ON;

Most code never types these; a driver or ORM sends them.

• Java: connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE), or Spring’s @Transactional(isolation = Isolation.REPEATABLE_READ). Spring’s Isolation.DEFAULT means the database’s default, which differs between PostgreSQL and MySQL.
• Python: SQLAlchemy takes isolation_level="REPEATABLE READ" on the engine or via execution_options. Django sets it in the database OPTIONS, and on MySQL defaults to READ COMMITTED, not MySQL’s REPEATABLE READ.
• Go: db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable}). .NET: connection.BeginTransaction(IsolationLevel.Snapshot).

Behind a transaction-mode pooler such as PgBouncer, set the level with BEGIN ISOLATION LEVEL …; a session-level SET can leak to whichever client gets that server connection next. See connection pooling.

Choosing a level

Start from the anomaly you must prevent, then pick the cheapest tool that prevents it. Often that tool is not an isolation level at all.

1. Keep READ COMMITTED for ordinary OLTP and fix single-row races in the write: an atomic UPDATE … SET stock = stock - 1 WHERE id = ? AND stock > 0, SELECT … FOR UPDATE, or a version-column check (see optimistic vs pessimistic locking).
2. Use one snapshot for reads that must agree: reconciliation, exports, dashboards. Mark the transaction READ ONLY and keep it short.
3. For a rule spanning rows, try a constraint first: UNIQUE on (train, date, berth), or PostgreSQL’s EXCLUDE for overlapping bookings (database constraints). Otherwise use SERIALIZABLE with a retry loop, or lock one shared parent row with FOR UPDATE so conflicting transactions queue.
4. Never drop to READ UNCOMMITTED for speed. On MVCC engines it buys nothing; on SQL Server it is the NOLOCK habit, which can return rows that are later rolled back.

Applied to familiar systems:

The retry loop is part of the contract

PostgreSQL’s REPEATABLE READ and SERIALIZABLE, CockroachDB’s SERIALIZABLE, Oracle’s SERIALIZABLE and SQL Server’s SNAPSHOT all resolve conflicts by aborting a transaction: SQLSTATE 40001 in PostgreSQL and CockroachDB, ORA-08177 in Oracle, error 3960 in SQL Server. Locking levels abort deadlock victims too: 40P01 in PostgreSQL, error 1213 (SQLSTATE 40001) in MySQL, error 1205 in SQL Server. Choosing one of these levels without retry code leaves the job half done.

import random, time
import psycopg

RETRYABLE = {"40001", "40P01"}  # serialization failure, deadlock (PostgreSQL)

def run_serializable(conn, work, max_attempts=5):
    # conn is in autocommit mode, so BEGIN and COMMIT below are explicit
    for attempt in range(1, max_attempts + 1):
        try:
            conn.execute("BEGIN ISOLATION LEVEL SERIALIZABLE")
            result = work(conn)          # every read AND write of the unit
            conn.execute("COMMIT")
            return result
        except psycopg.Error as e:
            conn.execute("ROLLBACK")
            if e.sqlstate not in RETRYABLE or attempt == max_attempts:
                raise
            time.sleep(random.uniform(0, 0.02 * 2 ** attempt))  # jittered backoff

Retry the whole transaction, reads included, because what you read is exactly what changed. Keep emails, payment-gateway calls and Kafka messages out of the retried body, or make them idempotent (idempotency, transactional outbox). Cap attempts with jittered backoff so a hot row doesn’t become a retry storm.

What stronger isolation costs

No honest single number exists; the price depends on how often transactions touch the same data. What changes is the shape of the cost. Locking makes conflicting transactions wait and sometimes deadlock. Snapshot-based schemes never make readers wait but throw away work on conflict. Under low contention both are cheap; under high contention locking becomes queues and timeouts, and optimistic schemes become abort storms. Hermitage’s README notes that PostgreSQL’s and MySQL’s SERIALIZABLE give the same guarantees with very different implementations and performance, so measure your own workload.

Going deeper: traps that bite in production

Snapshot reads and current writes in one MySQL transaction

At REPEATABLE READ, InnoDB serves plain SELECTs from the read view but runs UPDATE and DELETE against the newest rows. MySQL’s manual shows a SELECT COUNT(*) returning 0, then an UPDATE changing 10 rows another transaction just committed. If a decision depends on a read, make it a locking read (FOR UPDATE or FOR SHARE).

When does the snapshot start?

Not at BEGIN. PostgreSQL takes it at the first statement that isn’t transaction control; InnoDB at the first consistent read, unless you use START TRANSACTION WITH CONSISTENT SNAPSHOT; SQL Server’s SNAPSHOT at the first data access. If you BEGIN, call a slow API and then read, your snapshot is from after the call.

Long snapshots are not free

A snapshot pins every row version it might need: dead tuples VACUUM can’t remove in PostgreSQL, undo history purge can’t discard in InnoDB. A two-hour REPEATABLE READ report on a busy primary is a slow-motion bloat incident; run it on a replica or split it (vacuum and bloat).

SSI’s false positives

PostgreSQL tracks serializable reads with non-blocking predicate locks. Short of memory, it coarsens them from rows to pages to tables, and a sequential scan always locks the whole table, so spurious 40001s rise. The docs advise declaring READ ONLY transactions, keeping transactions small and capping connections. For long reports, SERIALIZABLE READ ONLY DEFERRABLE waits once for a safe snapshot and then can’t be cancelled by conflicts.

Isolation stops at the database boundary

Each level describes one database. A read from an asynchronous replica can be stale whatever the primary runs, a cache has no isolation, and a workflow across services gets none: see data replication and sagas. And SERIALIZABLE is about transactions, not recency; it is not CAP’s linearizability.

Cheat sheet

How to say it in the interview

“Isolation is how much concurrent transactions see of each other. Serializable means the result matches some serial order, but it costs blocking or aborts, so most databases default lower: PostgreSQL to read committed, MySQL to repeatable read. I think in anomalies, not level names. For a wallet debit the risk is a lost update, so I stay at read committed with an atomic conditional UPDATE. A report that sums many rows runs as a read-only snapshot transaction. For a rule across rows, like at least one doctor on call, I use a constraint if one fits, otherwise SERIALIZABLE with a retry loop on 40001 and side effects kept outside the retried block.”

What’s the default in PostgreSQL vs MySQL? PostgreSQL defaults to READ COMMITTED, a fresh snapshot per statement. MySQL InnoDB defaults to REPEATABLE READ: plain reads use a snapshot from the first read, while locking reads and writes see the latest data and take next-key locks. Oracle and SQL Server default to READ COMMITTED; CockroachDB to SERIALIZABLE.

Does snapshot isolation equal serializable? No. It stops dirty reads, fuzzy reads, read skew, lost updates and read phantoms, which is why it passes the strict ANSI wording, but it allows write skew: two transactions read the same data, write different rows, and produce a result no serial order could. PostgreSQL’s SERIALIZABLE adds dependency tracking on top of SI to close that gap; Oracle’s SERIALIZABLE does not.

How do you prevent write skew without SERIALIZABLE? Turn the rule into a write conflict. Lock the rows the decision depends on with SELECT … FOR UPDATE, or one shared parent row such as the shift. Use a constraint the database enforces for everyone (UNIQUE, or PostgreSQL’s EXCLUDE for overlapping ranges). Or materialise the conflict: one row per room per time slot, so concurrent bookings collide on the same row.

Why might you not want SERIALIZABLE everywhere? Its cost grows with contention: waits and deadlocks with locking engines, aborts and retries with SSI. Every caller needs retry code and idempotent side effects. And most transactions only need protection from one specific anomaly, which an atomic update or a constraint gives more cheaply.

Why doesn’t PostgreSQL’s REPEATABLE READ show phantoms? It is snapshot isolation, so every query reads the same snapshot and later commits stay invisible. The standard sets minimums, so giving more is allowed. The same design is why it still allows write skew.

Where to go next

• ACID transactions: where isolation sits beside atomicity, consistency and durability.
• Read committed vs repeatable read: the two common defaults head to head.
• Serializable isolation: serial execution, two-phase locking and SSI compared.
• MVCC: how row versions and snapshots are implemented.
• Lost update problem and phantom read: the two anomalies you will fix most often.
• Optimistic vs pessimistic locking: FOR UPDATE vs version columns under contention.
• Transaction isolation levels in practice: a hands-on deep dive with real workloads.
• Contention and double booking: the same ideas applied to tickets, seats and flash sales.

References and further reading

1. A Critique of ANSI SQL Isolation Levels (Berenson, Bernstein, Gray, Melton, O’Neil and O’Neil, SIGMOD 1995; MSR-TR-95-51)microsoft.comThe P0–P4 and A5A/A5B definitions, strict versus broad readings of the ANSI phenomena, and snapshot isolation’s place outside the ladder.
2. PostgreSQL documentation: Transaction Isolationpostgresql.orgDefault READ COMMITTED, READ UNCOMMITTED mapped to it, REPEATABLE READ as snapshot isolation without phantoms, SSI-based SERIALIZABLE and SQLSTATE 40001.
3. PostgreSQL documentation: SET TRANSACTIONpostgresql.orgPer-transaction versus session defaults, default_transaction_isolation, and READ ONLY DEFERRABLE for long reports.
4. MySQL 8.4 Reference Manual: Transaction Isolation Levelsdev.mysql.comInnoDB’s default REPEATABLE READ, next-key and gap locking, READ COMMITTED semi-consistent reads, and SERIALIZABLE turning SELECT into FOR SHARE.
5. MySQL 8.4 Reference Manual: Consistent Nonlocking Readsdev.mysql.comWhen the read view is taken, and why UPDATE and DELETE can act on rows a plain SELECT in the same transaction cannot see.
6. MySQL 8.4 Reference Manual: SET TRANSACTION Statementdev.mysql.comNext-transaction, SESSION and GLOBAL scopes, and the transaction-isolation server option.
7. SQL Server documentation: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)learn.microsoft.comLocking versus row-versioned READ COMMITTED, READ_COMMITTED_SNAPSHOT defaults on SQL Server and Azure SQL Database, SNAPSHOT, and range-locked SERIALIZABLE.
8. Oracle Database Concepts: Data Concurrency and Consistencydocs.oracle.comStatement-level versus transaction-level read consistency, READ COMMITTED as default, and SERIALIZABLE failing with ORA-08177.
9. CockroachDB documentation: Transactions (isolation levels)cockroachlabs.comSERIALIZABLE by default, opt-in READ COMMITTED, automatic level upgrades, and 40001 retry errors.
10. Hermitage: testing transaction isolation levels (Kleppmann)github.comHand-run tests showing which anomalies each vendor’s named level actually prevents.