Chapter

Isolation Levels and What You Can Observe

Part D · Correct Changes|3,928 words|about 17 min read|Volume D

23.0 What this chapter gives you#

A transaction needs an answer to a deceptively simple question: which changes made by other transactions may it see? Isolation levels describe parts of that answer and the anomalies that remain possible.
You will distinguish statement snapshots from transaction snapshots, trace non-repeatable reads and write skew, and understand what serializable execution promises without mistaking it for a single physically sequential worker.
Product-specific statements in this chapter refer to PostgreSQL 17 unless explicitly marked otherwise. SQLite examples are kept separate. The written schedules are teaching models, not fabricated server traces.

23.1 Visibility rules#

23.1.1 PLAIN — in simple words#

While Mira is changing an order, Dev may be reading it. The database needs rules about whether Dev sees the old version, the new version or a wait before receiving an answer.
“A transaction hides changes” is too vague. We must ask whose changes, until which boundary, and whether two statements in the same transaction can see different committed worlds.
An isolation level does not decide whether a recorded sale actually occurred. It governs interactions among database operations. Good visibility rules cannot transform invented input into a verified real-world event.

23.1.2 PLAIN — a picture in your head#

Imagine viewing a noticeboard through a window while someone updates its cards. One rule gives you a fresh photograph whenever you ask a question. Another gives you one photograph to use throughout a consultation.
Both avoid watching half-erased handwriting, but they do not give the same answers after the board changes.
Where the comparison breaks: transactions can normally see their own earlier writes, so their view is not simply a frozen photograph of everyone else’s board. Locking and conflict checks may also affect what happens when they write.

23.1.3 PLAIN — a worked example#

Suppose product ISO-PEN has price 100 in a separate example. Transaction A changes it to 120 but has not committed. Transaction B asks for the price.
In the PostgreSQL levels discussed here, B does not read A’s uncommitted replacement as an ordinary visible committed value. What B later sees after A commits depends on B’s snapshot and statement timing.
If A rolls back, its transactional price replacement must not become an accepted committed price. A read that relied on an uncommitted replacement would have relied on a change that never became committed history.
Keep this separate from sequences: PostgreSQL sequence advancement has special non-rollback behaviour. A generated number is not itself proof of a committed business record. [S122]

23.1.4 PLAIN — what is really happening inside#

Engines combine snapshots, locks, row versions and conflict detection in different ways. An isolation-level name is not a substitute for the relevant product documentation.
The SQL standard’s traditional anomaly categories are useful vocabulary, but implementations can offer stronger guarantees than the minimum associated with a name.
A practical contract names the engine, version, level, transaction boundaries and participating writers. It also states what the application does when the engine refuses a conflicting transaction.

23.1.5 TECHNICAL — the engineer’s version#

PostgreSQL 17 implements Read Uncommitted as Read Committed. Its Repeatable Read prevents phantom reads as well as non-repeatable reads, while serialization anomalies remain possible. Serializable adds protection against those anomalies through its concurrency-control implementation. [S66]
A dirty read observes data written by an uncommitted transaction. A non-repeatable read obtains a different value when rereading a row after another transaction commits a change. A phantom concerns changed membership of a repeated predicate query. These categories describe observations, not every possible application failure.
SQLite normally serializes writers; WAL mode permits readers to retain snapshots while a writer works. Its documented exceptions and upgrade failures must be treated as SQLite-specific behaviour, not inferred from PostgreSQL names. [S54]

23.1.6 WORDS — remember these#

Isolation level: the chosen rules for overlapping transactions — a named contract restricting visibility and permitted concurrency anomalies. Dirty read: seeing a change before it commits — observation of another transaction’s uncommitted data. Phantom: a repeated condition matches a changed set — a predicate-query membership change caused by another transaction’s committed work.

23.2 Statement and transaction snapshots#

23.2.1 PLAIN — in simple words#

A statement snapshot gives one query a consistent view of relevant committed data at a particular point. A later query may receive a newer view.
A transaction snapshot lets a transaction continue using an earlier view of other transactions’ committed work. This helps related reads agree, but it does not mean the transaction can safely make every possible decision from that view.
Snapshot age also matters operationally. A long-running reader can keep old information relevant to the engine long after ordinary short requests have moved on.

23.2.2 PLAIN — a picture in your head#

