# Bug: Concurrent cascaded updates (FK ON UPDATE CASCADE) can silently miss
# child rows, leaving the child table inconsistent with the parent table.
#
# Two concurrent transactions update different parent rows, each firing an
# ON UPDATE CASCADE on a non-unique referenced column. The second
# transaction's cascade scan of the child index does not see the first
# transaction's not-yet-inserted child index entries, so rows are silently
# missed. Because cascaded row changes are not binlogged, a replica that
# replays the same parent row events fires its own complete cascade and
# diverges from the master.
#
# Deterministic reproduction: session con1 holds an X lock on the clustered
# record of the FIRST child row that con2's cascade would update, so con2
# blocks BEFORE inserting any new child secondary index entry; con3 then
# runs its own parent update, whose cascade scan only sees the old child
# index entries and completes without waiting; finally the lock is released.
#
# Reproduces on 8.0.30 in both READ COMMITTED and REPEATABLE READ.


--echo #
--echo # Case 1: READ COMMITTED
--echo #

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);

connect(con1, localhost, root,,);
connect(con2, localhost, root,,);
connect(con3, localhost, root,,);

# con1: take an X lock on the clustered record of the first child row that
# con2's cascade will try to update (t1.id=9), and hold it.
connection con1;
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;

# con2 (A): update parent row id=643 (k: 456 -> 457). Its cascade of the
# t1 rows with k=456 blocks on the X lock above, BEFORE any new t1.k_1
# entry with k=457 has been inserted.
connection con2;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION innodb_lock_wait_timeout=60;
send UPDATE sbtest1 SET k=k+1 WHERE id=643;

# Wait until A is actually blocked in lock wait.
connection default;
let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.innodb_trx
                     WHERE trx_state='LOCK WAIT';
--source include/wait_condition.inc

# con3 (B): update a different parent row id=544 (k: 457 -> 458). B's
# cascade scans t1.k_1 for k=457, but A's new entries are not inserted
# yet, so B only sees the two native k=457 rows (id=261, id=544). B is
# not blocked by anything and completes immediately.
connection con3;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION innodb_lock_wait_timeout=60;
UPDATE sbtest1 SET k=k+1 WHERE id=544;

# Release the lock; A unblocks and finishes its cascade.
connection con1;
ROLLBACK;

connection con2;
reap;

# Verify. Serialized execution of the same two UPDATEs gives the correct
# result: all of id in (9,261,509,544,643) at k=458. With the bug, rows
# 9, 509 and 643 are missed by B's cascade and stay at k=457.
connection default;
--echo # CORRECT: all five rows at k=458 (verified by serialized execution).
--echo # BUG: rows 9, 509, 643 stay at k=457 (missed by B's cascade).
SELECT id,k FROM t1 ORDER BY id;
SELECT COUNT(*) AS rows_missed_by_cascade FROM t1 WHERE k=457;

connection con1;
disconnect con1;
connection con2;
disconnect con2;
connection con3;
disconnect con3;

connection default;
DROP TABLE t1, sbtest1;

--echo #
--echo # Case 2: REPEATABLE READ (same race can be hit)
--echo #

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);

connect(con1, localhost, root,,);
connect(con2, localhost, root,,);
connect(con3, localhost, root,,);

connection con1;
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;

connection con2;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION innodb_lock_wait_timeout=60;
send UPDATE sbtest1 SET k=k+1 WHERE id=643;

connection default;
let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.innodb_trx
                     WHERE trx_state='LOCK WAIT';
--source include/wait_condition.inc

connection con3;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION innodb_lock_wait_timeout=60;
UPDATE sbtest1 SET k=k+1 WHERE id=544;

connection con1;
ROLLBACK;

connection con2;
reap;

connection default;
SELECT id,k FROM t1 ORDER BY id;
SELECT COUNT(*) AS rows_missed_by_cascade FROM t1 WHERE k=457;

connection con1;
disconnect con1;
connection con2;
disconnect con2;
connection con3;
disconnect con3;

connection default;
DROP TABLE t1, sbtest1;

--echo #
--echo # Control: serialized execution of the same two UPDATEs (no locking
--echo # session) produces the correct result, all five rows at k=458.
--echo #

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;
SELECT COUNT(*) AS rows_missed_by_cascade FROM t1 WHERE k=457;
DROP TABLE t1, sbtest1;