What are transaction isolation levels? | Interview Question

Q: What are transaction isolation levels?

**Target Audience:** Beginners who know what a transaction is and want to understand what "isolation level" means, and candidates who need the anomaly names, the four standard levels and the real engine defaults. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 (all two-session timelines were run on it with two concurrent `psql` sessions) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples use). MySQL, SQL Server and Oracle facts are cited from their documentation. The PostgreSQL-specific detail for each level is on its own page: [Transaction isolation levels in Postgres — what anomalies does each level prevent?](/interview-question/transaction-isolation-levels-in-postgres-what-anomalies-does-each-level-prevent) > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verif

The problem

Question presented to candidate: "What are transaction isolation levels? Name them, tell me which problems each one prevents, and tell me what your database uses by default and why it is not the strictest one."

What a strong answer should cover:

Isolation is a dial between correctness and concurrency: the stricter the level, the more the database must block or abort concurrent transactions
The three read phenomena the SQL standard uses: dirty read, non-repeatable read, phantom read — and the four levels defined by which of them they allow (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)
Anomalies the standard table misses: lost update and write skew; only SERIALIZABLE rules out all of them
Real defaults: PostgreSQL, SQL Server and Oracle use READ COMMITTED, MySQL InnoDB uses REPEATABLE READ, SQLite is serializable; engines may implement a level more strictly than the standard (PostgreSQL treats READ UNCOMMITTED as READ COMMITTED)
Implementation: locks versus MVCC snapshots, and how to set the level per transaction

Code / implementation expected: Optional — a two-session timeline showing one anomaly, and the syntax to set a level for one transaction.

Schema used by the examples:

isolation-levels

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know what a transaction is and want to understand what "isolation level" means, and candidates who need the anomaly names, the four standard levels and the real engine defaults. Difficulty: Medium Engines: PostgreSQL 16.15 (all two-session timelines were run on it with two concurrent psql sessions) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples use). MySQL, SQL Server and Oracle facts are cited from their documentation. The PostgreSQL-specific detail for each level is on its own page: Transaction isolation levels in Postgres — what anomalies does each level prevent?

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interv

The solution is hidden so you can try first

14 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLA lost update replayed step by step: two sessions read 5, both write 4

SQLThe same two sales as atomic updates: the database reads and writes in one step

PostgreSQL: set the level for one transaction and read it back

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT current_setting('transaction_isolation')         AS this_transaction,
       current_setting('default_transaction_isolation') AS server_default;
COMMIT;

PostgreSQL: READ UNCOMMITTED is accepted (it behaves as READ COMMITTED)

BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SELECT current_setting('transaction_isolation') AS level_you_asked_for,
       (SELECT balance FROM accounts WHERE id = 1) AS alice_balance;
COMMIT;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.