Transaction isolation levels in practice

An isolation level is a name from the SQL standard, not a behaviour. PostgreSQL’s REPEATABLE READ is snapshot isolation and aborts a transaction that updates a row someone else changed; MySQL InnoDB’s REPEATABLE READ reads from a snapshot but writes against the latest data, and lets that same lost update through silently. In production, what matters is which anomalies your code can hit at your engine’s default, and which tool closes each one: an atomic update, a row lock, a constraint, or SERIALIZABLE with a retry loop.

This is the production follow-on to transaction isolation levels. One-sentence recap: the SQL standard defines READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE by which of dirty reads, non-repeatable reads and phantoms each one forbids.

Picture it first: the photocopy and the live register

An auditor at a bank branch gets a photocopy of the ledger at 10:00 and works from it all morning. Her totals agree with each other, because the copy never changes. But to correct an entry she must walk to the live register, which other clerks have been updating since 10:00.

Branches handle that moment differently. At one, the head clerk checks whether the entry changed after her photocopy was made, and if it did, refuses: “your copy is stale, start again.” At another, he lets her write on the live register, even though her arithmetic came from the old copy. Both branches call their policy “repeatable read”. A third branch adds a supervisor who watches what every auditor read, and cancels one of them whenever two drew conclusions that can’t both be true.

Map it back: the photocopy is an MVCC snapshot (MVCC). The live register is the latest committed row version that UPDATEs and locking reads use. The strict clerk is PostgreSQL’s REPEATABLE READ, the lenient one is InnoDB’s REPEATABLE READ, and the supervisor is PostgreSQL’s SERIALIZABLE.

The levels are names, not behaviours

In 1995, Hal Berenson, Phil Bernstein, Jim Gray and colleagues showed in “A Critique of ANSI SQL Isolation Levels” that the standard’s definitions are ambiguous and miss anomalies that matter, notably the lost update and write skew, and that snapshot isolation, which most MVCC databases implement, doesn’t fit the standard’s ladder at all. Vendors filled the gaps in different ways. Martin Kleppmann’s Hermitage project tests real engines against precise anomaly definitions; its results table is the best one-page answer to “what does this level actually prevent?”

The defaults differ too:

• PostgreSQL: READ COMMITTED.
• MySQL InnoDB: REPEATABLE READ.
• SQL Server: READ COMMITTED, implemented with shared locks unless the READ_COMMITTED_SNAPSHOT database option is on, in which case each statement reads row versions; that option is on by default in Azure SQL Database (Microsoft docs).
• Oracle: READ COMMITTED; its SERIALIZABLE behaves as snapshot isolation in Hermitage’s tests, so write skew can still happen.

PostgreSQL: what each level really does

READ COMMITTED (default). Every statement takes a fresh snapshot. If an UPDATE or SELECT … FOR UPDATE reaches a row that a concurrent transaction is changing, it waits; once that transaction commits, it re-checks its WHERE clause against the new row version and acts on that. So a single atomic statement such as SET balance = balance - 300 WHERE balance >= 300 always works on the newest balance. Two statements in one transaction, though, can see different data, and a read in your code followed by a write is unprotected.

REPEATABLE READ is snapshot isolation. The snapshot is taken at the first statement (not at BEGIN), and every query in the transaction sees it, so phantoms are impossible too. If you update or lock a row that another transaction changed and committed after your snapshot, you get ERROR: could not serialize access due to concurrent update (SQLSTATE 40001) and must retry the whole transaction. Read-only transactions never fail. What it doesn’t stop is write skew: two transactions read overlapping data, write different rows, and both commit.

SERIALIZABLE is Serializable Snapshot Isolation (SSI). It is REPEATABLE READ plus tracking of read/write dependencies through non-blocking SIRead predicate locks. When the dependencies could produce a result that no serial order allows, one transaction fails with could not serialize access due to read/write dependencies among transactions, also 40001. It adds no blocking, but it costs some CPU and memory and can abort transactions that would have been fine; sequential scans take table-level predicate locks and raise the failure rate. The manual’s advice: declare READ ONLY where you can, keep transactions short, and pool connections. A SERIALIZABLE READ ONLY DEFERRABLE transaction waits for a safe snapshot and then never fails, which suits long reports. How SSI decides is covered in serializable isolation and concurrency control.

