diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/debug_sync.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/handler_innodb.result | 154 | ||||
| -rw-r--r-- | mysql-test/r/handler_myisam.result | 154 | ||||
| -rw-r--r-- | mysql-test/r/innodb-lock.result | 39 | ||||
| -rw-r--r-- | mysql-test/r/innodb_mysql_lock.result | 32 | ||||
| -rw-r--r-- | mysql-test/r/lock.result | 79 | ||||
| -rw-r--r-- | mysql-test/r/lock_multi.result | 67 | ||||
| -rw-r--r-- | mysql-test/r/lock_sync.result | 7 | ||||
| -rw-r--r-- | mysql-test/r/mdl_sync.result | 1854 | ||||
| -rw-r--r-- | mysql-test/r/sp-threads.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/truncate_coverage.result | 21 |
11 files changed, 2277 insertions, 134 deletions
diff --git a/mysql-test/r/debug_sync.result b/mysql-test/r/debug_sync.result index 8b46334204c..25fdf523200 100644 --- a/mysql-test/r/debug_sync.result +++ b/mysql-test/r/debug_sync.result @@ -263,7 +263,7 @@ DROP TABLE t1; SET DEBUG_SYNC= 'RESET'; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 INT); -LOCK TABLE t1 WRITE; +LOCK TABLE t1 READ; connection con1 SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2'; INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/handler_innodb.result b/mysql-test/r/handler_innodb.result index a3e3e325e7d..6ed7b572403 100644 --- a/mysql-test/r/handler_innodb.result +++ b/mysql-test/r/handler_innodb.result @@ -745,11 +745,29 @@ drop table t1; handler t1 read a next; ERROR 42S02: Unknown table 't1' in HANDLER drop table if exists t1; -create table t1 (a int, key a (a)); +# First test case which is supposed trigger the execution +# path on which problem was discovered. +create table t1 (a int); insert into t1 values (1); handler t1 open; +lock table t1 write; alter table t1 engine=memory; handler t1 read a next; +ERROR HY000: Table storage engine for 't1' doesn't have this option +handler t1 close; +unlock tables; +drop table t1; +# Now test case which was reported originally but which no longer +# triggers execution path which has caused the problem. +create table t1 (a int, key(a)); +insert into t1 values (1); +handler t1 open; +alter table t1 engine=memory; +# Since S metadata lock was already acquired at HANDLER OPEN time +# and TL_READ lock requested by HANDLER READ is compatible with +# ALTER's TL_WRITE_ALLOW_READ the below statement should succeed +# without waiting. The old version of table should be used in it. +handler t1 read a next; a 1 handler t1 close; @@ -1217,14 +1235,19 @@ create table t1 (a int, key a(a)); create table t2 like t1; handler t1 open; # --> connection con1 -lock table t2 read; +lock table t1 write, t2 write; # --> connection default drop table t2; +# --> connection con2 +# Waiting for 'drop table t2' to get blocked... +# --> connection con1 +drop table t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -rename table t2 to t3; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +unlock tables; +# --> connection default # Demonstrate that there is no deadlock with FLUSH TABLE, # even though it is waiting for the other table to go away +create table t2 like t1; # Sending: flush table t2; # --> connection con2 @@ -1239,29 +1262,43 @@ drop table t2; # lead to deadlocks # create table t1 (a int, key a(a)); +insert into t1 values (1), (2); # --> connection default begin; select * from t1; a +1 +2 handler t1 open; # --> connection con1 +# Sending: lock tables t1 write; +# --> connection con2 +# Check that 'lock tables t1 write' waits until transaction which +# has read from the table commits. +# --> connection default +# The below 'handler t1 read ...' should not be blocked as +# 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; +a +1 +# Unblock 'lock tables t1 write'. +commit; +# --> connection con1 +# Reap 'lock tables t1 write'. # --> connection default # Sending: handler t1 read a next; # --> connection con1 # Waiting for 'handler t1 read a next' to get blocked... -# Sending: +# The below 'drop table t1' should be able to proceed without +# waiting as it will force HANDLER to be closed. drop table t1; -# --> connection con2 -# Waiting for 'drop table t1' to get blocked... +unlock tables; # --> connection default # Reaping 'handler t1 read a next'... -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +ERROR 42S02: Table 'test.t1' doesn't exist handler t1 close; -commit; -# --> connection con1 -# Reaping 'drop table t1'... # --> connection con1 # --> connection con2 # --> connection con3 @@ -1324,3 +1361,98 @@ a b handler t2 read first; a b drop table t1, t2, t3, t4; +# +# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +# +set autocommit=0; +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +create table t2 like t1; +insert into t2 (a, b) select a, b from t1; +create table t3 like t1; +insert into t3 (a, b) select a, b from t1; +commit; +flush tables with read lock; +handler t1 open; +lock table t1 read; +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# This implicitly leaves LOCK TABLES but doesn't drop the GLR +lock table not_exists_write read; +ERROR 42S02: Table 'test.not_exists_write' doesn't exist +# We still have the read lock. +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +handler t1 open; +select a from t2; +a +1 +2 +3 +4 +5 +handler t1 read next; +a b +1 1 +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +a b +1 1 +select a from t3; +a +1 +2 +3 +4 +5 +handler t2 read next; +a b +1 1 +handler t1 close; +rollback; +handler t2 close; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +commit; +flush tables; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; +# +# HANDLER statement and operation-type aware metadata locks. +# Check that when we clone a ticket for HANDLER we downrade +# the lock. +# +# Establish an auxiliary connection con1. +# -> connection default +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +begin; +insert into t1 (a, b) values (6, 6); +handler t1 open; +handler t1 read a last; +a b +6 6 +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +a b +7 7 +commit; +# -> connection con1 +# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. +lock table t1 write; +unlock tables; +# -> connection default +handler t1 read a prev; +a b +6 6 +handler t1 close; +# Cleanup. +drop table t1; +# -> connection con1 +# -> connection default diff --git a/mysql-test/r/handler_myisam.result b/mysql-test/r/handler_myisam.result index f5c5bfebd15..0dd039eeb23 100644 --- a/mysql-test/r/handler_myisam.result +++ b/mysql-test/r/handler_myisam.result @@ -743,11 +743,29 @@ drop table t1; handler t1 read a next; ERROR 42S02: Unknown table 't1' in HANDLER drop table if exists t1; -create table t1 (a int, key a (a)); +# First test case which is supposed trigger the execution +# path on which problem was discovered. +create table t1 (a int); insert into t1 values (1); handler t1 open; +lock table t1 write; alter table t1 engine=memory; handler t1 read a next; +ERROR HY000: Table storage engine for 't1' doesn't have this option +handler t1 close; +unlock tables; +drop table t1; +# Now test case which was reported originally but which no longer +# triggers execution path which has caused the problem. +create table t1 (a int, key(a)); +insert into t1 values (1); +handler t1 open; +alter table t1 engine=memory; +# Since S metadata lock was already acquired at HANDLER OPEN time +# and TL_READ lock requested by HANDLER READ is compatible with +# ALTER's TL_WRITE_ALLOW_READ the below statement should succeed +# without waiting. The old version of table should be used in it. +handler t1 read a next; a 1 handler t1 close; @@ -1214,14 +1232,19 @@ create table t1 (a int, key a(a)); create table t2 like t1; handler t1 open; # --> connection con1 -lock table t2 read; +lock table t1 write, t2 write; # --> connection default drop table t2; +# --> connection con2 +# Waiting for 'drop table t2' to get blocked... +# --> connection con1 +drop table t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -rename table t2 to t3; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +unlock tables; +# --> connection default # Demonstrate that there is no deadlock with FLUSH TABLE, # even though it is waiting for the other table to go away +create table t2 like t1; # Sending: flush table t2; # --> connection con2 @@ -1236,29 +1259,43 @@ drop table t2; # lead to deadlocks # create table t1 (a int, key a(a)); +insert into t1 values (1), (2); # --> connection default begin; select * from t1; a +1 +2 handler t1 open; # --> connection con1 +# Sending: lock tables t1 write; +# --> connection con2 +# Check that 'lock tables t1 write' waits until transaction which +# has read from the table commits. +# --> connection default +# The below 'handler t1 read ...' should not be blocked as +# 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; +a +1 +# Unblock 'lock tables t1 write'. +commit; +# --> connection con1 +# Reap 'lock tables t1 write'. # --> connection default # Sending: handler t1 read a next; # --> connection con1 # Waiting for 'handler t1 read a next' to get blocked... -# Sending: +# The below 'drop table t1' should be able to proceed without +# waiting as it will force HANDLER to be closed. drop table t1; -# --> connection con2 -# Waiting for 'drop table t1' to get blocked... +unlock tables; # --> connection default # Reaping 'handler t1 read a next'... -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +ERROR 42S02: Table 'test.t1' doesn't exist handler t1 close; -commit; -# --> connection con1 -# Reaping 'drop table t1'... # --> connection con1 # --> connection con2 # --> connection con3 @@ -1322,6 +1359,101 @@ handler t2 read first; a b drop table t1, t2, t3, t4; # +# A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +# +set autocommit=0; +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +create table t2 like t1; +insert into t2 (a, b) select a, b from t1; +create table t3 like t1; +insert into t3 (a, b) select a, b from t1; +commit; +flush tables with read lock; +handler t1 open; +lock table t1 read; +handler t1 read next; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# This implicitly leaves LOCK TABLES but doesn't drop the GLR +lock table not_exists_write read; +ERROR 42S02: Table 'test.not_exists_write' doesn't exist +# We still have the read lock. +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +handler t1 open; +select a from t2; +a +1 +2 +3 +4 +5 +handler t1 read next; +a b +1 1 +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +a b +1 1 +select a from t3; +a +1 +2 +3 +4 +5 +handler t2 read next; +a b +1 1 +handler t1 close; +rollback; +handler t2 close; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +commit; +flush tables; +drop table t1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; +# +# HANDLER statement and operation-type aware metadata locks. +# Check that when we clone a ticket for HANDLER we downrade +# the lock. +# +# Establish an auxiliary connection con1. +# -> connection default +create table t1 (a int, b int, key a (a)); +insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5); +begin; +insert into t1 (a, b) values (6, 6); +handler t1 open; +handler t1 read a last; +a b +6 6 +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +a b +7 7 +commit; +# -> connection con1 +# Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. +lock table t1 write; +unlock tables; +# -> connection default +handler t1 read a prev; +a b +6 6 +handler t1 close; +# Cleanup. +drop table t1; +# -> connection con1 +# -> connection default +# # BUG #46456: HANDLER OPEN + TRUNCATE + DROP (temporary) TABLE, crash # CREATE TABLE t1 AS SELECT 1 AS f1; diff --git a/mysql-test/r/innodb-lock.result b/mysql-test/r/innodb-lock.result index 4ace4065c34..ab7e9aa7b25 100644 --- a/mysql-test/r/innodb-lock.result +++ b/mysql-test/r/innodb-lock.result @@ -25,6 +25,12 @@ id x 0 2 commit; drop table t1; +# +# Old lock method (where LOCK TABLE was ignored by InnoDB) no longer +# works due to fix for bugs #46272 "MySQL 5.4.4, new MDL: unnecessary +# deadlock" and bug #37346 "innodb does not detect deadlock between +# update and alter table". +# set @@innodb_table_locks=0; create table t1 (id integer primary key, x integer) engine=INNODB; insert into t1 values(0, 0),(1,1),(2,2); @@ -32,26 +38,27 @@ commit; SELECT * from t1 where id = 0 FOR UPDATE; id x 0 0 +# Connection 'con2'. set autocommit=0; set @@innodb_table_locks=0; -lock table t1 write; -update t1 set x=10 where id = 2; -SELECT * from t1 where id = 2; -id x -2 2 -UPDATE t1 set x=3 where id = 2; -commit; -SELECT * from t1; +# The following statement should block because SQL-level lock +# is taken on t1 which will wait until concurrent transaction +# is commited. +# Sending: +lock table t1 write;; +# Connection 'con1'. +# Wait until LOCK TABLE is blocked on SQL-level lock. +# We should be able to do UPDATEs and SELECTs within transaction. +update t1 set x=1 where id = 0; +select * from t1; id x -0 0 +0 1 1 1 -2 3 +2 2 +# Unblock LOCK TABLE. commit; +# Connection 'con2'. +# Reap LOCK TABLE. unlock tables; -commit; -select * from t1; -id x -0 0 -1 1 -2 10 +# Connection 'con1'. drop table t1; diff --git a/mysql-test/r/innodb_mysql_lock.result b/mysql-test/r/innodb_mysql_lock.result index 374f67358eb..375ae8aeb12 100644 --- a/mysql-test/r/innodb_mysql_lock.result +++ b/mysql-test/r/innodb_mysql_lock.result @@ -26,6 +26,38 @@ commit; set @@autocommit=1; set @@autocommit=1; # +# Test for bug #37346 "innodb does not detect deadlock between update +# and alter table". +# +drop table if exists t1; +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; +# Run statement which acquires X-lock on one of table's rows. +update t1 set c3=c3+1 where c2=3; +# +# Switching to connection 'con37346'. +# The below ALTER TABLE statement should wait till transaction +# in connection 'default' is complete and then succeed. +# It should not deadlock or fail with ER_LOCK_DEADLOCK error. +# Sending: +alter table t1 add column c4 int;; +# +# Switching to connection 'default'. +# Wait until the above ALTER TABLE gets blocked because this +# connection holds SW metadata lock on table to be altered. +# The below statement should succeed. It should not +# deadlock or end with ER_LOCK_DEADLOCK error. +update t1 set c3=c3+1 where c2=4; +# Unblock ALTER TABLE by committing transaction. +commit; +# +# Switching to connection 'con37346'. +# Reaping ALTER TABLE. +# +# Switching to connection 'default'. +drop table t1; +# # Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB # table cause warnings in errlog # diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result index 5dbfa66b43e..c1e1ccb5bce 100644 --- a/mysql-test/r/lock.result +++ b/mysql-test/r/lock.result @@ -151,6 +151,12 @@ select * from t2; a select * from t3; ERROR HY000: Table 't3' was not locked with LOCK TABLES +Dropping of implicitly locked table is disallowed. +drop table t1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +unlock tables; +Now let us also lock table explicitly and drop it. +lock tables t1 write, v_bug5719 write; drop table t1; sic: left LOCK TABLES mode @@ -282,6 +288,79 @@ insert into t1 values (1); # Ensure that metadata locks held by the transaction are released. drop table t1; # +# Coverage for situations when we try to execute DDL on tables +# which are locked by LOCK TABLES only implicitly. +# +drop tables if exists t1, t2; +drop view if exists v1; +drop function if exists f1; +create table t1 (i int); +create table t2 (j int); +# +# Try to perform DDL on table which is locked through view. +create view v1 as select * from t2; +lock tables t1 write, v1 write; +flush table t2; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +drop table t2; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +alter table t2 add column k int; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +create trigger t2_bi before insert on t2 for each row set @a:=1; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +# Repair produces error as part of its result set. +repair table t2; +Table Op Msg_type Msg_text +test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated +test.t2 repair status Operation failed +unlock tables; +drop view v1; +# +# Now, try DDL on table which is locked through routine. +create function f1 () returns int +begin +insert into t2 values (1); +return 0; +end| +create view v1 as select f1() from t1; +lock tables v1 read; +flush table t2; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +drop table t2; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +alter table t2 add column k int; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +create trigger t2_bi before insert on t2 for each row set @a:=1; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +# Repair produces error as part of its result set. +repair table t2; +Table Op Msg_type Msg_text +test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated +test.t2 repair status Operation failed +unlock tables; +drop view v1; +drop function f1; +# +# 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; +flush table t2; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +drop table t2; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +alter table t2 add column k int; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +create trigger t2_bi before insert on t2 for each row set @a:=1; +ERROR HY000: Table 't2' was locked with a READ lock and can't be updated +# Repair produces error as part of its result set. +repair table t2; +Table Op Msg_type Msg_text +test.t2 repair Error Table 't2' was locked with a READ lock and can't be updated +test.t2 repair status Operation failed +unlock tables; +drop trigger t1_ai; +drop tables t1, t2; +# # Bug#45035 " Altering table under LOCK TABLES results in # "Error 1213 Deadlock found..." # diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 5d12e0efd64..4b08c175ee2 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -1,21 +1,39 @@ drop table if exists t1,t2; create table t1(n int); insert into t1 values (1); -lock tables t1 write; +select get_lock("mysqltest_lock", 100); +get_lock("mysqltest_lock", 100) +1 +update t1 set n = 2 and get_lock('mysqltest_lock', 100); update low_priority t1 set n = 4; select n from t1; -unlock tables; +select release_lock("mysqltest_lock"); +release_lock("mysqltest_lock") +1 +select release_lock("mysqltest_lock"); +release_lock("mysqltest_lock") +1 n 4 drop table t1; create table t1(n int); insert into t1 values (1); -lock tables t1 read; +select get_lock("mysqltest_lock", 100); +get_lock("mysqltest_lock", 100) +1 +select n from t1 where get_lock('mysqltest_lock', 100); update low_priority t1 set n = 4; select n from t1; n 1 -unlock tables; +select release_lock("mysqltest_lock"); +release_lock("mysqltest_lock") +1 +n +1 +select release_lock("mysqltest_lock"); +release_lock("mysqltest_lock") +1 drop table t1; create table t1 (a int, b int); create table t2 (c int, d int); @@ -35,6 +53,7 @@ create table t2 (a int); lock table t1 write, t2 write; insert t1 select * from t2; drop table t2; +unlock tables; ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; create table t1 (a int); @@ -42,6 +61,7 @@ create table t2 (a int); lock table t1 write, t2 write, t1 as t1_2 write, t2 as t2_2 write; insert t1 select * from t2; drop table t2; +unlock tables; ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; End of 4.1 tests @@ -221,6 +241,36 @@ connection: default flush tables; drop table t1; # +# Test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock". +# +drop table if exists t1; +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; +# +# Switching to connection 'con46272'. +# The below ALTER TABLE statement should wait till transaction +# in connection 'default' is complete and then succeed. +# It should not deadlock or fail with ER_LOCK_DEADLOCK error. +# Sending: +alter table t1 add column c4 int;; +# +# Switching to connection 'default'. +# Wait until the above ALTER TABLE gets blocked because this +# connection holds SW metadata lock on table to be altered. +# The below statement should succeed. It should not +# deadlock or end with ER_LOCK_DEADLOCK error. +update t1 set c3=c3+1 where c2=4; +# Unblock ALTER TABLE by committing transaction. +commit; +# +# Switching to connection 'con46272'. +# Reaping ALTER TABLE. +# +# Switching to connection 'default'. +drop table t1; +# # Bug#47249 assert in MDL_global_lock::is_lock_type_compatible # DROP TABLE IF EXISTS t1; @@ -228,14 +278,15 @@ DROP VIEW IF EXISTS v1; # # Test 1: LOCK TABLES v1 WRITE, t1 READ; # +# Thanks to the fact that we no longer allow DDL on tables +# which are locked for write implicitly, the exact scenario +# in which assert was failing is no longer repeatable. CREATE TABLE t1 ( f1 integer ); CREATE VIEW v1 AS SELECT f1 FROM t1 ; -# Connection 2 LOCK TABLES v1 WRITE, t1 READ; FLUSH TABLE t1; -# Connection 1 -LOCK TABLES t1 WRITE; -FLUSH TABLE t1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +UNLOCK TABLES; DROP TABLE t1; DROP VIEW v1; # diff --git a/mysql-test/r/lock_sync.result b/mysql-test/r/lock_sync.result index fc4e8c850f6..0b57b38f5ec 100644 --- a/mysql-test/r/lock_sync.result +++ b/mysql-test/r/lock_sync.result @@ -6,6 +6,7 @@ # statements which tried to acquire stronger write lock (TL_WRITE, # TL_WRITE_ALLOW_READ) on this table might have led to deadlock. drop table if exists t1; +drop view if exists v1; # Create auxiliary connections used through the test. # Reset DEBUG_SYNC facility before using it. set debug_sync= 'RESET'; @@ -14,6 +15,9 @@ set debug_sync= 'RESET'; set @old_general_log = @@global.general_log; set @@global.general_log= OFF; create table t1 (i int) engine=InnoDB; +# We have to use view in order to make LOCK TABLES avoid +# acquiring SNRW metadata lock on table. +create view v1 as select * from t1; insert into t1 values (1); # Prepare user lock which will be used for resuming execution of # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock. @@ -36,7 +40,7 @@ select count(*) > 0 from t1 as a, t1 as b for update;; # acquiring lock for the the first instance of 't1'. set debug_sync= 'now WAIT_FOR parked'; # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1': -lock table t1 write;; +lock table v1 write;; # Switch to connection 'default'. # Wait until this LOCK TABLES statement starts waiting for table lock. # Allow SELECT ... FOR UPDATE to resume. @@ -63,4 +67,5 @@ unlock tables; # Do clean-up. set debug_sync= 'RESET'; set @@global.general_log= @old_general_log; +drop view v1; drop table t1; diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result index 8c4d7272e29..8d8672377f0 100644 --- a/mysql-test/r/mdl_sync.result +++ b/mysql-test/r/mdl_sync.result @@ -10,7 +10,7 @@ alter table t1 rename t3; connection: default set debug_sync= 'now WAIT_FOR parked'; connection: con2 -set debug_sync='mdl_acquire_exclusive_locks_wait SIGNAL go'; +set debug_sync='mdl_acquire_lock_wait SIGNAL go'; drop table t1,t2; connection: con1 connection: default @@ -20,6 +20,1740 @@ ERROR 42S02: Unknown table 't1' drop table t3; SET DEBUG_SYNC= 'RESET'; # +# Basic test coverage for type-of-operation aware metadata locks. +# +drop table if exists t1, t2, t3; +set debug_sync= 'RESET'; +create table t1 (c1 int); +# +# A) First let us check compatibility rules between differend kinds of +# type-of-operation aware metadata locks. +# Of course, these rules are already covered by the tests scattered +# across the test suite. But it still makes sense to have one place +# which covers all of them. +# +# 1) Acquire S (simple shared) lock on the table (by using HANDLER): +# +handler t1 open; +# +# Switching to connection 'mdl_con1'. +# 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'; +column_name +c1 +select count(*) from t1; +count(*) +0 +insert into t1 values (1), (1); +# Check that SNW lock is compatible with it. To do this use ALTER TABLE +# which will fail after opening the table and thus obtaining SNW metadata +# lock. +alter table t1 add primary key (c1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# Check that SNRW lock is compatible with S lock. +lock table t1 write; +insert into t1 values (1); +unlock tables; +# Check that X lock is incompatible with S lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above RENAME is blocked because of S lock. +# +# Switching to connection 'default'. +# Unblock RENAME TABLE. +handler t1 close; +# +# Switching to connection 'mdl_con1'. +# Reaping RENAME TABLE. +# Restore the original state of the things. +rename table t2 to t1; +# +# Switching to connection 'default'. +handler t1 open; +# +# Switching to connection 'mdl_con1'. +# Check that upgrade from SNW to X is blocked by presence of S lock. +# Sending: +alter table t1 add column c2 int;; +# +# Switching to connection 'mdl_con2'. +# Check that the above ALTER TABLE is blocked because of S lock. +# +# Switching to connection 'default'. +# Unblock ALTER TABLE. +handler t1 close; +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER TABLE. +# Restore the original state of the things. +alter table t1 drop column c2; +# +# Switching to connection 'default'. +handler t1 open; +# +# Switching to connection 'mdl_con1'. +# Check that upgrade from SNRW to X is blocked by presence of S lock. +lock table t1 write; +# Sending: +alter table t1 add column c2 int;; +# +# Switching to connection 'mdl_con2'. +# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked +# because of S lock. +# +# Switching to connection 'default'. +# Unblock ALTER TABLE. +handler t1 close; +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER TABLE. +# Restore the original state of the things. +alter table t1 drop column c2; +unlock tables; +# +# Switching to connection 'default'. +# +# 2) Acquire SH (shared high-priority) lock on the table. +# We have to involve DEBUG_SYNC facility for this as usually +# such kind of locks are short-lived. +# +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +# Sending: +select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# 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'; +column_name +c1 +select count(*) from t1; +count(*) +3 +insert into t1 values (1); +# Check that SNW lock is compatible with it. To do this use ALTER TABLE +# which will fail after opening the table and thus obtaining SNW metadata +# lock. +alter table t1 add primary key (c1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# Check that SNRW lock is compatible with SH lock. +lock table t1 write; +delete from t1 limit 1; +unlock tables; +# Check that X lock is incompatible with SH lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above RENAME is blocked because of SH lock. +# Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping SELECT ... FROM I_S. +table_name table_type auto_increment table_comment +t1 BASE TABLE NULL +# +# Switching to connection 'mdl_con1'. +# Reaping RENAME TABLE. +# Restore the original state of the things. +rename table t2 to t1; +# +# Switching to connection 'default'. +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +# Sending: +select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# Check that upgrade from SNW to X is blocked by presence of SH lock. +# Sending: +alter table t1 add column c2 int;; +# +# Switching to connection 'mdl_con2'. +# Check that the above ALTER TABLE is blocked because of SH lock. +# Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping SELECT ... FROM I_S. +table_name table_type auto_increment table_comment +t1 BASE TABLE NULL +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER TABLE. +# Restore the original state of the things. +alter table t1 drop column c2; +# +# Switching to connection 'default'. +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# Check that upgrade from SNRW to X is blocked by presence of S lock. +lock table t1 write; +# Sending: +alter table t1 add column c2 int;; +# +# Switching to connection 'mdl_con2'. +# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked +# because of S lock. +# Unblock RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping SELECT ... FROM I_S. +table_name table_type auto_increment table_comment +t1 BASE TABLE NULL +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER TABLE. +# Restore the original state of the things. +alter table t1 drop column c2; +unlock tables; +# +# Switching to connection 'default'. +# +# +# 3) Acquire SR lock on the table. +# +# +begin; +select count(*) from t1; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# 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'; +column_name +c1 +select count(*) from t1; +count(*) +3 +insert into t1 values (1); +# Check that SNW lock is compatible with it. To do this use ALTER TABLE +# which will fail after opening the table and thus obtaining SNW metadata +# lock. +alter table t1 add primary key (c1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# Check that SNRW lock is not compatible with SR lock. +# Sending: +lock table t1 write;; +# +# Switching to connection 'default'. +# Check that the above LOCK TABLES is blocked because of SR lock. +# Unblock LOCK TABLES. +commit; +# +# Switching to connection 'mdl_con1'. +# Reaping LOCK TABLES. +delete from t1 limit 1; +unlock tables; +# +# Switching to connection 'default'. +begin; +select count(*) from t1; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# Check that X lock is incompatible with SR lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above RENAME is blocked because of SR lock. +# +# Switching to connection 'default'. +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reaping RENAME TABLE. +# Restore the original state of the things. +rename table t2 to t1; +# +# Switching to connection 'default'. +begin; +select count(*) from t1; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# Check that upgrade from SNW to X is blocked by presence of SR lock. +# Sending: +alter table t1 add column c2 int;; +# +# Switching to connection 'mdl_con2'. +# Check that the above ALTER TABLE is blocked because of SR lock. +# +# Switching to connection 'default'. +# Unblock ALTER TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER TABLE. +# Restore the original state of the things. +alter table t1 drop column c2; +# +# There is no need to check that upgrade from SNRW to X is blocked +# by presence of SR lock because SNRW is incompatible with SR anyway. +# +# +# Switching to connection 'default'. +# +# +# 4) Acquire SW lock on the table. +# +# +begin; +insert into t1 values (1); +# +# Switching to connection 'mdl_con1'. +# 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'; +column_name +c1 +select count(*) from t1; +count(*) +4 +insert into t1 values (1); +# Check that SNW lock is not compatible with SW lock. +# Again we use ALTER TABLE which fails after opening +# the table to avoid upgrade of SNW -> X. +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'default'. +# Check that the above ALTER TABLE is blocked because of SW lock. +# Unblock ALTER TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'default'. +begin; +insert into t1 values (1); +# +# Switching to connection 'mdl_con1'. +# Check that SNRW lock is not compatible with SW lock. +# Sending: +lock table t1 write;; +# +# Switching to connection 'default'. +# Check that the above LOCK TABLES is blocked because of SW lock. +# Unblock LOCK TABLES. +commit; +# +# Switching to connection 'mdl_con1'. +# Reaping LOCK TABLES. +delete from t1 limit 2; +unlock tables; +# +# Switching to connection 'default'. +begin; +insert into t1 values (1); +# +# Switching to connection 'mdl_con1'. +# Check that X lock is incompatible with SW lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above RENAME is blocked because of SW lock. +# +# Switching to connection 'default'. +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reaping RENAME TABLE. +# Restore the original state of the things. +rename table t2 to t1; +# +# There is no need to check that upgrade from SNW/SNRW to X is +# blocked by presence of SW lock because SNW/SNRW is incompatible +# with SW anyway. +# +# +# Switching to connection 'default'. +# +# +# 5) Acquire SNW lock on the table. We have to use DEBUG_SYNC for +# this, to prevent SNW from being immediately upgraded to X. +# +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# 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'; +column_name +c1 +select count(*) from t1; +count(*) +5 +# Check that SW lock is incompatible with SNW lock. +# Sending: +delete from t1 limit 2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above DELETE is blocked because of SNW lock. +# Unblock ALTER and thus DELETE. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con1'. +# Reaping DELETE. +# +# Switching to connection 'default'. +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# Check that SNW lock is incompatible with SNW lock. +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con2'. +# Check that the above ALTER is blocked because of SNW lock. +# Unblock ALTERs. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping first ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con1'. +# Reaping another ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'default'. +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# Check that SNRW lock is incompatible with SNW lock. +# Sending: +lock table t1 write;; +# +# Switching to connection 'mdl_con2'. +# Check that the above LOCK TABLES is blocked because of SNW lock. +# Unblock ALTER and thus LOCK TABLES. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con1'. +# Reaping LOCK TABLES +insert into t1 values (1); +unlock tables; +# +# Switching to connection 'default'. +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# Check that X lock is incompatible with SNW lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above RENAME is blocked because of SNW lock. +# Unblock ALTER and thus RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con1'. +# Reaping RENAME TABLE +# Revert back to original state of things. +rename table t2 to t1; +# +# There is no need to check that upgrade from SNW/SNRW to X is +# blocked by presence of another SNW lock because SNW/SNRW is +# incompatible with SNW anyway. +# +# Switching to connection 'default'. +# +# +# 6) Acquire SNRW lock on the table. +# +# +lock table t1 write; +# +# Switching to connection 'mdl_con1'. +# 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'; +column_name +c1 +# Check that SR lock is incompatible with SNRW lock. +# Sending: +select count(*) from t1;; +# +# Switching to connection 'default'. +# Check that the above SELECT is blocked because of SNRW lock. +# Unblock SELECT. +unlock tables; +# +# Switching to connection 'mdl_con1'. +# Reaping SELECT. +count(*) +4 +# +# Switching to connection 'default'. +lock table t1 write; +# +# Switching to connection 'mdl_con1'. +# Check that SW lock is incompatible with SNRW lock. +# Sending: +delete from t1 limit 1;; +# +# Switching to connection 'default'. +# Check that the above DELETE is blocked because of SNRW lock. +# Unblock DELETE. +unlock tables; +# +# Switching to connection 'mdl_con1'. +# Reaping DELETE. +# +# Switching to connection 'default'. +lock table t1 write; +# +# Switching to connection 'mdl_con1'. +# Check that SNW lock is incompatible with SNRW lock. +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'default'. +# Check that the above ALTER is blocked because of UNWR lock. +# Unblock ALTER. +unlock tables; +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'default'. +lock table t1 write; +# +# Switching to connection 'mdl_con1'. +# Check that SNRW lock is incompatible with SNRW lock. +# Sending: +lock table t1 write;; +# +# Switching to connection 'default'. +# Check that the above LOCK TABLES is blocked because of SNRW lock. +# Unblock waiting LOCK TABLES. +unlock tables; +# +# Switching to connection 'mdl_con1'. +# Reaping LOCK TABLES +insert into t1 values (1); +unlock tables; +# +# Switching to connection 'default'. +lock table t1 write; +# +# Switching to connection 'mdl_con1'. +# Check that X lock is incompatible with SNRW lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'default'. +# Check that the above RENAME is blocked because of SNRW lock. +# Unblock RENAME TABLE +unlock tables; +# +# Switching to connection 'mdl_con1'. +# Reaping RENAME TABLE +# Revert back to original state of things. +rename table t2 to t1; +# +# There is no need to check that upgrade from SNW/SNRW to X is +# blocked by presence of another SNRW lock because SNW/SNRW is +# incompatible with SNRW anyway. +# +# Switching to connection 'default'. +# +# +# 7) Now do the same round of tests for X lock. We use additional +# table to get long-lived lock of this type. +# +create table t2 (c1 int); +# +# Switching to connection 'mdl_con2'. +# Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked +# after acquiring X lock on t1. +lock tables t2 read; +# +# Switching to connection 'default'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME has acquired X lock on t1 and is waiting for t2. +# Check that S lock in incompatible with X lock. +# Sending: +handler t1 open;; +# +# Switching to connection 'mdl_con2'. +# Check that the above HANDLER statement is blocked because of X lock. +# Unblock RENAME TABLE +unlock tables; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping HANDLER. +handler t1 close; +# +# Switching to connection 'mdl_con2'. +# Prepare for blocking RENAME TABLE. +lock tables t2 read; +# +# Switching to connection 'default'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME has acquired X lock on t1 and is waiting for t2. +# Check that SH lock in incompatible with X lock. +# Sending: +select column_name from information_schema.columns where table_schema='test' and table_name='t1';; +# +# Switching to connection 'mdl_con2'. +# Check that the above SELECT ... FROM I_S ... statement is blocked +# because of X lock. +# Unblock RENAME TABLE +unlock tables; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping SELECT ... FROM I_S. +column_name +c1 +# +# Switching to connection 'mdl_con2'. +# Prepare for blocking RENAME TABLE. +lock tables t2 read; +# +# Switching to connection 'default'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME has acquired X lock on t1 and is waiting for t2. +# Check that SR lock in incompatible with X lock. +# Sending: +select count(*) from t1;; +# +# Switching to connection 'mdl_con2'. +# Check that the above SELECT statement is blocked +# because of X lock. +# Unblock RENAME TABLE +unlock tables; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping SELECT. +count(*) +4 +# +# Switching to connection 'mdl_con2'. +# Prepare for blocking RENAME TABLE. +lock tables t2 read; +# +# Switching to connection 'default'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME has acquired X lock on t1 and is waiting for t2. +# Check that SW lock in incompatible with X lock. +# Sending: +delete from t1 limit 1;; +# +# Switching to connection 'mdl_con2'. +# Check that the above DELETE statement is blocked +# because of X lock. +# Unblock RENAME TABLE +unlock tables; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping DELETE. +# +# Switching to connection 'mdl_con2'. +# Prepare for blocking RENAME TABLE. +lock tables t2 read; +# +# Switching to connection 'default'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME has acquired X lock on t1 and is waiting for t2. +# Check that SNW lock is incompatible with X lock. +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con2'. +# Check that the above ALTER statement is blocked +# because of X lock. +# Unblock RENAME TABLE +unlock tables; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con2'. +# Prepare for blocking RENAME TABLE. +lock tables t2 read; +# +# Switching to connection 'default'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME has acquired X lock on t1 and is waiting for t2. +# Check that SNRW lock is incompatible with X lock. +# Sending: +lock table t1 write;; +# +# Switching to connection 'mdl_con2'. +# Check that the above LOCK TABLE statement is blocked +# because of X lock. +# Unblock RENAME TABLE +unlock tables; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping LOCK TABLE. +unlock tables; +# +# Switching to connection 'mdl_con2'. +# Prepare for blocking RENAME TABLE. +lock tables t2 read; +# +# Switching to connection 'default'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME has acquired X lock on t1 and is waiting for t2. +# Check that X lock is incompatible with X lock. +# Sending: +rename table t1 to t3;; +# +# Switching to connection 'mdl_con2'. +# Check that the above RENAME statement is blocked +# because of X lock. +# Unblock RENAME TABLE +unlock tables; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping RENAME. +rename table t3 to t1; +# +# B) Now let us test compatibility in cases when both locks +# are pending. I.e. let us test rules for priorities between +# different types of metadata locks. +# +# +# Switching to connection 'mdl_con2'. +# +# 1) Check compatibility for pending SNW lock. +# +# Acquire SW lock in order to create pending SNW lock later. +begin; +insert into t1 values (1); +# +# Switching to connection 'default'. +# Add pending SNW lock. +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con1'. +# Check that ALTER TABLE is waiting with pending SNW lock. +# 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'; +column_name +c1 +select count(*) from t1; +count(*) +4 +# Check that SW is incompatible with pending SNW +# Sending: +delete from t1 limit 1;; +# +# Switching to connection 'mdl_con2'. +# Check that the above DELETE is blocked because of pending SNW lock. +# Unblock ALTER TABLE. +commit; +# +# Switching to connection 'default'. +# Reaping ALTER. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con1'. +# Reaping DELETE. +# +# We can't do similar check for SNW, SNRW and X locks because +# they will also be blocked by active SW lock. +# +# +# Switching to connection 'mdl_con2'. +# +# 2) Check compatibility for pending SNRW lock. +# +# Acquire SR lock in order to create pending SNRW lock. +begin; +select count(*) from t1; +count(*) +3 +# +# Switching to connection 'default'. +# Add pending SNRW lock. +# Sending: +lock table t1 write;; +# +# Switching to connection 'mdl_con1'. +# Check that LOCK TABLE is waiting with pending SNRW lock. +# 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'; +column_name +c1 +# Check that SR is incompatible with pending SNRW +# Sending: +select count(*) from t1;; +# +# Switching to connection 'mdl_con2'. +# Check that the above SELECT is blocked because of pending SNRW lock. +# Unblock LOCK TABLE. +commit; +# +# Switching to connection 'default'. +# Reaping LOCK TABLE. +unlock tables; +# +# Switching to connection 'mdl_con1'. +# Reaping SELECT. +count(*) +3 +# Restore pending SNRW lock. +# +# Switching to connection 'mdl_con2'. +begin; +select count(*) from t1; +count(*) +3 +# +# Switching to connection 'default'. +# Sending: +lock table t1 write;; +# +# Switching to connection 'mdl_con1'. +# Check that LOCK TABLE is waiting with pending SNRW lock. +# Check that SW is incompatible with pending SNRW +# Sending: +insert into t1 values (1);; +# +# Switching to connection 'mdl_con2'. +# Check that the above INSERT is blocked because of pending SNRW lock. +# Unblock LOCK TABLE. +commit; +# +# Switching to connection 'default'. +# Reaping LOCK TABLE. +unlock tables; +# +# Switching to connection 'mdl_con1'. +# Reaping INSERT. +# Restore pending SNRW lock. +# +# Switching to connection 'mdl_con2'. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'default'. +# Sending: +lock table t1 write;; +# +# Switching to connection 'mdl_con1'. +# Check that LOCK TABLE is waiting with pending SNRW lock. +# Check that SNW is compatible with pending SNRW +# So ALTER TABLE statements are not starved by LOCK TABLEs. +alter table t1 add primary key (c1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con2'. +# Unblock LOCK TABLE. +commit; +# +# Switching to connection 'default'. +# Reaping LOCK TABLE. +unlock tables; +# +# We can't do similar check for SNRW and X locks because +# they will also be blocked by active SR lock. +# +# +# Switching to connection 'mdl_con2'. +# +# 3) Check compatibility for pending X lock. +# +# Acquire SR lock in order to create pending X lock. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'default'. +# Add pending X lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME TABLE is waiting with pending X lock. +# Check that SH locks are compatible with pending X +select column_name from information_schema.columns where +table_schema='test' and table_name='t1'; +column_name +c1 +# Check that S is incompatible with pending X +# Sending: +handler t1 open;; +# +# Switching to connection 'mdl_con2'. +# Check that the above HANDLER OPEN is blocked because of pending X lock. +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping HANDLER t1 OPEN. +handler t1 close; +# Restore pending X lock. +# +# Switching to connection 'mdl_con2'. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'default'. +# Add pending X lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME TABLE is waiting with pending X lock. +# Check that SR is incompatible with pending X +# Sending: +select count(*) from t1;; +# +# Switching to connection 'mdl_con2'. +# Check that the above SELECT is blocked because of pending X lock. +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping SELECT. +count(*) +4 +# Restore pending X lock. +# +# Switching to connection 'mdl_con2'. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'default'. +# Add pending X lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME TABLE is waiting with pending X lock. +# Check that SW is incompatible with pending X +# Sending: +delete from t1 limit 1;; +# +# Switching to connection 'mdl_con2'. +# Check that the above DELETE is blocked because of pending X lock. +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping DELETE. +# Restore pending X lock. +# +# Switching to connection 'mdl_con2'. +begin; +select count(*) from t1; +count(*) +3 +# +# Switching to connection 'default'. +# Add pending X lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME TABLE is waiting with pending X lock. +# Check that SNW is incompatible with pending X +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con2'. +# Check that the above ALTER TABLE is blocked because of pending X lock. +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# Restore pending X lock. +# +# Switching to connection 'mdl_con2'. +handler t1 open; +# +# Switching to connection 'default'. +# Add pending X lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con1'. +# Check that RENAME TABLE is waiting with pending X lock. +# Check that SNRW is incompatible with pending X +# Sending: +lock table t1 write;; +# +# Switching to connection 'mdl_con3'. +# Check that the above LOCK TABLES is blocked because of pending X lock. +# +# Switching to connection 'mdl_con2'. +# Unblock RENAME TABLE. +handler t1 close; +# +# Switching to connection 'default'. +# Reaping RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'mdl_con1'. +# Reaping LOCK TABLES. +unlock tables; +# +# Switching to connection 'default'. +# +# +# C) Now let us test how type-of-operation locks are handled in +# transactional context. Obviously we are mostly interested +# in conflicting types of locks. +# +# +# 1) Let us check how various locks used within transactional +# context interact with active/pending SNW lock. +# +# We start with case when we are acquiring lock on the table +# which was not used in the transaction before. +begin; +select count(*) from t1; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# Create an active SNW lock on t2. +# We have to use DEBUG_SYNC facility as otherwise SNW lock +# 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'; +# Sending: +alter table t2 add primary key (c1);; +# +# Switching to connection 'default'. +set debug_sync= 'now WAIT_FOR locked'; +# SR lock should be acquired without any waiting. +select count(*) from t2; +count(*) +2 +commit; +# Now let us check that we will wait in case of SW lock. +begin; +select count(*) from t1; +count(*) +3 +# Sending: +insert into t2 values (1);; +# +# Switching to connection 'mdl_con2'. +# Check that the above INSERT is blocked. +# Unblock ALTER TABLE and thus INSERT. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'mdl_con1'. +# Reap ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'default'. +# Reap INSERT. +commit; +# +# Now let us see what happens when we are acquiring lock on the table +# which is already used in transaction. +# +# *) First, case when transaction which has SR lock on the table also +# locked in SNW mode acquires yet another SR lock and then tries +# to acquire SW lock. +begin; +select count(*) from t1; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# Create an active SNW lock on t1. +set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'default'. +set debug_sync= 'now WAIT_FOR locked'; +# We should still be able to get SR lock without waiting. +select count(*) from t1; +count(*) +3 +# Since the above ALTER TABLE is not upgrading SNW lock to X by waiting +# for SW lock we won't create deadlock. +# So the below INSERT should not end-up with ER_LOCK_DEADLOCK error. +# Sending: +insert into t1 values (1);; +# +# Switching to connection 'mdl_con2'. +# Check that the above INSERT is blocked. +# Unblock ALTER TABLE and thus INSERT. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'mdl_con1'. +# Reap ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'default'. +# Reap INSERT. +commit; +# +# **) Now test in which transaction that has SW lock on the table +# against which there is pending SNW lock acquires SR and SW +# locks on this table. +# +begin; +insert into t1 values (1); +# +# Switching to connection 'mdl_con1'. +# Create pending SNW lock on t1. +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'default'. +# Wait until ALTER TABLE starts waiting for SNW lock. +# We should still be able to get both SW and SR locks without waiting. +select count(*) from t1; +count(*) +5 +delete from t1 limit 1; +# Unblock ALTER TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'default'. +# +# 2) Now similar tests for active SNW lock which is being upgraded +# to X lock. +# +# Again we start with case when we are acquiring lock on the +# table which was not used in the transaction before. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con2'. +# Start transaction which will prevent SNW -> X upgrade from +# completing immediately. +begin; +select count(*) from t2; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# Create SNW lock pending upgrade to X on t2. +# Sending: +alter table t2 add column c2 int;; +# +# Switching to connection 'default'. +# Wait until ALTER TABLE starts waiting X lock. +# Check that attempt to acquire SR lock on t2 causes waiting. +# Sending: +select count(*) from t2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above SELECT is blocked. +# Unblock ALTER TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap ALTER TABLE. +# +# Switching to connection 'default'. +# Reap SELECT. +count(*) +3 +commit; +# Do similar check for SW lock. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con2'. +# Start transaction which will prevent SNW -> X upgrade from +# completing immediately. +begin; +select count(*) from t2; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# Create SNW lock pending upgrade to X on t2. +# Sending: +alter table t2 drop column c2;; +# +# Switching to connection 'default'. +# Wait until ALTER TABLE starts waiting X lock. +# Check that attempt to acquire SW lock on t2 causes waiting. +# Sending: +insert into t2 values (1);; +# +# Switching to connection 'mdl_con2'. +# Check that the above INSERT is blocked. +# Unblock ALTER TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap ALTER TABLE. +# +# Switching to connection 'default'. +# Reap INSERT. +commit; +# +# Test for the case in which we are acquiring lock on the table +# which is already used in transaction. +# +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con1'. +# Create SNW lock pending upgrade to X. +# Sending: +alter table t1 add column c2 int;; +# +# Switching to connection 'default'. +# Wait until ALTER TABLE starts waiting X lock. +# Check that transaction is still able to acquire SR lock. +select count(*) from t1; +count(*) +4 +# Waiting trying to acquire SW lock will cause deadlock and +# therefore should cause an error. +delete from t1 limit 1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# Unblock ALTER TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap ALTER TABLE. +# +# Switching to connection 'default'. +# +# 3) Check how various locks used within transactional context +# interact with active/pending SNRW lock. +# +# Once again we start with case when we are acquiring lock on +# the table which was not used in the transaction before. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con1'. +lock table t2 write; +# +# Switching to connection 'default'. +# Attempt to acquire SR should be blocked. It should +# not cause errors as it does not creates deadlock. +# Sending: +select count(*) from t2;; +# +# Switching to connection 'mdl_con1'. +# Check that the above SELECT is blocked +# Unblock SELECT. +unlock tables; +# +# Switching to connection 'default'. +# Reap SELECT. +count(*) +4 +commit; +# Repeat the same test for SW lock. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con1'. +lock table t2 write; +# +# Switching to connection 'default'. +# Again attempt to acquire SW should be blocked and should +# not cause any errors. +# Sending: +delete from t2 limit 1;; +# +# Switching to connection 'mdl_con1'. +# Check that the above DELETE is blocked +# Unblock DELETE. +unlock tables; +# +# Switching to connection 'default'. +# Reap DELETE. +commit; +# +# Now coverage for the case in which we are acquiring lock on +# the table which is already used in transaction and against +# which there is a pending SNRW lock request. +# +# *) Let us start with case when transaction has only a SR lock. +# +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con1'. +# Sending: +lock table t1 write;; +# +# Switching to connection 'default'. +# Wait until LOCK TABLE is blocked creating pending request for X lock. +# Check that another instance of SR lock is granted without waiting. +select count(*) from t1; +count(*) +4 +# Attempt to wait for SW lock will lead to deadlock, thus +# the below statement should end with ER_LOCK_DEADLOCK error. +delete from t1 limit 1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# Unblock LOCK TABLES. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap LOCK TABLES. +unlock tables; +# +# Switching to connection 'default'. +# +# **) Now case when transaction has a SW lock. +# +begin; +delete from t1 limit 1; +# +# Switching to connection 'mdl_con1'. +# Sending: +lock table t1 write;; +# +# Switching to connection 'default'. +# Wait until LOCK TABLE is blocked creating pending request for X lock. +# Check that both SR and SW locks are granted without waiting +# and errors. +select count(*) from t1; +count(*) +3 +insert into t1 values (1, 1); +# Unblock LOCK TABLES. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap LOCK TABLES. +unlock tables; +# +# Switching to connection 'default'. +# +# 4) Check how various locks used within transactional context +# interact with active/pending X lock. +# +# As usual we start with case when we are acquiring lock on +# the table which was not used in the transaction before. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con2'. +# Start transaction which will prevent X lock from going away +# immediately. +begin; +select count(*) from t2; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# Create pending X lock on t2. +# Sending: +rename table t2 to t3;; +# +# Switching to connection 'default'. +# Wait until RENAME TABLE starts waiting with pending X lock. +# Check that attempt to acquire SR lock on t2 causes waiting. +# Sending: +select count(*) from t2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above SELECT is blocked. +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap RENAME TABLE. +# +# Switching to connection 'default'. +# Reap SELECT. +ERROR 42S02: Table 'test.t2' doesn't exist +commit; +rename table t3 to t2; +# The same test for SW lock. +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con2'. +# Start transaction which will prevent X lock from going away +# immediately. +begin; +select count(*) from t2; +count(*) +3 +# +# Switching to connection 'mdl_con1'. +# Create pending X lock on t2. +# Sending: +rename table t2 to t3;; +# +# Switching to connection 'default'. +# Wait until RENAME TABLE starts waiting with pending X lock. +# Check that attempt to acquire SW lock on t2 causes waiting. +# Sending: +delete from t2 limit 1;; +# +# Switching to connection 'mdl_con2'. +# Check that the above DELETE is blocked. +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap RENAME TABLE. +# +# Switching to connection 'default'. +# Reap DELETE. +ERROR 42S02: Table 'test.t2' doesn't exist +commit; +rename table t3 to t2; +# +# Coverage for the case in which we are acquiring lock on +# the table which is already used in transaction and against +# which there is a pending X lock request. +# +# *) The first case is when transaction has only a SR lock. +# +begin; +select count(*) from t1; +count(*) +4 +# +# Switching to connection 'mdl_con1'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'default'. +# Wait until RENAME TABLE is blocked creating pending request for X lock. +# Check that another instance of SR lock is granted without waiting. +select count(*) from t1; +count(*) +4 +# Attempt to wait for SW lock will lead to deadlock, thus +# the below statement should end with ER_LOCK_DEADLOCK error. +delete from t1 limit 1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'default'. +# +# **) The second case is when transaction has a SW lock. +# +begin; +delete from t1 limit 1; +# +# Switching to connection 'mdl_con1'. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'default'. +# Wait until RENAME TABLE is blocked creating pending request for X lock. +# Check that both SR and SW locks are granted without waiting +# and errors. +select count(*) from t1; +count(*) +3 +insert into t1 values (1, 1); +# Unblock RENAME TABLE. +commit; +# +# Switching to connection 'mdl_con1'. +# Reap RENAME TABLE. +ERROR 42S01: Table 't2' already exists +# +# Switching to connection 'default'. +# Clean-up. +set debug_sync= 'RESET'; +drop table t1, t2; +# +# Additional coverage for some scenarios in which not quite +# correct use of S metadata locks by HANDLER statement might +# have caused deadlocks. +# +drop table if exists t1, t2; +create table t1 (i int); +create table t2 (j int); +insert into t1 values (1); +# +# First, check scenario in which we upgrade SNRW lock to X lock +# on a table while having HANDLER READ trying to acquire TL_READ +# on the same table. +# +handler t1 open; +# +# Switching to connection 'handler_con1'. +lock table t1 write; +# Upgrade SNRW to X lock. +# Sending: +alter table t1 add column j int;; +# +# Switching to connection 'handler_con2'. +# Wait until ALTER is blocked during upgrade. +# +# Switching to connection 'default'. +# The below statement should not cause deadlock. +handler t1 read first;; +# +# Switching to connection 'handler_con1'. +# Reap ALTER TABLE. +unlock tables; +# +# Switching to connection 'default'. +# Reap HANDLER READ. +i j +1 NULL +handler t1 close; +# +# Now, check scenario in which upgrade of SNRW lock to X lock +# can be blocked by HANDLER which is open in connection currently +# waiting to get table-lock owned by connection doing upgrade. +# +handler t1 open; +# +# Switching to connection 'handler_con1'. +lock table t1 write, t2 read; +# +# Switching to connection 'default'. +# Execute statement which will be blocked on table-level lock +# owned by connection 'handler_con1'. +# Sending: +insert into t2 values (1);; +# +# Switching to connection 'handler_con1'. +# Wait until INSERT is blocked on table-level lock. +# The below statement should not cause deadlock. +alter table t1 drop column j; +unlock tables; +# +# Switching to connection 'default'. +# Reap INSERT. +handler t1 close; +# +# Then, check the scenario in which upgrade of SNRW lock to X +# lock is blocked by HANDLER which is open in connection currently +# waiting to get SW lock on the same table. +# +handler t1 open; +# +# Switching to connection 'handler_con1'. +lock table t1 write; +# +# Switching to connection 'default'. +# The below insert should be blocked because active SNRW lock on 't1'. +# Sending: +insert into t1 values (1);; +# +# Switching to connection 'handler_con1'. +# Wait until INSERT is blocked because of SNRW lock. +# The below ALTER TABLE will be blocked because of presence of HANDLER. +# Sending: +alter table t1 add column j int;; +# +# Switching to connection 'default'. +# INSERT should be chosen as victim for resolving deadlock. +# Reaping INSERT. +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# Close HANDLER to unblock ALTER TABLE. +handler t1 close; +# +# Switching to connection 'handler_con1'. +# Reaping ALTER TABLE. +unlock tables; +# +# Switching to connection 'default'. +# +# Finally, test in which upgrade of SNRW lock to X lock is blocked +# by HANDLER which is open in connection currently waiting to get +# SR lock on the table on which lock is upgraded. +# +handler t1 open; +# +# Switching to connection 'handler_con1'. +lock table t1 write, t2 write; +# +# Switching to connection 'default'. +# The below insert should be blocked because active SNRW lock on 't1'. +# Sending: +insert into t2 values (1);; +# +# Switching to connection 'handler_con1'. +# Wait until INSERT is blocked because of SNRW lock. +# The below ALTER TABLE will be blocked because of presence of HANDLER. +# Sending: +alter table t1 drop column j;; +# +# Switching to connection 'default'. +# INSERT should be chosen as victim for resolving deadlock. +# Reaping INSERT. +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# Close HANDLER to unblock ALTER TABLE. +handler t1 close; +# +# Switching to connection 'handler_con1'. +# Reaping ALTER TABLE. +unlock tables; +# +# Switching to connection 'default'. +# Clean-up. +drop tables t1, t2; +# # Test coverage for basic deadlock detection in metadata # locking subsystem. # @@ -118,53 +1852,46 @@ commit; # # Switching to connection 'deadlock_con1'. begin; -insert into t1 values (1); -# -# Switching to connection 'deadlock_con2'. -begin; -insert into t3 values (1); +insert into t2 values (1); # # Switching to connection 'default'. -# Send: -rename table t2 to t0, t3 to t2, t0 to t3;; +lock table t1 write; # # Switching to connection 'deadlock_con1'. -# Wait until the above RENAME TABLE is blocked because it has to wait -# for 'deadlock_con2' which holds shared metadata lock on 't3'. # The below SELECT statement should wait for metadata lock -# on table 't2' and should not produce ER_LOCK_DEADLOCK +# on table 't1' and should not produce ER_LOCK_DEADLOCK # immediately as no deadlock is possible at the moment. -select * from t2;; +select * from t1;; # -# Switching to connection 'deadlock_con3'. -# Wait until the above SELECT * FROM t2 is starts waiting -# for an exclusive metadata lock to go away. +# Switching to connection 'deadlock_con2'. +# Wait until the above SELECT * FROM t1 is starts waiting +# for an UNRW metadata lock to go away. # Send RENAME TABLE statement that will deadlock with the # SELECT statement and thus should abort the latter. -rename table t1 to t5, t2 to t1, t5 to t2;; +rename table t1 to t0, t2 to t1, t0 to t2;; +# +# Switching to connection 'default'. +# Wait till above RENAME TABLE is blocked while holding +# pending X lock on t1. +# Allow the above RENAME TABLE to acquire lock on t1 and +# create pending lock on t2 thus creating deadlock. +unlock tables; # # Switching to connection 'deadlock_con1'. # Since the latest RENAME TABLE entered in deadlock with SELECT # statement the latter should be aborted and emit ER_LOCK_DEADLOCK # error. -# Reap SELECT * FROM t2. +# Reap SELECT * FROM t1. ERROR 40001: Deadlock found when trying to get lock; try restarting transaction # # Again let us check that failure of the SELECT statement has not -# released metadata lock on table 't1', i.e. that the latest RENAME +# released metadata lock on table 't2', i.e. that the latest RENAME # is blocked. # Commit transaction to unblock this RENAME TABLE. commit; # # Switching to connection 'deadlock_con2'. -# Commit transaction to unblock the first RENAME TABLE. -commit; -# -# Switching to connection 'default'. -# Reap RENAME TABLE t2 TO t0 ... . -# -# Switching to connection 'deadlock_con3'. -# Reap RENAME TABLE t1 TO t5 ... . +# Reap RENAME TABLE ... . # # Switching to connection 'default'. drop tables t1, t2, t3, t4; @@ -173,10 +1900,19 @@ drop tables t1, t2, t3, t4; # also takes into account requests for metadata lock upgrade. # create table t1 (i int); +insert into t1 values (1); +# Avoid race which occurs when SELECT in 'deadlock_con1' connection +# accesses table before the above INSERT unlocks the table and thus +# its result becomes visible to other connections. +select * from t1; +i +1 # # Switching to connection 'deadlock_con1'. begin; -insert into t1 values (1); +select * from t1; +i +1 # # Switching to connection 'default'. # Send: @@ -200,42 +1936,6 @@ commit; # Reap ALTER TABLE ... RENAME. drop table t2; # -# Finally, test case in which deadlock (or potentially livelock) occurs -# between metadata locking subsystem and table definition cache/table -# locks, but which should still be detected by our empiric. -# -create table t1 (i int); -# -# Switching to connection 'deadlock_con1'. -begin; -insert into t1 values (1); -# -# Switching to connection 'default'. -lock tables t1 write; -# -# Switching to connection 'deadlock_con1'. -# Send: -insert into t1 values (2);; -# -# Switching to connection 'default'. -# Wait until INSERT in connection 'deadlock_con1' is blocked on -# table-level lock. -# Send: -alter table t1 add column j int;; -# -# Switching to connection 'deadlock_con1'. -# The above ALTER TABLE statement should cause INSERT statement in -# this connection to be aborted and emit ER_LOCK_DEADLOCK error. -# Reap INSERT -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -# Commit transaction to unblock ALTER TABLE. -commit; -# -# Switching to connection 'default'. -# Reap ALTER TABLE. -unlock tables; -drop table t1; -# # Test for bug #46748 "Assertion in MDL_context::wait_for_locks() # on INSERT + CREATE TRIGGER". # @@ -297,7 +1997,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR locked'; # Now INSERT has a MDL on the non-existent table t1. # # 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'; # Try to create that table. CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1)); # Connection 2 @@ -323,7 +2023,7 @@ SET DEBUG_SYNC= 'now WAIT_FOR locked'; # Now INSERT has a MDL on the non-existent table t1. # # 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'; # Try to create that table. CREATE TABLE t1 LIKE t2; # Connection 2 @@ -347,10 +2047,10 @@ create table t1 (i int); # Let us check that we won't deadlock if during filling # of I_S table we encounter conflicting metadata lock # which owner is in its turn waiting for our connection. -lock tables t1 write; +lock tables t1 read; # Switching to connection 'con46044'. # Sending: -create table t2 select * from t1;; +create table t2 select * from t1 for update;; # Switching to connection 'default'. # Waiting until CREATE TABLE ... SELECT ... is blocked. # First let us check that SHOW FIELDS/DESCRIBE doesn't @@ -386,10 +2086,10 @@ drop table t2; # # We check same three queries to I_S in this new situation. # Switching to connection 'con46044_2'. -lock tables t1 write; +lock tables t1 read; # Switching to connection 'con46044'. # Sending: -create table t2 select * from t1;; +create table t2 select * from t1 for update;; # Switching to connection 'default'. # Waiting until CREATE TABLE ... SELECT ... is blocked. # Let us check that SHOW FIELDS/DESCRIBE gets blocked. @@ -406,10 +2106,10 @@ Field Type Null Key Default Extra i int(11) YES NULL drop table t2; # Switching to connection 'con46044_2'. -lock tables t1 write; +lock tables t1 read; # Switching to connection 'con46044'. # Sending: -create table t2 select * from t1;; +create table t2 select * from t1 for update;; # Switching to connection 'default'. # Waiting until CREATE TABLE ... SELECT ... is blocked. # Check that I_S query which reads only .FRMs gets blocked. @@ -426,10 +2126,10 @@ column_name i drop table t2; # Switching to connection 'con46044_2'. -lock tables t1 write; +lock tables t1 read; # Switching to connection 'con46044'. # Sending: -create table t2 select * from t1;; +create table t2 select * from t1 for update;; # Switching to connection 'default'. # Waiting until CREATE TABLE ... SELECT ... is blocked. # Finally, check that I_S query which does full-blown table open @@ -458,7 +2158,9 @@ set debug_sync= 'RESET'; 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; +c1 c2 c3 +3 3 0 # # Switching to connection 'con46273'. set debug_sync='after_lock_tables_takes_lock SIGNAL alter_table_locked WAIT_FOR alter_go'; @@ -466,11 +2168,11 @@ alter table t1 add column e int, rename to t2;; # # Switching to 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'; # The below statement should get ER_LOCK_DEADLOCK error # (i.e. it should not allow ALTER to proceed, and then # fail due to 't1' changing its name to 't2'). -update t1 set c3=c3+1 where c2=4; +update t1 set c3=c3+1 where c2 = 3; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction # # Let us check that failure of the above statement has not released diff --git a/mysql-test/r/sp-threads.result b/mysql-test/r/sp-threads.result index d974cfb9605..a14d099c673 100644 --- a/mysql-test/r/sp-threads.result +++ b/mysql-test/r/sp-threads.result @@ -35,7 +35,7 @@ call bug9486(); show processlist; Id User Host db Command Time State Info # root localhost test Sleep # NULL -# root localhost test Query # Table lock update t1, t2 set val= 1 where id1=id2 +# root localhost test Query # Waiting for table update t1, t2 set val= 1 where id1=id2 # root localhost test Query # NULL show processlist # root localhost test Sleep # NULL unlock tables; diff --git a/mysql-test/r/truncate_coverage.result b/mysql-test/r/truncate_coverage.result index bb036329f6f..7a5021f55e2 100644 --- a/mysql-test/r/truncate_coverage.result +++ b/mysql-test/r/truncate_coverage.result @@ -7,18 +7,20 @@ CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); # # connection con1 -START TRANSACTION; -INSERT INTO t1 VALUES (2); +HANDLER t1 OPEN; # # connection default LOCK TABLE t1 WRITE; SET DEBUG_SYNC='mdl_upgrade_shared_lock_to_exclusive SIGNAL waiting'; TRUNCATE TABLE t1; # -# connection con1 +# connection con2 SET DEBUG_SYNC='now WAIT_FOR waiting'; KILL QUERY @id; -COMMIT; +# +# connection con1 +# Release shared metadata lock by closing HANDLER. +HANDLER t1 CLOSE; # # connection default ERROR 70100: Query execution was interrupted @@ -29,17 +31,18 @@ CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); # # connection con1 -START TRANSACTION; -INSERT INTO t1 VALUES (2); +HANDLER t1 OPEN; # # connection default LOCK TABLE t1 WRITE; SET DEBUG_SYNC='mdl_upgrade_shared_lock_to_exclusive SIGNAL waiting'; TRUNCATE TABLE t1; # -# connection con1 +# connection con2 SET DEBUG_SYNC='now WAIT_FOR waiting'; -COMMIT; +# +# connection con1 +HANDLER t1 CLOSE; # # connection default ERROR 42S02: Table 'test.t1' doesn't exist @@ -55,7 +58,7 @@ START TRANSACTION; INSERT INTO t1 VALUES (2); # # connection default -SET DEBUG_SYNC='mdl_acquire_exclusive_locks_wait SIGNAL waiting'; +SET DEBUG_SYNC='mdl_acquire_lock_wait SIGNAL waiting'; TRUNCATE TABLE t1; # # connection con1 |