MDEV-13626: Add innodb.innodb-read-view

2 files changed, 441 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-read-view.result b/mysql-test/suite/innodb/r/innodb-read-view.result
new file mode 100644
index 00000000000..eb48a5ab661
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-read-view.result
@@ -0,0 +1,227 @@
+CREATE TABLE t1 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
+INSERT INTO t1 VALUES(0, "0");
+INSERT INTO t1 VALUES(1, "1");
+INSERT INTO t1 VALUES(2, "2");
+INSERT INTO t1 VALUES(3, "3");
+CREATE TABLE t2 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
+INSERT INTO t2 VALUES(0, "a");
+INSERT INTO t2 VALUES(1, "b");
+INSERT INTO t2 VALUES(2, "c");
+INSERT INTO t2 VALUES(3, "d");
+connect  con1,localhost,root,,;
+connect  con2,localhost,root,,;
+connection con1;
+'T1'
+SET AUTOCOMMIT=0;
+BEGIN;
+SELECT * FROM t2;
+c1	c2
+0	a
+1	b
+2	c
+3	d
+connection default;
+'T2'
+SET AUTOCOMMIT=0;
+BEGIN;
+SELECT * FROM t1;
+c1	c2
+0	0
+1	1
+2	2
+3	3
+connection con2;
+'T3'
+SET AUTOCOMMIT=0;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
+SELECT * FROM t1;
+c1	c2
+0	0
+1	1
+2	2
+3	3
+SELECT * FROM t2;
+c1	c2
+0	a
+1	b
+2	c
+3	d
+connection con1;
+'T1'
+UPDATE t2 SET c1 = c1 + 100;
+SELECT * FROM t2;
+c1	c2
+100	a
+101	b
+102	c
+103	d
+COMMIT;
+connection default;
+'T2'
+UPDATE t1 SET c1 = c1 + 100;
+SELECT * FROM t1;
+c1	c2
+100	0
+101	1
+102	2
+103	3
+COMMIT;
+connection con2;
+'T3'
+SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1';
+SELECT * FROM t1;;
+connection default;
+'T2'
+SET DEBUG_SYNC='now SIGNAL waiting1';
+'Signalled T3'
+connection con2;
+'T3'
+c1	c2
+0	0
+1	1
+2	2
+3	3
+connection con2;
+'T3'
+SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1';
+SELECT * FROM t2;;
+connection default;
+'T2'
+SET DEBUG_SYNC='now SIGNAL waiting1';
+'Signalled T3'
+connection con2;
+'T3'
+c1	c2
+0	a
+1	b
+2	c
+3	d
+connection default;
+disconnect con1;
+disconnect con2;
+connect  con1,localhost,root,,;
+connect  con2,localhost,root,,;
+connection con1;
+'T1'
+SET AUTOCOMMIT=0;
+BEGIN;
+SELECT * FROM t1;
+c1	c2
+100	0
+101	1
+102	2
+103	3
+connection default;
+'T2'
+SET AUTOCOMMIT=0;
+BEGIN;
+SELECT * FROM t2;
+c1	c2
+100	a
+101	b
+102	c
+103	d
+UPDATE t2 SET c1 = c1 + 100;
+SELECT * FROM t2;
+c1	c2
+200	a
+201	b
+202	c
+203	d
+COMMIT;
+connection con2;
+'T3'
+SET AUTOCOMMIT=0;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
+SELECT * FROM t1;
+c1	c2
+100	0
+101	1
+102	2
+103	3
+SELECT * FROM t2;
+c1	c2
+200	a
+201	b
+202	c
+203	d
+connection con1;
+'T1'
+UPDATE t1 SET c1 = c1 + 100;
+SELECT * FROM t1;
+c1	c2
+200	0
+201	1
+202	2
+203	3
+COMMIT;
+connection con2;
+'T3'
+SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1';
+SELECT * FROM t1;;
+connection con1;
+'T2'
+SET DEBUG_SYNC='now SIGNAL waiting1';
+'Signalled T3'
+connection con2;
+'T3'
+c1	c2
+100	0
+101	1
+102	2
+103	3
+connection con2;
+'T3'
+SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1';
+SELECT * FROM t2;;
+connection default;
+'T2'
+SET DEBUG_SYNC='now SIGNAL waiting1';
+'Signalled T3'
+connection con2;
+'T3'
+c1	c2
+200	a
+201	b
+202	c
+203	d
+connection default;
+disconnect con1;
+disconnect con2;
+DROP TABLE t1;
+DROP TABLE t2;
+#
+# Bug 21433768: NON-REPEATABLE READ WITH REPEATABLE READ ISOLATION
+#
+connect  con1,localhost,root,,;
+# connection con1
+CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE = InnoDB;
+INSERT INTO t1 values (1, 0), (2, 0);
+SELECT * FROM t1 ORDER BY col1;
+col1	col2
+1	0
+2	0
+START TRANSACTION;
+UPDATE t1 SET col2 = 100;
+SET DEBUG_SYNC = 'after_trx_committed_in_memory SIGNAL s1 WAIT_FOR s2';
+COMMIT;;
+connection default;
+# connection default
+SET DEBUG_SYNC = 'now WAIT_FOR s1';
+UPDATE t1 SET col2 = col2 + 10 where col1 = 1;
+COMMIT;
+SELECT * FROM t1 ORDER BY col1;
+col1	col2
+1	110
+2	100
+SET DEBUG_SYNC = 'now SIGNAL s2';
+connection con1;
+# connection con1
+# reap COMMIT for con1
+connection default;
+# connection default
+disconnect con1;
+DROP TABLE t1;
+SET DEBUG_SYNC= 'RESET';
diff --git a/mysql-test/suite/innodb/t/innodb-read-view.test b/mysql-test/suite/innodb/t/innodb-read-view.test
new file mode 100644
index 00000000000..ec4026d10c6
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-read-view.test
@@ -0,0 +1,214 @@
+# DEBUG_SYNC must be compiled in.
+--source include/have_debug_sync.inc
+
+# We need to test the use case:
+#    a. Create a transaction T1 that will be promoted to RW.
+#    b. Create a transaction T2 that will be promoted to RW.
+#    a. Create a RO transaction T3
+#    d. T3 does a select - creates a read view that doesn't include T1 and T2
+#    e. T1 & T2 do some updates - this promotes T1 & T2 to RW transactions
+#    f. T1 & T2 Commit
+#    g. T3 Does a select - it should not see the changes of T1 & T2
+
+--source include/have_innodb.inc
+--source include/count_sessions.inc
+
+CREATE TABLE t1 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
+INSERT INTO t1 VALUES(0, "0");
+INSERT INTO t1 VALUES(1, "1");
+INSERT INTO t1 VALUES(2, "2");
+INSERT INTO t1 VALUES(3, "3");
+
+CREATE TABLE t2 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
+INSERT INTO t2 VALUES(0, "a");
+INSERT INTO t2 VALUES(1, "b");
+INSERT INTO t2 VALUES(2, "c");
+INSERT INTO t2 VALUES(3, "d");
+
+--connect (con1,localhost,root,,)
+--connect (con2,localhost,root,,)
+
+connection con1;
+--echo 'T1'
+SET AUTOCOMMIT=0;
+BEGIN;
+SELECT * FROM t2;
+
+connection default;
+--echo 'T2'
+SET AUTOCOMMIT=0;
+BEGIN;
+SELECT * FROM t1;
+
+connection con2;
+--echo 'T3'
+SET AUTOCOMMIT=0;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection con1;
+--echo 'T1'
+UPDATE t2 SET c1 = c1 + 100;
+SELECT * FROM t2;
+COMMIT;
+
+connection default;
+--echo 'T2'
+UPDATE t1 SET c1 = c1 + 100;
+SELECT * FROM t1;
+COMMIT;
+
+connection con2;
+--echo 'T3'
+SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1';
+--send SELECT * FROM t1;
+
+connection default;
+--echo 'T2'
+SET DEBUG_SYNC='now SIGNAL waiting1';
+--echo 'Signalled T3'
+
+connection con2;
+--echo 'T3'
+reap;
+
+connection con2;
+--echo 'T3'
+SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1';
+--send SELECT * FROM t2;
+
+connection default;
+--echo 'T2'
+SET DEBUG_SYNC='now SIGNAL waiting1';
+--echo 'Signalled T3'
+
+connection con2;
+--echo 'T3'
+reap;
+
+connection default;
+disconnect con1;
+disconnect con2;
+
+# We need to test the use case:
+#    a. Create a transaction T1 that will be promoted to RW.
+#    b. Create a transaction T2 that will be promoted to RW.
+#    c. T2 does some updates - this promotes T2 to RW transactions
+#    d. T2 Commits
+#    e. Create a RO transaction T3
+#    f. T3 does a select - creates a read view that doesn't include T1
+#    g. T1 does some updates - this promotes T1 to RW transactions
+#    h. T1 Commits
+#    i. T3 Does a select - it should not see the changes made by T1 but should
+#       see the changes by T2
+
+--connect (con1,localhost,root,,)
+--connect (con2,localhost,root,,)
+
+connection con1;
+--echo 'T1'
+SET AUTOCOMMIT=0;
+BEGIN;
+SELECT * FROM t1;
+
+connection default;
+--echo 'T2'
+SET AUTOCOMMIT=0;
+BEGIN;
+SELECT * FROM t2;
+UPDATE t2 SET c1 = c1 + 100;
+SELECT * FROM t2;
+COMMIT;
+
+connection con2;
+--echo 'T3'
+SET AUTOCOMMIT=0;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection con1;
+--echo 'T1'
+UPDATE t1 SET c1 = c1 + 100;
+SELECT * FROM t1;
+COMMIT;
+
+connection con2;
+--echo 'T3'
+SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1';
+--send SELECT * FROM t1;
+
+connection con1;
+--echo 'T2'
+SET DEBUG_SYNC='now SIGNAL waiting1';
+--echo 'Signalled T3'
+
+connection con2;
+--echo 'T3'
+reap;
+
+connection con2;
+--echo 'T3'
+SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1';
+--send SELECT * FROM t2;
+
+connection default;
+--echo 'T2'
+SET DEBUG_SYNC='now SIGNAL waiting1';
+--echo 'Signalled T3'
+
+connection con2;
+--echo 'T3'
+reap;
+
+connection default;
+disconnect con1;
+disconnect con2;
+
+DROP TABLE t1;
+DROP TABLE t2;
+
+--echo #
+--echo # Bug 21433768: NON-REPEATABLE READ WITH REPEATABLE READ ISOLATION
+--echo #
+
+--connect (con1,localhost,root,,)
+--echo # connection con1
+
+CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE = InnoDB;
+INSERT INTO t1 values (1, 0), (2, 0);
+SELECT * FROM t1 ORDER BY col1;
+
+START TRANSACTION;
+UPDATE t1 SET col2 = 100;
+SET DEBUG_SYNC = 'after_trx_committed_in_memory SIGNAL s1 WAIT_FOR s2';
+--send COMMIT;
+
+connection default;
+--echo # connection default
+SET DEBUG_SYNC = 'now WAIT_FOR s1';
+UPDATE t1 SET col2 = col2 + 10 where col1 = 1;
+COMMIT;
+
+SELECT * FROM t1 ORDER BY col1;
+SET DEBUG_SYNC = 'now SIGNAL s2';
+
+connection con1;
+--echo # connection con1
+--echo # reap COMMIT for con1
+reap;
+
+connection default;
+--echo # connection default
+disconnect con1;
+
+DROP TABLE t1;
+
+# Clean up resources used in this test case.
+--disable_warnings
+SET DEBUG_SYNC= 'RESET';
+--enable_warnings
+--source include/wait_until_count_sessions.inc