READ UNCOMMITTED is accepted and behaves exactly like READ COMMITTED.

MySQL InnoDB: two views inside one transaction

REPEATABLE READ (default) splits a transaction in two. Plain SELECTs are consistent nonlocking reads from a snapshot taken at the transaction’s first read. But SELECT … FOR UPDATE, FOR SHARE, UPDATE and DELETE work on the latest committed rows and lock them. The manual’s own example: a SELECT counts zero rows matching a value, then an UPDATE with the same condition changes ten rows that another transaction has just committed. It advises against mixing the two kinds of statement in one REPEATABLE READ transaction.

That split explains InnoDB’s most surprising production bug: it does not detect lost updates. Your SELECT reads ₹500 from the snapshot, another transaction commits ₹200, and your UPDATE … SET balance = 300 simply overwrites it, with no error. Hermitage records lost updates as possible at InnoDB’s REPEATABLE READ.

For locking reads and writes, REPEATABLE READ takes next-key locks: a lock on each index record scanned plus the gap before it, so no other transaction can insert a phantom into the range. An equality search on a unique index locks only the one record.

READ COMMITTED gives each plain read a fresh snapshot, locks only index records (gaps are locked only for foreign-key and duplicate-key checks), and releases locks on rows that fail the WHERE clause. The manual says this greatly reduces deadlocks; you pay with phantoms, and it requires row-based binary logging.

SERIALIZABLE is REPEATABLE READ with every plain SELECT turned into SELECT … FOR SHARE when autocommit is off. Readers now block writers, and conflicts show up as lock waits and deadlocks instead of snapshot errors. READ UNCOMMITTED really does read uncommitted rows.

Gap locks and their side effects

Gap locks cause three production surprises:

• Collateral blocking. A range lock taken for one user blocks inserts for neighbouring values, as in the figure. A missing or unselective index makes the scanned range, and the locked range, much larger.
• Gap-lock deadlocks. Gap locks never conflict with each other, so two transactions can both run SELECT … FOR UPDATE on the same empty range, find nothing, and each try to insert into it. Each insert waits for the other’s gap lock; InnoDB detects the cycle and rolls one back with error 1213, SQLSTATE 40001. The classic trigger is “check the row doesn’t exist with FOR UPDATE, then insert it”, run by two requests at once; letting a unique key reject the second insert avoids the pattern.
• Long lock waits. innodb_lock_wait_timeout defaults to 50 seconds, and error 1205 rolls back only the waiting statement, not the transaction, unless the server runs with innodb_rollback_on_timeout.

Lost update: the wallet race

A user’s wallet holds ₹500. A ₹300 food order and a ₹200 subscription renewal reach two app servers at the same moment, and both run the obvious code:

balance = db.scalar("SELECT balance FROM wallets WHERE user_id = %s", [7])
if balance >= amount:
    db.execute("UPDATE wallets SET balance = %s WHERE user_id = %s",
               [balance - amount, 7])

Both read 500. One writes 200, then the other writes 300. ₹500 has been spent and the wallet still shows ₹300. This happens at READ COMMITTED in both engines and, silently, at InnoDB’s default REPEATABLE READ; at PostgreSQL’s REPEATABLE READ the second UPDATE fails with 40001 instead. The anomaly itself is explained in the lost update problem.

Four fixes, in the order to reach for them:

1. Atomic conditional update. Let the database do the read-modify-write: UPDATE wallets SET balance = balance - 300 WHERE user_id = 7 AND balance >= 300, then check that exactly one row changed. Both engines evaluate it against the newest row version under a row lock. You gain correctness without retries at READ COMMITTED; you pay by fitting the rule into one statement. (At PostgreSQL’s REPEATABLE READ or SERIALIZABLE, T2 waits and then gets 40001 instead of re-reading, so wrap it in the retry loop below.)
2. Lock first with SELECT … FOR UPDATE. Read the row with a lock, decide in code, write, commit. You gain arbitrary logic; you pay with blocked writers while you hold the lock. Keep the transaction to milliseconds, never hold it across a network call, and lock several rows in a fixed order (by id, say) to avoid deadlocks.
3. Optimistic version column. UPDATE wallets SET balance = 200, version = 42 WHERE user_id = 7 AND version = 41; zero rows changed means someone got there first, so re-read and retry. It suits rare conflicts, or a read and write in different requests such as an edit form; see optimistic vs pessimistic locking.
4. Let PostgreSQL detect it. At REPEATABLE READ or SERIALIZABLE the loser gets 40001 and retries. InnoDB’s REPEATABLE READ gives you no such error.

