diff options
| -rw-r--r-- | mysql-test/r/partition_innodb_semi_consistent.result | 128 | ||||
| -rw-r--r-- | mysql-test/t/partition_innodb_semi_consistent-master.opt | 1 | ||||
| -rw-r--r-- | mysql-test/t/partition_innodb_semi_consistent.test | 194 | ||||
| -rw-r--r-- | sql/ha_partition.cc | 30 | ||||
| -rw-r--r-- | sql/ha_partition.h | 6 |
5 files changed, 358 insertions, 1 deletions
diff --git a/mysql-test/r/partition_innodb_semi_consistent.result b/mysql-test/r/partition_innodb_semi_consistent.result new file mode 100644 index 00000000000..1bb39af043a --- /dev/null +++ b/mysql-test/r/partition_innodb_semi_consistent.result @@ -0,0 +1,128 @@ +drop table if exists t1; +set binlog_format=mixed; +set session transaction isolation level read committed; +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; +select * from t1 where a=3 lock in share mode; +a +3 +set binlog_format=mixed; +set session transaction isolation level read committed; +set autocommit=0; +update t1 set a=10 where a=5; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +commit; +update t1 set a=10 where a=5; +select * from t1 where a=2 for update; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select * from t1 where a=2 limit 1 for update; +a +2 +update t1 set a=11 where a=6; +update t1 set a=12 where a=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +update t1 set a=13 where a=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +commit; +update t1 set a=14 where a=1; +commit; +select * from t1; +a +10 +11 +14 +2 +3 +4 +7 +drop table t1; +SET SESSION AUTOCOMMIT = 0; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +set binlog_format=mixed; +# Switch to connection con1 +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); +# 1. test for locking: +BEGIN; +UPDATE t1 SET b = 12 WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +1 12 +# Switch to connection con2 +UPDATE t1 SET b = 21 WHERE a = 1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# Switch to connection con1 +SELECT * FROM t1; +a b +1 12 +ROLLBACK; +# 2. test for serialized update: +CREATE TABLE t2 (a INT); +TRUNCATE t1; +INSERT INTO t1 VALUES (1,'init'); +CREATE PROCEDURE p1() +BEGIN +UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; +INSERT INTO t2 VALUES (); +END| +BEGIN; +UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +1 init+con1 +# Switch to connection con2 +CALL p1;; +# Switch to connection con1 +SELECT * FROM t1; +a b +1 init+con1 +COMMIT; +SELECT * FROM t1; +a b +1 init+con1 +# Switch to connection con2 +SELECT * FROM t1; +a b +1 init+con1+con2 +# Switch to connection con1 +# 3. test for updated key column: +TRUNCATE t1; +TRUNCATE t2; +INSERT INTO t1 VALUES (1,'init'); +BEGIN; +UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +2 init+con1 +# Switch to connection con2 +CALL p1;; +# Switch to connection con1 +SELECT * FROM t1; +a b +2 init+con1 +COMMIT; +SELECT * FROM t1; +a b +2 init+con1 +# Switch to connection con2 +SELECT * FROM t1; +a b +2 init+con1 +DROP PROCEDURE p1; +DROP TABLE t1, t2; diff --git a/mysql-test/t/partition_innodb_semi_consistent-master.opt b/mysql-test/t/partition_innodb_semi_consistent-master.opt new file mode 100644 index 00000000000..e76299453d3 --- /dev/null +++ b/mysql-test/t/partition_innodb_semi_consistent-master.opt @@ -0,0 +1 @@ +--innodb_lock_wait_timeout=2 diff --git a/mysql-test/t/partition_innodb_semi_consistent.test b/mysql-test/t/partition_innodb_semi_consistent.test new file mode 100644 index 00000000000..cfa170f575b --- /dev/null +++ b/mysql-test/t/partition_innodb_semi_consistent.test @@ -0,0 +1,194 @@ +-- 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 read committed; +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 read committed; +set autocommit=0; +-- error ER_LOCK_WAIT_TIMEOUT +update t1 set a=10 where a=5; +connection a; +#DELETE FROM t1 WHERE a=5; +commit; +connection b; +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; +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; +--echo # Switch to connection con1 +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; + +--echo # Switch to connection con2 +connection con2; + +--enable_info +--disable_abort_on_error +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET b = 21 WHERE a = 1; +--disable_info + +--echo # Switch to connection con1 +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 + UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; + INSERT INTO t2 VALUES (); +END| +DELIMITER ;| + +BEGIN; +--enable_info +UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; +--disable_info +SELECT * FROM t1; + +--echo # Switch to connection con2 +connection con2; + +--send CALL p1; + +--echo # Switch to connection con1 +connection con1; +SELECT * FROM t1; +COMMIT; + +let $bug31310 = 1; +while ($bug31310) +{ + let $bug31310= `SELECT 1 - COUNT(*) FROM t2`; +} + +SELECT * FROM t1; + +--echo # Switch to connection con2 +connection con2; +--reap +SELECT * FROM t1; + +--echo # Switch to connection con1 +connection con1; + +--echo # 3. test for updated key column: + +TRUNCATE t1; +TRUNCATE 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; + +--echo # Switch to connection con2 +connection con2; + +--send CALL p1; + +--echo # Switch to connection con1 +connection con1; +SELECT * FROM t1; +COMMIT; + +let $bug31310 = 1; +while ($bug31310) +{ + let $bug31310= `SELECT 1 - COUNT(*) FROM t2`; +} + +SELECT * FROM t1; + +--echo # Switch to connection con2 +connection con2; +SELECT * FROM t1; + +connection default; +disconnect con1; +disconnect con2; +DROP PROCEDURE p1; +DROP TABLE t1, t2; + diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 852f437b213..47d2d36ac8d 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -2818,6 +2818,36 @@ void ha_partition::unlock_row() DBUG_VOID_RETURN; } +/** + Check if semi consistent read was used + + SYNOPSIS + was_semi_consistent_read() + + RETURN VALUE + TRUE Previous read was a semi consistent read + FALSE Previous read was not a semi consistent read + + DESCRIPTION + See handler.h: + In an UPDATE or DELETE, if the row under the cursor was locked by another + transaction, and the engine used an optimistic read of the last + committed row value under the cursor, then the engine returns 1 from this + function. MySQL must NOT try to update this optimistic value. If the + optimistic value does not match the WHERE condition, MySQL can decide to + skip over this row. Currently only works for InnoDB. This can be used to + avoid unnecessary lock waits. + + If this method returns nonzero, it will also signal the storage + engine that the next read will be a locking re-read of the row. +*/ +bool ha_partition::was_semi_consistent_read() +{ + DBUG_ENTER("ha_partition::was_semi_consistent_read"); + DBUG_ASSERT(m_last_part < m_tot_parts && + bitmap_is_set(&(m_part_info->used_partitions), m_last_part)); + DBUG_RETURN(m_file[m_last_part]->was_semi_consistent_read()); +} /** Use semi consistent read if possible diff --git a/sql/ha_partition.h b/sql/ha_partition.h index dd06d8d647b..bae2d03a8ce 100644 --- a/sql/ha_partition.h +++ b/sql/ha_partition.h @@ -112,7 +112,7 @@ private: uint m_reorged_parts; // Number of reorganised parts uint m_tot_parts; // Total number of partitions; uint m_no_locks; // For engines like ha_blackhole, which needs no locks - uint m_last_part; // Last file that we update,write + uint m_last_part; // Last file that we update,write,read int m_lock_type; // Remembers type of last // external_lock part_id_range m_part_spec; // Which parts to scan @@ -326,6 +326,10 @@ public: */ virtual void unlock_row(); /* + Check if semi consistent read + */ + virtual bool was_semi_consistent_read(); + /* Call to hint about semi consistent read */ virtual void try_semi_consistent_read(bool); |