Transaction isolation levels in DBMS and SQL

How much one transaction is allowed to see of another that is running at the same time. Each level removes specific anomalies.

By devskills editorial team · Last updated 26 October 2026

What problem do isolation levels solve?

Two transactions running at once can interfere: one reads data the other has not committed, or reads the same row twice and gets two answers, or both read, both decide, and both write. The isolation level is the setting that decides which of these interferences the database prevents for you.

It matters in interviews because the defaults are weaker than most people assume. PostgreSQL defaults to read committed and MySQL's InnoDB to repeatable read; neither is serializable. Bugs like double-booking and overdrawn balances are fully possible at the default level, and the database will not raise an error.

What isolation levels cost you

What it costs you

Stronger isolation means more waiting or more aborted transactions. Locking implementations make transactions queue and deadlock more often. Snapshot-based implementations keep old row versions around and abort conflicting transactions, so the application must retry. Serializable is correct by construction and costs the most throughput under contention.

When to reach for isolation levels, and when not to

Use it when

Choose consciously whenever a transaction reads data and then writes based on what it read: balances, stock, seat maps, rotas.
Serializable for invariants that span several rows ("at least one doctor on call", "total allocations under the budget").
Read committed, the common default, for ordinary single-row updates written as atomic statements.

Do not, when

Do not raise the whole database to serializable to fix one race. Fix that transaction, or use it only there.
Do not rely on an isolation level you have not checked for your database: "repeatable read" means different things in PostgreSQL and MySQL.

How isolation levels actually work

Write skew, the anomaly snapshot isolation still allows. Each transaction checks the rule against its own snapshot, writes a different row, and both commit.

The anomalies, in the order levels remove them. A dirty read sees another transaction's uncommitted change, which may be rolled back. A non-repeatable read reads the same row twice and gets different values because someone committed in between. A phantom runs the same query twice and gets a different set of rows, because someone inserted or deleted a matching row. A lost update happens when two transactions read a value, both change it, and the second write overwrites the first. Write skew happens when two transactions read the same rows, each checks a rule, and each writes a different row, so neither overwrites the other, yet together they break the rule.

The four SQL standard levels. Read uncommitted allows all of them. Read committed prevents dirty reads. Repeatable read also prevents non-repeatable reads. Serializable guarantees the result is the same as running the transactions one at a time in some order. The standard defines the first three by which of three phenomena they forbid (dirty read, non-repeatable read, phantom), and a well-known 1995 paper by Berenson and colleagues showed that this leaves gaps: lost updates and write skew are not covered by those definitions, and it named snapshot isolation, which many databases implement.

Snapshot isolation. Each transaction reads from a consistent snapshot taken when it started, using multiple row versions (MVCC), so readers never block writers. If two transactions update the same row, one of them fails, which prevents lost updates. It still allows write skew, because the two transactions write different rows.

PostgreSQL. The default is read committed, where each statement sees a fresh snapshot. Read uncommitted behaves like read committed. Repeatable read is snapshot isolation: the whole transaction sees one snapshot, phantoms do not appear, and updating a row that another transaction changed since your snapshot fails with a serialization error. Serializable adds Serializable Snapshot Isolation (SSI), which tracks read/write dependencies and aborts a transaction that would make the result non-serializable, including write skew. Any transaction at repeatable read or serializable must be retried when it fails with SQLSTATE 40001.

MySQL (InnoDB). The default is repeatable read. Plain SELECTs read from a snapshot established by the first read in the transaction. But UPDATE, DELETE and locking reads (SELECT ... FOR UPDATE or FOR SHARE) work on the latest committed rows and take next-key locks over the ranges they scan, which blocks inserts into those gaps. The consequence to know: a value you read with a plain SELECT may be older than the row your UPDATE changes, so computing a new value in application code and writing it back can silently lose someone else's update. Use a locking read or an atomic UPDATE ... SET qty = qty - 1. At serializable, InnoDB turns plain SELECTs into locking reads when autocommit is off.

Picking a level, by example. A transfer between two accounts written as two atomic UPDATEs with a balance check in the WHERE clause is safe at read committed. A booking that reads "is this seat free?" and then inserts a booking needs either a unique constraint on the seat, a locking read, or serializable. A rota rule across several rows ("at least one doctor on call") needs serializable, or a SELECT ... FOR UPDATE on every row the rule reads. For inventory, the simplest safe pattern is a conditional decrement (WHERE qty >= n), which needs no special level.

Level	Dirty read	Non-repeatable read	Phantom	Lost update	Write skew
Read uncommitted	Possible	Possible	Possible	Possible	Possible
Read committed	Prevented	Possible	Possible	Possible	Possible
Repeatable read (standard)	Prevented	Prevented	Possible	Prevented with locking	Possible
Snapshot isolation (PostgreSQL repeatable read)	Prevented	Prevented	Prevented for reads	Prevented (one transaction fails)	Possible
Serializable	Prevented	Prevented	Prevented	Prevented	Prevented

