Each One Right on Its Own: Snapshot Isolation, Write Skew, and What Serializable Really Means

The Hook: Two Transactions, Both Correct, Together Wrong

Picture an on-call rotation. The rule is simple and written into the operating agreement: at least one person must always be signed up. In the database that is a table oncall with a column available. The application checks the rule cleanly, the way you are taught to:

BEGIN;
SELECT count(*) FROM oncall WHERE available = true;  -- returns 2
-- 2 > 1, so I am allowed to sign off
UPDATE oncall SET available = false WHERE person = 'Anna';
COMMIT;

This code is not naive. It reads the state, checks the invariant, and acts only if the invariant survives. In a single-user world it is provably correct.

Now Anna and Bernd do the same thing at the same time. Anna's transaction reads 2 and decides: fine. Bernd's transaction, concurrently, also reads 2 and decides: fine. Anna signs off. Bernd signs off. Both transactions commit successfully. Nobody is on call.

And here is the part this article is built on: at the isolation levels most production systems actually run at, this is not a bug but documented behavior. No lost update, no dirty read, no phantom in the sense of the SQL standard. Both transactions saw a fully self-consistent state of the database. Both computed correctly. There simply is no order in which you could run them one after the other and get this result — because whichever ran second would have read 1 and aborted.

This anomaly has a name, it has been formally described since 1995, and it is called write skew. It is why the question "which isolation level does this run at?" is not a database-administration question but an architectural one — and why the answer "SERIALIZABLE, so we're safe" was historically wrong in two of the most widely deployed databases in the world.

Part 1: What Isolation Actually Promises

The I in ACID is usually explained in one sentence: transactions should not get in each other's way. That is true, but too vague to derive anything from. The precise version is called serializability:

A concurrent execution of a set of transactions is serializable if its result is identical to the result of some execution of the same transactions one after another, one at a time.

That is a strong and very useful guarantee, because it says something about proofs. If every individual transaction, considered on its own, preserves an invariant, and if the execution is serializable, then the concurrent execution preserves the invariant too — by induction over the serial order. Serializability is the property that lets application developers think single-threaded: check the invariant inside your transaction, and the database makes sure nobody interferes.

The price is the reason this story gets complicated at all. The classical route to serializability is strict two-phase locking (S2PL): reads take read locks, writes take write locks, all locks are held until commit. That works, is provably correct — and means that readers block writers. In an OLTP system with reports, dashboards, and long analytical queries, that is not practical. So the industry built shortcuts, and the SQL standard retroactively declared them to be levels.

The PostgreSQL documentation defines the violation of the serializability guarantee as a phenomenon in its own right, in exactly these words:

"serialization anomaly: The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time."

Part 2: The Standard and Its Design Flaw

The Four Levels and the Three Phenomena

ANSI SQL-92 defines four isolation levels — in the standard's own enumeration: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — and characterizes the lower three by what they prevent. The three phenomena, in the original wording:

• P1 (Dirty Read): "Transaction T1 modifies a data item. Another transaction T2 then reads that data item before T1 performs a COMMIT or ROLLBACK."
• P2 (Fuzzy/Non-repeatable Read): "Transaction T1 reads a data item. Another transaction T2 then modifies or deletes that data item and commits."
• P3 (Phantom): "Transaction T1 reads a set of data items satisfying some <search condition>. Transaction T2 then creates data items that satisfy T1's <search condition> and commits."

From this follows the familiar matrix: READ UNCOMMITTED allows all three, READ COMMITTED prevents P1, REPEATABLE READ prevents P1 and P2, SERIALIZABLE prevents all three.

The Critique That Cracked the Standard Open

In 1995, at SIGMOD in San Jose, a paper appeared that took this standard systematically apart: Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil, and Patrick O'Neil — A Critique of ANSI SQL Isolation Levels. The author list is remarkable: Microsoft, Sybase, and Jim Gray, the Turing Award laureate for transaction processing. This is not an academic footnote; it is the industry correcting itself.

The paper does three things that still shape the vocabulary today.

