Database Transaction Isolation Levels Explained (With Real Dirty, Non-Repeatable, and Phantom Reads)

Quick Answer

A transaction isolation level controls how much of one transaction's in-progress work another concurrent transaction is allowed to see. The SQL standard defines four levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — and each stricter level closes off one more concurrency bug: dirty reads, non-repeatable reads, and phantom reads. We didn't just describe these anomalies, we reproduced all three live on a real InnoDB-backed MySQL/MariaDB server so you can see the actual values a transaction read at each level, not a diagram of what should happen.

Why Isolation Levels Exist

The moment more than one transaction touches a database at the same time, you have to decide how much of transaction A's half-finished work transaction B is allowed to see. Lock everything down completely and you get correctness but transactions queue up behind each other, killing throughput. Let every transaction see everything immediately and you get speed but also broken, inconsistent reads. Isolation levels are the dial between those two extremes, and the SQL standard names four settings on that dial, each one closing off exactly one more class of bug than the level below it.

Three specific anomalies give the whole topic its vocabulary, and it's worth being precise about each one because they sound similar but are genuinely different bugs:

• Dirty read — your transaction reads a value that another transaction wrote but has not committed yet. If that other transaction then rolls back, you made a decision based on data that never actually existed in the database's history.
• Non-repeatable read — you read the same row twice inside one transaction and get two different answers, because another transaction committed a change to that row in between your two reads.
• Phantom read — you run the same range query (a WHERE clause matching a set of rows, not a single row) twice inside one transaction, and the second run returns a different set of rows because another transaction inserted or deleted a row that falls inside your range.

The four standard isolation levels map onto these three anomalies like this:

That table is the textbook version. The three demos below show what each row of that table actually looks like as real SQL output on a live server, and one of them turns up a genuine surprise: a popular real-world engine does better than the standard technically requires.

Everything in this article was run against a real MariaDB 10.1 server with the InnoDB storage engine, using two separate client connections to simulate two concurrent transactions, labelled A and B below. InnoDB is the same storage engine MySQL uses, so the behavior transfers directly.

Demo: A Real Dirty Read Under READ UNCOMMITTED

Here's the setup: an accounts table with one row, balance 1000. Transaction A updates that balance to 9999 but does not commit, holds the transaction open for a few seconds, then rolls back. While A is sitting there uncommitted, transaction B tries to read the balance.

Transaction A's script:

START TRANSACTION;
UPDATE accounts SET balance = 9999 WHERE id = 1;
-- not committed yet, holding the transaction open
SELECT SLEEP(4);
ROLLBACK;

Transaction B, run while A is mid-transaction, first with READ UNCOMMITTED:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

The real output from that second query, captured while A was still uncommitted:

label                           balance
B (READ UNCOMMITTED) sees:      9999

B read 9999 — a value that, from the database's point of view, never actually existed as a committed fact. A few seconds later A rolled back, and the table went right back to 1000:

id  balance
1   1000

That's a textbook dirty read: B made a decision on data that was later erased. Now the exact same scenario, except B uses READ COMMITTED instead:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

Real output this time:

label                         balance
B (READ COMMITTED) sees:      1000

Same uncommitted update happening on A's side, but B now correctly sees the last committed value, 1000, and never sees the in-flight 9999 at all. That's the entire practical difference between the two weakest isolation levels, demonstrated with real numbers instead of a diagram: Read Uncommitted will hand you a value that might get erased a moment later; Read Committed never will. Almost no production system actually runs at Read Uncommitted for exactly this reason — the only upside is slightly less locking overhead, and the downside is that any report, balance check, or conditional logic built on top of a dirty read can act on a fact that turns out to be false.

Demo: A Real Non-Repeatable Read Under READ COMMITTED

