Write Skew: The Anomaly the SQL Standard Forgot

Imagine the following scenario: a family credit account has a limit of $1000 a month, shared across both cards.

The banking system enforces this limit in an obvious way. Before every purchase, count how much money has already been spent across both cards combined. If a new purchase is less than the remaining credit, allow it.

Consider the following example, where at the very beginning of the month, one member of the family tries to make a $700 purchase:

BEGIN;  
SELECT sum(balance_used) FROM cards WHERE account_id = 7;  
-- returns 0, so a $700 purchase fits under the $1000 limit  
UPDATE cards SET balance_used = balance_used + 700 WHERE card_id = 1;  
COMMIT;

Now, imagine that two cardholders both attempt to buy something for $700 at the same exact minute, each on their own card. Neither purchase will have been committed yet when the other one runs its check, so both transactions read the account's spending as $0. Both conclude that $700 fits under the $1000 limit, so it's safe. Both commit. $1400 goes through on a $1000 limit — the code does exactly what it was written to do, and the limit gets violated anyway. Nobody decided which of them explains the extra $400 to the bank.

How does that end up happening? In this scenario, one could surmise that every test would have passed, and that the logic is correct when you read it. It's certainly correct when one person runs it. It only breaks when two transactions overlap in time, which is the state your database is in essentially all the time in production and essentially never in your test suite.

This is exactly the class of bug isolation levels exist for, but unfortunately most engineers don't even choose their isolation level — they just work with whatever the database ships with by default.

The I in ACID is the weakest letter of the four. Atomicity and durability are close to binary: either the transaction applied or it didn't, either the write survived the power cut or it didn't. But isolation is a range, and every value on it trades correctness against throughput.

The strongest isolation setting is called serializable. Under this setting, the database guarantees that the outcome of concurrent transactions will be the same as if they had run one after another, in some order. Not necessarily the order you'd expect, and not necessarily the order they arrived in. Just some serial order. That's a strong guarantee and it costs something to provide, so databases offer weaker settings and let you decide how much correctness you're willing to sell for speed.

The trouble is how the SQL standard describes those weaker settings: it names which three specific failures each level doesn't allow, not what the level actually guarantees. That makes it easy to conclude that crossing out those three failures is the same as being correct. It isn't, but let's start from the beginning.

Rather than saying what each level guarantees, the standard names three specific failures — anomalies, situations where one transaction sees data mid-change from another transaction — and defines the levels by which ones each permits.

A transaction sees another transaction's changes before they're finally committed, which can lead to decisions being made on data that was never confirmed.

Unlike the previous case, the first transaction reads the same row twice and gets a different value the second time — because a second transaction updated that row and committed in between the two reads. This can produce unpredictable or incorrect results, since the data can shift out from under you mid-transaction.

The key difference between a non-repeatable read and a dirty read is this: with a dirty read, the transaction sees another transaction's changes before they're finally committed, even if that other transaction eventually rolls back. With a non-repeatable read, only completed transactions are taken into account.

You run a query with a WHERE clause, someone inserts a row that matches it, you run the same query again, and a row appears that wasn't there before. The rows you already read didn't change themselves.

What's key here is the set of rows a single transaction needs: it got bigger, smaller, or shifted because of another transaction.

The four isolation levels are built from which combination of these three anomalies each one permits:

The choice in front of data engineers is speed or reliability — the stricter the level, the lower the chance of a failure, but the higher the cost in performance.

Let's go back to the credit card bug example from earlier. Which anomaly was it illustrating?

Not a dirty read — nobody read uncommitted data. Not a non-repeatable read — neither transaction read the same row twice. It wasn't a phantom read either — nobody inserted anything.

Both transactions read a set of rows, checked an invariant against it (a rule that's supposed to hold at all times, not just at the moment it's checked) — and then wrote to different rows in the same table. Those writes could never physically collide: there wasn't a single row they shared that needed locking. The database had nothing to detect as a conflict, and each transaction, on its own, was completely correct. The invariant lived across the whole set of rows, not inside any single one of them.