First: the phenomena were ambiguously worded. The authors show that the English sentences can be formalized in two ways. In their notation — w1[x] is a write by T1 on x, r2[x] a read by T2, c1/a1 are commit and abort — there is a strict reading that forbids only the anomaly that actually occurred:

A1:  w1[x] ... r2[x] ... (a1 and c2 in any order)
A2:  r1[x] ... w2[x] ... c2 ... r1[x] ... c1
A3:  r1[P] ... w2[y in P] ... c2 ... r1[P] ... c1

and a loose reading that forbids the whole pattern, regardless of how it ends:

P1:  w1[x] ... r2[x] ... ((c1 or a1) and (c2 or a2) in any order)
P2:  r1[x] ... w2[x] ... ((c1 or a1) and (c2 or a2) any order)
P3:  r1[P] ... w2[y in P] ... ((c1 or a1) and (c2 or a2) any order)

The difference is not pedantry. The authors construct the money-transfer history

H1: r1[x=50] w1[x=10] r2[x=10] r2[y=50] c2 r1[y=50] w1[y=90] c1

in which T1 moves fifty units from x to y and T2 reads a wrong total in between. This history violates P1 but none of A1, A2, A3. Read the standard strictly and you have to permit it at READ COMMITTED — even though T2 saw a state that never existed. The paper's verdict is unambiguous (Remark 4): "Strict interpretations A1, A2, and A3 have unintended weaknesses. The correct interpretations are the Loose ones."

Second: one phenomenon was missing entirely. The authors add P0 (Dirty Write): w1[x] ... w2[x] ... (c1 or a1) — two transactions overwrite each other before either has committed. Remark 3: "ANSI SQL isolation should be modified to require P0 for all isolation levels." That sounds obvious, but it was not; without P0 you cannot even guarantee that a rollback restores the previous value.

Third, and conceptually the most important point: the standard's levels are not properties but descriptions of an implementation. The paper says so openly: "P0, P1, P2, and P3 are disguised redefinition's of Locking behavior." The four levels are essentially the answer to the question of how long a lock-based scheduler holds which locks. For a system that does not lock at all, they are an analogy at best.

And that third problem turned urgent — because the industry started not to lock.

Part 3: Snapshot Isolation — The Shortcut That Worked Too Well

The Mechanism

The idea behind multiversion concurrency control (MVCC) is simple and radical: an UPDATE overwrites nothing; it writes a new version of the row. Old versions stay around as long as someone still needs them. That lets a reader obtain an arbitrarily old but internally consistent view of the database — without a single lock and without blocking anyone.

Berenson et al. formalize the resulting isolation level and give it the name that is standard today. The definition, in the original:

"A transaction executing with Snapshot Isolation always reads data from a snapshot of the (committed) data as of the time the transaction started, called its Start-Timestamp."

"A transaction running in Snapshot Isolation is never blocked attempting a read as long as the snapshot data from its Start-Timestamp can be maintained."

The decisive part is the commit rule:

"When the transaction T1 is ready to commit, it gets a Commit-Timestamp, which is larger than any existing Start-Timestamp or Commit-Timestamp. The transaction successfully commits only if no other transaction T2 with a Commit-Timestamp in T1's interval [Start-Timestamp, Commit-Timestamp] wrote data that T1 also wrote. Otherwise, T1 will abort."

"This feature, called First-committer-wins prevents lost updates (phenomenon P4)."

The entire procedure fits into two sentences, and they are worth memorizing:

1. Reads come from the past. Every transaction sees the database as it looked when it started. Full stop. Nothing committed afterwards is visible to it.
2. Writes come with a conflict check — but only against writes. If two concurrent transactions write the same row, one loses. If they write different rows, both win, no matter what they read.

Point 2 is the whole story in one sentence. The conflict check is a write-write check. Read-write relationships do not appear in it at all.

Why SI Became So Successful

Because point 1 is fantastic in daily operation. Readers never block writers, writers never block readers. A report that runs for twenty minutes across a billion rows sees a clean state from twenty minutes ago and does not hold up a single OLTP UPDATE while doing it. Deadlocks become rare. Response times become more predictable. For systems with mixed workloads that is not an optimization but the difference between working and not working.