This time transaction A reads the same balance twice inside one transaction, with a 3-second gap in between. During that gap, transaction B commits a change to the row. Under REPEATABLE READ (InnoDB's default), A's script was:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- first read
SELECT SLEEP(3);
SELECT balance FROM accounts WHERE id = 1; -- second read, same transaction
COMMIT;

While A was sleeping, B ran and committed: UPDATE accounts SET balance=500 WHERE id=1; — an ordinary, immediately-committed update. A's real output:

label                                           balance
A [REPEATABLE READ] first read:                 1000
A [REPEATABLE READ] second read (same txn):     1000

Both reads came back 1000, even though B's update to 500 had already committed in between them. A's transaction is reading from a consistent snapshot taken at the start of the transaction, and that snapshot doesn't move until A commits or rolls back.

Now the identical scenario, with A set to READ COMMITTED instead:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- first read
SELECT SLEEP(3);
SELECT balance FROM accounts WHERE id = 1; -- second read, same transaction
COMMIT;

Real output:

label                                         balance
A [READ COMMITTED] first read:                1000
A [READ COMMITTED] second read (same txn):    500

Same transaction, same two SELECT statements, but the second one returned a different number than the first, purely because B committed in between. That's a non-repeatable read, reproduced with the actual two values a real transaction saw. If A's logic was something like 'read balance, do some calculation, read balance again to confirm it's still correct before finalizing,' Read Committed just handed it two different realities to reconcile — and most application code isn't written to expect that.

This is the exact mechanical reason Repeatable Read (or Postgres's equivalent snapshot-based levels) exists: it fixes a transaction's view of the world for its entire duration, so every read inside it tells a consistent story, no matter what anyone else commits in the meantime.

Demo: A Real Phantom Read — and a Genuine Surprise From InnoDB's MVCC

Phantom reads are about a changing set of rows rather than a changing value in one row. The setup: an orders table with three rows (amounts 50, 60, 70), and transaction A runs SELECT COUNT(*) FROM orders WHERE amount > 40 twice, 3 seconds apart. In between, transaction B inserts a brand new row with amount 999, which also matches amount > 40, and commits it.

Under READ COMMITTED, A's real output:

label                                                   n
A [READ COMMITTED] first range read (amount>40):        3
A [READ COMMITTED] second range read, same txn:         4

The count changed from 3 to 4 inside the same transaction — B's new row 'phantomed' into A's result set. That's the anomaly by the book.

Here's the surprise. Run the identical scenario under REPEATABLE READ, the level the SQL standard says is still allowed to have phantom reads:

label                                                   n
A [REPEATABLE READ] first range read (amount>40):       3
A [REPEATABLE READ] second range read, same txn:        3

The count stayed at 3 both times. InnoDB's Repeatable Read did not let the phantom row show up, even though the SQL standard's own definition of Repeatable Read explicitly permits this anomaly. This isn't a mistake in our demo — it's a documented property of how InnoDB implements MVCC: an ordinary (non-locking) SELECT inside a Repeatable Read transaction reuses the same consistent read view, taken at the first read of the transaction, for every subsequent plain read in that transaction. Since B's insert happened after that read view was created, it simply isn't visible to A at all, for a range query exactly as much as for a single-row read.

The catch is the word 'ordinary.' This protection only covers plain, non-locking SELECT statements. The moment you use a locking read — SELECT ... FOR UPDATE, or an UPDATE/DELETE with a matching WHERE clause — InnoDB switches mechanisms entirely and uses gap locks and next-key locks on the index to physically block other transactions from inserting into the locked range, rather than relying on a snapshot. Both mechanisms end up preventing phantoms for Repeatable Read in InnoDB, but through two different paths depending on whether your read was a plain snapshot read or a locking one. The practical takeaway: don't assume 'the SQL standard says Repeatable Read allows phantoms' tells you what your specific database actually does — check the engine's documentation, because InnoDB is deliberately stricter than the floor the standard sets.

Real-World Defaults Are Not What the Standard Picks

It's worth knowing what each major engine actually ships with by default, because it's rarely the strictest option:

• MySQL and MariaDB (InnoDB) default to REPEATABLE READ. We confirmed this directly on the server used for every demo above — running SELECT @@tx_isolation; against a fresh connection returned REPEATABLE-READ with no configuration changes.
• PostgreSQL defaults to READ COMMITTED. Postgres's own documentation states this plainly, and also notes you can raise it per-session or per-transaction with SET TRANSACTION ISOLATION LEVEL.
• The SQL standard itself actually specifies SERIALIZABLE as the default when nothing else is set — meaning both of the most popular open-source databases ship a weaker default than the standard assumes, trading some correctness guarantees for throughput out of the box.

This matters because 'my database guarantees repeatable reads' is only true if you (or your ORM, or your driver) haven't silently inherited Postgres's Read Committed default. A piece of code written and tested against MySQL's Repeatable Read default can behave subtly differently when the same queries run against a Postgres instance still on its own Read Committed default — the non-repeatable-read demo above is exactly the kind of bug that would only show up on one of the two engines.

Serializable, the strictest level, isn't shown in a live demo here because its defining property isn't a specific anomaly, it's a stronger guarantee: transactions behave as if they ran one after another in some order, full stop, even though they were actually interleaved for performance. Engines implement this with either full locking (blocking any conflicting transaction until the first one finishes) or optimistic conflict detection (letting transactions proceed and rolling one back if a conflict is detected at commit time, called Serializable Snapshot Isolation in Postgres). Both approaches can cause a transaction to simply fail and need a retry under contention, which is the real-world cost of the strongest guarantee.

Which Level Should You Actually Reach For

For the overwhelming majority of application code — reading a user's profile, listing products, rendering a dashboard — the engine's default is fine, and you should leave it alone rather than cargo-culting a stricter setting you read about once. The cost of Serializable isn't theoretical; it's measurably more locking or more transaction retries, and most reads don't need protection from anomalies that would only matter if two transactions happened to touch the exact same rows in the exact same window.

The decision point is specific logic that reads a value, makes a decision based on it, and then writes something back, where a stale or inconsistent read genuinely changes the outcome — not just displays slightly old data. Classic cases: decrementing inventory before confirming a sale, moving money between two account rows, or any 'check a condition, then act on it' sequence spanning more than one statement. For those, either raise the isolation level for that specific transaction, or use an explicit locking read (SELECT ... FOR UPDATE) to force serialization on just the rows that matter, rather than paying the cost of Serializable for every transaction in the system. If you want to go deeper on how databases physically avoid blocking readers against writers while still giving these guarantees, that's the subject of MVCC, which is a direct follow-up to everything demonstrated here. You can also practice all of this directly against a real schema in the SQL course on Priodemy.

One more habit worth building: treat an isolation-level bug the same way you'd treat any other bug that only shows up under specific timing — write a reproduction first, the way every demo in this article did, rather than reasoning about it in the abstract. A 'this occasionally shows the wrong total' report from production is nearly impossible to debug directly, but the same bug becomes trivial to understand once you can force two sessions to interleave in a controlled way and watch exactly which read saw which value. The SQL scripts shown above, run with a deliberate SLEEP() to hold a transaction open while a second session acts, are a reusable pattern for exactly that kind of reproduction, and they work the same way against Postgres with its own session-level isolation commands, not just the MySQL/MariaDB syntax shown here.

Frequently Asked Questions