diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb-read-view.test')
-rw-r--r-- | mysql-test/suite/innodb/t/innodb-read-view.test | 208 |
1 files changed, 208 insertions, 0 deletions
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..425cbeb08c8 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-read-view.test @@ -0,0 +1,208 @@ +# DEBUG_SYNC must be compiled in. +--source include/have_debug_sync.inc +--source include/have_debug.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,,) + +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; +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; +reap; +disconnect con1; + +connection default; + +DROP TABLE t1; + +# Clean up resources used in this test case. +SET DEBUG_SYNC= 'RESET'; +--source include/wait_until_count_sessions.inc |