On top of that, SI looks surprisingly good against the standard's phenomena. In Remark 8, Berenson et al. establish "READ COMMITTED « Snapshot Isolation" and argue: "first-committer-wins precludes P0 (dirty writes), and the timestamp mechanism prevents P1 (dirty reads), so Snapshot Isolation is no weaker than READ COMMITTED." They explicitly call SI "surprisingly strong, even stronger than READ COMMITTED." No dirty read, no non-repeatable read, no lost update, and no phantoms in the read path either — because nothing can appear in a snapshot that was not already in it.

It looks like SERIALIZABLE. And it is not serializable. The paper says that too, in one sentence, and it is the most precise sentence about SI that I know:

"Snapshot Isolation is non-serializable because a transaction's Reads come at one instant and the Writes at another."

Part 4: The Two Anomalies That Got the Name

Berenson et al. introduce two new anomalies, because the standard's three are not enough to characterize SI.

A5A: Read Skew

A5A:  r1[x] ... w2[x] ... w2[y] ... c2 ... r1[y] ... (c1 or a1)

"Suppose transaction T1 reads x, and then a second transaction T2 updates x and y to new values and commits. If now T1 reads y, it may see an inconsistent state."

This is the classic half-read transfer: you read the account before the booking and the counter-account after it, and the total does not add up. Snapshot isolation prevents this anomaly completely — that is exactly the point that makes SI stronger than READ COMMITTED, where it remains possible.

A5B: Write Skew

A5B:  r1[x] ... r2[y] ... w1[y] ... w2[x] ... (c1 and c2 occur)

The paper's example (history H5) is a bank with an invariant across two accounts:

H5: r1[x=50] r1[y=50] r2[x=50] r2[y=50] w1[y=-40] w2[x=-40] c1 c2

The constraint is "x + y should be positive," and the authors explain:

"Write Skew (A5B) could arise from a constraint at a bank, where account balances are allowed to go negative as long as the sum of commonly held balances remains non-negative."

Before: 50 + 50 = 100, the invariant holds. Each transaction withdraws 90 from its account and correctly verifies that the sum will stay positive — against the state it read. After: −40 + −40 = −80. Both computed correctly. The database ran both computations against different, individually consistent realities.

And here the mechanism from Part 3 does not engage, for an entirely mundane reason: T1 and T2 wrote different rows. T1 wrote y, T2 wrote x. The first-committer-wins check finds no overlap because there is none. The conflict does not live in the writes but in the relationship between the read of one transaction and the write of the other. SI structurally does not check that relationship.

The on-call example from the opening is exactly this shape, just with count(*) instead of account balances. And it occurs in real systems often enough that the pattern is worth recognizing:

What all the rows have in common: the invariant concerns a set of rows, the check is a SELECT, and the write goes to a different row than the one checked.

The Nasty Special Case: Write Skew Over Phantoms

Fekete et al. (2005) present a variant that matters more in practice than the two-account one, because it defeats the obvious workaround. Let the invariant be: an employee may not be scheduled for more than eight hours per day. The check is a SELECT sum(hours) ... WHERE employee = ? AND day = ?. Two transactions each schedule four hours concurrently, both read the sum 4, and both insert a new row.

The standard reflex against write skew is to lock the rows you read — in SQL, SELECT ... FOR UPDATE. That does not work here, and the paper says why: the row you would need to lock does not exist yet. In the original:

"it is not possible to avoid this anomaly by performing Select For Update on any set of data items involved."

This is the same place where lock-based systems need predicate locks — except that as an application developer you do not have them. The authors' solution is a design change: a total_work_hours table with exactly one row per employee and day, which must be kept up to date. That turns the read-write conflict into a write-write conflict — and SI sees those.

Part 5: The Conceptual Rebuild — Adya's Implementation-Independent Definitions

Before the solution, one intermediate step is needed, because otherwise you cannot see with what the solution is proven.

The third problem from Part 2 — the levels describe locking behavior, not properties — was addressed in 2000 at ICDE by Atul Adya, Barbara Liskov, and Patrick O'Neil in Generalized Isolation Level Definitions. The rationale is stated in the abstract:

"as stated in [8], these definitions were simply 'disguised versions of locking' and therefore disallow optimistic and multi-version mechanisms. […] Thus, these definitions fail to meet the goals of ANSI-SQL with respect to implementation-independence."

The way out is a graph. The authors define the Direct Serialization Graph (DSG): nodes are committed transactions, edges are direct conflicts, and there are exactly three kinds:

• ww (write-dependency): "A transaction Tj directly write-depends on Ti if Ti installs a version xi and Tj installs x's next version (after xi) in the version order."
• wr (read-dependency): Tj reads a version written by Ti.
• rw (anti-dependency): Tj overwrites a version that Ti read.

The rw anti-dependency is the real advance. It is the only one of the three edges where information flows "backwards": T2 does not learn something from T1; T2 invalidates something T1 saw. And that edge is precisely what is missing from snapshot isolation's conflict check.

With the graph, the anomalies become cycles rather than patterns:

• G0 (Write Cycles): "A history H exhibits phenomenon G0 if DSG(H) contains a directed cycle consisting entirely of write-dependency edges."
• G1a/G1b: reading from aborted or from non-final intermediate versions.
• G1c (Circular Information Flow): "A history H exhibits phenomenon G1c if DSG(H) contains a directed cycle consisting entirely of dependency edges."
• G2 (Anti-dependency Cycles): "A history H exhibits phenomenon G2 if DSG(H) contains a directed cycle with one or more anti-dependency edges."

From these come the portable levels PL-1 (disallows G0), PL-2 (disallows G1), PL-2.99 (disallows G1 and G2-item), and PL-3 (disallows G1 and G2) — with the authors noting: "our PL-3 conditions provide what is normally considered as serializability."

The gain from this shift is not elegance but decidability. "Does this level prevent phantoms?" is a question about wording. "Does the dependency graph contain a cycle with at least one anti-dependency edge?" is a question about an object you can construct and inspect. And five years later, the solution follows from exactly that shift.

Part 6: The Dangerous Structure — The Theorem That Changes Everything

In 2005, ACM Transactions on Database Systems, Vol. 30, No. 2, pages 492–528, published the paper that turns the diagnosis into a therapy: Alan Fekete, Dimitrios Liarokapis, Elizabeth O'Neil, Patrick O'Neil, and Dennis Shasha — Making Snapshot Isolation Serializable.

The starting point is an observation about the graphs SI can produce at all. Under SI the version order is fixed by commit times — in the original: "the version order is defined by the temporal sequence of the commit times of the transactions that produced the versions." That constrains the possible cycles severely. And then comes the central theorem. The follow-up paper by Cahill et al. quotes it as:

"any cycle must have two rw-dependency edges that occur consecutively, and further, each of these edges is between two concurrent transactions."

In words: every non-serializable execution under snapshot isolation contains two immediately consecutive rw anti-dependency edges, each between concurrent transactions. Such an edge is called a vulnerable edge, the pattern of two consecutive ones a dangerous structure, and the transaction in the middle — the one with an incoming and an outgoing vulnerable edge — is the pivot. Cahill et al. summarize the consequence: "The theory of [9] shows that there is a pivot in any non-serializable execution allowed by SI."

I consider this one of the finest sentences in database theory, precisely because of its direction. It does not claim "these patterns are bad." It claims: everything that can go wrong has a common, locally detectable signature. You do not need to search the whole graph for cycles — you only need to look for a single transaction with two flags set at once.

What You Could Already Do With It

The paper is from 2005, and at that time no database implemented any of it. So the authors address application developers, and their two recipes are to this day the emergency tools for anyone stuck on SI:

Promotion — turn a read into a write:

"One way to do this is to require in the transactional program that each Read of X and Y to update Y give the impression of a Write of X (this is possible in Oracle using the Select For Update statement)."

Materializing the conflict — give the invariant a datum of its own:

"Another approach requires that each constraint on the sum of two accounts X and Y be materialized in another data item Z, and insists that all updates of X and Y must keep Z up to date."