Write skew: the tatkal hold limit

Say your booking rule is “at most two active tatkal holds per user”, and user 7 has one. The user double-taps on two devices, and two requests run this at the same moment:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM holds WHERE user_id = 7 AND status = 'ACTIVE';  -- 1: allowed
INSERT INTO holds (user_id, train_no, status) VALUES (7, '12951', 'ACTIVE');
COMMIT;

Both count one, both insert, both commit: the user now has three holds. Snapshot isolation stops two transactions from writing the same row, but these two wrote different rows, so nothing conflicts. Berenson’s paper calls this write skew. InnoDB’s REPEATABLE READ behaves the same way with a plain SELECT. Two on-call doctors both going off duty, and one username claimed through two different tables, have the same shape.

The fixes:

• Lock a row that stands for the invariant. SELECT id FROM users WHERE id = 7 FOR UPDATE at the start serializes hold creation per user in both engines. Locking the existing hold rows is not enough, because the new row can’t be locked before it exists.

• Materialize the conflict. Keep a counter row per user and change it atomically, so write skew becomes an ordinary write-write conflict on one row:

  UPDATE tatkal_quota SET active = active + 1
  WHERE user_id = 7 AND active < 2;   -- 0 rows changed: limit reached

• Use a constraint. When the invariant is uniqueness, a unique index is the cheapest serializer at any level: “one active hold per berth” is a partial unique index in PostgreSQL, and claimed usernames belong in one table with a unique key, not two.

• Use PostgreSQL’s SERIALIZABLE. SSI catches the pattern without you naming the invariant: one transaction gets 40001, retries, sees two holds and refuses. In InnoDB, SERIALIZABLE’s shared locks also prevent it, mostly by deadlocking one side.

For scarce seats under heavy load, contention and double booking compares these approaches at scale.

Retrying serialization failures and deadlocks

At PostgreSQL’s REPEATABLE READ and SERIALIZABLE, and wherever deadlocks can happen in either engine, aborted transactions are normal operation, not bugs. PostgreSQL reports 40001 (serialization failure) and 40P01 (deadlock detected); MySQL reports a deadlock as error 1213 with SQLSTATE 40001. The answer is a retry loop around the whole transaction:

import random, time

RETRYABLE = {"40001", "40P01"}   # serialization failure, deadlock (MySQL 1213 is 40001 too)

def run_transaction(work, isolation="SERIALIZABLE", attempts=5, base=0.01):
    for attempt in range(1, attempts + 1):
        try:
            with db.transaction(isolation=isolation) as tx:   # BEGIN … COMMIT, ROLLBACK on error
                return work(tx)          # all reads and writes; no side effects outside the DB
        except DatabaseError as err:
            if err.sqlstate not in RETRYABLE or attempt == attempts:
                raise
            time.sleep(random.uniform(0, base * 2 ** attempt))   # backoff with full jitter

• Retry from BEGIN and re-read everything. The new attempt gets a fresh snapshot; re-running only the failed statement keeps the stale reads that caused the abort.
• No side effects inside work. An email or a payment call made before the abort happens again on the retry. Write it to an outbox row in the same transaction instead (transactional outbox).
• Plan for an unknown outcome. If the connection drops during COMMIT, you can’t tell whether it committed. An idempotency key with a unique constraint makes the blind retry safe (idempotency).
• Bound it and watch it. With a 10 ms base and 5 attempts, the sleeps add up to at most 20 + 40 + 80 + 160 = 300 ms. A rising retry rate means you should remove the contention, not retry harder (timeouts, retries and backoff).
• MySQL’s lock wait timeout is not in the set. Error 1205 has SQLSTATE HY000 and leaves the transaction open, so roll back explicitly before deciding to retry.

