diff options
Diffstat (limited to 'mysql-test')
24 files changed, 5085 insertions, 337 deletions
diff --git a/mysql-test/include/handler.inc b/mysql-test/include/handler.inc index 8342a072ef9..e16c53bc1ee 100644 --- a/mysql-test/include/handler.inc +++ b/mysql-test/include/handler.inc @@ -732,10 +732,13 @@ connection default; --disable_warnings drop table if exists t1; --enable_warnings -create table t1 (a int, key a (a)); +--echo # First test case which is supposed trigger the execution +--echo # path on which problem was discovered. +create table t1 (a int); insert into t1 values (1); handler t1 open; connection con1; +lock table t1 write; send alter table t1 engine=memory; connection con2; let $wait_condition= @@ -743,10 +746,34 @@ let $wait_condition= where state = "Waiting for table" and info = "alter table t1 engine=memory"; --source include/wait_condition.inc connection default; +--error ER_ILLEGAL_HA handler t1 read a next; handler t1 close; connection con1; --reap +unlock tables; +drop table t1; +--echo # Now test case which was reported originally but which no longer +--echo # triggers execution path which has caused the problem. +connection default; +create table t1 (a int, key(a)); +insert into t1 values (1); +handler t1 open; +connection con1; +send alter table t1 engine=memory; +connection con2; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "alter table t1 engine=memory"; +--source include/wait_condition.inc +connection default; +--echo # Since S metadata lock was already acquired at HANDLER OPEN time +--echo # and TL_READ lock requested by HANDLER READ is compatible with +--echo # ALTER's TL_WRITE_ALLOW_READ the below statement should succeed +--echo # without waiting. The old version of table should be used in it. +handler t1 read a next; +handler t1 close; +connection con1; drop table t1; disconnect con1; --source include/wait_until_disconnected.inc @@ -1228,15 +1255,27 @@ create table t2 like t1; handler t1 open; --echo # --> connection con1 connection con1; -lock table t2 read; +lock table t1 write, t2 write; --echo # --> connection default connection default; +send drop table t2; +--echo # --> connection con2 +connection con2; +--echo # Waiting for 'drop table t2' to get blocked... +let $wait_condition=select count(*)=1 from information_schema.processlist where state='Waiting for table' and info='drop table t2'; +--source include/wait_condition.inc +--echo # --> connection con1 +connection con1; --error ER_LOCK_DEADLOCK -drop table t2; ---error ER_LOCK_DEADLOCK -rename table t2 to t3; +drop table t1; +unlock tables; +--echo # --> connection default +connection default; +reap; + --echo # Demonstrate that there is no deadlock with FLUSH TABLE, --echo # even though it is waiting for the other table to go away +create table t2 like t1; --echo # Sending: --send flush table t2 --echo # --> connection con2 @@ -1256,6 +1295,7 @@ drop table t2; --echo # lead to deadlocks --echo # create table t1 (a int, key a(a)); +insert into t1 values (1), (2); --echo # --> connection default connection default; @@ -1265,7 +1305,31 @@ handler t1 open; --echo # --> connection con1 connection con1; -lock tables t1 write; +--echo # Sending: +--send lock tables t1 write + +--echo # --> connection con2 +connection con2; +--echo # Check that 'lock tables t1 write' waits until transaction which +--echo # has read from the table commits. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table" and info = "lock tables t1 write"; +--source include/wait_condition.inc + +--echo # --> connection default +connection default; +--echo # The below 'handler t1 read ...' should not be blocked as +--echo # 'lock tables t1 write' has not succeeded yet. +handler t1 read a next; + +--echo # Unblock 'lock tables t1 write'. +commit; + +--echo # --> connection con1 +connection con1; +--echo # Reap 'lock tables t1 write'. +--reap --echo # --> connection default connection default; @@ -1279,29 +1343,18 @@ let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Table lock" and info = "handler t1 read a next"; --source include/wait_condition.inc ---echo # Sending: ---send drop table t1 ---echo # --> connection con2 -connection con2; ---echo # Waiting for 'drop table t1' to get blocked... -let $wait_condition= - select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = "drop table t1"; ---source include/wait_condition.inc +--echo # The below 'drop table t1' should be able to proceed without +--echo # waiting as it will force HANDLER to be closed. +drop table t1; +unlock tables; --echo # --> connection default connection default; --echo # Reaping 'handler t1 read a next'... ---error ER_LOCK_DEADLOCK +--error ER_NO_SUCH_TABLE --reap handler t1 close; -commit; - ---echo # --> connection con1 -connection con1; ---echo # Reaping 'drop table t1'... ---reap --echo # --> connection con1 connection con1; @@ -1357,3 +1410,84 @@ rename table t4 to t5, t3 to t4, t5 to t3; handler t1 read first; handler t2 read first; drop table t1, t2, t3, t4; + +--echo # +--echo # A test for FLUSH TABLES WITH READ LOCK and HANDLER statements. +--echo # +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; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +handler t1 read next; +--echo # This implicitly leaves LOCK TABLES but doesn't drop the GLR +--error ER_NO_SUCH_TABLE +lock table not_exists_write read; +--echo # We still have the read lock. +--error ER_CANT_UPDATE_WITH_READLOCK +drop table t1; +handler t1 open; +select a from t2; +handler t1 read next; +flush tables with read lock; +handler t2 open; +flush tables with read lock; +handler t1 read next; +select a from t3; +handler t2 read next; +handler t1 close; +rollback; +handler t2 close; +--error ER_CANT_UPDATE_WITH_READLOCK +drop table t1; +commit; +flush tables; +--error ER_CANT_UPDATE_WITH_READLOCK +drop table t1; +unlock tables; +drop table t1; +set autocommit=default; +drop table t2, t3; + +--echo # +--echo # HANDLER statement and operation-type aware metadata locks. +--echo # Check that when we clone a ticket for HANDLER we downrade +--echo # the lock. +--echo # +--echo # Establish an auxiliary connection con1. +connect (con1,localhost,root,,); +--echo # -> connection default +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; +insert into t1 (a, b) values (7, 7); +handler t1 read a last; +commit; +--echo # -> connection con1 +connection con1; +--echo # Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE. +lock table t1 write; +unlock tables; +--echo # -> connection default +connection default; +handler t1 read a prev; +handler t1 close; +--echo # Cleanup. +drop table t1; +--echo # -> connection con1 +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +--echo # -> connection default +connection default; 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 diff --git a/mysql-test/suite/funcs_1/datadict/processlist_val.inc b/mysql-test/suite/funcs_1/datadict/processlist_val.inc index 8b10cfc5e97..6fcaf45c848 100644 --- a/mysql-test/suite/funcs_1/datadict/processlist_val.inc +++ b/mysql-test/suite/funcs_1/datadict/processlist_val.inc @@ -425,7 +425,7 @@ echo # Poll till INFO is no more NULL and State = 'Table Lock'. ; let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST - WHERE INFO IS NOT NULL AND STATE = 'Table Lock'; + WHERE INFO IS NOT NULL AND STATE = 'Waiting for table'; --source include/wait_condition.inc echo # Expect result: diff --git a/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result b/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result index 34b2e48fc7e..e8ee784bec4 100644 --- a/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result +++ b/mysql-test/suite/funcs_1/r/processlist_val_no_prot.result @@ -195,9 +195,11 @@ SELECT COUNT(*) FROM test.t1; # Poll till INFO is no more NULL and State = 'Table Lock'. +Timeout in wait_condition.inc for SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE INFO IS NOT NULL AND STATE = 'Table Lock' SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; ID USER HOST DB COMMAND TIME STATE INFO -<ID> test_user <HOST_NAME> information_schema Query <TIME> Table lock SELECT COUNT(*) FROM test.t1 +<ID> test_user <HOST_NAME> information_schema Query <TIME> Waiting for table SELECT COUNT(*) FROM test.t1 <ID> test_user <HOST_NAME> information_schema Sleep <TIME> NULL <ID> root <HOST_NAME> information_schema Query <TIME> executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST UNLOCK TABLES; diff --git a/mysql-test/suite/rpl/t/rpl_sp.test b/mysql-test/suite/rpl/t/rpl_sp.test index 231f0c6bcc0..3d94415fbb5 100644 --- a/mysql-test/suite/rpl/t/rpl_sp.test +++ b/mysql-test/suite/rpl/t/rpl_sp.test @@ -655,7 +655,7 @@ connection master; connection master1; --echo # Waitng for 'insert into t1 ...' to get blocked on table lock... let $wait_condition=select count(*)=1 from information_schema.processlist -where state='Table lock' and info='insert into t1 (a) values (f1())'; +where state='Waiting for table' and info='insert into t1 (a) values (f1())'; --source include/wait_condition.inc --echo # Sending 'drop function f1'. It will abort the table lock wait. drop function f1; 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. |