For each question, Mira either asks for today’s newly printed report or keeps consulting the report she collected when the meeting began.
A fresh report can include later corrections; a retained report supports consistent comparisons within the meeting. Neither is automatically preferable for every purpose.
Where the comparison breaks: a database snapshot is not usually a complete copied report. Visibility is determined using version and transaction metadata, and the transaction can see changes it made itself.

23.2.3 PLAIN — a worked example#

Start with ISO-PEN=100. Reader R runs its first SELECT and sees 100. Writer W then commits a change to 120. R runs the same SELECT again.
Under PostgreSQL 17 Read Committed, ordinary separate SELECT statements can return 100 and then 120 because the second statement obtains a later snapshot.
Under PostgreSQL 17 Repeatable Read, R’s snapshot begins with its first non-transaction-control statement. If that snapshot precedes W’s commit, the repeated ordinary read continues to see 100, excluding R’s own changes for simplicity.
Neither result is inherently a lie. The question is which visibility contract the application needed and actually selected. [S66]

23.2.4 PLAIN — what is really happening inside#

The engine checks whether a row version is visible to the snapshot. A later committed replacement can exist while an older transaction still reads an earlier visible version.
Read Committed does not mean every operation inside one statement behaves like a simplistic immutable photograph. PostgreSQL’s modifying statements have rules for waiting on and rechecking concurrently updated rows.
A report requiring several related totals should explicitly choose how their common observation boundary is established. Repeated independent queries cannot be assumed to describe one shared instant.

23.2.5 TECHNICAL — the engineer’s version#

In PostgreSQL 17, Read Committed ordinary SELECT uses a snapshot as of the query’s start and sees the transaction’s own preceding writes. Repeatable Read uses the snapshot established at the transaction’s first non-transaction-control statement. [S66]
A stable snapshot is a visibility mechanism, not a full serializability proof. Transactions can read the same condition and write disjoint rows in ways that violate a combined invariant.
For reproducible demonstrations, record when the snapshot-establishing statement ran. Writing only BEGIN in a timeline can assign the snapshot to the wrong moment in this implementation.

23.2.6 WORDS — remember these#

Statement snapshot: a view chosen for one statement — the visibility boundary used by a query under statement-level snapshot rules. Transaction snapshot: a retained view for related statements — a visibility boundary reused through a transaction under the chosen implementation. Snapshot establishment: the event that chooses the view — the implementation-defined point at which a transaction’s snapshot is acquired.

23.3 Non-repeatable reads#

23.3.1 PLAIN — in simple words#

A non-repeatable read means that reading the same row twice can produce different values because another transaction committed between the reads.
This is sometimes useful: a screen refresh should often reveal a newly changed value. It is dangerous when a calculation assumes that an earlier value remained unchanged across several steps.
The solution is not always “use the strongest level everywhere.” First decide whether the operation needs a common snapshot, a guarded write, a lock or a different workflow.

23.3.2 PLAIN — a picture in your head#

Mira checks the price card, walks to the till, and checks it again after Dev updates the card. The second answer differs because the world she is consulting changed.
A quotation process must decide whether the first observed price was merely information or an agreement that should be preserved.
Where the comparison breaks: a database snapshot can preserve an earlier stored view, but it cannot create a customer agreement that the application never recorded. Historical meaning and transaction visibility are related but different responsibilities.

23.3.3 PLAIN — a worked example#

Consider an analytical exercise with A’s balance=60 and B’s balance=40. A transfer moves 10 from A to B in one transaction, leaving 50 and 50. The total is 100 before and after.
A reader first queries A before the transfer commits and receives 60. It then separately queries B after the transfer commits and receives 50. Adding the two observations gives 110.
Neither query necessarily returned an individually incorrect committed value. The reader combined different observation boundaries and called the result one total.
A single appropriately defined query or a suitable retained snapshot can align the reads. This example is fictional arithmetic, not a banking implementation or financial advice.

23.3.4 PLAIN — what is really happening inside#

The transfer’s atomicity protects its own related writes. It does not automatically force a separate reader’s two statements to share a snapshot.
A transaction can therefore be locally atomic while a poorly designed multi-statement report mixes versions. Different guarantees protect different boundaries.
Document whether a report is “as of one snapshot,” “latest available per query,” or an eventually assembled view. The label affects how readers should interpret discrepancies.

23.3.5 TECHNICAL — the engineer’s version#

