#
# Case 1: READ COMMITTED
#
CREATE TABLE sbtest1(id INT PRIMARY KEY, k INT, KEY k_1(k)) ENGINE=InnoDB;
CREATE TABLE t1(id INT PRIMARY KEY, k INT, KEY k_1(k),
CONSTRAINT fkey_t FOREIGN KEY(k) REFERENCES sbtest1(k)
ON UPDATE CASCADE) ENGINE=InnoDB;
INSERT INTO sbtest1 VALUES (9,456),(509,456),(643,456),(261,457),(544,457),(999,458);
INSERT INTO t1     VALUES (9,456),(509,456),(643,456),(261,457),(544,457),(999,458);
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION innodb_lock_wait_timeout=60;
BEGIN;
SELECT id,k FROM t1 WHERE id=9 FOR UPDATE;
id	k
9	456
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION innodb_lock_wait_timeout=60;
UPDATE sbtest1 SET k=k+1 WHERE id=643;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION innodb_lock_wait_timeout=60;
UPDATE sbtest1 SET k=k+1 WHERE id=544;
ROLLBACK;
# CORRECT: all five rows at k=458 (verified by serialized execution).
# BUG: rows 9, 509, 643 stay at k=457 (missed by B's cascade).
SELECT id,k FROM t1 ORDER BY id;
id	k
9	457
261	458
509	457
544	458
643	457
999	458
SELECT COUNT(*) AS rows_missed_by_cascade FROM t1 WHERE k=457;
rows_missed_by_cascade
3
DROP TABLE t1, sbtest1;
#
# Case 2: REPEATABLE READ (same race can be hit)
#
CREATE TABLE sbtest1(id INT PRIMARY KEY, k INT, KEY k_1(k)) ENGINE=InnoDB;
CREATE TABLE t1(id INT PRIMARY KEY, k INT, KEY k_1(k),
CONSTRAINT fkey_t FOREIGN KEY(k) REFERENCES sbtest1(k)
ON UPDATE CASCADE) ENGINE=InnoDB;
INSERT INTO sbtest1 VALUES (9,456),(509,456),(643,456),(261,457),(544,457),(999,458);
INSERT INTO t1     VALUES (9,456),(509,456),(643,456),(261,457),(544,457),(999,458);
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION innodb_lock_wait_timeout=60;
BEGIN;
SELECT id,k FROM t1 WHERE id=9 FOR UPDATE;
id	k
9	456
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION innodb_lock_wait_timeout=60;
UPDATE sbtest1 SET k=k+1 WHERE id=643;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION innodb_lock_wait_timeout=60;
UPDATE sbtest1 SET k=k+1 WHERE id=544;
ROLLBACK;
SELECT id,k FROM t1 ORDER BY id;
id	k
9	457
261	458
509	457
544	458
643	457
999	458
SELECT COUNT(*) AS rows_missed_by_cascade FROM t1 WHERE k=457;
rows_missed_by_cascade
3
DROP TABLE t1, sbtest1;
#
# Control: serialized execution of the same two UPDATEs (no locking
# session) produces the correct result, all five rows at k=458.
#
CREATE TABLE sbtest1(id INT PRIMARY KEY, k INT, KEY k_1(k)) ENGINE=InnoDB;
CREATE TABLE t1(id INT PRIMARY KEY, k INT, KEY k_1(k),
CONSTRAINT fkey_t FOREIGN KEY(k) REFERENCES sbtest1(k)
ON UPDATE CASCADE) ENGINE=InnoDB;
INSERT INTO sbtest1 VALUES (9,456),(509,456),(643,456),(261,457),(544,457),(999,458);
INSERT INTO t1     VALUES (9,456),(509,456),(643,456),(261,457),(544,457),(999,458);
UPDATE sbtest1 SET k=k+1 WHERE id=643;
UPDATE sbtest1 SET k=k+1 WHERE id=544;
SELECT id,k FROM t1 ORDER BY id;
id	k
9	458
261	458
509	458
544	458
643	458
999	458
SELECT COUNT(*) AS rows_missed_by_cascade FROM t1 WHERE k=457;
rows_missed_by_cascade
0
DROP TABLE t1, sbtest1;