What does the isolation property of ACID promise about transactions that run at the same time, and why do real database engines let you weaken that promise?
answer
- Serializable = equivalent to *some* serial order
- Only ACID letter with a dial
- Cost: lock-and-block, or track-and-abort
- Levels defined by permitted phenomena, not implementation
- SET TRANSACTION ISOLATION LEVEL, applies from transaction start
basics
~20 sIsolation means concurrent transactions must not see each other's unfinished work: the outcome should match some order where they ran one at a time. Enforcing that fully costs locking, blocking and aborts, so engines offer weaker levels that trade specific anomalies for throughput.
solid answer
~50 s**Isolation** is the ACID property saying a transaction should behave as if it had the database to itself. The formal ideal is **serializability**: whatever interleaving the engine actually executed, the final state and every value read must be explainable by *some* serial, one-at-a-time order of those transactions. The catch is cost. Guaranteeing serializability requires either holding locks until commit (readers block writers or writers block readers, deadlocks appear) or tracking read/write dependencies and aborting transactions that would break the order. Both cut concurrency and add retries. So isolation is the one ACID property that is deliberately **tunable**. The SQL standard defines weaker levels, each defined by which concurrency phenomena it may permit, and you select one per session or per transaction. Atomicity and durability are all-or-nothing; isolation is a dial you turn per workload, accepting named anomalies in exchange for throughput.
code
sql · 5 linesSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT balance FROM account WHERE id = 42;
UPDATE account SET balance = balance - 100 WHERE id = 42;
COMMIT;go deeper
Define isolation as 'concurrent transactions must not see each other's half-finished work', name serializability as the ideal, and say engines offer weaker levels for speed.
Add the mechanism: locks held to commit versus snapshot plus dependency checks, and that levels are defined by which phenomena they permit.
Frame it as an explicit correctness/throughput budget per workload, and mention that the safest fix is often pushing the invariant into a constraint or atomic statement rather than raising the level everywhere.
Discuss it as a system-wide policy: default level, which flows get raised, mandatory retry handling, and how engine-specific interpretations of the standard names affect portability.
## What isolation claims A transaction is a group of reads and writes the database treats as one unit. With one transaction at a time, reasoning is trivial: it sees a stable database, does its work, commits. Isolation tries to preserve that illusion while many transactions actually run interleaved on many cores. The textbook definition is **serializability**. The engine executes a *schedule* — the real, interleaved order of individual reads and writes from all in-flight transactions. A schedule is serializable if its effects (the values every transaction read, and the final database state) are identical to those of at least one *serial* schedule, where the same transactions ran back to back in some order. Note what that does *not* promise: it does not promise a particular order, and it does not promise real-time order. It only promises that no transaction can observe evidence that another was halfway done. ## Why weaker levels exist Serializability is expensive because it must constrain interleaving. Two families of mechanism dominate. *Pessimistic*: two-phase locking. Each transaction acquires shared locks to read and exclusive locks to write, and holds them all until commit. Correct, but readers and writers block each other, hot rows serialize the whole workload, and lock cycles produce deadlocks that the engine resolves by killing a victim. *Optimistic / multi-version*: readers see a consistent snapshot and never block; the engine tracks dependencies between transactions and aborts one when the observed interleaving could not have come from any serial order. No blocking, but a retry loop is mandatory and abort rates rise with contention. Either way you pay in latency, in throughput on contended data, or in application-visible retries. Most business transactions do not actually need full serializability — a read of a product description or a dashboard aggregate tolerates slight staleness. So the SQL standard exposes a ladder of levels, each *defined by the anomalies it is permitted to allow* rather than by an implementation. From weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Weaker levels hold fewer or shorter locks (or check fewer dependencies), so they let more work proceed in parallel — and let specific misreads happen. ## How you select it Standard SQL sets it with `SET TRANSACTION ISOLATION LEVEL <level>`, either for the next transaction or, in most engines, as a session default that new transactions inherit. It takes effect at transaction start; you cannot meaningfully raise the level of a transaction that has already read data, since the reads it already made were made under the old rules. In practice frameworks expose it declaratively (a transaction annotation or a connection setting) and almost every engine ships READ COMMITTED as the default. A critical detail: the standard defines levels by *permitted* phenomena, not required ones. An engine is free to be stricter than the label. That is why the same level name can behave differently across products, and why you should reason about the concrete guarantee your engine documents rather than about the name alone. ## Isolation vs the other ACID letters Atomicity (all-or-nothing) and durability (committed means survives a crash) are binary — no engine offers you 70% of them. Consistency in ACID means the transaction moves the database from one valid state to another with respect to declared constraints, which is largely the application's and the constraint system's job. Isolation is the only letter with a dial, and the dial exists because the alternative to weak isolation is not just slower software but frequently unusable software under contention. ## What this means when you build Choosing a level is choosing which incorrect-but-tolerable observations your code may see. That decision only holds if it is written down and tested: a report that was fine seeing slightly stale rows becomes a bug the day someone reuses that query to compute a balance you then write back. Where money, inventory counts or uniqueness are at stake, either raise the level for that transaction or push the invariant into the database — a unique constraint, a check constraint, or an atomic `UPDATE ... SET qty = qty - 1 WHERE qty >= 1` — so the engine enforces it regardless of the level chosen.
- Serializability guarantees equivalence to some serial order — does it guarantee the order matches when the transactions actually started?No. Plain serializability only requires that *some* serial order explains the outcome; it may be an order unrelated to real time. The stronger property that also respects real-time ordering is strict serializability, which distributed systems care about because a client may observe a commit and then read from another node. Single-node engines usually give you strictness in practice, but the ACID definition alone does not demand it.
- Why can't you just run everything at SERIALIZABLE and stop thinking about it?You often can, and for low-contention systems it is a reasonable default. The costs are blocking or serialization failures under contention: lock-based implementations turn hot rows into a queue, and dependency-tracking implementations abort transactions that must be retried. Serializable also demands that every application path handles a retryable failure, which is a real code discipline, not a config flag.
- Does the isolation level affect what a transaction's own uncommitted writes look like to itself?No. Every level guarantees a transaction reads its own uncommitted writes — that is read-your-own-writes within the transaction and is not considered an anomaly. Isolation levels only govern what one transaction may observe of *other* transactions' concurrent work.
Serializability is like a single-lane bridge: cars cross one at a time so nobody sees a half-crossed car. Weaker isolation opens extra lanes for throughput and accepts that a driver may glimpse a neighbour mid-manoeuvre.
saying these in an interview costs you the question
- Saying isolation means transactions literally run one at a time (it means the result is equivalent to some serial order)
- Claiming all four ACID properties are equally absolute — isolation is the tunable one
- Assuming the same level name gives identical behaviour on every engine
- Thinking a weaker level makes the transaction less atomic or less durable
- Saying serializability guarantees transactions commit in the order they started