ACID's Isolation property was left deliberately vague in the last lesson: how isolated two transactions actually are from each other is adjustable, not a single fixed guarantee. This lesson covers the four standard levels, and the real, specific default each database ships with.
Learning Objectives
- Name the three anomalies (dirty read, non-repeatable read, phantom read) that isolation levels exist to prevent.
- Match each of the four standard isolation levels to the anomalies it prevents and the ones it still allows.
- Know each database's real default isolation level, since no two of these six behave identically here.
Dirty Read
Sees another transaction's uncommitted change
Non-Repeatable Read
Same row, different value, re-read mid-transaction
Phantom Read
A re-run query finds new rows
SERIALIZABLE
Prevents all three
The Three Anomalies
A dirty read sees a value another transaction has changed but not yet committed, which can vanish entirely if that other transaction rolls back. A non-repeatable read re-reads the exact same row twice inside one transaction and gets two different values, because another, already-committed transaction changed it in between. A phantom read re-runs the exact same WHERE condition twice and gets a different set of rows, because another transaction inserted or deleted a matching row in between.
The Four Standard Isolation Levels
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Prevented | Possible | Possible |
| REPEATABLE READ | Prevented | Prevented | Possible (by the standard's minimum) |
| SERIALIZABLE | Prevented | Prevented | Prevented |
This table is the ANSI SQL standard's minimum guarantee for each level, what a database is allowed to permit. Several real databases prevent more than their level's minimum requires, covered next.
Setting an Isolation Level
Every database here except SQLite supports this same ANSI-style statement. SQLite has no SET TRANSACTION ISOLATION LEVEL statement at all, its own locking (or WAL) model gives behavior close to SERIALIZABLE by default, without exposing the four named levels as a setting.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
The Real Defaults and Quirks, by Database
| Database | Default Level | What's Actually Different |
|---|---|---|
| MySQL / MariaDB (InnoDB) | REPEATABLE READ | The one database defaulting here. InnoDB's REPEATABLE READ also prevents most phantom reads via next-key locking, stronger than the standard's bare minimum for this level. |
| PostgreSQL | READ COMMITTED | Only 3 distinct levels actually exist, requesting READ UNCOMMITTED silently behaves exactly like READ COMMITTED, dirty reads are structurally impossible under PostgreSQL's MVCC engine regardless of level. |
| SQL Server | READ COMMITTED | The only one of these six also offering a 5th, non-standard SNAPSHOT level (optimistic, row-versioning based), off by default alongside the standard four. |
| Oracle | READ COMMITTED | Only 2 levels exist at all, READ COMMITTED and SERIALIZABLE, there's no REPEATABLE READ and no READ UNCOMMITTED, the same MVCC reasoning as PostgreSQL rules out a true dirty read entirely. |
| SQLite | No named levels | No SET TRANSACTION ISOLATION LEVEL statement exists, its default locking behavior is close to SERIALIZABLE. |
Try It Yourself
Beacon Robotics' reporting job must never see another transaction's uncommitted salary change while it runs. Which standard isolation level is the minimum that guarantees this, and is it already the default on PostgreSQL?
READ COMMITTED is the minimum level that prevents dirty reads, and it's already PostgreSQL's default, no SET TRANSACTION ISOLATION LEVEL statement is needed here at all.
Frequently Asked Questions
Does a higher isolation level always mean slower performance?
Generally yes, more isolation means more locking or more work tracking concurrent versions, SERIALIZABLE is the strictest and typically the most expensive under real concurrent load. Most applications use their database's default (READ COMMITTED or REPEATABLE READ) and only raise it for the specific transactions that genuinely need the stronger guarantee.
Why does MySQL default to REPEATABLE READ when most of the others default to READ COMMITTED?
It's simply InnoDB's own design choice, made stronger than the standard's bare minimum for that level specifically to prevent most phantom reads too, via its next-key locking scheme, rather than a requirement every database follows.
Common Beginner Mistakes
Assuming every database's REPEATABLE READ behaves identically
It doesn't, MySQL/MariaDB's InnoDB implementation also prevents most phantom reads, stronger than the ANSI minimum. Oracle doesn't even offer REPEATABLE READ as a selectable level at all, only READ COMMITTED and SERIALIZABLE exist there.
Expecting SET TRANSACTION ISOLATION LEVEL to work on SQLite
It isn't a SQLite statement, SQLite has no concept of the four named isolation levels as a setting, its own locking or WAL model governs concurrent access instead.
Best Practices
- Know your database's actual default, don't assume it matches the ANSI standard's suggested baseline or another database you've used before.
- Raise the isolation level only for the specific transaction that needs the stronger guarantee, not globally, since it generally costs concurrency and performance.
- Test concurrent behavior against the real database being used, isolation-level quirks are specific to each engine's actual implementation, not just the standard's definitions.
Interview Questions
What's the difference between a non-repeatable read and a phantom read?
A non-repeatable read is the same row returning a different value on a second read within one transaction, because another transaction updated that specific row and committed in between. A phantom read is a re-run query returning a different set of rows entirely, because another transaction inserted or deleted a row matching the WHERE condition and committed in between, the row count changes, not just one row's value.
Why can PostgreSQL and Oracle never produce a true dirty read, regardless of isolation level?
Both use MVCC (multi-version concurrency control): every transaction reads from a consistent snapshot of already-committed data, an uncommitted change from another transaction simply isn't visible in any snapshot at all, there's no mechanism by which it could leak through, unlike a purely lock-based engine where READ UNCOMMITTED means skipping the lock check entirely.
Summary
Four standard isolation levels, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, trade off how much of another open transaction's work can leak through against how much concurrency is lost. Every database here defaults differently (MySQL: REPEATABLE READ, everyone else that has named levels: READ COMMITTED), and several implement their levels more strictly than the standard's bare minimum, SQLite doesn't expose named levels at all.
What's Next?
The last lesson in this module puts transactions to work on a full, realistic Beacon Robotics example, including SAVEPOINT and the rollback-on-error pattern an application actually uses.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →