ACID - Write Skew

Write Skew Nedir ?

İki farkı satırım değiştirilmesi sonucunda olmaması gereken bir noktaya varmaktir.

Açıklaması şöyle

Imagine you’re building a hospital management system.

The hospital has two doctors on call. Either doctor can request time off, but the system must always ensure at least one doctor remains available.

The business rule sounds simple: At least one doctor must remain on call at all times.

Now imagine both doctors request time off at almost exactly the same moment. Doctor A checks the database and sees two active doctors. Doctor B performs the same check and also sees two active doctors. Both conclude that taking time off is safe. Doctor A updates their status to OFF_DUTY. Doctor B does the same.

Both transactions commit successfully.

What does the database contain now?

Zero doctors are on call.

Neither transaction violated the rule as conflict isn’t between two writes to the same row. Both transactions executed successfully. Yet the final database state violates an important business constraint.

This is called Write Skew, and it exposes an important limitation of Snapshot Isolation.

Örnek - Postgres

Açıklaması şöyle

PostgreSQL's SERIALIZABLE isolation level prevents write skew.

PostgreSQL's Serializable Snapshot Isolation (SSI) detects dangerous dependency cycles that could produce non-serializable results.

For example:

1. Two doctors are on call.

2. Each transaction checks that at least two doctors are on call.

3. Transaction A takes doctor 1 off call.

4. Transaction B takes doctor 2 off call.

5. Both transactions attempt to commit.

Under REPEATABLE READ, both transactions can commit, leaving zero doctors on call.

Under PostgreSQL SERIALIZABLE, PostgreSQL detects the conflict and aborts at least one transaction.

Important: You must retry the entire aborted transaction when PostgreSQL returns SQLSTATE 40001.

So, if you need protection against write skew, PostgreSQL SERIALIZABLE is the appropriate isolation level.

Örnek - SqlServer

Açıklaması şöyle

In SQL Server, SERIALIZABLE prevents write skew.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

-- Read and update data

COMMIT;

Unlike PostgreSQL SSI, SQL Server primarily achieves this through locking, including key-range locks.

Database Serializable implementation

PostgreSQL SSI (conflict detection)

SQL Server Locking and key-range locks

Important: SQL Server's SNAPSHOT isolation does not prevent write skew. It can allow two transactions to update different rows based on the same initial state.

For your SQL Server applications, use SERIALIZABLE when you need this guarantee, or consider explicit UPDLOCK, HOLDLOCK when you want more targeted locking.

Lost Update vs Write Skew

Örnek

Bir örnek şöyle

Case 3: Consider your team is on production support for a week. The requirement is at least 1 person should always be on support. Is it possible that if you and teammate try to unassign themselves (assuming the other one is still available) then no one is on support ?

Hatalı kod şöyle

## Psuedo code
if(totalMembersOnSupport() > 1) {
  removeFromSupport(user_id);
}
totalMembersOnSupport() -> Select count(user_id) from support where on_support = 't';
removeFromSupport() -> update support set on_support = 'f' where user_id = '234';

Açıklaması şöyle. Yani her iki kullanıcı bir sayaca bakarak kendi satırlarına güncelleme yapıyorlar.

One difference between this case and the previously discussed cases is that in the previous cases concurrent transactions were trying to update the same row. But in this case, they are updating two different rows.

This is known as Write Skew