Snapshot Isolation Anomalies and Serializability

Hello! Welcome back to our study of database concurrency control.

In our last lesson, we explored Snapshot Isolation (SI), an efficient isolation level built on Multi-Version Concurrency Control (MVCC). We learned that SI provides each transaction with a consistent view of the database and prevents lost updates using a "first-committer-wins" rule for write-write conflicts.

However, as previewed, SI's efficiency comes at a cost: it is not truly SERIALIZABLE. Today, we will investigate the specific concurrency anomalies that can still occur under Snapshot Isolation. Our goal is to identify and understand write skew and its close relative, phantoms—subtle but critical bugs that SERIALIZABLE isolation prevents. Understanding these anomalies is crucial for any developer building robust, concurrent applications.

What Snapshot Isolation Misses: Write Skew

Let's start with a puzzle. The "first-committer-wins" rule we learned about prevents two concurrent transactions from writing to the same piece of data. But what if they read the same data, and then, based on that read, decide to write to different pieces of data?

This is the essence of the write skew anomaly. It typically follows this pattern:

Transaction T1 reads a set of objects, say A and B.
Transaction T2 concurrently reads the same objects, A and B.
T1 makes a decision based on the values of A and B, and then modifies object A.
T2 makes a decision based on the same initial values of A and B, and then modifies object B.
Since T1 wrote to A and T2 wrote to B, their write sets do not overlap. Under Snapshot Isolation, both transactions are allowed to commit.

The problem is that the decisions made by T1 and T2 were based on a state of the world that is no longer true after both commits. This can lead to the violation of a crucial application-level rule, or invariant.

Write Skew Anomaly Illustration — This diagram shows a classic write skew scenario. Alice and Bob both read the same post data. Bob updates the post title, while Alice updates the post details. Since they modified different tables (or rows), their writes don't directly conflict. However, the final state is inconsistent because Alice's update is based on the old post title ('Transactions'), which Bob has already changed to 'ACID'. A serializable system would have prevented this.

For a more practical, code-oriented perspective, the following video explains write skew and phantoms using clear, intuitive examples relevant to system design interviews.

Write Skew and Phantom Writes | Systems Design Interview: 0 to 1 with Google Software Engineer

This video from 'Jordan has no life' breaks down write skew and phantoms. Pay close attention to the emergency room doctor example for write skew—it perfectly illustrates how an application invariant can be violated.

Watch from 00:56 to 03:21. Notice how both transactions can commit because they are modifying different rows, yet together they break the rule that 'at least one doctor must be active'.

As the video shows, the core issue is that the check (SELECT COUNT(*) FROM doctors WHERE active = true) and the action (UPDATE doctors SET active = false) happen in two separate transactions that are blind to each other's writes. Snapshot Isolation's conflict detection is too narrow to catch this.

Test your understanding!

Imagine you are building a system for managing on-call engineers for two services, payment-service and inventory-service. The business rule is that you must have at least one engineer on-call across all services combined.

Currently, two engineers are on-call: Alice (payment-service) and Bob (inventory-service).

Manager 1 starts a transaction, checks the on-call roster, sees two engineers are on-call, and decides to take Alice off the schedule.
Manager 2 starts a transaction, also checks the roster, sees two engineers are on-call, and decides to take Bob off the schedule.

Assuming the system runs on a database using Snapshot Isolation, what is the final state of the on-call roster after both managers commit their changes? Why does this happen?

Show answer

The final state will be zero engineers on-call, violating the business rule.

This happens because it's a classic write skew scenario:

Manager 1's transaction reads the state (Alice and Bob are on-call) and writes to Alice's record.
Manager 2's transaction reads the same initial state and writes to Bob's record.
Since they wrote to different records, Snapshot Isolation does not detect a write-write conflict and allows both transactions to commit. The invariant is broken.

Phantom Writes: The Ghost in the Machine

A closely related anomaly is the phantom write, or simply phantom. This is a specific type of write skew where a transaction's write creates a new row that conflicts with a predicate read by another concurrent transaction.

The problem is that you can't lock a row that doesn't exist yet. This makes phantoms particularly tricky to handle without full serializability.

Write Skew and Phantom Writes | Systems Design Interview: 0 to 1 with Google Software Engineer

Let's continue with the same video, which now explains phantoms using a bakery example. This demonstrates why phantoms are a problem of write-write conflicts on data that doesn't exist at the time of the read.

Watch from 03:21 to 05:10. The key insight is that both transactions are trying to insert a new row for 'cupcake', and since the row doesn't exist to be locked, they both succeed, breaking the 'one-per-item' rule.

Why Serializability Matters: Local vs. Global Reasoning