Under PostgreSQL Read Committed, successive commands can see different committed data. This makes it unsuitable for a multi-command invariant calculation that silently assumes a stable view unless additional controls establish the required condition. [S66]
Predicate membership also matters. Repeating WHERE status='active' may change the rows returned even when no previously returned row changes value. Analyse both row values and the set being counted.
Do not “fix” a mixed-snapshot report by clamping an unexpected total to a preferred number. Preserve evidence, identify the observation boundary and rerun the defined query under a suitable contract.

23.3.6 WORDS — remember these#

Non-repeatable read: rereading a row yields a later committed value — a permitted observation change under some isolation contracts. Mixed-snapshot calculation: combining facts observed at different boundaries — a derived result that may correspond to no single database state. Observation contract: the stated point or interval a result describes — the temporal and visibility meaning assigned to a query or report.

23.4 Write skew#

23.4.1 PLAIN — in simple words#

Write skew is a different trap from two people overwriting the same row. Two transactions can change different rows and still break a rule about those rows together.
Each transaction may see a stable, internally consistent snapshot. The trouble is that both make decisions without seeing the other’s eventual change.
Protecting only the changed rows may miss the shared condition. The rule, not just the UPDATE target, must guide the concurrency design.

23.4.2 PLAIN — a picture in your head#

Two assistants are on duty, and the rule requires at least one to remain. Each sees that the other is present and decides to leave.
They lock their own lockers carefully, but neither coordinates the shared requirement that somebody stay.
Where the comparison breaks: real staffing has human communication and policy enforcement outside a database. This is a small logical model of a cross-row invariant, not a recommended scheduling system.

23.4.3 PLAIN — a worked example#

A new table contains (Mira,on_duty=1) and (Dev,on_duty=1). The invariant is count(on_duty=1) >= 1.
T1’s snapshot sees two assistants and sets Mira’s row to 0. T2’s snapshot also sees two and sets Dev’s row to 0. Their writes concern different rows.
If both transactions are allowed to commit under this snapshot model, the final count is 0. Either serial order would have made the second transaction see only one assistant and refuse its departure.
The example illustrates a serialization anomaly possible under snapshot isolation, including PostgreSQL’s Repeatable Read model. It is not claimed as a reproduced SQLite two-writer trace. [S66]

23.4.4 PLAIN — what is really happening inside#

Each transaction’s decision depends on a fact changed by the other. Those cross-dependencies create a cycle that cannot be explained by a safe serial order of the stated decision logic.
Locking only Mira’s row in T1 and only Dev’s row in T2 still leaves distinct protected targets. A protocol must cover the shared duty condition, not merely each private update.
Possible designs include a common guard record, locking an appropriately defined shared set with consistent ordering, or serializable transactions with full retries. Each proposal must include every path that can change the duty state.

23.4.5 TECHNICAL — the engineer’s version#

Snapshot isolation can prevent same-row concurrent update patterns while still allowing write skew through disjoint write sets and intersecting read dependencies. Stable reads therefore do not imply serializability. [S66]
A correct serializable execution of the stated decision logic cannot commit both departures from the initial two-person state. At least one transaction must observe the changed condition, wait appropriately or abort and retry under the implementation’s protocol.
A serializable transaction containing faulty decision logic can still commit a bad business outcome. Isolation cannot enforce an invariant that the program neither checks nor represents correctly.

23.4.6 WORDS — remember these#

Write skew: separate writes jointly break a shared rule — an anomaly in which transactions act on overlapping read conditions while changing different records. Read dependency: a decision relies on an observed fact — a relationship between a transaction’s read and another transaction’s relevant write. Serializable outcome: a result explainable by a legal one-at-a-time execution — an outcome equivalent to some serial order of the participating transactions.

23.5 Serialisable execution#

23.5.1 PLAIN — in simple words#

Serializable means that committed transactions behave as though they occurred in some one-at-a-time order. The engine may still run their internal work concurrently.
The word “some” matters. Serializability alone is not a universal promise about real-time order observed by external callers across every system component.
An engine may preserve the guarantee by refusing a transaction and asking the application to retry. A refusal is part of the concurrency contract, not proof that the database has become unreliable.

23.5.2 PLAIN — a picture in your head#

