Write Skew vs Phantom Writes in Databases

Sep 6, 2023 · 5 min read

Hey everyone, it’s alanturrr1703 back with another blog! 😄 Today, we’ll be tackling two advanced phenomena in databases: Write Skew and Phantom Writes. If you’re familiar with isolation levels and database transactions, understanding these concepts will help you dig deeper into potential concurrency issues. Let’s dive in!

The Problem with Concurrency

When multiple transactions run at the same time in a database, they can interact with each other in unexpected ways. That’s where issues like Write Skew and Phantom Writes come in.

These issues occur when transactions interact with data in ways that cause subtle, often hard-to-detect inconsistencies. Let’s break them down and understand the impact they have on database consistency.

What is Write Skew?

Write Skew happens when two concurrent transactions read the same data, make decisions based on what they read, and then write changes that end up violating some business rules or constraints.

How It Works:

Write skew occurs in systems with Snapshot Isolation or even Serializable Isolation when two transactions read overlapping data and then perform writes that depend on the result of those reads. Since each transaction reads from a consistent snapshot, they’re unaware of each other’s changes, leading to skewed results.

Example:

Let’s say you have two doctors on call at a hospital, and the rule is that at least one doctor must be on call at all times. You have a table tracking their availability:

Doctor A: On Call
Doctor B: On Call

Now, both Doctor A and Doctor B decide they want to go off call at the same time. Here’s what happens:

Transaction 1 (Doctor A) reads the table, sees that Doctor B is still on call, and decides to go off call.
Transaction 2 (Doctor B) reads the table, sees that Doctor A is still on call, and also decides to go off call.
Both transactions commit without realizing the other is going off call too, violating the rule that one doctor must be on call at all times.

After both transactions commit, neither doctor is on call, which breaks the business rule. This is a classic write skew scenario because both transactions made changes based on stale data without knowing what the other transaction was doing.

Key Points:

Write Skew is a concurrency anomaly where two transactions make decisions based on outdated data, leading to an inconsistent result.
It happens even in Snapshot Isolation, where transactions get a consistent snapshot of the data.

What are Phantom Writes?

Phantom Writes occur when a transaction reads a set of rows, makes a decision based on that data, and then another transaction adds or removes rows that would have changed the original transaction’s decision.

How It Works:

Phantom writes happen when a transaction queries a range of rows (e.g., all rows where a condition is true) and then another transaction inserts or deletes rows that would have matched that condition.

In lower isolation levels like Read Committed or Repeatable Read, these new rows appear or disappear like “phantoms,” hence the name phantom writes.

Example:

Let’s consider a banking scenario. A transaction (Transaction 1) is checking the total balance of all accounts that have a balance greater than ₹10,000 to ensure the bank holds enough liquidity. Here’s what happens:

Transaction 1 reads all accounts with a balance over ₹10,000.
Transaction 2 inserts a new account with a balance of ₹15,000, but this happens after Transaction 1 has finished its read.
Transaction 1 is unaware of this new account when it makes its decision about liquidity.

If Transaction 1’s decision impacts something like bank reserves, the result might be incorrect because phantom rows (like the new ₹15,000 account) were inserted during the transaction.

Key Points:

Phantom Writes refer to rows that “appear” or “disappear” during a transaction’s execution, affecting decisions based on queries.
These issues typically arise with range queries (e.g., “find all rows where the balance is over ₹10,000”).

Key Differences Between Write Skew and Phantom Writes

Here’s a quick comparison of these two phenomena:

Feature	Write Skew	Phantom Writes
Type of Issue	Caused by concurrent decisions based on outdated reads	Caused by inserts or deletes during a transaction
Occurs When	Two transactions read and write overlapping data	A transaction reads a range of rows, and another inserts or deletes rows in that range
Common in	Snapshot Isolation	Read Committed, Repeatable Read
Example Scenario	Both doctors going off call based on outdated reads	New account added during a transaction that reads accounts over ₹10,000

How to Prevent Write Skew and Phantom Writes?

Both Write Skew and Phantom Writes can lead to dangerous inconsistencies, but fortunately, there are ways to prevent these issues:

1. For Write Skew:

Serializable Isolation: This isolation level is stricter than Snapshot Isolation and ensures that transactions behave as if they are executed one after another, preventing write skew.
Explicit Locking: You can use locks on the rows or tables involved in the transactions to avoid write skew by preventing concurrent transactions from interfering with each other.

2. For Phantom Writes:

Serializable Isolation: This is the highest level of isolation and ensures that transactions read data as if they were executed sequentially, preventing phantom writes.
Range Locks: Some databases allow locking ranges of data so that new rows can’t be inserted into the range during a transaction.

Wrapping It Up

Both Write Skew and Phantom Writes can introduce subtle bugs in your database systems if not properly handled. These concurrency anomalies might not occur often, but when they do, they can lead to serious inconsistencies, especially in systems that require high data integrity like banking or medical records.

Understanding these issues and knowing how to prevent them through proper isolation levels or locking mechanisms is crucial for building reliable database applications.

That’s all for today! Hope this post helped you get a deeper understanding of these concurrency issues. Until next time! 🚀