skip to content

Name the four transaction isolation levels defined by the SQL standard and explain how each is defined — what distinguishes one level from the next?

level: middleimportance: must knowfreq 78%

answer

  1. Four levels: RU, RC, RR, SER
  2. Defined by permitted phenomena, not by mechanism
  3. Dirty / non-repeatable / phantom columns
  4. Ceiling not floor — engines may be stricter
  5. Phenomena list is incomplete; snapshot RR passes it anyway

basics

~20 s

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. The standard defines each by which phenomena it may permit: dirty reads at the lowest level only, then non-repeatable reads, then phantoms, and SERIALIZABLE permits none. Levels are upper bounds, so engines may be stricter.

solid answer

~50 s

The SQL standard defines four levels, weakest to strongest: - **READ UNCOMMITTED** — may see other transactions' uncommitted writes. - **READ COMMITTED** — only committed data, but a re-read of the same row can return a different value. - **REPEATABLE READ** — rows you already read stay stable, but a repeated *query* may return newly matching rows. - **SERIALIZABLE** — the result must be equivalent to some serial execution. The key framing: the standard defines levels **negatively**, by which phenomena each is *allowed* to permit, not by implementation. So a level is an upper bound on badness, and an engine may legally be stricter — many multi-version engines never show uncommitted data at any level, and several block far more than the name requires at REPEATABLE READ. You select it with `SET TRANSACTION ISOLATION LEVEL`, per transaction or as a session default; READ COMMITTED is the most common shipped default.

code

sql · 7 lines
sql
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  SELECT SUM(amount) FROM ledger WHERE account_id = 7;
  SELECT COUNT(*)    FROM ledger WHERE account_id = 7;
COMMIT;

go deeper

for a junior

List the four levels in order and the three phenomena each does or does not permit; that recall alone passes a screening question.

for a middle

Stress that levels are defined as ceilings on permitted phenomena, so engines can be stricter, and give the typical locking behaviour behind each level.

for a senior

Point out that the phenomena list is incomplete and lock-centric, and describe how you would verify the concrete guarantee your engine gives at each level before relying on it.

for a principal

Treat level selection as a portability and policy question: a default level, documented exceptions, and invariants enforced by constraints so correctness does not hinge on a name whose meaning varies by product.

## The ladder The SQL standard names four isolation levels and orders them from weakest to strongest: **READ UNCOMMITTED**, **READ COMMITTED**, **REPEATABLE READ**, **SERIALIZABLE**. Each step up forbids more of the ways a concurrent transaction's work can leak into yours. Crucially the standard does not describe *how* an engine achieves a level. It describes each level by the set of concurrency phenomena the level is **permitted to allow**. Three phenomena appear in the classic table: - **Dirty read** — you read a row version written by a transaction that has not committed. - **Non-repeatable read** — you read a row, someone else commits an update to it, you read it again in the same transaction and get a different value. - **Phantom read** — you run a range query, someone else commits an insert (or an update) that changes which rows match, you re-run the query and the row *set* differs. The permitted-phenomena table: | Level | Dirty read | Non-repeatable read | Phantom | |---|---|---|---| | READ UNCOMMITTED | possible | possible | possible | | READ COMMITTED | no | possible | possible | | REPEATABLE READ | no | no | possible | | SERIALIZABLE | no | no | no | ## Reading the table correctly The single most common mistake is treating those cells as *guarantees that the anomaly happens*. They are permissions. A level is a ceiling on how much anomaly you may be exposed to, so an engine that is stricter than the label still conforms. Two consequences follow. First, the same level name can behave differently across products. Multi-version engines maintain multiple committed versions of each row and serve readers from a snapshot; such an engine typically cannot produce a dirty read at all, so asking for READ UNCOMMITTED buys you nothing beyond the syntax. Meanwhile a lock-based engine implements READ COMMITTED by taking a shared lock for the duration of the statement and releasing it immediately, which really does allow the row to change before your next read. Second, the phenomena list is famously *incomplete*. It was written around lock-based implementations, and it does not name every way a schedule can be non-serializable. Snapshot-based implementations of REPEATABLE READ avoid all three listed phenomena yet still admit interleavings that no serial order could produce, which is why the standard's names are a poor substitute for reading what your engine actually guarantees. ## What each level costs and buys **READ UNCOMMITTED** exists to let a reader avoid taking read locks entirely in lock-based systems. It buys throughput for scans that tolerate garbage; it can return data that never existed after a rollback, and on some engines a lock-free scan can also miss or double-count rows while pages move underneath it. **READ COMMITTED** is the pragmatic default nearly everywhere. Each statement sees a fresh view of committed data. Two statements in the same transaction may disagree, which is fine for most request-scoped work and cheap because read locks are short or absent. **REPEATABLE READ** stabilizes what you have already read for the whole transaction — typically by holding shared locks until commit, or by pinning one snapshot taken at transaction start. Useful when a transaction reads the same data more than once, or reads several tables that must agree with each other. **SERIALIZABLE** is the only level that promises the outcome is explainable by some one-at-a-time order. Implementations either escalate to range/predicate locking (blocking, deadlocks) or detect dangerous read/write dependencies and abort a participant. Either way the application must be prepared to wait or to retry. ## Selecting a level `SET TRANSACTION ISOLATION LEVEL READ COMMITTED;` before `BEGIN` (or `SET SESSION ...` for a default) is the standard mechanism, and every mainstream ORM or transaction manager exposes it declaratively. It applies from the start of the transaction: a level chosen after you have already read data cannot retroactively make those reads stable. Practical guidance: keep a sane global default (usually READ COMMITTED), raise the level only for the specific transactions whose correctness depends on it, and remember that many invariants are better enforced with a constraint or a single atomic statement than by escalating everyone's isolation.

  • The standard says REPEATABLE READ permits phantoms. If an engine's REPEATABLE READ never shows phantoms, is it non-conforming?
    No. The table lists phenomena a level *may* permit, so being stricter is legal. Several engines implement REPEATABLE READ over a transaction-wide snapshot or with next-key locks and never expose phantoms to a plain query. This is exactly why you should verify the guarantee in your engine's documentation rather than reasoning from the level's name.
  • If the four levels are defined by three phenomena, why isn't avoiding all three the same as serializability?
    Because the three phenomena were catalogued from lock-based implementations and do not enumerate every non-serializable schedule. Snapshot-based isolation avoids dirty reads, non-repeatable reads and phantoms, yet two transactions can each read a consistent snapshot and then write disjoint rows in a combination no serial order allows. Serializability is defined by schedule equivalence, not by passing a checklist.
  • When during a transaction can the isolation level be changed?
    Only before the transaction has performed any reads or writes — in practice, set it before or immediately at BEGIN, or set a session default that new transactions inherit. Once statements have executed, their reads were already governed by the old rules, so most engines reject a mid-transaction change outright.

saying these in an interview costs you the question

  • Reading the phenomena table as 'this anomaly will happen' rather than 'may happen'
  • Assuming a level name means the same behaviour on every engine
  • Claiming SERIALIZABLE simply means 'no phantoms'
  • Saying REPEATABLE READ prevents all lost updates or all write anomalies
  • Believing the isolation level can be raised in the middle of a transaction and retroactively stabilize earlier reads

context

resolving links…