About this video

• Video Title: Write Skew and Phantom Writes | Systems Design Interview: 0 to 1 with Google Software Engineer
• Channel: Jordan has no life
• Speakers: Jordan
• Duration: 00:06:38

Overview

This video explains two types of database race conditions: Right Skew and Phantoms. The speaker uses real-world examples, like an emergency room and a bakery, to illustrate how these issues can arise and how they can be resolved using locking mechanisms and materializing conflicts.

Key takeaways

• Right Skew: This race condition occurs when concurrent transactions modify different rows that impact the same database invariant. For example, if two doctors in an ER both try to set their status to inactive simultaneously, the invariant that at least one doctor must be active could be broken.
• Solution to Right Skew: To prevent right skew, transactions need to acquire locks on all relevant rows, even those not directly being modified, if those rows affect the same invariant. In the ER example, a transaction trying to set a doctor to inactive would need to acquire locks on all other active doctors' rows.
• Phantom Writes: This race condition happens when concurrent transactions insert new rows that conflict with each other, and these new rows were not present when locks were initially acquired. For instance, two people trying to claim the last item of a specific type (like cupcakes) in a bakery could both create a new entry for themselves.
• Solution to Phantom Writes: Phantom writes can be resolved by "materializing conflicts." This involves pre-populating rows in the database for all possible items or scenarios that could lead to a conflict, ensuring that a lock can be acquired on these pre-existing rows before any new data is written.

Visual and Written Notes

Right Skew

• Problem: Two transactions modify different rows, but these rows impact the same database invariant.
• Example: Emergency Room with doctors. Invariant: always have >1 active doctor.
  • Dr. Oz and Dr. Toboggan are active.
  • Both try to set themselves to inactive.
  • Dr. Oz writes "inactive" to his row.
  • Dr. Toboggan writes "inactive" to his row.
  • Result: 0 active doctors, invariant broken.
• Why it happens: Standard row-level locks aren't sufficient because each transaction only locks the row it's modifying. Dr. Oz doesn't lock Dr. Toboggan's row, and vice-versa.
• Solution: Lock all relevant rows.
  • In the ER example: When setting a doctor to inactive, acquire locks on all other currently active doctors' rows as well.
  • This forces one transaction to wait or fail if the other holds a necessary lock, thus protecting the invariant.

---

Phantom Writes

• Problem: Two transactions insert new rows that conflict, and these new rows don't exist when locks are initially checked.
• Example: Bakery with cookies, pie, and cupcakes.
  • Jordan claims cupcakes (inserts a new row).
  • Trump claims cupcakes at the same time (inserts another new row).
  • Result: Two rows for cupcakes, but the system might expect only one or have logic that breaks if multiple claims exist.
• Why it happens: You can't lock a row that doesn't exist yet. The transactions are adding new data, bypassing existing lock mechanisms.
• Solution: Materialize Conflicts.
  • Pre-populate rows for all possible items that could be claimed (e.g., create rows for "cookies", "pie", "cupcakes" even if they are initially empty or have a zero quantity).
  • Now, when Jordan and Trump want to claim cupcakes, they are attempting to lock/modify the pre-existing "cupcakes" row.
  • Standard locking mechanisms can then prevent the race condition, ensuring only one person successfully claims the cupcakes.

---

Key Concepts:

• Database Race Condition: When multiple transactions access and manipulate shared data concurrently, and the final outcome depends on the unpredictable timing of their execution.
• Invariant: A condition or property that must always hold true in a database system.
• Locking: A concurrency control mechanism where transactions acquire locks on data items to prevent other transactions from accessing or modifying them simultaneously.
• Row-level Lock: A lock acquired on a specific row in a database table.
• Materializing Conflicts: A technique to prevent phantom writes by ensuring that all potential data entries involved in a conflict exist beforehand, allowing for standard locking.