Both recipes do the same thing: they convert an rw edge that SI cannot see into a ww edge that it can. That is the entire trick, and you can apply it in any ORM.

And then the abstract contains a finding that explains why the world got along with SI for so long:

"SI does not guarantee serializability in all cases, but the TPC-C benchmark application [TPC-C], for example, executes under SI without serialization anomalies."

TPC-C, the industry standard for OLTP benchmarks, runs anomaly-free under snapshot isolation. Not because the database prevents anything, but because the structure of that application cannot produce a dangerous structure. That is the statistical explanation for a phenomenon every operator knows: write skew is not a frequent bug but a rare one — and therefore one that first shows up in production, under load.

Part 7: From Theory to Product

Cahill 2008: Runtime Detection

At SIGMOD 2008 in Vancouver, Michael J. Cahill, Uwe Röhm, and Alan D. Fekete showed in Serializable Isolation for Snapshot Databases that the pivot can be detected at runtime — with two bits per transaction. In the original:

"T.inConflict indicates whether there is an rw-dependency from another concurrent transaction to T, and T.outConflict indicates whether there is an rw-dependency from T to another concurrent transaction. Thus a potential non-serializability is detected when T.inConflict and T.outConflict are both true."

To notice the edges at all, you need bookkeeping about reads. The paper introduces a new lock mode whose decisive property is that it does not lock:

"We also introduce a new lock mode called SIREAD. This remembers the fact that an SI transaction has read a version of an item. However, obtaining the SIREAD lock does not cause any blocking, even if a WRITE lock is held already…"

"the presence of both SIREAD and WRITE locks on an item is a sign of an rw-dependency, and so we set the appropriate inConflict and outConflict flags."

The correctness argument is three lines long and consists of exactly Fekete's theorem plus completeness of detection plus the abort:

"1. Non-serializable executions under SI consist of a cycle including two consecutive rw-dependencies. 2. Our algorithm detects every rw-dependency. 3. When two consecutive rw-dependencies are detected, at least one transaction is aborted which breaks the cycle."

Two things about this paper are notable. First, the method is conservative, and the authors say so explicitly: "However, we do sometimes make false positive detections; for example, an unnecessary detection may happen because we do not check whether the two rw-dependency edges occur within a cycle." You check only the local signature, not the global cycle. That costs unnecessary aborts and saves the cycle search. Second, the intervention was tiny: implemented "in Oracle Berkeley DB version 4.6.21," and "In total, only 692 lines of code (LOC) were modified out of a total of over 200,000 lines of code in Berkeley DB."

PostgreSQL 9.1: The First Production Implementation

In 2012, the Proceedings of the VLDB Endowment, Vol. 5, No. 12, published the paper that turned it into a product: Dan R. K. Ports (MIT CSAIL) and Kevin Grittner (Consolidated Court Automation Programs, Wisconsin Supreme Court) — Serializable Snapshot Isolation in PostgreSQL. The claim is in the abstract:

"This is the first implementation of SSI in a production database release as well as the first in a database that did not previously have a lock-based serializable isolation level."

"In the latest PostgreSQL 9.1 release, we introduced a serializable isolation level that retains many of the performance benefits of snapshot isolation while still guaranteeing true serializability."

The contributions beyond Cahill are essentially what turns a paper prototype into an operable system:

• A dedicated lock manager for read dependencies: "Lacking this infrastructure, we were required to build a new lock manager. Our lock manager is specifically optimized for tracking SSI read dependencies…"
• Bounded memory consumption through summarization of old transactions: "we describe a transaction summarization technique that ensures that the SSI implementation uses a bounded amount of RAM without limiting the number of concurrent transactions."
• Safe retry — the rule that an immediately retried attempt does not fail at the same place again: "We also introduce a safe retry rule, which resolves conflicts by aborting transactions in such a way that an immediately retried transaction does not fail in the same way."
• Safe snapshots and deferrable transactions for read-only workloads: "which allow certain read-only transactions to execute without the overhead of SSI by identifying cases where snapshot isolation anomalies cannot occur." The definition: "A read-only transaction T has a safe snapshot if no concurrent read/write transaction has committed with a rw-antidependency out to a transaction that committed before T's snapshot, or has the possibility to do so."

