8.3 Weak Isolation Levels

Isolation levels

level

ReadReadSnapshotSerializable

4/6

prevented

2

left to you

	Dirty read	Dirty write	Read skew	Lost update	Write skew	Phantom
Read uncommitted	✗	✓	✗	✗	✗	✗
Read committed	✓	✓	✗	✗	✗	✗
Snapshot isolation	✓	✓	✓	✓	✗	✗
Serializable	✓	✓	✓	✓	✓	✓

✓ prevented · ✗ possible

Problem

Snapshot isolation still permits: Write skew, Phantom. Write skew is the trap here — snapshot isolation is strong enough that developers stop thinking about concurrency, and write skew is invisible until two transactions read an overlapping set and each invalidates the other’s premise.

Concurrency bugs are hard to find by testing, because they are triggered only when you get unlucky with the timing. They occur rarely and are usually difficult to reproduce. Concurrency is also difficult to reason about, especially in a large application where you don't necessarily know which other pieces of code are accessing the database.

And they are not theoretical:

Concurrency bugs caused by weak isolation have caused substantial loss of money, INCLUDING BANKRUPTING A BITCOIN EXCHANGE, led to investigation by financial auditors, and caused customer data to be corrupted.

A popular comment is "Use an ACID database if you're handling financial data!" — but that MISSES THE POINT. Even many popular relational database systems (usually considered ACID) USE WEAK ISOLATION, so they wouldn't necessarily have prevented these bugs.

And the security framing, which people forget:

Even if concurrency issues are rare in normal operation, you have to consider that AN ATTACKER MIGHT DELIBERATELY SEND A BURST OF HIGHLY CONCURRENT REQUESTS TO YOUR API in an attempt to exploit concurrency bugs. To build applications that are reliable and secure, such bugs must be systematically prevented.

(A great aside: much of the banking system relies on text files exchanged via secure FTP. In this context, having an AUDIT TRAIL and human-level fraud prevention is actually more important than ACID properties.)

3.1 Read Committed

Two guarantees:

1. No dirty reads — you read only committed data
2. No dirty writes — you overwrite only committed data

It is the DEFAULT in Oracle, PostgreSQL, SQL Server, and many others.

No dirty reads

“Any writes by a transaction become visible to others only when that transaction commits — and then all its writes become visible at once.”

Why preventing dirty reads matters:

• A transaction updating several rows → a dirty read means another transaction sees SOME updates but not others (the email/counter case). Seeing a partially updated state is confusing to users and may cause other transactions to make INCORRECT DECISIONS.
• If a transaction aborts, its writes are rolled back. With dirty reads, a transaction may see data that is LATER ROLLED BACK — data that was never actually committed. Any transaction that read uncommitted data would ALSO need to be aborted → CASCADING ABORTS.

No dirty writes

A dirty write = a later write overwrites a value written by a transaction that hasn't committed yet. Prevented usually by DELAYING the second write until the first transaction commits or aborts.

A used car sale needs two writes: update the listing, and send the invoice. Two buyers try to buy the same car at the same time.

Bryce’s write wins the listing and Aaliyah’s write wins the invoice: the sale is awarded to Bryce, but the invoice is sent to Aaliyah. Read-committed isolation prevents such mishaps.

⚠️ BUT read-committed does NOT prevent the lost-update race of §1.3. There, the second write happens AFTER the first transaction committed — so it's not a dirty write. It's still incorrect, but for a different reason.

Implementing read committed

Dirty writes → row-level locks. A transaction wanting to modify a row must acquire a lock and hold it until commit or abort. Only one transaction holds it at a time. Done automatically in read-committed mode and stronger.

Dirty reads — two options:

Read uncommitted is even weaker: prevents dirty writes but NOT dirty reads. Better performance (no need to store two versions) and reduces the probability of — but does not prevent — lost updates.

3.2 Snapshot Isolation and Repeatable Read

The anomaly read-committed still permits — READ SKEW:

Aaliyah has $1,000: account 1 = $500, account 2 = $500. A transaction transfers $100 from account 2 to account 1.

Aaliyah sees $900. “It seems that $100 has vanished into thin air.”

This is read skew, an example of a nonrepeatable read: reading account 1 again at the end would give $600, a different value from the first query. Read skew is considered acceptable under read committed — both balances Aaliyah saw were indeed committed at the time she read them.

(Terminology note: "skew" is overloaded — in Ch 7 it meant an unbalanced workload with hot spots; here it means a TIMING ANOMALY.)

Where temporary inconsistency is NOT tolerable:

Snapshot isolation: EACH TRANSACTION READS FROM A CONSISTENT SNAPSHOT — it sees all the data committed in the database at the start of that transaction. Even if the data is subsequently changed, each transaction sees only the old data from that particular point in time.

A boon for long-running read-only queries. It is very hard to reason about the meaning of a query if the data it operates on is changing at the same time.

Supported by PostgreSQL, MySQL/InnoDB, Oracle, SQL Server. Some databases — Oracle, TiDB, Aurora DSQL — choose snapshot isolation as their HIGHEST isolation level. Cloud warehouses like BigQuery frequently use it, since it provides a point-in-time view for analytical queries.

Multiversion concurrency control (MVCC)

The key performance principle of snapshot isolation: READERS NEVER BLOCK WRITERS, AND WRITERS NEVER BLOCK READERS. This lets a database handle long-running read queries on a consistent snapshot at the same time as processing writes normally, without any lock contention between the two. (Writes still take write locks against each other, to prevent dirty writes.)

Instead of two versions per row, the database keeps SEVERAL COMMITTED VERSIONS, because various in-progress transactions may need to see different points in time.

PostgreSQL's implementation:

Every transaction gets a unique, always-increasing transaction ID (txid), and every write is tagged with the writer’s txid. All versions live in the same heap, committed or not:

account	balance	inserted_by	deleted_by
2	$500	3	13	old version, marked deleted
2	$400	13	—	new version

An update is internally translated into a delete and an insert. Deletion doesn’t remove the row — it sets deleted_by. Later, a garbage collection process removes rows marked for deletion, once no transaction can access them, and frees their space. Versions of a row form a linked list so queries can iterate over them.