Which anomaly each level allows. "Standard" is the SQL standard's definition; real databases differ, as the paragraphs above explain.

write skew, and two fixes (PostgreSQL)

1-- the rule: at least one doctor on call for shift 7
2-- run T1 and T2 at the same time, each at REPEATABLE READ:
3BEGIN ISOLATION LEVEL REPEATABLE READ;
4SELECT count(*) FROM rota WHERE shift = 7 AND on_call;   -- both see 2
5UPDATE rota SET on_call = false WHERE shift = 7 AND doctor = 'alice';  -- T2: 'bob'
6COMMIT;                                                   -- both succeed: 0 on call
7
8-- fix 1: SERIALIZABLE. One of the two commits fails with SQLSTATE 40001;
9-- the application retries it, sees 1 on call, and refuses.
10BEGIN ISOLATION LEVEL SERIALIZABLE;
11-- ... same statements ...
12COMMIT;
13
14-- fix 2: lock the rows the rule reads, at any level.
15BEGIN;
16SELECT doctor FROM rota WHERE shift = 7 AND on_call FOR UPDATE;  -- T2 waits here
17UPDATE rota SET on_call = false WHERE shift = 7 AND doctor = 'alice';
18COMMIT;

Fix 1 needs retry logic for serialization failures. Fix 2 needs every code path that changes the rota to take the same locks.

Common trap

Assuming the default level is serializable. In PostgreSQL it is read committed; in MySQL InnoDB, repeatable read.
Using serializable or repeatable read without retrying on serialization failures (SQLSTATE 40001).
Treating "repeatable read" as one behaviour. PostgreSQL gives snapshot isolation; MySQL mixes snapshot reads with locking writes.
Forgetting write skew: no row is overwritten, yet the rule is broken.

Say this

"Postgres at its default, read committed. The seat booking is a single conditional update, so it is safe at that level. The rota change checks a rule across several rows, so that one transaction runs at serializable with a retry on 40001. Cost: under contention some of those transactions abort and retry, which I accept because the rule cannot be broken."

The follow-up an interviewer will ask about isolation levels

"Which isolation level would you use for a payments service?"

What a strong answer sounds like

I would not answer with one level for the whole service, because the level matters per transaction. Most payment writes can be made safe at read committed by writing them as single atomic statements: debit with a condition that the balance stays non-negative, and check that one row changed. A ledger insert with a unique idempotency key cannot double-post, whatever the level. Where a transaction reads several rows and decides based on all of them, such as checking a daily limit across today's payments before inserting a new one, read committed is not enough: two payments can each see room under the limit and both commit. That transaction runs at serializable, with a retry loop on serialization failures, or takes a lock on the account row first so the checks queue. Cost: serializable transactions abort under contention and the client must retry, so I keep them short and only use them where a rule spans rows.

Isolation levels: common questions

What is the default isolation level in PostgreSQL and MySQL?

PostgreSQL defaults to read committed. MySQL's InnoDB engine defaults to repeatable read. Both are documented in their manuals, and both allow anomalies that serializable would prevent.

What is the difference between repeatable read and serializable?

Repeatable read guarantees that rows you read do not change under you. Serializable guarantees the whole outcome is equivalent to running transactions one after another. The gap between them includes phantoms (in the standard) and write skew (in snapshot-based databases).

What is a phantom read?

Running the same query twice in one transaction and getting a different set of rows, because another transaction inserted or deleted a matching row and committed in between. It differs from a non-repeatable read, where an existing row's value changes.

What is snapshot isolation?

Each transaction reads from a consistent snapshot of the database as of its start, using row versions, and a transaction fails if it updates a row someone else changed since its snapshot. It prevents dirty reads, non-repeatable reads and lost updates, and still allows write skew.

Is serializable always slower?

It costs more under contention, through waiting (locking databases) or aborts and retries (PostgreSQL's SSI). With little contention the overhead can be modest. Measure on your workload rather than assuming either way.

Your turn

60-second self-check

Two transactions each read "2 doctors on call", and each takes a different doctor off call. Both commit at PostgreSQL repeatable read. What anomaly is this, and name two fixes.

Stuck? Take one hint at a time0 of 2 used

0 / 25 characters

Learn it from the source

PostgreSQL — Transaction Isolation ↗The levels as PostgreSQL implements them, including SSI and serialization failures.
MySQL 8.4 — Transaction Isolation Levels (InnoDB) ↗Consistent reads, locking reads and next-key locks at each level.
A Critique of ANSI SQL Isolation Levels (1995) ↗The paper that defined lost update, write skew and snapshot isolation precisely.
Jepsen — Consistency models ↗Where the isolation levels sit among distributed consistency models.

Related concepts

Transactions, isolation and locking Optimistic vs pessimistic locking Consistency models SQL vs NoSQL — and why, not just names

All concepts on one page: system design cheat sheet