An isolation level is the setting that determines how much concurrent transactions can see of each other’s work while they run, and therefore which read anomalies a database permits. In database transactions, it is the configurable form of the I in ACID: the stronger the level, the closer concurrent execution comes to a serial schedule, and the higher the cost in locking, aborts or retries.
The SQL standard defines four levels: Read uncommitted, Read committed, Repeatable read and Serializable. Real engines implement them with locks, multiversion concurrency control (MVCC) or a mix of both, so the same level name can behave differently in PostgreSQL, MySQL InnoDB, SQL Server and Oracle.
How an isolation level controls concurrent transactions
An isolation level works by limiting which versions of data a transaction may read and which conflicting writes it must wait for or reject. In a lock-based engine, the level decides how long shared read locks are held: not at all, only for the statement, or until commit. In an MVCC engine, the level decides when the read snapshot is taken: once per statement or once per transaction.
The goal is to rule out specific read phenomena, the observable effects of interleaving. The standard names three of them, and practice has added several more that matter just as much in production systems.
| Phenomenon | What happens | Typical consequence |
|---|---|---|
| Dirty read | A transaction reads data another transaction has not committed | Decisions based on values that may be rolled back |
| Non-repeatable read | The same row returns different values within one transaction | Inconsistent calculations across statements |
| Phantom read | A repeated range query returns new or missing rows | Wrong counts, totals or uniqueness checks |
| Lost update | Two read-modify-write cycles overwrite each other | Silently lost increments or edits |
| Write skew | Two transactions read overlapping data and write disjoint rows | Business invariants broken without any conflict |
The four standard isolation levels
The four levels form a ladder of guarantees: each level forbids everything the one below forbids and adds at least one more restriction. The standard defines them only by the phenomena they prevent, which leaves engines free to exceed the minimum, and many do.
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read uncommitted | Possible | Possible | Possible |
| Read committed | Prevented | Possible | Possible |
| Repeatable read | Prevented | Prevented | Possible by standard |
| Serializable | Prevented | Prevented | Prevented |
Serializable is the only level that guarantees the result equals some serial order of the transactions. Every weaker level trades part of that guarantee for higher concurrency. A further level, snapshot isolation, is not in the standard table but is widely implemented: each transaction reads a consistent snapshot and fails if it writes a row that another transaction already changed, yet it still permits write skew.
How major engines implement isolation levels
Level names are portable, but behavior is engine-specific, which is the most common source of surprises when an application moves between databases. Defaults also differ, so an application that never sets a level still runs under one.
| Engine | Default level | Implementation notes |
|---|---|---|
| PostgreSQL | Read committed | Repeatable read is snapshot isolation; Serializable uses SSI with dependency tracking |
| MySQL InnoDB | Repeatable read | Consistent snapshot for plain reads; next-key locks block phantoms for locking reads |
| SQL Server | Read committed | Lock-based by default; optional row versioning via snapshot settings |
| Oracle Database | Read committed | Statement-level snapshots; its Serializable is effectively snapshot isolation |
Serializable snapshot isolation (SSI), used by PostgreSQL, detects dangerous read-write dependency patterns and aborts one transaction with a serialization failure instead of blocking. Lock-based Serializable instead holds range or predicate locks, which prevents phantoms by making writers wait, at the cost of more blocking and a higher chance of a deadlock.
Choosing an isolation level for a workload
The right isolation level is the weakest level that still protects the invariants the workload depends on. The choice is made per transaction, not per database: most engines accept a SET TRANSACTION ISOLATION LEVEL statement at the start of a transaction, so a reporting query and a funds transfer can run under different rules.
| Situation | Recommended level | Reason |
|---|---|---|
| Simple single-row updates | Read committed | Row locks already prevent lost updates on one row |
| Multi-statement reports | Repeatable read or snapshot | All queries see one consistent point in time |
| Check-then-insert uniqueness rules | Serializable | Prevents phantoms and write skew |
| Balances, bookings, inventory limits | Serializable or explicit locking | Invariants span several rows |
| Approximate monitoring queries | Read committed | Read uncommitted rarely saves meaningful cost |
Stronger levels require retry logic in the application. Under Serializable, a transaction can fail with a serialization error even though nothing is wrong with its SQL; the correct response is to roll back and rerun the whole transaction, usually with a small backoff. Code that cannot retry safely should not rely on a strict level alone.
Common mistakes with isolation levels
Most isolation bugs come from assuming a guarantee the level does not give. They are hard to reproduce because they appear only under concurrent load, often in production rather than in tests.
- Assuming Repeatable read means serializable: snapshot-based Repeatable read still allows write skew.
- Relying on the default level without knowing it differs between engines.
- Using SELECT followed by UPDATE without
SELECT ... FOR UPDATEor an atomic update, which invites lost updates. - Raising the level without adding retry handling, which turns rare anomalies into frequent errors.
- Keeping transactions open for user think time, which lengthens lock waits and snapshot retention at any level.
A related misconception treats Read uncommitted as a performance setting. In MVCC engines readers do not block writers anyway, so the level saves little and exposes uncommitted data; PostgreSQL even treats it as Read committed.
A non-technical view
Imagine several clerks updating the same set of paper ledgers at once. An isolation level is the house rule about how much each clerk may look at the others’ unfinished entries. A loose rule lets clerks peek at pencil notes that might be erased. A strict rule makes every clerk work as if alone, even when that means waiting or starting over.
Stricter rules give more trustworthy results but slower work. The database lets each task choose the rule it needs, so quick lookups can stay fast while money movements stay exact.
A concrete example
A hospital scheduling system requires that at least one doctor stays on call for each shift. Two doctors, both on call, request leave at the same moment. Each request runs a transaction that counts on-call doctors, sees two, and removes its own doctor. Under snapshot-based Repeatable read, both transactions read the same snapshot, write different rows and both commit, leaving nobody on call: a classic write skew.
The team switches these leave transactions to Serializable and adds a retry loop with up to three attempts. Now the second transaction fails with a serialization error, retries, sees only one doctor on call and rejects the request. Other screens, such as the read-only schedule view, stay at Read committed, so the stricter level adds cost only where the invariant lives.