diff options
Diffstat (limited to 'mysql-test/main/partition_innodb_semi_consistent.test')
| -rw-r--r-- | mysql-test/main/partition_innodb_semi_consistent.test | 203 |
1 files changed, 203 insertions, 0 deletions
diff --git a/mysql-test/main/partition_innodb_semi_consistent.test b/mysql-test/main/partition_innodb_semi_consistent.test new file mode 100644 index 00000000..c1a516b2 --- /dev/null +++ b/mysql-test/main/partition_innodb_semi_consistent.test @@ -0,0 +1,203 @@ +-- source include/no_view_protocol.inc + +-- source include/have_partition.inc +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# basic tests of semi-consistent reads +# for verifying Bug#40595: Non-matching rows not released with READ-COMMITTED +# on tables with partitions + +connect (a,localhost,root,,); +connect (b,localhost,root,,); +connection a; +set binlog_format=mixed; +set session transaction isolation level repeatable read; +create table t1(a int not null) +engine=innodb +DEFAULT CHARSET=latin1 +PARTITION BY RANGE(a) +(PARTITION p0 VALUES LESS THAN (20), + PARTITION p1 VALUES LESS THAN MAXVALUE); +insert into t1 values (1),(2),(3),(4),(5),(6),(7); +set autocommit=0; +# this should lock the entire table +select * from t1 where a=3 lock in share mode; +connection b; +set binlog_format=mixed; +set session transaction isolation level repeatable read; +set autocommit=0; +-- error ER_LOCK_WAIT_TIMEOUT +update t1 set a=10 where a=5; +commit; +connection a; +#DELETE FROM t1 WHERE a=5; +commit; +connection b; +# perform a semi-consisent read (and unlock non-matching rows) +set session transaction isolation level read committed; +update t1 set a=10 where a=5; +connection a; +-- error ER_LOCK_WAIT_TIMEOUT +select * from t1 where a=2 for update; +# this should lock the records (1),(2) +select * from t1 where a=2 limit 1 for update; +connection b; +# semi-consistent read will skip non-matching locked rows a=1, a=2 +update t1 set a=11 where a=6; +-- error ER_LOCK_WAIT_TIMEOUT +update t1 set a=12 where a=2; +-- error ER_LOCK_WAIT_TIMEOUT +update t1 set a=13 where a=1; +connection a; +commit; +connection b; +update t1 set a=14 where a=1; +commit; +connection a; +--sorted_result +select * from t1; +drop table t1; + +connection default; +disconnect a; +disconnect b; + +# +# Bug #31310: Locked rows silently skipped in read-committed isolation level. +# (This also tests the '*_semi_consistent*' functions in partitioning) +# Copied from include/mix1.inc + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +SET SESSION AUTOCOMMIT = 0; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +set binlog_format=mixed; +connection con1; + +eval +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (300), + PARTITION p1 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1,2); + +--echo # 1. test for locking: + +BEGIN; +--enable_info +UPDATE t1 SET b = 12 WHERE a = 1; +--disable_info +SELECT * FROM t1; + +connection con2; + +--enable_info +--disable_abort_on_error +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET b = 21 WHERE a = 1; +--disable_info +ROLLBACK; + +connection con1; +SELECT * FROM t1; +ROLLBACK; + +--echo # 2. test for serialized update: + +CREATE TABLE t2 (a INT); + +TRUNCATE t1; +INSERT INTO t1 VALUES (1,'init'); + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + # retry the UPDATE in case it times out the lock before con1 has time + # to COMMIT. + DECLARE do_retry INT DEFAULT 0; + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1; + retry_loop:LOOP + UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; + IF do_retry = 0 THEN + LEAVE retry_loop; + END IF; + SET do_retry = 0; + END LOOP; + INSERT INTO t2 VALUES (); +END| +DELIMITER ;| + +BEGIN; +--enable_info +UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; +--disable_info +SELECT * FROM t1; + +connection con2; + +--send CALL p1; + +connection con1; +SELECT * FROM t1; +COMMIT; + +let $bug31310 = 1; +while ($bug31310) +{ + let $bug31310= `SELECT 1 - COUNT(*) FROM t2`; +} + +SELECT * FROM t1; + +connection con2; +--reap +SELECT * FROM t1; +COMMIT; + +connection con1; + +--echo # 3. test for updated key column: + +TRUNCATE t1; +DELETE FROM t2; + +INSERT INTO t1 VALUES (1,'init'); + +BEGIN; +--enable_info +UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; +--disable_info +SELECT * FROM t1; + +connection con2; + +--send CALL p1; + +connection con1; +SELECT * FROM t1; +COMMIT; + +let $bug31310 = 1; +while ($bug31310) +{ + let $bug31310= `SELECT 1 - COUNT(*) FROM t2`; +} + +SELECT * FROM t1; + +connection con2; +reap; +SELECT * FROM t1; + +connection default; +disconnect con1; +disconnect con2; +DROP PROCEDURE p1; +DROP TABLE t1, t2; + |