Several clerks prepare paperwork in parallel. The final accepted collection must be explainable as though the desk handled each complete case in a valid sequence.
If two prepared cases cannot both fit such a sequence, one may need to be prepared again using newer information.
Where the comparison breaks: the engine does not necessarily find and print a literal serial schedule for the application. Implementations use locks or dependency detection to prevent forbidden outcomes.

23.5.3 PLAIN — a worked example#

Return to the duty model. If Mira’s departure is treated first, one assistant remains. Dev’s later decision must then refuse to leave. Reverse the order and Mira must stay instead.
These are two different acceptable serial outcomes. The system need not choose the same assistant every time unless a separate fairness policy requires that.
Committing both departures is unacceptable under the stated serial decision logic. A retry must repeat the read and decision, not merely resubmit the old “set off duty” statement.
The operation’s correctness includes the retry path because that path may be the normal way the engine resolves competition.

23.5.4 PLAIN — what is really happening inside#

PostgreSQL’s serializable implementation tracks potentially dangerous read/write dependencies and may abort a transaction to prevent a serialization anomaly.
Its predicate-lock information is not the same as an ordinary blocking row lock. Treating every lock-related term as “another request must wait here” gives the wrong mental model.
Results read inside a transaction that later aborts cannot be presented as though that transaction’s business decision committed. Keep externally visible success aligned with the final outcome.

23.5.5 TECHNICAL — the engineer’s version#

PostgreSQL 17 Serializable uses Serializable Snapshot Isolation. Its SIReadLock predicate-lock records support dependency checking; they do not block ordinary operations or cause deadlocks in the manner of blocking locks. [S66]
Correctness arguments require appropriate participation by the transactions that interact with the invariant. Mixing arbitrary bypass writers into a supposedly protected application can invalidate the application’s proof.
Distinguish serializability from strict serializability and linearizability. Chapter 36 introduces real-time ordering explicitly; do not advertise stronger guarantees merely because a transaction used the Serializable label.

23.5.6 WORDS — remember these#

Serializability: concurrent work has a valid serial explanation — equivalence of committed transaction effects to an execution in some serial order. Serialization failure: a transaction is refused to preserve the isolation guarantee — an error requiring an appropriate full-operation retry or failure response. Predicate tracking: remember the read conditions that matter — implementation metadata used to detect dependencies involving rows or ranges a transaction read.

23.6 Retries and operational cost#

23.6.1 PLAIN — in simple words#

Stronger isolation can make the application simpler in one respect: it may avoid some fragile manual coordination. It does not remove the need to handle failures and retries.
A retry consumes work and can repeat unsafe external effects. Re-running a database decision is different from sending another payment instruction or email.
The operational question is whether the complete protocol meets its correctness and performance requirements under the actual workload. A label alone cannot answer that question.

23.6.2 PLAIN — a picture in your head#

A clerk whose draft conflicts with another case returns to the beginning, reads the current facts and prepares a new decision. Re-signing the old draft would preserve the original conflict.
The clerk also checks whether a confirmation was already dispatched. Starting again must not mean blindly sending everything twice.
Where the comparison breaks: software needs explicit error classes, deadlines and request identities. A human’s memory of “I already handled that” is not a durable concurrency protocol.

23.6.3 PLAIN — a worked example#

Suppose 100 submitted operations produce 100 initial transaction attempts, and 8 encounter retryable serialization failures. If each of those succeeds on one additional attempt, the database executed 108 attempts for 100 completed operations.
The retry count is 8, not an 8% business failure rate. But the extra attempts consumed resources and increased some callers’ latency.
If 2 operations exhaust their retry deadline, report 98 completions and 2 failures separately. Do not compute latency only for the fastest successes and call it the service’s complete performance.
These figures are invented arithmetic for reporting definitions, not a measured property of Serializable isolation.

23.6.4 PLAIN — what is really happening inside#

A retry must re-enter a clean transaction and repeat every read and decision that depended on the previous attempt’s view.
Classify errors. A serialization failure and an invalid required field have different remedies. Repeating an invalid field cannot make it valid.
Bound retry attempts by a deadline and cancellation policy, and use suitable backoff when contention warrants it. Keep a stable business request identity so uncertainty does not become accidental duplication.

23.6.5 TECHNICAL — the engineer’s version#