And the sentence that ended the debate about cost — from the paper's introduction:

"Our experiments with a transaction processing and a web application benchmark show that our serializable mode has a performance cost of less than 7% relative to snapshot isolation, and outperforms two-phase locking significantly on some workloads."

Less than seven percent against snapshot isolation, for true serializability. That is why the old trade-off of "correctness or performance" has not applied to this question since 2011.

Part 8: The Map — What Your Database Actually Does

Here the theory gets uncomfortably concrete, because the name of an isolation level does not tell you what it delivers.

PostgreSQL

The documentation is exemplary in its candor on this point. Three sentences suffice:

"In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like Read Committed."

"The Repeatable Read isolation level is implemented using a technique known in academic database literature and in some other database products as Snapshot Isolation."

"The Serializable isolation level is implemented using a technique known in academic database literature as Serializable Snapshot Isolation, which builds on Snapshot Isolation by adding checks for serialization anomalies."

REPEATABLE READ is snapshot isolation. Accordingly, the documentation lists "Serialization Anomaly" as a column of its own and marks it "Possible" for READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ, and "Not possible" only for SERIALIZABLE. In plain terms: write skew is possible at the level with the reassuring name REPEATABLE READ.

One detail with history: "Prior to PostgreSQL version 9.1, a request for the Serializable transaction isolation level provided exactly the same behavior described here." Anyone who requested SERIALIZABLE before 2011 got snapshot isolation.

The documentation's example is the write-skew shape in pure form: table mytab with (class, value) = (1,10), (1,20), (2,100), (2,200). Transaction A reads SELECT sum(value) WHERE class = 1 (= 30) and inserts 30 as a row of class 2; B reads the sum of class 2 (= 300) and inserts 300 as a row of class 1. Under REPEATABLE READ both commit. Under SERIALIZABLE one fails with ERROR: could not serialize access due to read/write dependencies among transactions. The documentation's reasoning: "if A had executed before B, B would have computed the sum 330, not 300."

Three points matter operationally. First, the predicate locks do not block — "In PostgreSQL these locks do not cause any blocking and therefore can not play any part in causing a deadlock" (visible in pg_locks as SIReadLock). Second, you need a retry mechanism — "it is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with an SQLSTATE value of '40001')." Third, the performance advice is counterintuitive: declare READ ONLY where possible, keep transactions small, pool connections, and explicitly: "Eliminate explicit locks, SELECT FOR UPDATE, and SELECT FOR SHARE where no longer needed due to the protections automatically provided by Serializable transactions." Plus a pitfall that hits real systems: "A sequential scan will always necessitate a relation-level predicate lock" — if the planner picks a seq scan, the predicate lock becomes so coarse that the abort rate rises.

SQL Server

Here the level is called what it is: SNAPSHOT, and it is not on by default but must be enabled per database (ALTER DATABASE … SET ALLOW_SNAPSHOT_ISOLATION ON). The versions live in the version store, per the documentation in tempdb — or, with Accelerated Database Recovery, in the database itself. Separately there is READ_COMMITTED_SNAPSHOT ON: with that, the existing READ COMMITTED uses row versions and delivers statement-level consistency instead of transaction-level consistency, without a code change.

The write conflict surfaces as error 3960: "Snapshot isolation transaction aborted due to update conflict. … Retry the transaction or change the isolation level for the update/delete statement." That is first-committer-wins, operationalized. The documentation also names the workaround from Part 6 — "You can avoid this by using UPDLOCK hints for SELECT statements that access data to be modified" — that is, promotion. What SQL Server does not offer is SSI: its SERIALIZABLE level is lock-based (with range locks), with the corresponding blocking and deadlock characteristics. So the choice here is a different one than in PostgreSQL: snapshot with write skew, or locks with waiting.

The Summary in One Table

The practical consequence: the same application, the same IsolationLevel constant in your C# code, two entirely different guarantees. And in both cases the default level is READ COMMITTED — the weakest one at which write skew is possible.