(Precision note: PostgreSQL txids are 32-bit, so they overflow after ~4 billion transactions. The VACUUM process performs cleanup to ensure overflow doesn't affect the data. This is the transaction-ID-wraparound hazard.)

Visibility rules

A row is visible if BOTH of these are true:

1. At the time the reader's transaction started, the transaction that INSERTED the row had already committed
2. The row is not marked for deletion, or if it is, the transaction that requested deletion HAD NOT YET COMMITTED at the time the reader's transaction started

Mechanically, four rules:

1. At transaction start, the database lists all OTHER transactions in progress at that time. Any writes those transactions make are IGNORED, EVEN IF THEY SUBSEQUENTLY COMMIT — this is what makes the snapshot unaffected by another transaction committing
2. Writes by transactions with a LATER txid are ignored, regardless of whether they committed
3. Writes by ABORTED transactions are ignored, regardless of when the abort happened — which conveniently means on abort we don't need to immediately remove rows; the visibility rule filters them out and GC removes them later
4. All other writes are visible

By never updating values in place but instead inserting a new version every time, the database can provide a consistent snapshot while incurring only a SMALL overhead. A long-running transaction may keep reading values that, from other transactions' points of view, have long been overwritten or deleted.

Indexes and MVCC

• Most common: each index entry points at one version of a row (oldest or newest); each version references the next-oldest/newest. A query using the index must ITERATE over the rows to find one that is visible AND matches. When GC removes invisible versions, the corresponding index entries can also be removed.
• PostgreSQL optimization: avoid index updates if different versions of the same row fit on the same page (HOT updates).
• Some databases store only DIFFERENCES between versions, to save space.
• CouchDB, Datomic, LMDB use an IMMUTABLE (copy-on-write) B-tree: don't overwrite pages, create a new copy of each modified page; parent pages up to the root are copied and updated to point to the new children. Pages unaffected by a write are SHARED with the new tree.

With immutable B-trees, every write transaction creates a NEW B-TREE ROOT, and a particular root IS a consistent snapshot of the database at the point it was created. There is NO NEED to filter rows by transaction ID, because subsequent writes cannot modify an existing B-tree — they can only create new roots. (Requires a background compaction/GC process.)

The naming disaster

The same thing, different names:

Database	What it calls the level	What the level actually is
PostgreSQL	“repeatable read”	snapshot isolation
Oracle	“serializable”	snapshot isolation

The same name, different things:

Database	What it calls the level	What the level actually is
PostgreSQL	“repeatable read”	snapshot isolation
MySQL	“repeatable read”	MVCC with Weaker consistency than snapshot isolation
IBM Db2	“repeatable read”	Serializability

Why: the SQL standard has no concept of snapshot isolation, because the standard is based on System R's 1975 definitions and snapshot isolation hadn't been invented. It defines repeatable read, which looks superficially similar. PostgreSQL calls its level "repeatable read" because it meets the standard's requirements and so it can claim standards compliance.

The SQL standard's definition of isolation levels is FLAWED — ambiguous, imprecise, and not as implementation-independent as a standard should be. Even though several databases implement "repeatable read," there are big differences in the guarantees they provide. As a result, NOBODY REALLY KNOWS WHAT REPEATABLE READ ISOLATION MEANS.

3.3 Preventing Lost Updates

The lost update problem: an application READS a value, MODIFIES it, and WRITES BACK the modified value. If two transactions do this concurrently, one modification can be LOST, because the second write doesn't include the first modification — the later write CLOBBERS the earlier write.

Where it shows up:

• Incrementing a counter or updating an account balance
• Making a local change to a complex value — adding an element to a list within a JSON document (parse, change, write back)
• Two users editing a wiki page, each saving by sending the ENTIRE page contents to the server, overwriting whatever is currently there

Five solutions:

⚠️ A subtle MVCC interaction with conditional writes: if another transaction concurrently modified content, the new content may not be visible under the MVCC visibility rules. Many MVCC implementations have an EXCEPTION: values written by other transactions ARE visible to the evaluation of the WHERE clause of UPDATE and DELETE queries, even though those writes are not otherwise visible in the snapshot.

Replication changes everything:

Locks and conditional writes ASSUME THERE IS A SINGLE UP-TO-DATE COPY OF THE DATA. Multi-leader and leaderless databases usually allow several writes concurrently and replicate asynchronously, so they CANNOT GUARANTEE a single up-to-date copy. Thus techniques based on locks or conditional writes DO NOT APPLY.

Instead: allow concurrent writes to create SIBLINGS and merge them afterward. Merging prevents lost updates if the updates are COMMUTATIVE — incrementing a counter, adding to a set. That's the idea behind CRDTs.

However, some operations (such as conditional writes) CANNOT be made commutative. And LWW — the default in many replicated databases — IS PRONE TO LOST UPDATES.

3.4 Write Skew and Phantoms

The on-call doctors example:

The rule is that at least one doctor must be on call. Aaliyah and Bryce are both on call, both feel unwell, and both click “go off call” at approximately the same time.

Now no doctor is on call and the invariant is violated. Both reads came from a consistent snapshot, so snapshot isolation permits this.

WRITE SKEW: neither a dirty write nor a lost update, because the two transactions are UPDATING TWO DIFFERENT OBJECTS. It's definitely a race condition: if they had run one after another, the second doctor would have been prevented from going off call.

Write skew is a GENERALIZATION of the lost-update problem: it can occur if two transactions READ THE SAME OBJECTS and then UPDATE SOME OF THOSE OBJECTS (different transactions may update different objects). In the special case where they update the SAME object, you get a dirty write or lost update instead.

Your options are far more restricted than for lost updates:

Four more examples of write skew

Phantoms — the underlying mechanism

The universal pattern:

1. A SELECT checks whether a requirement is satisfied by searching for rows matching a condition.
2. Application code decides how to continue based on the result.
3. If it goes ahead, it writes (INSERT/UPDATE/DELETE) and commits.

The write in step 3 changes the precondition of the decision in step 2. Repeating the SELECT after committing gives a different result. The steps may also occur in a different order — write first, then SELECT, then decide.

In the doctors example, the row modified in ③ was ONE OF THE ROWS RETURNED IN ①, so SELECT FOR UPDATE can lock it and make the transaction safe.

The other four examples are DIFFERENT: they check for the ABSENCE of rows matching a condition, and the write ADDS a row matching that same condition. IF THE QUERY IN ① DOESN'T RETURN ANY ROWS, SELECT FOR UPDATE CAN'T ATTACH LOCKS TO ANYTHING.

A PHANTOM is a write in one transaction that changes the result of a search query in another transaction. Snapshot isolation avoids phantoms in READ-ONLY queries, but in read/write transactions phantoms lead to particularly tricky cases of write skew. (SQL generated by ORMs is also prone to write skew.)

Materializing conflicts

If there's no object to lock, artificially introduce one.

For the meeting rooms: create a table of time slots and rooms, one row per room per 15-minute period, for all combinations ahead of time (e.g. the next six months). A transaction locks (SELECT FOR UPDATE) the rows for the desired room and period, then checks and inserts.

The additional table isn't used to store booking information — it's PURELY A COLLECTION OF LOCKS.

⚠️ It can be hard and error-prone to figure out how to materialize conflicts, and IT'S UGLY TO LET A CONCURRENCY CONTROL MECHANISM LEAK INTO THE APPLICATION DATA MODEL. Materializing conflicts should be considered A LAST RESORT. A serializable isolation level is preferable in most cases.

---