Write Skew: The Anomaly the SQL Standard Forgot

A family credit account had a limit: $1000 a month, shared across both cards.

The banking system enforced this the obvious way. Before every purchase, count how much has already been spent across both cards combined. If the new purchase fits under the remaining limit, allow it.

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;

Then imagine that both cardholders bought something for $700 at the same exact minute, each on their own card. Neither purchase had committed yet when the other one ran its check, so both transactions read the account's spending as $0. Both concluded $700 fits under the $1000 limit, so it's safe. Both committed. $1400 went through on a $1000 limit — the code did exactly what it was written to do, and the limit got violated anyway. Nobody's rules say which of them explains the extra $400 to the bank.

How did that happen? Every test passed. The logic is correct when you read it. It's even 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, 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 — sequential, one transaction after another — it guarantees the outcome is the same as if every concurrent transaction 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 sees different values each time it reads the same row within a single transaction. This can produce unpredictable or incorrect results, since the data can change between successive reads.

The key difference from 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 card bug. Which of the three anomalies was it?

Not a dirty read — nobody read uncommitted data. Not a non-repeatable read — neither transaction read the same row twice. Not a phantom — 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.

This is the fourth anomaly — write skew — yep, it's not in the 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 the argument is: 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, they just lost 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.

Know your default, and know it per environment. Run SHOW transaction_isolation; on Postgres or SELECT @@transaction_isolation; on MySQL — in production. Then check your ORM: plenty of them set the level themselves at connection time and never mention it.

Find the invariants that span multiple rows. Anything phrased as "at least one," "at most N," "the sum must not exceed," "no overlaps." Every one of these is a write-skew candidate, and none of them are protected by your default isolation level. In code review, the shape to watch for is a SELECT that checks a condition, followed by an UPDATE or INSERT that assumes the condition still holds. Between those two statements, the world is free to change.

Fix them explicitly, not by raising the global level. Three options, in ascending order of cost:

• SELECT ... FOR UPDATE on the rows the invariant covers — this turns the read into a lock and makes the second transaction wait.
• A real constraint, if the invariant can be expressed as one. A unique index or an exclusion constraint is enforced by the database itself and can't be raced. EXCLUDE USING gist on a time range solves double-booking permanently and costs nothing at runtime.
• SERIALIZABLE on the specific transactions that actually need it, with retry logic, rather than on everything.

The card bug is fixed by one line: SELECT sum(balance_used) ... FOR UPDATE. The second transaction blocks until the first commits, then sees the $700 already spent, then refuses. No isolation level change, no retry loop.

But you'll only write that line if you know there's a gap between the read and the write that the database will happily let another transaction walk straight through. The default settings won't tell you, the tests won't catch it, and the table in the textbook simply doesn't have a column for it.