Serializable
Some anomalies have no write-write conflict to detect. In write skew, two transactions each read an invariant, each write to a different row, and both commit — jointly breaking the rule both of them checked.
Write skew at REPEATABLE READ
Hospital rule: at least one doctor must stay on call. Alice and Bob both want the night off.
A> BEGIN;
Query OK
B> BEGIN;
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call; -- "two of us — safe for me to leave"
on_call
---------
2
(1 row)
B> SELECT count(*) AS on_call FROM doctors WHERE on_call; -- "two of us — safe for me to leave"
on_call
---------
2
(1 row)Each updates a DIFFERENT row, so there is no write-write conflict to detect.
A> UPDATE doctors SET on_call = false WHERE name = 'alice';
Query OK, 1 row affected
B> UPDATE doctors SET on_call = false WHERE name = 'bob';
Query OK, 1 row affected
A> COMMIT;
Query OK
B> COMMIT; -- both succeed!
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call; -- nobody is on call — the invariant is broken
on_call
---------
0
(1 row)Each transaction was internally consistent; together they broke the rule. Only SERIALIZABLE catches this.
Verified against MySQL 8.4.10 · Run it yourself · Scenario source
SERIALIZABLE stops it (with locks)
MySQL's SERIALIZABLE is REPEATABLE READ plus one rule: plain SELECTs act as SELECT … FOR SHARE, so every row you read gets a shared lock. Now the two night-off requests collide: each doctor's UPDATE needs an exclusive lock on a row the other is holding a shared lock on. That's a cycle — InnoDB's deadlock detector fires instantly:
Same story, same statements, same order — only the isolation level differs.
A> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK
B> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK
A> BEGIN;
Query OK
B> BEGIN;
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call;
on_call
---------
2
(1 row)
B> SELECT count(*) AS on_call FROM doctors WHERE on_call;
on_call
---------
2
(1 row)Those SELECTs took shared locks on the rows they read. A's write now waits for B…
A> UPDATE doctors SET on_call = false WHERE name = 'alice';
⏳ A is waiting for a lock……and B's write closes the cycle. InnoDB detects the deadlock and rolls B back entirely.
B> UPDATE doctors SET on_call = false WHERE name = 'bob'; -- ER_LOCK_DEADLOCK
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionB's rollback freed the locks — A's update proceeds.
⏵ A resumes:
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call; -- the invariant survived
on_call
---------
1
(1 row)PostgreSQL detects the same skew without blocking (SSI, at COMMIT). MySQL prevents it the classic way: locks and a deadlock victim.
Verified against MySQL 8.4.10 · Run it yourself · Scenario source
Two databases, two philosophies
PostgreSQL's SERIALIZABLE (SSI) lets both transactions run without blocking and aborts one at COMMIT with 40001. MySQL's is the classic lock-based approach: readers block writers, conflicts surface as deadlocks (errno 1213) mid-transaction. Same guarantee, very different failure mode — your retry logic must catch 1213, and it will fire before commit, not at it.
Every SELECT becomes a locking read
Switching SERIALIZABLE on globally turns your entire read traffic into shared-lock traffic — lock waits and deadlock storms where SELECTs used to be free. Scope it to the transactions whose invariants need it.
REPEATABLE READ won't stop write skew — both transactions commit and the invariant dies silently. SERIALIZABLE fixes that by turning every read into a shared lock, buying correctness through blocking, so expect deadlocks under contention: they're the mechanism here, not an accident, and your retry logic has to catch 1213. The cheaper targeted fix, here as everywhere, is to make the conflict explicit with SELECT … FOR UPDATE on the rows the decision depends on (locking reads).