diff options
Diffstat (limited to 'mysql-test/t/lock_multi.test')
| -rw-r--r-- | mysql-test/t/lock_multi.test | 263 |
1 files changed, 233 insertions, 30 deletions
diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 4a6b4ff5e56..54c2e4e76ee 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -16,10 +16,16 @@ lock tables t1 write; connection writer; send update low_priority t1 set n = 4; connection reader; ---sleep 2 +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "update low_priority t1 set n = 4"; +--source include/wait_condition.inc send select n from t1; connection locker; ---sleep 2 +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "select n from t1"; +--source include/wait_condition.inc unlock tables; connection writer; reap; @@ -34,15 +40,15 @@ lock tables t1 read; connection writer; send update low_priority t1 set n = 4; connection reader; ---sleep 2 -send select n from t1; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "update low_priority t1 set n = 4"; +--source include/wait_condition.inc +select n from t1; connection locker; ---sleep 2 unlock tables; connection writer; reap; -connection reader; -reap; drop table t1; # @@ -58,13 +64,9 @@ insert into t1 values(2,2); insert into t2 values(1,2); lock table t1 read; connection writer; ---sleep 2 -send update t1,t2 set c=a where b=d; +update t1,t2 set c=a where b=d; connection reader; ---sleep 2 select c from t2; -connection writer; -reap; connection locker; drop table t1; drop table t2; @@ -73,7 +75,7 @@ drop table t2; # Test problem when using locks on many tables and droping a table that # is to-be-locked by another thread # - +# connection locker; create table t1 (a int); create table t2 (a int); @@ -81,6 +83,10 @@ lock table t1 write, t2 write; connection reader; send insert t1 select * from t2; connection locker; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "insert t1 select * from t2"; +--source include/wait_condition.inc drop table t2; connection reader; --error 1146 @@ -99,6 +105,10 @@ lock table t1 write, t2 write, t1 as t1_2 write, t2 as t2_2 write; connection reader; send insert t1 select * from t2; connection locker; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "insert t1 select * from t2"; +--source include/wait_condition.inc drop table t2; connection reader; --error 1146 @@ -107,7 +117,7 @@ connection locker; drop table t1; -# End of 4.1 tests +--echo End of 4.1 tests # # BUG#9998 - MySQL client hangs on USE "database" @@ -131,15 +141,18 @@ connection locker; use mysql; LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE; FLUSH TABLES; ---sleep 1 # connection reader; use mysql; #NOTE: This must be a multi-table select, otherwise the deadlock will not occur send SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1; ---sleep 1 # connection locker; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = + "SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1"; +--source include/wait_condition.inc # Make test case independent from earlier grants. --replace_result "Table is already up to date" "OK" OPTIMIZE TABLES columns_priv, db, host, user; @@ -163,10 +176,13 @@ LOCK TABLE t1 WRITE; # This waits until t1 is unlocked. connection locker; send FLUSH TABLES WITH READ LOCK; ---sleep 1 # -# This must not block. connection writer; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables" and info = "FLUSH TABLES WITH READ LOCK"; +--source include/wait_condition.inc +# This must not block. CREATE TABLE t2 (c1 int); UNLOCK TABLES; # @@ -187,10 +203,13 @@ LOCK TABLE t1 WRITE; # This waits until t1 is unlocked. connection locker; send FLUSH TABLES WITH READ LOCK; ---sleep 1 # # This must not block. connection writer; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables" and info = "FLUSH TABLES WITH READ LOCK"; +--source include/wait_condition.inc --error 1100 CREATE TABLE t2 AS SELECT * FROM t1; UNLOCK TABLES; @@ -219,11 +238,15 @@ FLUSH TABLES WITH READ LOCK; # wait in wait_if_global_read_lock(). connection con2; send DROP DATABASE mysqltest_1; ---sleep 1 # # With bug in place: try to acquire LOCK_mysql_create_table... # When fixed: Reject dropping db because of the read lock. connection con1; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for release of readlock" + and info = "DROP DATABASE mysqltest_1"; +--source include/wait_condition.inc --error ER_CANT_UPDATE_WITH_READLOCK DROP DATABASE mysqltest_1; UNLOCK TABLES; @@ -249,17 +272,18 @@ create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) e --enable_warnings lock tables t1 write; connection writer; ---sleep 2 -delimiter //; -send alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; // -delimiter ;// +send alter table t1 auto_increment=0; connection reader; ---sleep 2 -delimiter //; -send alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; alter table t1 auto_increment=0; // -delimiter ;// +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "alter table t1 auto_increment=0"; +--source include/wait_condition.inc +send alter table t1 auto_increment=0; connection locker; ---sleep 2 +let $wait_condition= + select count(*) = 2 from information_schema.processlist + where state = "Locked" and info = "alter table t1 auto_increment=0"; +--source include/wait_condition.inc unlock tables; connection writer; reap; @@ -267,6 +291,185 @@ connection reader; reap; connection locker; drop table t1; +# +--echo End of 5.0 tests + + +# +# Bug #21281 "Pending write lock is incorrectly removed when its +# statement being KILLed" +# +create table t1 (i int); +connection locker; +lock table t1 read; +connection writer; +--send update t1 set i= 10; +connection reader; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "update t1 set i= 10"; +--source include/wait_condition.inc +--send select * from t1; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "select * from t1"; +--source include/wait_condition.inc +let $ID= `select id from information_schema.processlist where state = "Locked" and info = "update t1 set i= 10"`; +--replace_result $ID ID +eval kill query $ID; +connection reader; +--reap +connection writer; +--error ER_QUERY_INTERRUPTED +--reap +connection locker; +unlock tables; +connection default; +drop table t1; + +# +# Bug#25856 - HANDLER table OPEN in one connection lock DROP TABLE in another one +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int) ENGINE=MEMORY; +--echo --> client 2 +connection locker; +--error 1031 +handler t1 open; +--echo --> client 1 +connection default; +drop table t1; + +# +# Bug#32395 Alter table under a impending global read lock causes a server crash +# + +# +# Test ALTER TABLE under LOCK TABLES and FLUSH TABLES WITH READ LOCK +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int); +connect (flush,localhost,root,,test,,); +connection default; +--echo connection: default +lock tables t1 write; +connection flush; +--echo connection: flush +--send flush tables with read lock; +connection default; +--echo connection: default +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +alter table t1 add column j int; +connect (insert,localhost,root,,test,,); +connection insert; +--echo connection: insert +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +--send insert into t1 values (1,2); +--echo connection: default +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for release of readlock"; +--source include/wait_condition.inc +unlock tables; +connection flush; +--echo connection: flush +--reap +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for release of readlock"; +--source include/wait_condition.inc +select * from t1; +unlock tables; +connection insert; +--reap +connection default; +let $wait_condition= + select count(*) = 1 from t1; +--source include/wait_condition.inc +select * from t1; +drop table t1; +disconnect flush; +disconnect insert; -# End of 5.0 tests +# +# Test that FLUSH TABLES under LOCK TABLES protects write locked tables +# from a impending FLUSH TABLES WITH READ LOCK +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int); +connect (flush,localhost,root,,test,,); +connection default; +--echo connection: default +lock tables t1 write; +connection flush; +--echo connection: flush +--send flush tables with read lock; +connection default; +--echo connection: default +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +flush tables; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +unlock tables; +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +connection flush; +--reap +connection default; +disconnect flush; +drop table t1; + +# +# Bug#30331: Table_locks_waited shows inaccurate values +# + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings +create table t1 (a int); +flush status; +lock tables t1 read; +let $tlwa= `show status like 'Table_locks_waited'`; +connect (waiter,localhost,root,,); +connection waiter; +--send insert into t1 values(1); +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Locked" and info = "insert into t1 values(1)"; +--source include/wait_condition.inc +let $tlwb= `show status like 'Table_locks_waited'`; +unlock tables; +drop table t1; +disconnect waiter; +connection default; +--disable_query_log +eval SET @tlwa= SUBSTRING_INDEX('$tlwa', ' ', -1); +eval SET @tlwb= SUBSTRING_INDEX('$tlwb', ' ', -1); +--enable_query_log +select @tlwa < @tlwb; +--echo End of 5.1 tests |