You might wonder if these anomalies are just rare edge cases. The reason they are so important is that they fundamentally change how you have to reason about program correctness.

This technical blog post from Cockroach Labs provides an excellent explanation.

What write skew looks like

The article 'What write skew looks like' explains why serializability is so foundational for developers. We'll focus on the section that contrasts local and global reasoning.

Read the section titled 'Who cares?'. The author makes a critical point: with SERIALIZABLE isolation, you only need to verify that each individual transaction maintains database invariants. With any weaker level, including Snapshot Isolation, you must consider all possible interactions between concurrent transactions to guarantee correctness, which is vastly more complex.

This distinction is at the heart of why database experts advocate for SERIALIZABLE isolation: it provides a simple, powerful guarantee that makes application logic easier to write and prove correct.

A Deeper Look: Serialization Graphs

To understand write skew from a systems perspective, we can visualize transaction dependencies using a serialization graph. In this graph, transactions are nodes, and a directed edge T1 -> T2 means T1 must be ordered before T2 in any equivalent serial execution. An execution is serializable if and only if this graph has no cycles.

There are three main types of dependencies that create edges:

Write-Read (wr): T1 writes a value that T2 later reads. T1 must come before T2.
Write-Write (ww): T1 writes a value that T2 later overwrites. T1 must come before T2.
Read-Write (rw or anti-dependency): T1 reads a value that T2 later overwrites. T1 must come before T2.

Snapshot Isolation prevents wr and ww dependencies between concurrent transactions. However, it allows rw dependencies. Write skew occurs when you get a cycle composed of these rw dependencies.

In our on-call engineer example:

Manager 1's transaction (T1) reads the count of on-call engineers. This set of rows is later modified by Manager 2's transaction (T2) when it removes Bob. This creates an rw dependency: T1 -> T2.
Similarly, T2 reads the count of on-call engineers, and this is later modified by T1 when it removes Alice. This creates another rw dependency: T2 -> T1.

The result is a cycle (T1 -> T2 -> T1), which proves the execution is not serializable.

Preventing Write Skew: Towards True Serializability

So, how do databases prevent these anomalies?

Explicit Locking (Manual): As a developer, you can use SELECT ... FOR UPDATE to manually acquire locks on the rows you read, forcing other transactions to wait. In the on-call example, each manager would have to lock both Alice's and Bob's rows. This is effective but complex and error-prone.
Serializable Snapshot Isolation (Automatic): Advanced databases like PostgreSQL implement an algorithm called Serializable Snapshot Isolation (SSI). It enhances standard SI by tracking rw dependencies. If it detects that a transaction's commit would create a cycle in the serialization graph, it automatically aborts one of the transactions, thus preventing the write skew.

This blog post by database expert Vlad Mihalcea shows a concrete example of SSI in action.

A beginner's guide to the Write Skew anomaly...

This article demonstrates how different databases handle write skew. We'll focus on PostgreSQL, which uses SSI to automatically prevent the anomaly.

First, read 'The problem' section to understand the IT department budget scenario—it's another classic write skew. Then, skip down and read the 'PostgreSQL' section. Note how PostgreSQL, when set to the SERIALIZABLE isolation level, detects the conflict and causes one of the transactions to fail with a 'serialization failure' error. This is the database protecting you from write skew automatically.

Conclusion

Today, we've bridged the gap between the strong guarantees of Snapshot Isolation and the perfect correctness of SERIALIZABLE isolation. While SI is highly efficient and prevents most common anomalies, it is vulnerable to subtle bugs that can corrupt application data.

Key Takeaways:

Snapshot Isolation is not serializable. It allows anomalies that true SERIALIZABLE isolation prevents.
The primary anomaly in SI is write skew, where concurrent transactions read a shared state but write to different items, leading to an inconsistent outcome. Phantoms are a variant of write skew involving newly inserted rows.
These anomalies arise because SI only checks for write-write conflicts, but misses conflicts caused by read-write dependencies (rw or anti-dependencies).
From a formal perspective, write skew creates a cycle in the transaction serialization graph, proving the execution is not serializable.
Preventing these anomalies requires either careful manual locking by the developer or using a database that implements an algorithm like Serializable Snapshot Isolation (SSI) to detect and prevent these cycles automatically.

Preview of the Next Lesson

We have now completed our module on Concurrency Control, understanding how databases ensure correctness in the face of multiple simultaneous operations. We will now shift our focus from correctness to performance and efficiency. In the next module, "Query Execution," we will begin to explore how a database actually processes a query. Our first lesson will be: Implement an index-based selection operator using a B+ tree and analyze its cost versus a full table scan. We will see how data structures like B+ trees are fundamental to executing queries efficiently.