Checked writes

These MySQL 8.4.11 InnoDB REPEATABLE READ Scenarios support the stock and stale-edit decisions. They assert particular single-row schedules, not an application UI or all possible executions.

Two buyers

Two buyers request four units each from a stock of five. B waits for A's UPDATE, then its condition matches no row after A commits. B rolls back; the quantity is 1. A deliberate failed-repair branch then removes the condition and decrements again, leaving -3.

A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK

B> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK

A> BEGIN;
Query OK

B> BEGIN;
Query OK

A> UPDATE stock SET quantity = quantity - 4 WHERE id = 1 AND quantity >= 4;
Query OK, 1 row affected

B> UPDATE stock SET quantity = quantity - 4 WHERE id = 1 AND quantity >= 4;
⏳ B is waiting for a lock…

A> COMMIT;
Query OK

⏵ B resumes:
Query OK, 0 rows affected

B> ROLLBACK;
Query OK

A> SELECT quantity FROM stock WHERE id = 1;
 quantity 
----------
        1 
(1 row)

Contrast an unconditional decrement after the checked rejection. This deliberately breaks the stock rule.

B> BEGIN;
Query OK

B> UPDATE stock SET quantity = quantity - 4 WHERE id = 1;
Query OK, 1 row affected

B> COMMIT;
Query OK

A> SELECT quantity FROM stock WHERE id = 1;
 quantity 
----------
       -3 
(1 row)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

An editing interval

Both editors load version 1 before their save transactions. A saves version 2. B's checked replacement affects zero rows, and a fresh read after rollback still shows A's edit. A deliberate failed-repair branch then uses the latest version with B's old draft, replacing A's text. The Scenario does not model a merge UI or an automatic retry policy.

A> SELECT body, version FROM documents WHERE id = 1;
   body   | version 
----------+---------
 Original |       1 
(1 row)

B> SELECT body, version FROM documents WHERE id = 1;
   body   | version 
----------+---------
 Original |       1 
(1 row)

A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK

B> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK

A> BEGIN;
Query OK

A> UPDATE documents SET body = 'A edit', version = version + 1 WHERE id = 1 AND version = 1;
Query OK, 1 row affected

A> COMMIT;
Query OK

B> BEGIN;
Query OK

B> UPDATE documents SET body = 'B edit', version = version + 1 WHERE id = 1 AND version = 1;
Query OK, 0 rows affected

B> ROLLBACK;
Query OK

B> SELECT body, version FROM documents WHERE id = 1;
  body  | version 
--------+---------
 A edit |       2 
(1 row)

Contrast blindly substituting the latest version while keeping B's old draft. This deliberately replaces A's edit.

B> BEGIN;
Query OK

B> UPDATE documents SET body = 'B edit', version = version + 1 WHERE id = 1 AND version = 2;
Query OK, 1 row affected

B> COMMIT;
Query OK

A> SELECT body, version FROM documents WHERE id = 1;
  body  | version 
--------+---------
 B edit |       3 
(1 row)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Predict related results in two-writer practice. Use the protection guide for writer assumptions and application conflict responses.