Job queues: SKIP LOCKED and NOWAIT

A table used as a work queue is where plain FOR UPDATE hurts: twenty workers all try to lock the oldest job and queue up behind each other. SKIP LOCKED, available in PostgreSQL and MySQL 8.0, skips rows another transaction has locked, so each worker claims different jobs:

-- PostgreSQL: claim up to 10 jobs in one short transaction
UPDATE jobs SET status = 'running', lease_until = now() + interval '5 minutes'
WHERE id IN (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

In MySQL, run the SELECT … FOR UPDATE SKIP LOCKED and then the UPDATE … WHERE id IN (…) inside one transaction. Both manuals warn that skipping locked rows gives an inconsistent view, so use it for picking work items, never for counts or reports. Claim in a short transaction and do the work outside it, with a reaper that re-queues jobs whose lease has expired; holding the lock for a ten-minute job keeps a transaction open, with the costs in the next section. The patterns around this are in design a job scheduler and long-running jobs.

NOWAIT is the other option: instead of waiting, the locking read fails at once (SQLSTATE 55P03 in PostgreSQL). Use it where waiting is worse than an error, such as an operator screen that should say “someone else is editing this order”. Django exposes both as select_for_update(nowait=True) and select_for_update(skip_locked=True).

Long transactions, idle sessions and lock waits

An open transaction pins its snapshot, even when it is read-only or idle. PostgreSQL’s VACUUM can’t remove dead row versions that any open snapshot might still need, and the manual notes that a session sitting “idle in transaction” contributes to bloat even when it holds no locks. InnoDB’s purge can’t discard undo records an open read view needs; the MySQL manual recommends committing regularly, even read-only transactions, or the rollback segment grows and fills the undo tablespace. You see the backlog as “History list length” in SHOW ENGINE INNODB STATUS.

Find and cap them:

-- PostgreSQL: the oldest open transactions
SELECT pid, state, now() - xact_start AS open_for, left(query, 50) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

-- Limits for the application role (both are disabled by default)
ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app SET lock_timeout = '5s';

• PostgreSQL waits for a lock forever unless lock_timeout is set, and only checks for a deadlock after deadlock_timeout (1 s by default). PostgreSQL 17 added transaction_timeout, which caps the total length of any transaction. Set lock_timeout low in migration sessions so a DDL statement waiting behind a long transaction doesn’t block every query queued behind it.
• MySQL’s 50-second innodb_lock_wait_timeout is long for an interactive request; it can be lowered per session. SHOW ENGINE INNODB STATUS shows only the latest deadlock, so turn on innodb_print_all_deadlocks to log every one.
• Long reports belong on a replica or in a bounded window, not on the primary at peak. Bloat itself is covered in vacuum and bloat.

Frameworks and read replicas

Most frameworks inherit the database’s default, so identical code gets different guarantees per engine. Spring’s @Transactional defaults to Isolation.DEFAULT, meaning “whatever the data store uses”: READ COMMITTED on PostgreSQL, REPEATABLE READ on MySQL. Django is the notable exception: on PostgreSQL it uses READ COMMITTED like the server, but on MySQL it defaults to READ COMMITTED instead of the server’s REPEATABLE READ, and its documentation warns that data loss is possible at REPEATABLE READ. Whatever your framework, check the level it really runs at with SHOW transaction_isolation (PostgreSQL) or SELECT @@transaction_isolation (MySQL) in a request, rather than trusting a config file.

Isolation is also per server. A query on an asynchronous replica sees a consistent snapshot of the replica, which may be seconds behind; a SERIALIZABLE transaction on the primary does not make a later replica read current (data replication covers read-your-writes). PostgreSQL refuses SERIALIZABLE on a hot standby altogether. Long standby queries are cancelled when they conflict with replay, or, if you turn on hot_standby_feedback to avoid that, they hold back VACUUM on the primary, which is the long-transaction problem again in a different place.

Choosing a level per workload

Cheat sheet

How to say it in the interview

“I’d start from the engine, because the level names mislead. On PostgreSQL I’d keep most requests at READ COMMITTED and make every balance change one conditional UPDATE, so the database does the read-modify-write under a row lock. For the rule that spans rows, at most two active holds per user, I’d lock the user row with SELECT FOR UPDATE, or run that transaction at SERIALIZABLE in a retry loop on 40001 with jittered backoff and an idempotency key. On MySQL, REPEATABLE READ doesn’t catch lost updates and gap locks can deadlock inserts, so those patterns matter even more. And I’d time-limit idle and long transactions so one stuck session can’t bloat the database.”

Why not run everything at SERIALIZABLE? On PostgreSQL you can, and it removes a whole class of bugs, but every transaction must then be retryable, contention turns into aborts, and dependency tracking costs CPU and memory. On InnoDB it turns plain reads into shared locks, so readers block writers. Most teams use READ COMMITTED with targeted locks and keep SERIALIZABLE for the transactions that guard multi-row rules.

Does REPEATABLE READ prevent lost updates? On PostgreSQL, yes: the second writer gets 40001 and retries. On MySQL InnoDB, no: the UPDATE applies to the newest row version and silently overwrites the other write.

What does a long read-only transaction cost? It pins a snapshot, so PostgreSQL can’t vacuum newer dead rows and InnoDB can’t purge undo. Tables and undo grow, and queries slow down. Put a time limit on it or move it to a replica.

How do you handle deadlocks? Treat 40P01 and 1213 as retryable and retry the whole transaction. Then reduce them: lock rows in a consistent order, keep transactions short, add the index that shrinks InnoDB’s locked range, and read the deadlock log to see which statements collided.

Where to go next

• Transaction isolation levels: the four standard levels and the phenomena behind them.
• Serializable isolation: what true serializability guarantees and how engines achieve it.
• Concurrency control: 2PL, MVCC, SSI and deadlock detection under the hood.
• Read committed vs repeatable read: the two everyday levels side by side.
• Vacuum and bloat: what long transactions do to PostgreSQL tables.
• Contention and double booking: seat and inventory patterns under heavy load.
• ACID vs BASE: when to keep an invariant inside one transaction at all.

References and further reading

1. PostgreSQL documentation: Transaction Isolationpostgresql.orgRead Committed re-checks, Repeatable Read as snapshot isolation, Serializable as SSI, the 40001 SQLSTATE, and Read Uncommitted behaving as Read Committed.
2. PostgreSQL documentation: SELECT, the locking clausepostgresql.orgFOR UPDATE and FOR SHARE, NOWAIT, and SKIP LOCKED for queue-like tables.
3. PostgreSQL documentation: Client connection defaultspostgresql.orglock_timeout, idle_in_transaction_session_timeout and transaction_timeout, all disabled by default, and why idle transactions cause bloat.
4. PostgreSQL documentation: Hot Standbypostgresql.orgSerializable is unavailable on standbys, query cancellation, and how hot_standby_feedback delays cleanup on the primary.
5. MySQL 8.0 Reference Manual: Transaction isolation levelsdev.mysql.comInnoDB’s default Repeatable Read, gap and next-key locking per level, and Serializable turning plain SELECTs into FOR SHARE.
6. MySQL 8.0 Reference Manual: Consistent nonlocking readsdev.mysql.comThe snapshot taken at the first read, and DML that sees rows the snapshot cannot.
7. MySQL 8.0 Reference Manual: InnoDB lockingdev.mysql.comRecord, gap, next-key and insert intention locks, and why gap locks never conflict with each other.
8. MySQL 8.0 Reference Manual: InnoDB startup options and system variablesdev.mysql.cominnodb_lock_wait_timeout (50 s by default) rolls back only the waiting statement; deadlock detection is on by default.
9. Hermitage: testing transaction isolation levels (Kleppmann)github.comTested anomaly matrix per engine and level, including lost update at InnoDB Repeatable Read and Oracle’s snapshot-isolation Serializable.
10. A Critique of ANSI SQL Isolation Levels (Berenson et al., SIGMOD 1995)arxiv.orgFormal definitions of lost update (P4) and write skew (A5B), and why snapshot isolation does not fit the ANSI ladder.