Part 9: The Distributed Limit — Why SI Cannot Be Highly Available

There is one more result that explains why snapshot isolation is not merely a convenient shortcut but, in distributed systems, sometimes the strongest achievable level — and sometimes too strong.

In the Proceedings of the VLDB Endowment, Vol. 7, No. 3, pages 181–192, Peter Bailis, Aaron Davidson, Alan Fekete, Ali Ghodsi, Joseph M. Hellerstein, and Ion Stoica examine in Highly Available Transactions: Virtues and Limitations which guarantees are compatible with high availability in the CAP sense. The result, from the introduction:

"besides serializability, Snapshot Isolation and Repeatable Read isolation are not HAT-compliant, while most other isolation levels are achievable with high availability."

Achievable are, among others, Read Uncommitted, Read Committed, Monotonic Atomic View, item and predicate cut isolation, monotonic reads and writes; with "sticky" availability also read your writes, PRAM, and causal consistency. Not achievable are cursor stability, snapshot isolation, repeatable read, one-copy serializability, and linearizability.

The argument for SI is short and to the point — two clients on opposite sides of a network partition increment the same value:

"Each client's respective server might try to detect that another write occurred, but this requires knowing the version of the latest write to x. In our example, this reduces to a requirement for linearizability, which is, via Gilbert and Lynch's proof of the CAP Theorem, provably at odds with high availability."

That is the deeper punchline: in distributed systems, snapshot isolation does not fail at reading — a snapshot is cheap and locally answerable — but precisely at the one check it does perform, namely write-write detection. First-committer-wins needs a global order, and a global order needs coordination. The price is measurable: the paper reports for Amazon EC2 from São Paulo to Singapore "362.8ms RTT (95th percentile: 649ms)" and observes in its experiments across five data centers that the latency of the coordinating variant "increases to nearly 800ms per transaction."

The Central Takeaway

1. The name of an isolation level is not a contract. REPEATABLE READ in PostgreSQL is snapshot isolation and permits serialization anomalies; SERIALIZABLE there was the same thing before version 9.1. If you need guarantees, read the documentation of your concrete system, not the standard.
2. Snapshot isolation checks writes against writes — not reads against writes. Everything SI gets wrong follows from that single sentence. If your invariant spans a set of rows and you write to a different row than the one you checked, you are in the write-skew shape.
3. Every non-serializable SI execution has a locally detectable signature. Fekete et al. proved that two consecutive rw anti-dependencies are required — and only that theorem made SSI possible. That is the pattern: first the implementation-independent theory (Adya), then the structural theorem, then the cheap runtime check.
4. Correctness costs less here than the folklore claims. Ports and Grittner measure "less than 7%" relative to snapshot isolation. The argument against SERIALIZABLE in PostgreSQL is no longer throughput but the obligation to retry serialization failures (SQLSTATE 40001).
5. If you are stuck on SI, turn reads into writes. Promotion (SELECT … FOR UPDATE) or materializing the invariant into a row of its own converts the invisible rw edge into a visible ww edge. For the phantom variant only materialization helps — you cannot lock a row that does not exist yet.

A concrete next step for this week: search your codebase for the pattern read – check – write inside one transaction. In C# with Entity Framework that is every method which first runs an Any(), Count(), or Sum() query, then evaluates a condition, then calls SaveChangesAsync(). Take one such site and answer three questions in writing:

1. Which isolation level does this transaction actually run at? (Not: which one is configured, but which one is in effect in this database — the default is almost always READ COMMITTED.)
2. Am I writing to exactly the rows I read? If not, write skew is possible.
3. What happens if the invariant is violated — is it noticed, or does it sit there as a silent data error?

If question 2 answers "no" and question 3 answers "it sits there," you have one of three fixes: the invariant as a database constraint (UNIQUE, CHECK, an exclusion constraint), materialization into an aggregate row, or SERIALIZABLE with a retry loop. The order is deliberate: a constraint the database enforces itself is the only one of the three that still holds when someone later changes the isolation level.

