diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/debug_sync.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/innodb-lock.test | 49 | ||||
| -rw-r--r-- | mysql-test/t/innodb_mysql_lock.test | 54 | ||||
| -rw-r--r-- | mysql-test/t/lock.test | 76 | ||||
| -rw-r--r-- | mysql-test/t/lock_multi.test | 113 | ||||
| -rw-r--r-- | mysql-test/t/lock_sync.test | 9 | ||||
| -rw-r--r-- | mysql-test/t/mdl_sync.test | 2472 | ||||
| -rw-r--r-- | mysql-test/t/multi_update.test | 11 | ||||
| -rw-r--r-- | mysql-test/t/truncate_coverage.test | 39 |
9 files changed, 2647 insertions, 178 deletions
diff --git a/mysql-test/t/debug_sync.test b/mysql-test/t/debug_sync.test index 514e471b603..ebeeec61632 100644 --- a/mysql-test/t/debug_sync.test +++ b/mysql-test/t/debug_sync.test @@ -390,7 +390,7 @@ DROP TABLE IF EXISTS t1; # # Test. CREATE TABLE t1 (c1 INT); -LOCK TABLE t1 WRITE; +LOCK TABLE t1 READ; --echo connection con1 connect (con1,localhost,root,,); # Retain action after use. First used by general_log. diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test index eacf7e562be..d2f630ccaba 100644 --- a/mysql-test/t/innodb-lock.test +++ b/mysql-test/t/innodb-lock.test @@ -56,9 +56,12 @@ commit; drop table t1; -# -# Try with old lock method (where LOCK TABLE is ignored by InnoDB) -# +--echo # +--echo # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer +--echo # works due to fix for bugs #46272 "MySQL 5.4.4, new MDL: unnecessary +--echo # deadlock" and bug #37346 "innodb does not detect deadlock between +--echo # update and alter table". +--echo # set @@innodb_table_locks=0; @@ -67,36 +70,38 @@ insert into t1 values(0, 0),(1,1),(2,2); commit; SELECT * from t1 where id = 0 FOR UPDATE; +--echo # Connection 'con2'. connection con2; set autocommit=0; set @@innodb_table_locks=0; -# The following statement should work becase innodb doesn't check table locks -lock table t1 write; +--echo # The following statement should block because SQL-level lock +--echo # is taken on t1 which will wait until concurrent transaction +--echo # is commited. +--echo # Sending: +--send lock table t1 write; +--echo # Connection 'con1'. connection con1; +--echo # Wait until LOCK TABLE is blocked on SQL-level lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # We should be able to do UPDATEs and SELECTs within transaction. +update t1 set x=1 where id = 0; +select * from t1; +--echo # Unblock LOCK TABLE. +commit; -# This will be locked by MySQL ---send -update t1 set x=10 where id = 2; ---sleep 2 - +--echo # Connection 'con2'. connection con2; - -# Note that we will get a deadlock if we try to select any rows marked -# for update by con1 ! - -SELECT * from t1 where id = 2; -UPDATE t1 set x=3 where id = 2; -commit; -SELECT * from t1; -commit; +--echo # Reap LOCK TABLE. +--reap unlock tables; +--echo # Connection 'con1'. connection con1; -reap; -commit; -select * from t1; drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test index c8c38cd1ab1..6469ef2d229 100644 --- a/mysql-test/t/innodb_mysql_lock.test +++ b/mysql-test/t/innodb_mysql_lock.test @@ -66,6 +66,60 @@ connection default; disconnect con1; disconnect con3; + +--echo # +--echo # Test for bug #37346 "innodb does not detect deadlock between update +--echo # and alter table". +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (c1 int primary key, c2 int, c3 int) engine=InnoDB; +insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); +begin; +--echo # Run statement which acquires X-lock on one of table's rows. +update t1 set c3=c3+1 where c2=3; + +--echo # +--echo # Switching to connection 'con37346'. +connect (con37346,localhost,root,,test,,); +connection con37346; +--echo # The below ALTER TABLE statement should wait till transaction +--echo # in connection 'default' is complete and then succeed. +--echo # It should not deadlock or fail with ER_LOCK_DEADLOCK error. +--echo # Sending: +--send alter table t1 add column c4 int; + +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until the above ALTER TABLE gets blocked because this +--echo # connection holds SW metadata lock on table to be altered. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add column c4 int"; +--source include/wait_condition.inc + +--echo # The below statement should succeed. It should not +--echo # deadlock or end with ER_LOCK_DEADLOCK error. +update t1 set c3=c3+1 where c2=4; + +--echo # Unblock ALTER TABLE by committing transaction. +commit; + +--echo # +--echo # Switching to connection 'con37346'. +connection con37346; +--echo # Reaping ALTER TABLE. +--reap + +--echo # +--echo # Switching to connection 'default'. +connection default; +disconnect con37346; +drop table t1; + + --echo # --echo # Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB --echo # table cause warnings in errlog diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test index bc9d1ea8245..eda3e8451dd 100644 --- a/mysql-test/t/lock.test +++ b/mysql-test/t/lock.test @@ -202,6 +202,12 @@ select * from t1; select * from t2; --error ER_TABLE_NOT_LOCKED select * from t3; +--echo Dropping of implicitly locked table is disallowed. +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop table t1; +unlock tables; +--echo Now let us also lock table explicitly and drop it. +lock tables t1 write, v_bug5719 write; drop table t1; --echo --echo sic: left LOCK TABLES mode @@ -349,6 +355,76 @@ drop table t1; --echo # +--echo # Coverage for situations when we try to execute DDL on tables +--echo # which are locked by LOCK TABLES only implicitly. +--echo # +--disable_warnings +drop tables if exists t1, t2; +drop view if exists v1; +drop function if exists f1; +--enable_warnings +create table t1 (i int); +create table t2 (j int); +--echo # +--echo # Try to perform DDL on table which is locked through view. +create view v1 as select * from t2; +lock tables t1 write, v1 write; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +flush table t2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop table t2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +alter table t2 add column k int; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +create trigger t2_bi before insert on t2 for each row set @a:=1; +--echo # Repair produces error as part of its result set. +repair table t2; +unlock tables; +drop view v1; +--echo # +--echo # Now, try DDL on table which is locked through routine. +delimiter |; +create function f1 () returns int +begin + insert into t2 values (1); + return 0; +end| +delimiter ;| +create view v1 as select f1() from t1; +lock tables v1 read; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +flush table t2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop table t2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +alter table t2 add column k int; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +create trigger t2_bi before insert on t2 for each row set @a:=1; +--echo # Repair produces error as part of its result set. +repair table t2; +unlock tables; +drop view v1; +drop function f1; +--echo # +--echo # Finally, try DDL on table which is locked thanks to trigger. +create trigger t1_ai after insert on t1 for each row insert into t2 values (1); +lock tables t1 write; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +flush table t2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop table t2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +alter table t2 add column k int; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +create trigger t2_bi before insert on t2 for each row set @a:=1; +--echo # Repair produces error as part of its result set. +repair table t2; +unlock tables; +drop trigger t1_ai; +drop tables t1, t2; + + +--echo # --echo # Bug#45035 " Altering table under LOCK TABLES results in --echo # "Error 1213 Deadlock found..." --echo # diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 31a10f89796..b924923233b 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -8,14 +8,24 @@ drop table if exists t1,t2; # Test to see if select will get the lock ahead of low priority update connect (locker,localhost,root,,); +connect (locker2,localhost,root,,); connect (reader,localhost,root,,); connect (writer,localhost,root,,); connection locker; create table t1(n int); insert into t1 values (1); -lock tables t1 write; +connection locker2; +select get_lock("mysqltest_lock", 100); +connection locker; +send +update t1 set n = 2 and get_lock('mysqltest_lock', 100); connection writer; +# Wait till above update gets blocked on a user lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "User lock" and info = "update t1 set n = 2 and get_lock('mysqltest_lock', 100)"; +--source include/wait_condition.inc send update low_priority t1 set n = 4; connection reader; @@ -26,13 +36,16 @@ let $wait_condition= --source include/wait_condition.inc send select n from t1; -connection locker; +connection locker2; # Sleep a bit till the select of connection reader is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Table lock" and info = "select n from t1"; --source include/wait_condition.inc -unlock tables; +select release_lock("mysqltest_lock"); +connection locker; +reap; +select release_lock("mysqltest_lock"); connection writer; reap; connection reader; @@ -42,8 +55,17 @@ drop table t1; connection locker; create table t1(n int); insert into t1 values (1); -lock tables t1 read; +connection locker2; +select get_lock("mysqltest_lock", 100); +connection locker; +send +select n from t1 where get_lock('mysqltest_lock', 100); connection writer; +# Wait till above select gets blocked on a user lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "User lock" and info = "select n from t1 where get_lock('mysqltest_lock', 100)"; +--source include/wait_condition.inc send update low_priority t1 set n = 4; connection reader; @@ -53,8 +75,11 @@ let $wait_condition= where state = "Table lock" and info = "update low_priority t1 set n = 4"; --source include/wait_condition.inc select n from t1; +connection locker2; +select release_lock("mysqltest_lock"); connection locker; -unlock tables; +reap; +select release_lock("mysqltest_lock"); connection writer; reap; drop table t1; @@ -95,9 +120,10 @@ insert t1 select * from t2; connection locker; let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "insert t1 select * from t2"; + where state = "Waiting for table" and info = "insert t1 select * from t2"; --source include/wait_condition.inc drop table t2; +unlock tables; connection reader; --error ER_NO_SUCH_TABLE reap; @@ -119,9 +145,10 @@ connection locker; # Sleep a bit till the insert of connection reader is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "insert t1 select * from t2"; + where state = "Waiting for table" and info = "insert t1 select * from t2"; --source include/wait_condition.inc drop table t2; +unlock tables; connection reader; --error ER_NO_SUCH_TABLE reap; @@ -164,7 +191,7 @@ connection locker; # Sleep a bit till the select of connection reader is in work and hangs let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist - WHERE state = "Table lock" AND info = + 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. @@ -299,7 +326,7 @@ connection reader; # Wait till connection writer is blocked let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "alter table t1 auto_increment=0"; + where state = "Waiting for table" and info = "alter table t1 auto_increment=0"; --source include/wait_condition.inc send alter table t1 auto_increment=0; @@ -307,7 +334,7 @@ connection locker; # Wait till connection reader is blocked let $wait_condition= select count(*) = 2 from information_schema.processlist - where state = "Table lock" and info = "alter table t1 auto_increment=0"; + where state = "Waiting for table" and info = "alter table t1 auto_increment=0"; --source include/wait_condition.inc unlock tables; connection writer; @@ -502,6 +529,7 @@ drop table t1; # Disconnect sessions used in many subtests above disconnect locker; +disconnect locker2; disconnect reader; disconnect writer; @@ -668,6 +696,57 @@ disconnect flush; --echo # +--echo # Test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock". +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (c1 int primary key, c2 int, c3 int); +insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); +begin; +update t1 set c3=c3+1 where c2=3; + +--echo # +--echo # Switching to connection 'con46272'. +connect (con46272,localhost,root,,test,,); +connection con46272; +--echo # The below ALTER TABLE statement should wait till transaction +--echo # in connection 'default' is complete and then succeed. +--echo # It should not deadlock or fail with ER_LOCK_DEADLOCK error. +--echo # Sending: +--send alter table t1 add column c4 int; + +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until the above ALTER TABLE gets blocked because this +--echo # connection holds SW metadata lock on table to be altered. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add column c4 int"; +--source include/wait_condition.inc + +--echo # The below statement should succeed. It should not +--echo # deadlock or end with ER_LOCK_DEADLOCK error. +update t1 set c3=c3+1 where c2=4; + +--echo # Unblock ALTER TABLE by committing transaction. +commit; + +--echo # +--echo # Switching to connection 'con46272'. +connection con46272; +--echo # Reaping ALTER TABLE. +--reap + +--echo # +--echo # Switching to connection 'default'. +connection default; +disconnect con46272; +drop table t1; + + +--echo # --echo # Bug#47249 assert in MDL_global_lock::is_lock_type_compatible --echo # @@ -679,21 +758,17 @@ DROP VIEW IF EXISTS v1; --echo # --echo # Test 1: LOCK TABLES v1 WRITE, t1 READ; --echo # +--echo # Thanks to the fact that we no longer allow DDL on tables +--echo # which are locked for write implicitly, the exact scenario +--echo # in which assert was failing is no longer repeatable. CREATE TABLE t1 ( f1 integer ); CREATE VIEW v1 AS SELECT f1 FROM t1 ; ---echo # Connection 2 -connect (con2,localhost,root); LOCK TABLES v1 WRITE, t1 READ; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE FLUSH TABLE t1; -disconnect con2; ---source include/wait_until_disconnected.inc - ---echo # Connection 1 -connection default; -LOCK TABLES t1 WRITE; -FLUSH TABLE t1; # Assertion happened here +UNLOCK TABLES; # Cleanup DROP TABLE t1; diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test index de8a1d7e43e..460c0175808 100644 --- a/mysql-test/t/lock_sync.test +++ b/mysql-test/t/lock_sync.test @@ -21,6 +21,7 @@ --echo # TL_WRITE_ALLOW_READ) on this table might have led to deadlock. --disable_warnings drop table if exists t1; +drop view if exists v1; --enable_warnings --echo # Create auxiliary connections used through the test. connect (con_bug45143_1,localhost,root,,test,,); @@ -35,6 +36,9 @@ set @old_general_log = @@global.general_log; set @@global.general_log= OFF; create table t1 (i int) engine=InnoDB; +--echo # We have to use view in order to make LOCK TABLES avoid +--echo # acquiring SNRW metadata lock on table. +create view v1 as select * from t1; insert into t1 values (1); --echo # Prepare user lock which will be used for resuming execution of --echo # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock. @@ -65,14 +69,14 @@ connection con_bug45143_3; --echo # acquiring lock for the the first instance of 't1'. set debug_sync= 'now WAIT_FOR parked'; --echo # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1': ---send lock table t1 write; +--send lock table v1 write; --echo # Switch to connection 'default'. connection default; --echo # Wait until this LOCK TABLES statement starts waiting for table lock. let $wait_condition= select count(*)= 1 from information_schema.processlist where state= 'Table lock' and - info='lock table t1 write'; + info='lock table v1 write'; --source include/wait_condition.inc --echo # Allow SELECT ... FOR UPDATE to resume. --echo # Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance @@ -110,6 +114,7 @@ disconnect con_bug45143_2; disconnect con_bug45143_3; set debug_sync= 'RESET'; set @@global.general_log= @old_general_log; +drop view v1; drop table t1; diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test index 4cbaa689339..dda9ba991cf 100644 --- a/mysql-test/t/mdl_sync.test +++ b/mysql-test/t/mdl_sync.test @@ -44,7 +44,7 @@ set debug_sync= 'now WAIT_FOR parked'; connection con2; --echo connection: con2 -set debug_sync='mdl_acquire_exclusive_locks_wait SIGNAL go'; +set debug_sync='mdl_acquire_lock_wait SIGNAL go'; --send drop table t1,t2 connection con1; @@ -74,6 +74,2313 @@ SET DEBUG_SYNC= 'RESET'; --echo # +--echo # Basic test coverage for type-of-operation aware metadata locks. +--echo # +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings +connect(mdl_con1,localhost,root,,); +connect(mdl_con2,localhost,root,,); +connect(mdl_con3,localhost,root,,); +connection default; +set debug_sync= 'RESET'; +create table t1 (c1 int); + +--echo # +--echo # A) First let us check compatibility rules between differend kinds of +--echo # type-of-operation aware metadata locks. +--echo # Of course, these rules are already covered by the tests scattered +--echo # across the test suite. But it still makes sense to have one place +--echo # which covers all of them. +--echo # + +--echo # 1) Acquire S (simple shared) lock on the table (by using HANDLER): +--echo # +handler t1 open; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open t; +handler t close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +insert into t1 values (1), (1); +--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail after opening the table and thus obtaining SNW metadata +--echo # lock. +--error ER_DUP_ENTRY +alter table t1 add primary key (c1); +--echo # Check that SNRW lock is compatible with S lock. +lock table t1 write; +insert into t1 values (1); +unlock tables; +--echo # Check that X lock is incompatible with S lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of S lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Unblock RENAME TABLE. +handler t1 close; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping RENAME TABLE. +--reap +--echo # Restore the original state of the things. +rename table t2 to t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +handler t1 open; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that upgrade from SNW to X is blocked by presence of S lock. +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above ALTER TABLE is blocked because of S lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Unblock ALTER TABLE. +handler t1 close; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +--echo # +--echo # Switching to connection 'default'. +connection default; +handler t1 open; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that upgrade from SNRW to X is blocked by presence of S lock. +lock table t1 write; +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked +--echo # because of S lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Unblock ALTER TABLE. +handler t1 close; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # 2) Acquire SH (shared high-priority) lock on the table. +--echo # We have to involve DEBUG_SYNC facility for this as usually +--echo # such kind of locks are short-lived. +--echo # +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +insert into t1 values (1); +--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail after opening the table and thus obtaining SNW metadata +--echo # lock. +--error ER_DUP_ENTRY +alter table t1 add primary key (c1); +--echo # Check that SNRW lock is compatible with SH lock. +lock table t1 write; +delete from t1 limit 1; +unlock tables; +--echo # Check that X lock is incompatible with SH lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SH lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping SELECT ... FROM I_S. +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping RENAME TABLE. +--reap +--echo # Restore the original state of the things. +rename table t2 to t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that upgrade from SNW to X is blocked by presence of SH lock. +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above ALTER TABLE is blocked because of SH lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping SELECT ... FROM I_S. +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that upgrade from SNRW to X is blocked by presence of S lock. +lock table t1 write; +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked +--echo # because of S lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping SELECT ... FROM I_S. +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # +--echo # 3) Acquire SR lock on the table. +--echo # +--echo # +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +insert into t1 values (1); +--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE +--echo # which will fail after opening the table and thus obtaining SNW metadata +--echo # lock. +--error ER_DUP_ENTRY +alter table t1 add primary key (c1); +--echo # Check that SNRW lock is not compatible with SR lock. +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Check that the above LOCK TABLES is blocked because of SR lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock LOCK TABLES. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping LOCK TABLES. +--reap +delete from t1 limit 1; +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that X lock is incompatible with SR lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SR lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping RENAME TABLE. +--reap +--echo # Restore the original state of the things. +rename table t2 to t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that upgrade from SNW to X is blocked by presence of SR lock. +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above ALTER TABLE is blocked because of SR lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Unblock ALTER TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--reap +--echo # Restore the original state of the things. +alter table t1 drop column c2; +--echo # +--echo # There is no need to check that upgrade from SNRW to X is blocked +--echo # by presence of SR lock because SNRW is incompatible with SR anyway. +--echo # +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # +--echo # 4) Acquire SW lock on the table. +--echo # +--echo # +begin; +insert into t1 values (1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +insert into t1 values (1); +--echo # Check that SNW lock is not compatible with SW lock. +--echo # Again we use ALTER TABLE which fails after opening +--echo # the table to avoid upgrade of SNW -> X. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Check that the above ALTER TABLE is blocked because of SW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +begin; +insert into t1 values (1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that SNRW lock is not compatible with SW lock. +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Check that the above LOCK TABLES is blocked because of SW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock LOCK TABLES. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping LOCK TABLES. +--reap +delete from t1 limit 2; +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +begin; +insert into t1 values (1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that X lock is incompatible with SW lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping RENAME TABLE. +--reap +--echo # Restore the original state of the things. +rename table t2 to t1; +--echo # +--echo # There is no need to check that upgrade from SNW/SNRW to X is +--echo # blocked by presence of SW lock because SNW/SNRW is incompatible +--echo # with SW anyway. +--echo # +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # +--echo # 5) Acquire SNW lock on the table. We have to use DEBUG_SYNC for +--echo # this, to prevent SNW from being immediately upgraded to X. +--echo # +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that S, SH and SR locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +--echo # Check that SW lock is incompatible with SNW lock. +--echo # Sending: +--send delete from t1 limit 2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above DELETE is blocked because of SNW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "delete from t1 limit 2"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus DELETE. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that SNW lock is incompatible with SNW lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above ALTER is blocked because of SNW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock ALTERs. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping first ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping another ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that SNRW lock is incompatible with SNW lock. +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above LOCK TABLES is blocked because of SNW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus LOCK TABLES. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping LOCK TABLES +--reap +insert into t1 values (1); +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +set debug_sync= 'now WAIT_FOR locked'; +--echo # Check that X lock is incompatible with SNW lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above RENAME is blocked because of SNW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Unblock ALTER and thus RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping RENAME TABLE +--reap +--echo # Revert back to original state of things. +rename table t2 to t1; +--echo # +--echo # There is no need to check that upgrade from SNW/SNRW to X is +--echo # blocked by presence of another SNW lock because SNW/SNRW is +--echo # incompatible with SNW anyway. +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # +--echo # 6) Acquire SNRW lock on the table. +--echo # +--echo # +lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that S and SH locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +--echo # Check that SR lock is incompatible with SNRW lock. +--echo # Sending: +--send select count(*) from t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Check that the above SELECT is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "select count(*) from t1"; +--source include/wait_condition.inc +--echo # Unblock SELECT. +unlock tables; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping SELECT. +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that SW lock is incompatible with SNRW lock. +--echo # Sending: +--send delete from t1 limit 1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Check that the above DELETE is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "delete from t1 limit 1"; +--source include/wait_condition.inc +--echo # Unblock DELETE. +unlock tables; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that SNW lock is incompatible with SNRW lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Check that the above ALTER is blocked because of UNWR lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER. +unlock tables; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that SNRW lock is incompatible with SNRW lock. +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Check that the above LOCK TABLES is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock waiting LOCK TABLES. +unlock tables; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping LOCK TABLES +--reap +insert into t1 values (1); +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that X lock is incompatible with SNRW lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Check that the above RENAME is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping RENAME TABLE +--reap +--echo # Revert back to original state of things. +rename table t2 to t1; +--echo # +--echo # There is no need to check that upgrade from SNW/SNRW to X is +--echo # blocked by presence of another SNRW lock because SNW/SNRW is +--echo # incompatible with SNRW anyway. +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # +--echo # 7) Now do the same round of tests for X lock. We use additional +--echo # table to get long-lived lock of this type. +--echo # +create table t2 (c1 int); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked +--echo # after acquiring X lock on t1. +lock tables t2 read; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that S lock in incompatible with X lock. +--echo # Sending: +--send handler t1 open; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above HANDLER statement is blocked because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "handler t1 open"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping HANDLER. +--reap +handler t1 close; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SH lock in incompatible with X lock. +--echo # Sending: +--send select column_name from information_schema.columns where table_schema='test' and table_name='t1'; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above SELECT ... FROM I_S ... statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info like "select column_name from information_schema.columns%"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping SELECT ... FROM I_S. +--reap +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SR lock in incompatible with X lock. +--echo # Sending: +--send select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above SELECT statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "select count(*) from t1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping SELECT. +--reap +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SW lock in incompatible with X lock. +--echo # Sending: +--send delete from t1 limit 1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above DELETE statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "delete from t1 limit 1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SNW lock is incompatible with X lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above ALTER statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SNRW lock is incompatible with X lock. +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above LOCK TABLE statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping LOCK TABLE. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Prepare for blocking RENAME TABLE. +lock tables t2 read; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that X lock is incompatible with X lock. +--echo # Sending: +--send rename table t1 to t3; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above RENAME statement is blocked +--echo # because of X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t3"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping RENAME. +--reap +rename table t3 to t1; + +--echo # +--echo # B) Now let us test compatibility in cases when both locks +--echo # are pending. I.e. let us test rules for priorities between +--echo # different types of metadata locks. +--echo # + +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # +--echo # 1) Check compatibility for pending SNW lock. +--echo # +--echo # Acquire SW lock in order to create pending SNW lock later. +begin; +insert into t1 values (1); +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Add pending SNW lock. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that ALTER TABLE is waiting with pending SNW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Check that S, SH and SR locks are compatible with pending SNW +handler t1 open t; +handler t close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +select count(*) from t1; +--echo # Check that SW is incompatible with pending SNW +--echo # Sending: +--send delete from t1 limit 1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above DELETE is blocked because of pending SNW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "delete from t1 limit 1"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +commit; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping ALTER. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # +--echo # We can't do similar check for SNW, SNRW and X locks because +--echo # they will also be blocked by active SW lock. +--echo # +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # +--echo # 2) Check compatibility for pending SNRW lock. +--echo # +--echo # Acquire SR lock in order to create pending SNRW lock. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Add pending SNRW lock. +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that LOCK TABLE is waiting with pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that S and SH locks are compatible with pending SNRW +handler t1 open t; +handler t close; +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +--echo # Check that SR is incompatible with pending SNRW +--echo # Sending: +--send select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above SELECT is blocked because of pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "select count(*) from t1"; +--source include/wait_condition.inc +--echo # Unblock LOCK TABLE. +commit; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping LOCK TABLE. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping SELECT. +--reap +--echo # Restore pending SNRW lock. +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that LOCK TABLE is waiting with pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that SW is incompatible with pending SNRW +--echo # Sending: +--send insert into t1 values (1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above INSERT is blocked because of pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "insert into t1 values (1)"; +--source include/wait_condition.inc +--echo # Unblock LOCK TABLE. +commit; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping LOCK TABLE. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping INSERT. +--reap +--echo # Restore pending SNRW lock. +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that LOCK TABLE is waiting with pending SNRW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that SNW is compatible with pending SNRW +--echo # So ALTER TABLE statements are not starved by LOCK TABLEs. +--error ER_DUP_ENTRY +alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Unblock LOCK TABLE. +commit; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping LOCK TABLE. +--reap +unlock tables; +--echo # +--echo # We can't do similar check for SNRW and X locks because +--echo # they will also be blocked by active SR lock. +--echo # +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # +--echo # 3) Check compatibility for pending X lock. +--echo # +--echo # Acquire SR lock in order to create pending X lock. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SH locks are compatible with pending X +select column_name from information_schema.columns where + table_schema='test' and table_name='t1'; +--echo # Check that S is incompatible with pending X +--echo # Sending: +--send handler t1 open; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above HANDLER OPEN is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "handler t1 open"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping HANDLER t1 OPEN. +--reap +handler t1 close; +--echo # Restore pending X lock. +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SR is incompatible with pending X +--echo # Sending: +--send select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above SELECT is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "select count(*) from t1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping SELECT. +--reap +--echo # Restore pending X lock. +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SW is incompatible with pending X +--echo # Sending: +--send delete from t1 limit 1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above DELETE is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "delete from t1 limit 1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping DELETE. +--reap +--echo # Restore pending X lock. +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SNW is incompatible with pending X +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above ALTER TABLE is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # Restore pending X lock. +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +handler t1 open; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Add pending X lock. +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that RENAME TABLE is waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that SNRW is incompatible with pending X +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'mdl_con3'. +connection mdl_con3; +--echo # Check that the above LOCK TABLES is blocked because of pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Unblock RENAME TABLE. +handler t1 close; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reaping RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reaping LOCK TABLES. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; + +--echo # +--echo # +--echo # C) Now let us test how type-of-operation locks are handled in +--echo # transactional context. Obviously we are mostly interested +--echo # in conflicting types of locks. +--echo # + +--echo # +--echo # 1) Let us check how various locks used within transactional +--echo # context interact with active/pending SNW lock. +--echo # +--echo # We start with case when we are acquiring lock on the table +--echo # which was not used in the transaction before. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Create an active SNW lock on t2. +--echo # We have to use DEBUG_SYNC facility as otherwise SNW lock +--echo # will be immediately released (or upgraded to X lock). +insert into t2 values (1), (1); +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t2 add primary key (c1); +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'now WAIT_FOR locked'; +--echo # SR lock should be acquired without any waiting. +select count(*) from t2; +commit; +--echo # Now let us check that we will wait in case of SW lock. +begin; +select count(*) from t1; +--echo # Sending: +--send insert into t2 values (1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above INSERT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "insert into t2 values (1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE and thus INSERT. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap INSERT. +--reap +commit; +--echo # +--echo # Now let us see what happens when we are acquiring lock on the table +--echo # which is already used in transaction. +--echo # +--echo # *) First, case when transaction which has SR lock on the table also +--echo # locked in SNW mode acquires yet another SR lock and then tries +--echo # to acquire SW lock. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Create an active SNW lock on t1. +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'default'. +connection default; +set debug_sync= 'now WAIT_FOR locked'; +--echo # We should still be able to get SR lock without waiting. +select count(*) from t1; +--echo # Since the above ALTER TABLE is not upgrading SNW lock to X by waiting +--echo # for SW lock we won't create deadlock. +--echo # So the below INSERT should not end-up with ER_LOCK_DEADLOCK error. +--echo # Sending: +--send insert into t1 values (1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above INSERT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "insert into t1 values (1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE and thus INSERT. +set debug_sync= 'now SIGNAL finish'; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap INSERT. +--reap +commit; +--echo # +--echo # **) Now test in which transaction that has SW lock on the table +--echo # against which there is pending SNW lock acquires SR and SW +--echo # locks on this table. +--echo # +begin; +insert into t1 values (1); +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Create pending SNW lock on t1. +--echo # Sending: +--send alter table t1 add primary key (c1); +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until ALTER TABLE starts waiting for SNW lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "alter table t1 add primary key (c1)"; +--source include/wait_condition.inc +--echo # We should still be able to get both SW and SR locks without waiting. +select count(*) from t1; +delete from t1 limit 1; +--echo # Unblock ALTER TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap ALTER TABLE. +--error ER_DUP_ENTRY +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # 2) Now similar tests for active SNW lock which is being upgraded +--echo # to X lock. +--echo # +--echo # Again we start with case when we are acquiring lock on the +--echo # table which was not used in the transaction before. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Start transaction which will prevent SNW -> X upgrade from +--echo # completing immediately. +begin; +select count(*) from t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Create SNW lock pending upgrade to X on t2. +--echo # Sending: +--send alter table t2 add column c2 int; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until ALTER TABLE starts waiting X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "alter table t2 add column c2 int"; +--source include/wait_condition.inc +--echo # Check that attempt to acquire SR lock on t2 causes waiting. +--echo # Sending: +--send select count(*) from t2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above SELECT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "select count(*) from t2"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap ALTER TABLE. +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap SELECT. +--reap +commit; +--echo # Do similar check for SW lock. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Start transaction which will prevent SNW -> X upgrade from +--echo # completing immediately. +begin; +select count(*) from t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Create SNW lock pending upgrade to X on t2. +--echo # Sending: +--send alter table t2 drop column c2; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until ALTER TABLE starts waiting X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "alter table t2 drop column c2"; +--source include/wait_condition.inc +--echo # Check that attempt to acquire SW lock on t2 causes waiting. +--echo # Sending: +--send insert into t2 values (1); +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above INSERT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "insert into t2 values (1)"; +--source include/wait_condition.inc +--echo # Unblock ALTER TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap ALTER TABLE. +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap INSERT. +--reap +commit; +--echo # +--echo # Test for the case in which we are acquiring lock on the table +--echo # which is already used in transaction. +--echo # +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Create SNW lock pending upgrade to X. +--echo # Sending: +--send alter table t1 add column c2 int; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until ALTER TABLE starts waiting X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "alter table t1 add column c2 int"; +--source include/wait_condition.inc +--echo # Check that transaction is still able to acquire SR lock. +select count(*) from t1; +--echo # Waiting trying to acquire SW lock will cause deadlock and +--echo # therefore should cause an error. +--error ER_LOCK_DEADLOCK +delete from t1 limit 1; +--echo # Unblock ALTER TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap ALTER TABLE. +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # 3) Check how various locks used within transactional context +--echo # interact with active/pending SNRW lock. +--echo # +--echo # Once again we start with case when we are acquiring lock on +--echo # the table which was not used in the transaction before. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +lock table t2 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Attempt to acquire SR should be blocked. It should +--echo # not cause errors as it does not creates deadlock. +--echo # Sending: +--send select count(*) from t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that the above SELECT is blocked +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "select count(*) from t2"; +--source include/wait_condition.inc +--echo # Unblock SELECT. +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap SELECT. +--reap +commit; +--echo # Repeat the same test for SW lock. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +lock table t2 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Again attempt to acquire SW should be blocked and should +--echo # not cause any errors. +--echo # Sending: +--send delete from t2 limit 1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Check that the above DELETE is blocked +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "delete from t2 limit 1"; +--source include/wait_condition.inc +--echo # Unblock DELETE. +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap DELETE. +--reap +commit; +--echo # +--echo # Now coverage for the case in which we are acquiring lock on +--echo # the table which is already used in transaction and against +--echo # which there is a pending SNRW lock request. +--echo # +--echo # *) Let us start with case when transaction has only a SR lock. +--echo # +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until LOCK TABLE is blocked creating pending request for X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that another instance of SR lock is granted without waiting. +select count(*) from t1; +--echo # Attempt to wait for SW lock will lead to deadlock, thus +--echo # the below statement should end with ER_LOCK_DEADLOCK error. +--error ER_LOCK_DEADLOCK +delete from t1 limit 1; +--echo # Unblock LOCK TABLES. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap LOCK TABLES. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # **) Now case when transaction has a SW lock. +--echo # +begin; +delete from t1 limit 1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Sending: +--send lock table t1 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until LOCK TABLE is blocked creating pending request for X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "lock table t1 write"; +--source include/wait_condition.inc +--echo # Check that both SR and SW locks are granted without waiting +--echo # and errors. +select count(*) from t1; +insert into t1 values (1, 1); +--echo # Unblock LOCK TABLES. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap LOCK TABLES. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # 4) Check how various locks used within transactional context +--echo # interact with active/pending X lock. +--echo # +--echo # As usual we start with case when we are acquiring lock on +--echo # the table which was not used in the transaction before. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Start transaction which will prevent X lock from going away +--echo # immediately. +begin; +select count(*) from t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Create pending X lock on t2. +--echo # Sending: +--send rename table t2 to t3; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until RENAME TABLE starts waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t2 to t3"; +--source include/wait_condition.inc +--echo # Check that attempt to acquire SR lock on t2 causes waiting. +--echo # Sending: +--send select count(*) from t2; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above SELECT is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "select count(*) from t2"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap RENAME TABLE. +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap SELECT. +--error ER_NO_SUCH_TABLE +--reap +commit; +rename table t3 to t2; +--echo # The same test for SW lock. +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Start transaction which will prevent X lock from going away +--echo # immediately. +begin; +select count(*) from t2; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Create pending X lock on t2. +--echo # Sending: +--send rename table t2 to t3; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until RENAME TABLE starts waiting with pending X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t2 to t3"; +--source include/wait_condition.inc +--echo # Check that attempt to acquire SW lock on t2 causes waiting. +--echo # Sending: +--send delete from t2 limit 1; +--echo # +--echo # Switching to connection 'mdl_con2'. +connection mdl_con2; +--echo # Check that the above DELETE is blocked. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "delete from t2 limit 1"; +--source include/wait_condition.inc +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap RENAME TABLE. +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap DELETE. +--error ER_NO_SUCH_TABLE +--reap +commit; +rename table t3 to t2; +--echo # +--echo # Coverage for the case in which we are acquiring lock on +--echo # the table which is already used in transaction and against +--echo # which there is a pending X lock request. +--echo # +--echo # *) The first case is when transaction has only a SR lock. +--echo # +begin; +select count(*) from t1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until RENAME TABLE is blocked creating pending request for X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that another instance of SR lock is granted without waiting. +select count(*) from t1; +--echo # Attempt to wait for SW lock will lead to deadlock, thus +--echo # the below statement should end with ER_LOCK_DEADLOCK error. +--error ER_LOCK_DEADLOCK +delete from t1 limit 1; +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # +--echo # **) The second case is when transaction has a SW lock. +--echo # +begin; +delete from t1 limit 1; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Sending: +--send rename table t1 to t2; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait until RENAME TABLE is blocked creating pending request for X lock. +let $wait_condition= +select count(*) = 1 from information_schema.processlist +where state = "Waiting for table" and info = "rename table t1 to t2"; +--source include/wait_condition.inc +--echo # Check that both SR and SW locks are granted without waiting +--echo # and errors. +select count(*) from t1; +insert into t1 values (1, 1); +--echo # Unblock RENAME TABLE. +commit; +--echo # +--echo # Switching to connection 'mdl_con1'. +connection mdl_con1; +--echo # Reap RENAME TABLE. +--error ER_TABLE_EXISTS_ERROR +--reap +--echo # +--echo # Switching to connection 'default'. +connection default; + +--echo # Clean-up. +disconnect mdl_con1; +disconnect mdl_con2; +disconnect mdl_con3; +set debug_sync= 'RESET'; +drop table t1, t2; + + +--echo # +--echo # Additional coverage for some scenarios in which not quite +--echo # correct use of S metadata locks by HANDLER statement might +--echo # have caused deadlocks. +--echo # +--disable_warnings +drop table if exists t1, t2; +--enable_warnings +connect(handler_con1,localhost,root,,); +connect(handler_con2,localhost,root,,); +connection default; +create table t1 (i int); +create table t2 (j int); +insert into t1 values (1); + +--echo # +--echo # First, check scenario in which we upgrade SNRW lock to X lock +--echo # on a table while having HANDLER READ trying to acquire TL_READ +--echo # on the same table. +--echo # +handler t1 open; +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +lock table t1 write; +--echo # Upgrade SNRW to X lock. +--echo # Sending: +--send alter table t1 add column j int; +--echo # +--echo # Switching to connection 'handler_con2'. +connection handler_con2; +--echo # Wait until ALTER is blocked during upgrade. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 add column j int"; +--source include/wait_condition.inc +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # The below statement should not cause deadlock. +--send handler t1 read first; +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +--echo # Reap ALTER TABLE. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap HANDLER READ. +--reap +handler t1 close; + +--echo # +--echo # Now, check scenario in which upgrade of SNRW lock to X lock +--echo # can be blocked by HANDLER which is open in connection currently +--echo # waiting to get table-lock owned by connection doing upgrade. +--echo # +handler t1 open; +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +lock table t1 write, t2 read; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Execute statement which will be blocked on table-level lock +--echo # owned by connection 'handler_con1'. +--echo # Sending: +--send insert into t2 values (1); +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +--echo # Wait until INSERT is blocked on table-level lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Table lock" and info = "insert into t2 values (1)"; +--source include/wait_condition.inc +--echo # The below statement should not cause deadlock. +alter table t1 drop column j; +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Reap INSERT. +--reap +handler t1 close; + +--echo # +--echo # Then, check the scenario in which upgrade of SNRW lock to X +--echo # lock is blocked by HANDLER which is open in connection currently +--echo # waiting to get SW lock on the same table. +--echo # +handler t1 open; +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +lock table t1 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # The below insert should be blocked because active SNRW lock on 't1'. +--echo # Sending: +--send insert into t1 values (1); +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +--echo # Wait until INSERT is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "insert into t1 values (1)"; +--echo # The below ALTER TABLE will be blocked because of presence of HANDLER. +--echo # Sending: +--send alter table t1 add column j int; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # INSERT should be chosen as victim for resolving deadlock. +--echo # Reaping INSERT. +--error ER_LOCK_DEADLOCK +--reap +--echo # Close HANDLER to unblock ALTER TABLE. +handler t1 close; +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +--echo # Reaping ALTER TABLE. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; + +--echo # +--echo # Finally, test in which upgrade of SNRW lock to X lock is blocked +--echo # by HANDLER which is open in connection currently waiting to get +--echo # SR lock on the table on which lock is upgraded. +--echo # +handler t1 open; +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +lock table t1 write, t2 write; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # The below insert should be blocked because active SNRW lock on 't1'. +--echo # Sending: +--send insert into t2 values (1); +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +--echo # Wait until INSERT is blocked because of SNRW lock. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "insert into t2 values (1)"; +--echo # The below ALTER TABLE will be blocked because of presence of HANDLER. +--echo # Sending: +--send alter table t1 drop column j; +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # INSERT should be chosen as victim for resolving deadlock. +--echo # Reaping INSERT. +--error ER_LOCK_DEADLOCK +--reap +--echo # Close HANDLER to unblock ALTER TABLE. +handler t1 close; +--echo # +--echo # Switching to connection 'handler_con1'. +connection handler_con1; +--echo # Reaping ALTER TABLE. +--reap +unlock tables; +--echo # +--echo # Switching to connection 'default'. +connection default; + +--echo # Clean-up. +disconnect handler_con1; +disconnect handler_con2; +drop tables t1, t2; + + +--echo # --echo # Test coverage for basic deadlock detection in metadata --echo # locking subsystem. --echo # @@ -236,47 +2543,47 @@ connection default; --echo # Switching to connection 'deadlock_con1'. connection deadlock_con1; begin; -insert into t1 values (1); - ---echo # ---echo # Switching to connection 'deadlock_con2'. -connection deadlock_con2; -begin; -insert into t3 values (1); +insert into t2 values (1); --echo # --echo # Switching to connection 'default'. connection default; ---echo # Send: ---send rename table t2 to t0, t3 to t2, t0 to t3; +lock table t1 write; --echo # --echo # Switching to connection 'deadlock_con1'. connection deadlock_con1; ---echo # Wait until the above RENAME TABLE is blocked because it has to wait ---echo # for 'deadlock_con2' which holds shared metadata lock on 't3'. -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "rename table t2 to t0, t3 to t2, t0 to t3"; ---source include/wait_condition.inc --echo # The below SELECT statement should wait for metadata lock ---echo # on table 't2' and should not produce ER_LOCK_DEADLOCK +--echo # on table 't1' and should not produce ER_LOCK_DEADLOCK --echo # immediately as no deadlock is possible at the moment. ---send select * from t2; +--send select * from t1; --echo # ---echo # Switching to connection 'deadlock_con3'. -connection deadlock_con3; ---echo # Wait until the above SELECT * FROM t2 is starts waiting ---echo # for an exclusive metadata lock to go away. +--echo # Switching to connection 'deadlock_con2'. +connection deadlock_con2; +--echo # Wait until the above SELECT * FROM t1 is starts waiting +--echo # for an UNRW metadata lock to go away. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "select * from t2"; + where state = "Waiting for table" and info = "select * from t1"; --source include/wait_condition.inc --echo # Send RENAME TABLE statement that will deadlock with the --echo # SELECT statement and thus should abort the latter. ---send rename table t1 to t5, t2 to t1, t5 to t2; +--send rename table t1 to t0, t2 to t1, t0 to t2; + +--echo # +--echo # Switching to connection 'default'. +connection default; +--echo # Wait till above RENAME TABLE is blocked while holding +--echo # pending X lock on t1. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "rename table t1 to t0, t2 to t1, t0 to t2"; +--source include/wait_condition.inc +--echo # Allow the above RENAME TABLE to acquire lock on t1 and +--echo # create pending lock on t2 thus creating deadlock. +unlock tables; --echo # --echo # Switching to connection 'deadlock_con1'. @@ -284,17 +2591,17 @@ connection deadlock_con1; --echo # Since the latest RENAME TABLE entered in deadlock with SELECT --echo # statement the latter should be aborted and emit ER_LOCK_DEADLOCK --echo # error. ---echo # Reap SELECT * FROM t2. +--echo # Reap SELECT * FROM t1. --error ER_LOCK_DEADLOCK --reap --echo # --echo # Again let us check that failure of the SELECT statement has not ---echo # released metadata lock on table 't1', i.e. that the latest RENAME +--echo # released metadata lock on table 't2', i.e. that the latest RENAME --echo # is blocked. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "rename table t1 to t5, t2 to t1, t5 to t2"; + where state = "Waiting for table" and info = "rename table t1 to t0, t2 to t1, t0 to t2"; --source include/wait_condition.inc --echo # Commit transaction to unblock this RENAME TABLE. commit; @@ -302,19 +2609,7 @@ commit; --echo # --echo # Switching to connection 'deadlock_con2'. connection deadlock_con2; ---echo # Commit transaction to unblock the first RENAME TABLE. -commit; - ---echo # ---echo # Switching to connection 'default'. -connection default; ---echo # Reap RENAME TABLE t2 TO t0 ... . ---reap - ---echo # ---echo # Switching to connection 'deadlock_con3'. -connection deadlock_con3; ---echo # Reap RENAME TABLE t1 TO t5 ... . +--echo # Reap RENAME TABLE ... . --reap; --echo # @@ -328,12 +2623,17 @@ drop tables t1, t2, t3, t4; --echo # also takes into account requests for metadata lock upgrade. --echo # create table t1 (i int); +insert into t1 values (1); +--echo # Avoid race which occurs when SELECT in 'deadlock_con1' connection +--echo # accesses table before the above INSERT unlocks the table and thus +--echo # its result becomes visible to other connections. +select * from t1; --echo # --echo # Switching to connection 'deadlock_con1'. connection deadlock_con1; begin; -insert into t1 values (1); +select * from t1; --echo # --echo # Switching to connection 'default'. @@ -376,62 +2676,6 @@ connection default; drop table t2; ---echo # ---echo # Finally, test case in which deadlock (or potentially livelock) occurs ---echo # between metadata locking subsystem and table definition cache/table ---echo # locks, but which should still be detected by our empiric. ---echo # -create table t1 (i int); - ---echo # ---echo # Switching to connection 'deadlock_con1'. -connection deadlock_con1; -begin; -insert into t1 values (1); - ---echo # ---echo # Switching to connection 'default'. -connection default; -lock tables t1 write; - ---echo # ---echo # Switching to connection 'deadlock_con1'. -connection deadlock_con1; ---echo # Send: ---send insert into t1 values (2); - ---echo # ---echo # Switching to connection 'default'. -connection default; ---echo # Wait until INSERT in connection 'deadlock_con1' is blocked on ---echo # table-level lock. -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "insert into t1 values (2)"; ---source include/wait_condition.inc - ---echo # Send: ---send alter table t1 add column j int; - ---echo # ---echo # Switching to connection 'deadlock_con1'. -connection deadlock_con1; ---echo # The above ALTER TABLE statement should cause INSERT statement in ---echo # this connection to be aborted and emit ER_LOCK_DEADLOCK error. ---echo # Reap INSERT ---error ER_LOCK_DEADLOCK ---reap ---echo # Commit transaction to unblock ALTER TABLE. -commit; - ---echo # ---echo # Switching to connection 'default'. -connection default; ---echo # Reap ALTER TABLE. ---reap -unlock tables; - -drop table t1; disconnect deadlock_con1; disconnect deadlock_con2; disconnect deadlock_con3; @@ -535,7 +2779,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR locked'; --echo # --echo # Continue the INSERT once CREATE waits for exclusive lock -SET DEBUG_SYNC= 'mdl_acquire_exclusive_locks_wait SIGNAL finish'; +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; --echo # Try to create that table. --send CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1)) @@ -575,7 +2819,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR locked'; --echo # --echo # Continue the INSERT once CREATE waits for exclusive lock -SET DEBUG_SYNC= 'mdl_acquire_exclusive_locks_wait SIGNAL finish'; +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; --echo # Try to create that table. --send CREATE TABLE t1 LIKE t2 @@ -615,19 +2859,19 @@ create table t1 (i int); --echo # Let us check that we won't deadlock if during filling --echo # of I_S table we encounter conflicting metadata lock --echo # which owner is in its turn waiting for our connection. -lock tables t1 write; +lock tables t1 read; --echo # Switching to connection 'con46044'. connection con46044; --echo # Sending: ---send create table t2 select * from t1; +--send create table t2 select * from t1 for update; --echo # Switching to connection 'default'. connection default; --echo # Waiting until CREATE TABLE ... SELECT ... is blocked. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "create table t2 select * from t1"; + where state = "Table lock" and info = "create table t2 select * from t1 for update"; --source include/wait_condition.inc --echo # First let us check that SHOW FIELDS/DESCRIBE doesn't @@ -668,19 +2912,19 @@ drop table t2; --echo # Switching to connection 'con46044_2'. connection con46044_2; -lock tables t1 write; +lock tables t1 read; --echo # Switching to connection 'con46044'. connection con46044; --echo # Sending: ---send create table t2 select * from t1; +--send create table t2 select * from t1 for update; --echo # Switching to connection 'default'. connection default; --echo # Waiting until CREATE TABLE ... SELECT ... is blocked. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "create table t2 select * from t1"; + where state = "Table lock" and info = "create table t2 select * from t1 for update"; --source include/wait_condition.inc --echo # Let us check that SHOW FIELDS/DESCRIBE gets blocked. @@ -710,19 +2954,19 @@ drop table t2; --echo # Switching to connection 'con46044_2'. connection con46044_2; -lock tables t1 write; +lock tables t1 read; --echo # Switching to connection 'con46044'. connection con46044; --echo # Sending: ---send create table t2 select * from t1; +--send create table t2 select * from t1 for update; --echo # Switching to connection 'default'. connection default; --echo # Waiting until CREATE TABLE ... SELECT ... is blocked. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "create table t2 select * from t1"; + where state = "Table lock" and info = "create table t2 select * from t1 for update"; --source include/wait_condition.inc --echo # Check that I_S query which reads only .FRMs gets blocked. @@ -753,19 +2997,19 @@ drop table t2; --echo # Switching to connection 'con46044_2'. connection con46044_2; -lock tables t1 write; +lock tables t1 read; --echo # Switching to connection 'con46044'. connection con46044; --echo # Sending: ---send create table t2 select * from t1; +--send create table t2 select * from t1 for update; --echo # Switching to connection 'default'. connection default; --echo # Waiting until CREATE TABLE ... SELECT ... is blocked. let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Table lock" and info = "create table t2 select * from t1"; + where state = "Table lock" and info = "create table t2 select * from t1 for update"; --source include/wait_condition.inc --echo # Finally, check that I_S query which does full-blown table open @@ -817,7 +3061,7 @@ create table t1 (c1 int primary key, c2 int, c3 int); insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); begin; -update t1 set c3=c3+1 where c2=3; +select * from t1 where c2 = 3; --echo # --echo # Switching to connection 'con46273'. @@ -829,12 +3073,12 @@ set debug_sync='after_lock_tables_takes_lock SIGNAL alter_table_locked WAIT_FOR --echo # Switching to connection 'default'. connection default; set debug_sync='now WAIT_FOR alter_table_locked'; -set debug_sync='wait_for_lock SIGNAL alter_go'; +set debug_sync='before_open_table_wait_refresh SIGNAL alter_go'; --echo # The below statement should get ER_LOCK_DEADLOCK error --echo # (i.e. it should not allow ALTER to proceed, and then --echo # fail due to 't1' changing its name to 't2'). --error ER_LOCK_DEADLOCK -update t1 set c3=c3+1 where c2=4; +update t1 set c3=c3+1 where c2 = 3; --echo # --echo # Let us check that failure of the above statement has not released diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 6f8cc94e6b7..68b44a33428 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -474,7 +474,8 @@ drop table t1,t2; # # Test alter table and a concurrent multi update -# (This will force update to reopen tables) +# (Before we have introduced data-lock-aware metadata locks +# this test case forced update to reopen tables). # create table t1 (a int, b int); @@ -494,9 +495,9 @@ send alter table t1 add column c int default 100 after a; connect (updater,localhost,root,,test); connection updater; # Wait till "alter table t1 ..." of session changer is in work. -# = There is one session is in state "Locked". +# = There is one session waiting. let $wait_condition= select count(*)= 1 from information_schema.processlist - where state= 'Table lock'; + where state= 'Waiting for table'; --source include/wait_condition.inc send update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a; @@ -505,9 +506,9 @@ connection locker; # - "alter table t1 ..." of session changer and # - "update t1, v1 ..." of session updater # are in work. -# = There are two session is in state "Locked". +# = There are two session waiting. let $wait_condition= select count(*)= 2 from information_schema.processlist - where state= 'Table lock'; + where state= 'Waiting for table'; --source include/wait_condition.inc unlock tables; diff --git a/mysql-test/t/truncate_coverage.test b/mysql-test/t/truncate_coverage.test index 9870fbb5ebf..b7c08b03c8b 100644 --- a/mysql-test/t/truncate_coverage.test +++ b/mysql-test/t/truncate_coverage.test @@ -23,14 +23,14 @@ DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); # -# Start a transaction and execute a DML in it. Since 5.4.4 this leaves -# a shared meta data lock (MDL) behind. TRUNCATE shall block on it. +# Acquire a shared metadata lock on table by opening HANDLER for it and wait. +# TRUNCATE shall block on this metadata lock. +# We can't use normal DML as such statements would also block LOCK TABLES. # --echo # --echo # connection con1 --connect (con1, localhost, root,,) -START TRANSACTION; -INSERT INTO t1 VALUES (2); +HANDLER t1 OPEN; # # Get connection id of default connection. # Lock the table and start TRUNCATE, which will block on MDL upgrade. @@ -48,12 +48,17 @@ send TRUNCATE TABLE t1; # from wait_while_table_is_used(). # --echo # ---echo # connection con1 ---connection con1 +--echo # connection con2 +--connect (con2, localhost, root,,) SET DEBUG_SYNC='now WAIT_FOR waiting'; let $invisible_assignment_in_select = `SELECT @id := $ID`; KILL QUERY @id; -COMMIT; +--disconnect con2 +--echo # +--echo # connection con1 +--connection con1 +--echo # Release shared metadata lock by closing HANDLER. +HANDLER t1 CLOSE; --disconnect con1 --echo # --echo # connection default @@ -69,14 +74,14 @@ SET DEBUG_SYNC='RESET'; CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); # -# Start a transaction and execute a DML in it. Since 5.4.4 this leaves -# a shared meta data lock (MDL) behind. TRUNCATE shall block on it. +# Acquire a shared metadata lock on table by opening HANDLER for it and wait. +# TRUNCATE shall block on this metadata lock. +# We can't use normal DML as such statements would also block LOCK TABLES. # --echo # --echo # connection con1 --connect (con1, localhost, root,,) -START TRANSACTION; -INSERT INTO t1 VALUES (2); +HANDLER t1 OPEN; # # Lock the table and start TRUNCATE, which will block on MDL upgrade. # @@ -91,11 +96,15 @@ send TRUNCATE TABLE t1; # Commit to let TRUNCATE continue. # --echo # ---echo # connection con1 ---connection con1 +--echo # connection con2 +--connect (con2, localhost, root,,) SET DEBUG_SYNC='now WAIT_FOR waiting'; --remove_file $MYSQLD_DATADIR/test/t1.frm -COMMIT; +--disconnect con2 +--echo # +--echo # connection con1 +--connection con1 +HANDLER t1 CLOSE; --disconnect con1 --echo # --echo # connection default @@ -129,7 +138,7 @@ INSERT INTO t1 VALUES (2); --echo # connection default --connection default let $ID= `SELECT @id := CONNECTION_ID()`; -SET DEBUG_SYNC='mdl_acquire_exclusive_locks_wait SIGNAL waiting'; +SET DEBUG_SYNC='mdl_acquire_lock_wait SIGNAL waiting'; send TRUNCATE TABLE t1; # # Get the default connection ID into a variable in an invisible statement. |