Serializable
For a prediction task, open the staffing practice before reading the explanations below.
A cross-row business rule can fail even when concurrent transactions write different rows. These MySQL 8.4.11 InnoDB Scenarios use the rule that at least one doctor stays on call. Each request counts two doctors and removes a different one. Each request would preserve the rule if it ran alone from that state.
Write skew at REPEATABLE READ
Both requests commit in the displayed snapshot-read schedule. The final count is zero. This demonstrates item write skew (G2-item), not every predicate anomaly.
A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK
B> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OKHospital 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. SERIALIZABLE or suitable coordination by all relevant writers can protect it; the next Scenario executes the SERIALIZABLE schedule.
Verified against MySQL 8.4.11 · Run it yourself · Scenario source
SERIALIZABLE stops it (with locks)
The MySQL 8.4 SERIALIZABLE contract uses shared locking reads for plain SELECTs in explicit multi-statement transactions, as executed here, or when autocommit is disabled. Lock coverage depends on the query and index, including relevant ranges, not just returned rows. A's UPDATE waits for B; B's UPDATE closes the cycle. With deadlock detection enabled, this schedule selects B as victim (1213), A commits, and one doctor remains. The victim and detection timing are not universal guarantees.
A> SELECT @@innodb_deadlock_detect AS deadlock_detect;
deadlock_detect
-----------------
1
(1 row)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)B starts a fresh SERIALIZABLE transaction and repeats the count. With only one doctor remaining, the application declines the update.
B> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK
B> BEGIN;
Query OK
B> SELECT count(*) AS on_call FROM doctors WHERE on_call;
on_call
---------
1
(1 row)
B> COMMIT;
Query OK
A> SELECT name, on_call FROM doctors ORDER BY name;
name | on_call
-------+---------
alice | 0
bob | 1
(2 rows)A standalone SERIALIZABLE SELECT with autocommit=1 is a nonlocking consistent read here.
A> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK
A> SELECT @@autocommit AS autocommit;
autocommit
------------
1
(1 row)
B> BEGIN;
Query OK
B> UPDATE doctors SET on_call = false WHERE name = 'bob';
Query OK, 1 row affected
A> SELECT count(*) AS on_call FROM doctors WHERE on_call;
on_call
---------
1
(1 row)With autocommit=0, A's SERIALIZABLE SELECT instead waits for B's row lock.
A> SET autocommit = 0;
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call;
⏳ A is waiting for a lock…
B> ROLLBACK;
Query OK
⏵ A resumes:
on_call
---------
1
(1 row)
A> ROLLBACK;
Query OK
A> SET autocommit = 1;
Query OKVerified against MySQL 8.4.11 · Run it yourself · Scenario source
The fresh B attempt repeats the count and declines to leave. Retry the whole transaction with fresh reads and decisions, or return a controlled failure. A successful retry is not guaranteed. The error-handling contract says deadlocks roll back the entire transaction. With deadlock detection disabled, lock-wait timeouts can resolve the cycle instead; their rollback scope depends on innodb_rollback_on_timeout. These configuration alternatives are not executed here.
Two databases, different conflict handling
PostgreSQL's SSI example detects this dependency conflict without the count SELECTs taking InnoDB-style row locks. PostgreSQL SERIALIZABLE can still wait for ordinary conflicting writes or locks, and 40001 is not confined to COMMIT. InnoDB deadlocks are not confined to SERIALIZABLE.
Not every SERIALIZABLE SELECT locks
A standalone autocommit SELECT can be a nonlocking consistent read. The final part of this Scenario asserts that it reads the last committed on-call count during B's uncommitted change. With autocommit disabled, the same SELECT waits until B rolls back. Applying a session level does not combine separate autocommit statements into one protected read-modify-write transaction.
Entailed guarantee: for this rule, if every relevant request uses the SERIALIZABLE transaction boundary and checks the count before removing a doctor, the locking read prevents a competing removal from silently invalidating that decision before commit. A serial execution of the requests preserves at least one doctor; waits or aborted attempts must be handled without reporting a commit. This derivation uses the manual's locking-read and SERIALIZABLE contracts, not just the observed count of one. Other writers that bypass the check can still break the rule. Suitable explicit coordination can also protect it; SERIALIZABLE is not the only possible repair, and no cost comparison is measured here.
Lock-wait relationships
Text equivalent: both count reads hold shared locks covering Alice and Bob. A's UPDATE requests exclusive access to Alice and waits for B's shared lock. B's UPDATE requests exclusive access to Bob, conflicting with A's shared lock. That request closes the cycle; B is rejected with 1213 in this asserted schedule. Its rollback releases the conflict, A completes, and the fresh B attempt checks the rule again. The SELECT-to-lock explanation uses the MySQL 8.4 SERIALIZABLE contract, not an assertion that this diagram decodes every acquired lock.
The PostgreSQL dependency picture uses dashed reader-to-writer edges for logical ordering requirements. Those dependencies need not block a statement. The REPEATABLE READ separate-row schedules on both engines have that logical cycle without this lock-wait cycle; InnoDB does not implement PostgreSQL SSI. Preserve that distinction when reading either generated timeline.
For the participating writers and conflict policy, use the on-call worked decision.