1# 2# Bug #84958 InnoDB's MVCC has O(N^2) behaviors 3# https://bugs.mysql.com/bug.php?id=84958 4# 5# Set up the test with a procedure and a function. 6# 7SET @saved_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency; 8SET GLOBAL innodb_purge_rseg_truncate_frequency= 1; 9CREATE PROCEDURE insert_n(start int, end int) 10BEGIN 11DECLARE i INT DEFAULT start; 12START TRANSACTION; 13WHILE i <= end do 14INSERT INTO t1 VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = i; 15SET i = i + 1; 16END WHILE; 17COMMIT; 18END~~ 19CREATE FUNCTION num_pages_get() 20RETURNS INT 21BEGIN 22DECLARE ret INT; 23SELECT variable_value INTO ret 24FROM information_schema.global_status 25WHERE variable_name = 'innodb_buffer_pool_read_requests'; 26RETURN ret; 27END~~ 28# 29# Create a table with one record in it and start an RR transaction 30# 31CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY(a,b), KEY (b,c)) 32ENGINE=InnoDB; 33BEGIN; 34SELECT * FROM t1; 35a b c 36# 37# Create 100 newer record versions in con2 and con3 38# 39connect con2, localhost, root,,; 40connection con2; 41INSERT INTO t1 VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = NULL; 42CALL insert_n(1, 50);; 43connect con3, localhost, root,,; 44connection con3; 45CALL insert_n(51, 100);; 46connection con2; 47connection con3; 48INSERT INTO t1 VALUES (1, 2, 1) ON DUPLICATE KEY UPDATE c = NULL; 49connection default; 50# 51# Connect to default and record how many pages were accessed 52# when selecting the record using the secondary key. 53# 54InnoDB 4 transactions not purged 55SET @num_pages_1 = num_pages_get(); 56SELECT * FROM t1 force index (b); 57a b c 58SET @num_pages_2= num_pages_get(); 59SELECT IF(@num_pages_2 - @num_pages_1 < 5000, 'OK', @num_pages_2 - @num_pages_1) num_pages_diff; 60num_pages_diff 61OK 62# 63# Commit and show the final record. 64# 65SELECT * FROM t1; 66a b c 67SELECT * FROM t1 force index (b); 68a b c 69COMMIT; 70SELECT * FROM t1 force index (b); 71a b c 721 2 NULL 73SELECT * FROM t1; 74a b c 751 2 NULL 76CHECK TABLE t1; 77Table Op Msg_type Msg_text 78test.t1 check status OK 79# 80# Cleanup 81# 82disconnect con2; 83disconnect con3; 84SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; 85DROP TABLE t1; 86DROP PROCEDURE insert_n; 87DROP FUNCTION num_pages_get; 88