A question to sit with: the progress in this story did not come from a faster algorithm but from someone stopping to define correctness in terms of forbidden patterns and starting to define it in terms of a mathematical object you can search. Only then was it provable that all failures share a common, local signature — and only then was the check cheap. Which correctness rule in your own system is today a list of forbidden cases that grows with every new feature — and what object would you have to construct to turn that list into a property?

Cross-References in the Vault

• The Promise Nobody Can Keep: Two-Phase Commit, Sagas and the End of the Distributed Transaction – atomicity as the counterpart to isolation: that article is about the A in ACID across system boundaries, this one about the I inside a single system.
• Agreement Without a Vote: CRDTs and the Art of Conflict-Free Replication – the radical counter-answer to Part 9: if coordination is incompatible with availability, design data types that need none.
• Order Without a Clock: Lamport Timestamps, Vector Clocks, and Causality in Distributed Systems – the prehistory of the dependency edges: happened-before is exactly the tool Adya's DSG operates on.
• Clocks That Know Their Own Uncertainty: Google Spanner, TrueTime, and Mastering Time in the Cloud – how to assign start and commit timestamps globally if you are willing to invest in hardware.
• How Machines Come to Agree: Distributed Consensus from FLP to Paxos to Raft – what coordination costs when you cannot avoid it.
• The Logbook of Truth: Understanding Event Sourcing and CQRS – a design that moves invariant checking into the write path of a single stream and thereby rules out write skew structurally.
• Write First, Sort Later – Log-Structured Merge-Trees and the Inversion of the Database – the storage side of the multiversion idea: leave old versions in place instead of overwriting them.
• The Long Tail of Latency: Tail Latency and Why Averages Lie in the Cloud – why abort-and-retry hides in the mean and shows up in the 99th percentile.

Sources and Further Reading

• Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil, Patrick O'Neil – A Critique of ANSI SQL Isolation Levels, SIGMOD '95, San Jose, CA, 1995: https://www.cs.umb.edu/~poneil/iso.pdf
• Atul Adya, Barbara Liskov, Patrick O'Neil – Generalized Isolation Level Definitions, Proceedings of the IEEE International Conference on Data Engineering (ICDE), San Diego, CA, March 2000: https://pmg.csail.mit.edu/papers/icde00.pdf
• Alan Fekete, Dimitrios Liarokapis, Elizabeth O'Neil, Patrick O'Neil, Dennis Shasha – Making Snapshot Isolation Serializable, ACM Transactions on Database Systems, Vol. 30, No. 2, June 2005, pp. 492–528: https://dsf.berkeley.edu/cs286/papers/ssi-tods2005.pdf
• Michael J. Cahill, Uwe Röhm, Alan D. Fekete – Serializable Isolation for Snapshot Databases, SIGMOD '08, Vancouver, BC, June 2008: https://people.eecs.berkeley.edu/~kubitron/courses/cs262a-F13/handouts/papers/p729-cahill.pdf
• Dan R. K. Ports, Kevin Grittner – Serializable Snapshot Isolation in PostgreSQL, Proceedings of the VLDB Endowment, Vol. 5, No. 12, 2012 (arXiv:1208.4179): https://arxiv.org/abs/1208.4179
• Peter Bailis, Aaron Davidson, Alan Fekete, Ali Ghodsi, Joseph M. Hellerstein, Ion Stoica – Highly Available Transactions: Virtues and Limitations, Proceedings of the VLDB Endowment, Vol. 7, No. 3, pp. 181–192: https://www.vldb.org/pvldb/vol7/p181-bailis.pdf
• PostgreSQL Global Development Group – PostgreSQL Documentation, Chapter 13.2: Transaction Isolation: https://www.postgresql.org/docs/current/transaction-iso.html
• Microsoft – Snapshot isolation in SQL Server: https://learn.microsoft.com/sql/connect/ado-net/sql/snapshot-isolation-sql-server
• Microsoft – Transaction locking and row versioning guide: https://learn.microsoft.com/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

Created as part of the daily learning workflow. Area of interest: Software Architecture. Estimated reading time: ~30 minutes.

← All articles