PostgreSQL recommends retrying the complete transaction after SQLSTATE 40001, including application logic that selected the statements and values. Deadlock error 40P01 can also justify a retry, while other errors require context-specific classification. [S120]
Measure attempt counts, conflict classes, wait time, successful-operation latency and deadline exhaustion. Do not equate higher raw attempt throughput with more useful business completions.
The companion exercises model anomaly schedules and test bounded SQLite transaction behaviour. PostgreSQL isolation examples here are documentation-grounded, not executed server experiments. Independent engine-specific testing remains necessary before deploying a matching protocol.

23.6.6 WORDS — remember these#

Full-transaction retry: repeat the decision from fresh protected reads — a new attempt that does not reuse an invalidated transaction’s conclusions blindly. Attempt amplification: extra work caused by retries — the ratio or count of transaction attempts relative to completed business operations. Retry deadline: the point beyond which another attempt is not allowed — an operation-level bound on time spent resolving transient failures.

23.97 Practice and worked answers#

Choose the snapshot boundary. PostgreSQL Repeatable Read begins at 09:00, but its first ordinary statement runs at 09:01. Answer: the documented snapshot is established by the first non-transaction-control statement, not merely by BEGIN.
Trace two reads. R reads 100, W commits 120, R reads again under Read Committed. Answer: 100 then 120 is permitted for ordinary separate SELECT statements.
Explain a total of 110. A transfer preserves 60+40=50+50=100, but a reader combines old A=60 with new B=50. Answer: the reader mixed observation boundaries; the transfer’s own atomicity did not force the reader’s statements to share a snapshot.
Identify write skew. Two transactions see two on-duty assistants and each disables a different assistant. Answer: disjoint writes can jointly violate the shared minimum-one invariant under the stated snapshot model.
Assess a row-lock proposal. Each assistant locks only their own row. Answer: that does not by itself protect the shared count. Specify a protocol covering the invariant and all writers.
Interpret Serializable. Must the engine run every transaction physically one after another? Answer: no. Committed results must have a valid serial explanation; internal execution may overlap.
Retry correctly. A serialization failure occurs after a stock decision. Answer: start a clean transaction and repeat the relevant reads and decision. Do not resubmit only the final write with stale assumptions.
Report attempts. One hundred operations need 108 attempts and all complete. Answer: report 100 completions and 8 extra attempts, with latency and retry distribution. Do not call the extra attempts eight failed business operations.

23.98 Common wrong ideas#

Wrong: isolation names have identical behaviour in every engine. Right: consult the chosen implementation and version.
Wrong: Repeatable Read universally permits phantoms. Right: PostgreSQL 17 provides stronger snapshot behaviour than that minimum description.
Wrong: a stable snapshot prevents every business anomaly. Right: write skew can involve disjoint writes based on a shared snapshot condition.
Wrong: an atomic transfer guarantees every separately assembled report sees one total. Right: the reader needs its own observation contract.
Wrong: Serializable means one physical worker and no parallelism. Right: it constrains the explanation of committed outcomes.
Wrong: a serialization failure can be fixed by replaying only the last UPDATE. Right: repeat the entire dependent decision in a new transaction.
Wrong: every database error deserves a retry. Right: permanent validation and identity conflicts need different handling.
Wrong: a documented PostgreSQL schedule is a measured SQLite result. Right: implementation claims and experiment evidence must remain separate.

23.99 Chapter summary in 20 lines#

Isolation describes how overlapping transactions interact and what they may observe.
Dirty reads concern another transaction’s uncommitted work.
Non-repeatable reads concern changed committed values across repeated reads.
Phantoms concern changed membership of a repeated predicate query.
PostgreSQL 17 treats Read Uncommitted as Read Committed.
PostgreSQL Read Committed ordinary SELECT obtains a statement-start snapshot.
Successive statements can therefore see different committed states.
PostgreSQL Repeatable Read retains its first non-control-statement snapshot.
A transaction can see its own preceding writes under the documented rules.
Snapshot consistency is not a universal serializability guarantee.
Mixing snapshots can produce a total corresponding to no single state.
Write skew can break a shared rule through changes to different rows.
Protect the invariant, not merely each writer’s individual target.
Serializable outcomes must be explainable by a valid serial order.
Serializable execution need not be physically sequential.
PostgreSQL’s dependency tracking can abort a transaction to preserve that guarantee.
Repeat the complete dependent decision after a retryable serialization failure.
Keep external effects outside blind transaction retries.
Measure retry work and deadline failures alongside successful-operation latency.
Match every claim to the engine, version, configuration and evidence actually used.

Return to contents