That is actually the fourth anomaly — write skew — yep, it's not in that table. Two transactions read an overlapping set, make disjoint writes, and together they break a rule that neither one broke on its own.

The standard doesn't describe it, and that's by construction: all three of its anomalies are about what a single transaction sees on its own, while write skew is about how two transactions, never colliding directly, break a rule together. The standard's format simply isn't built for that. And there's no newer version that fixes it either — the same four levels have stood since SQL-92.

You'll find it anywhere an invariant spans more than one row — most of the time nobody notices, because the race window is narrow. Two bookings for the same room on the same dates, because both checked whether the room was free first. A bank account going negative because two withdrawals each checked the balance, and each was fine on its own. Any "at least one" or "at most N" rule enforced by "read and count first, write second" — as two separate statements (a SELECT, then an UPDATE), rather than one atomic expression like WHERE balance - amount >= 0.

Berenson and team published A Critique of ANSI SQL Isolation Levels back in 1995, and their argument was this: anomaly-based definitions are under-specified, and snapshot isolation, which nearly every modern database implements, doesn't fit anywhere in the standard's hierarchy. It's stronger than REPEATABLE READ on phantoms, and weaker than SERIALIZABLE on write skew. The standard simply has no name for it.

That paper is thirty years old, and nobody has disproven its argument since — and the table it's still taught from simply doesn't have write skew in it.

The standard is one thing. What your specific database actually does by default is something else entirely:

Only MySQL actually defaults to REPEATABLE READ — Postgres, Oracle, and SQL Server all default to READ COMMITTED. Easy to read, easy to still get wrong in practice: if you spun up MySQL locally out of habit, and Postgres runs in production — I bet nobody ever checked whether the two matched. It's the same SQL, the same code, but a different guarantee.

REPEATABLE READ is worth a closer look anyway — it's exactly the level most people move to the moment they notice any concurrency bug. It turns out to mean something different in every engine that implements it.

In the standard, it prevents non-repeatable reads and allows phantoms:

• In Postgres, it's snapshot isolation: your transaction sees a consistent snapshot from the moment it starts, so phantoms don't occur either — stronger than the standard requires.
• In MySQL, InnoDB uses next-key locking, which blocks most phantom inserts in range queries — also stronger than the standard, but through a different mechanism, with different edge cases.
• Oracle doesn't even try: REPEATABLE READ isn't even a valid option there, so you'd reach for its SERIALIZABLE instead, which is snapshot isolation under a different name and isn't strictly serializable either.

Three databases, one keyword, three incompatible answers.

If you'd only ever checked the standard's table, you'd conclude that REPEATABLE READ is REPEATABLE READ, and switching databases doesn't change anything that matters. In practice it does, and quite a lot.

None of them catch the card bug — by design. Write skew lives exactly at the level all three call safe. Rename it REPEATABLE READ, rename it SERIALIZABLE, the bug doesn't care.

The reason people avoid the level that really works is the memory of what it used to cost: whole tables locked, and every other transaction queued up waiting its turn. That's genuinely how serializable was implemented for decades — two-phase locking, where a transaction grabs every lock it might need up front and holds all of them until it's done, so anything touching the same rows just waits in line.

Postgres has used Serializable Snapshot Isolation (SSI) since version 9.1, and it works nothing like that: no extra locks, and readers never block each other. It tracks read/write dependencies between concurrent transactions and aborts one of them when it detects a cycle that could produce a result no serial execution could ever produce. Instead of making you wait, it makes you retry: any transaction can fail with a serialization error, and your application has to be ready to run it again.

Application code that assumes a committed transaction stays committed, or that treats any database error as something broken rather than something to retry, will start throwing errors under SSI that never happened under READ COMMITTED — on requests that did nothing wrong besides losing a race.

The fact that your code runs on a weaker isolation level is an architectural decision, not an emergency. It's just a decision almost nobody makes on purpose — the default was already sitting there when they joined the project.