#At file:///home/dlenev/src/bzr/mysql-next-4284-new-locks/ based on revid:dlenev@stripped
3055 Dmitry Lenev 2010-01-22
Tentative patch implementing new type-of-operation-aware metadata locks.
Fixes bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock" and bug
#37346 "innodb does not detect deadlock between update and alter table".
Commit to the tree to be used for review work.
modified:
mysql-test/include/handler.inc
mysql-test/r/debug_sync.result
mysql-test/r/events_2.result
mysql-test/r/handler_innodb.result
mysql-test/r/handler_myisam.result
mysql-test/r/innodb-lock.result
mysql-test/r/innodb_mysql_lock.result
mysql-test/r/lock_multi.result
mysql-test/r/lock_sync.result
mysql-test/r/mdl_sync.result
mysql-test/r/sp-threads.result
mysql-test/r/truncate_coverage.result
mysql-test/suite/rpl/t/rpl_sp.test
mysql-test/t/debug_sync.test
mysql-test/t/events_2.test
mysql-test/t/innodb-lock.test
mysql-test/t/innodb_mysql_lock.test
mysql-test/t/lock_multi.test
mysql-test/t/lock_sync.test
mysql-test/t/mdl_sync.test
mysql-test/t/multi_update.test
mysql-test/t/truncate_coverage.test
sql/mdl.cc
sql/mdl.h
sql/mysql_priv.h
sql/sp_head.cc
sql/sql_base.cc
sql/sql_handler.cc
sql/sql_parse.cc
sql/sql_prepare.cc
sql/sql_table.cc
sql/table.cc
sql/table.h
=== modified file 'mysql-test/include/handler.inc'
--- a/mysql-test/include/handler.inc 2010-01-21 20:43:03 +0000
+++ b/mysql-test/include/handler.inc 2010-01-22 05:53:57 +0000
@@ -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
@@ -1256,6 +1283,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 +1293,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 +1331,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;
=== modified file 'mysql-test/r/debug_sync.result'
--- a/mysql-test/r/debug_sync.result 2009-12-01 19:07:18 +0000
+++ b/mysql-test/r/debug_sync.result 2010-01-22 05:53:57 +0000
@@ -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);
=== modified file 'mysql-test/r/events_2.result'
--- a/mysql-test/r/events_2.result 2009-10-07 20:57:03 +0000
+++ b/mysql-test/r/events_2.result 2010-01-22 05:53:57 +0000
@@ -203,24 +203,14 @@ ERROR HY000: Table 'event' was locked wi
drop event e1;
ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
unlock tables;
-lock table t1 read, mysql.event write;
-ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
-lock table t1 write, mysql.event write;
-ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
-lock table mysql.event write;
-show create event e1;
-Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
-e1 SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 HOUR STARTS '#' ON COMPLETION NOT PRESERVE ENABLE DO select 1 utf8 utf8_general_ci latin1_swedish_ci
-select event_name from information_schema.events;
-event_name
-e1
-create event e2 on schedule every 10 hour do select 1;
-alter event e2 disable;
-alter event e2 rename to e3;
-drop event e3;
-drop event e1;
-unlock tables;
+#
+# QQ: Should we somehow detect deadlock caused by SHOW CREATE EVENT
+# and SELECT FROM I_S.EVENTS below and do something about it?
+# Alternatively we can try not to use separate MDL_context for
+# tables and thus avoid deadlock in this case...
+#
Make sure we have left no events
+drop event e1;
select event_name from information_schema.events;
event_name
=== modified file 'mysql-test/r/handler_innodb.result'
--- a/mysql-test/r/handler_innodb.result 2010-01-21 20:43:03 +0000
+++ b/mysql-test/r/handler_innodb.result 2010-01-22 05:53:57 +0000
@@ -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;
@@ -1239,29 +1257,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
=== modified file 'mysql-test/r/handler_myisam.result'
--- a/mysql-test/r/handler_myisam.result 2010-01-21 20:43:03 +0000
+++ b/mysql-test/r/handler_myisam.result 2010-01-22 05:53:57 +0000
@@ -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;
@@ -1236,29 +1254,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
=== modified file 'mysql-test/r/innodb-lock.result'
--- a/mysql-test/r/innodb-lock.result 2004-10-27 16:52:41 +0000
+++ b/mysql-test/r/innodb-lock.result 2010-01-22 05:53:57 +0000
@@ -25,33 +25,8 @@ id x
0 2
commit;
drop table t1;
-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);
-commit;
-SELECT * from t1 where id = 0 FOR UPDATE;
-id x
-0 0
-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;
-id x
-0 0
-1 1
-2 3
-commit;
-unlock tables;
-commit;
-select * from t1;
-id x
-0 0
-1 1
-2 10
-drop table t1;
+#
+# QQ With new semantics of LOCK TABLE WRITE we in essence ignore
+# innodb_table_locks=0 option.
+# Should we do anything about it?
+#
=== modified file 'mysql-test/r/innodb_mysql_lock.result'
--- a/mysql-test/r/innodb_mysql_lock.result 2009-12-09 15:13:00 +0000
+++ b/mysql-test/r/innodb_mysql_lock.result 2010-01-22 05:53:57 +0000
@@ -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
#
=== modified file 'mysql-test/r/lock_multi.result'
--- a/mysql-test/r/lock_multi.result 2009-12-10 10:53:20 +0000
+++ b/mysql-test/r/lock_multi.result 2010-01-22 05:53:57 +0000
@@ -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;
=== modified file 'mysql-test/r/lock_sync.result'
--- a/mysql-test/r/lock_sync.result 2009-10-26 19:38:03 +0000
+++ b/mysql-test/r/lock_sync.result 2010-01-22 05:53:57 +0000
@@ -5,62 +5,8 @@
# (TL_WRITE_ALLOW_WRITE) on several instances of the same table and
# 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;
-# Create auxiliary connections used through the test.
-# Reset DEBUG_SYNC facility before using it.
-set debug_sync= 'RESET';
-# Turn off logging so calls to locking subsystem performed
-# for general_log table won't interfere with our test.
-set @old_general_log = @@global.general_log;
-set @@global.general_log= OFF;
-create table t1 (i int) engine=InnoDB;
-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.
-select get_lock("lock_bug45143_wait", 0);
-get_lock("lock_bug45143_wait", 0)
-1
-# Switch to connection 'con_bug45143_1'.
-# Sending:
-insert into t1 values (get_lock("lock_bug45143_wait", 100));;
-# Switch to connection 'con_bug45143_2'.
-# Wait until the above INSERT takes TL_WRITE_ALLOW_WRITE lock on 't1'
-# and then gets blocked on user lock 'lock_bug45143_wait'.
-# Ensure that upcoming SELECT waits after acquiring TL_WRITE_ALLOW_WRITE
-# lock for the first instance of 't1'.
-set debug_sync='thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go';
-# Sending:
-select count(*) > 0 from t1 as a, t1 as b for update;;
-# Switch to connection 'con_bug45143_3'.
-# Wait until the above SELECT ... FOR UPDATE is blocked after
-# 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;;
-# Switch to connection 'default'.
-# Wait until this LOCK TABLES statement starts waiting for table lock.
-# Allow SELECT ... FOR UPDATE to resume.
-# Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance
-# of 't1' it should be able to get lock on the second instance without
-# waiting, even although there is another thread which has such lock
-# on this table and also there is a thread waiting for a TL_WRITE on it.
-set debug_sync= 'now SIGNAL go';
-# Switch to connection 'con_bug45143_2'.
-# Reap SELECT ... FOR UPDATE
-count(*) > 0
-1
-# Switch to connection 'default'.
-# Resume execution of the INSERT statement.
-select release_lock("lock_bug45143_wait");
-release_lock("lock_bug45143_wait")
-1
-# Switch to connection 'con_bug45143_1'.
-# Reap INSERT statement.
-# Switch to connection 'con_bug45143_3'.
-# Reap LOCK TABLES statement.
-unlock tables;
-# Switch to connection 'default'.
-# Do clean-up.
-set debug_sync= 'RESET';
-set @@global.general_log= @old_general_log;
-drop table t1;
+#
+# QQ: What we should do with this test? With introduction of new
+# types of locks this scenario becomes unrepeatable since InnoDB
+# no longer acquire TL_WRITE lock which is not supported by
+# semi-exclusive MDL lock.
=== modified file 'mysql-test/r/mdl_sync.result'
--- a/mysql-test/r/mdl_sync.result 2010-01-21 20:43:03 +0000
+++ b/mysql-test/r/mdl_sync.result 2010-01-22 05:53:57 +0000
@@ -20,6 +20,1683 @@ 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 UNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail after opening the table and thus obtaining UNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that UNRW 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 UNW 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 UNRW 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 UNRW 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 UNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail after opening the table and thus obtaining UNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that UNRW 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 UNW 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 UNRW 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 UNRW 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 UNW lock is compatible with it. To do this use ALTER TABLE
+# which will fail after opening the table and thus obtaining UNW metadata
+# lock.
+alter table t1 add primary key (c1);
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+# Check that UNRW 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 UNW 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 UNRW to X is blocked
+# by presence of SR lock because UNRW 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 UNW lock is not compatible with SW lock.
+# Again we use ALTER TABLE which fails after opening
+# the table to avoid upgrade of UNW -> 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 UNRW 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 UNW/UNRW to X is
+# blocked by presence of SW lock because UNW/UNRW is incompatible
+# with SW anyway.
+#
+#
+# Switching to connection 'default'.
+#
+#
+# 5) Acquire UNW lock on the table. We have to use DEBUG_SYNC for
+# this, to prevent UNW 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 UNW lock.
+# Sending:
+delete from t1 limit 2;;
+#
+# Switching to connection 'mdl_con2'.
+# Check that the above DELETE is blocked because of UNW 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 UNW lock is incompatible with UNW lock.
+# Sending:
+alter table t1 add primary key (c1);;
+#
+# Switching to connection 'mdl_con2'.
+# Check that the above ALTER is blocked because of UNW 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 UNRW lock is incompatible with UNW lock.
+# Sending:
+lock table t1 write;;
+#
+# Switching to connection 'mdl_con2'.
+# Check that the above LOCK TABLES is blocked because of UNW 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 UNW lock.
+# Sending:
+rename table t1 to t2;;
+#
+# Switching to connection 'mdl_con2'.
+# Check that the above RENAME is blocked because of UNW 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 UNW/UNRW to X is
+# blocked by presence of another UNW lock because UNW/UNRW is
+# incompatible with UNW anyway.
+#
+# Switching to connection 'default'.
+#
+#
+# 6) Acquire UNRW 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 UNRW lock.
+# Sending:
+select count(*) from t1;;
+#
+# Switching to connection 'default'.
+# Check that the above SELECT is blocked because of UNRW 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 UNRW lock.
+# Sending:
+delete from t1 limit 1;;
+#
+# Switching to connection 'default'.
+# Check that the above DELETE is blocked because of UNRW 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 UNW lock is incompatible with UNRW 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 UNRW lock is incompatible with UNRW lock.
+# Sending:
+lock table t1 write;;
+#
+# Switching to connection 'default'.
+# Check that the above LOCK TABLES is blocked because of UNRW 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 UNRW lock.
+# Sending:
+rename table t1 to t2;;
+#
+# Switching to connection 'default'.
+# Check that the above RENAME is blocked because of UNRW 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 UNW/UNRW to X is
+# blocked by presence of another UNRW lock because UNW/UNRW is
+# incompatible with UNRW 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 UNW 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 UNRW 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 UNW lock.
+#
+# Acquire SW lock in order to create pending UNW lock later.
+begin;
+insert into t1 values (1);
+#
+# Switching to connection 'default'.
+# Add pending UNW lock.
+# Sending:
+alter table t1 add primary key (c1);;
+#
+# Switching to connection 'mdl_con1'.
+# Check that ALTER TABLE is waiting with pending UNW lock.
+# Check that S, SH and SR locks are compatible with pending UNW
+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 UNW
+# Sending:
+delete from t1 limit 1;;
+#
+# Switching to connection 'mdl_con2'.
+# Check that the above DELETE is blocked because of pending UNW 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 UNW, UNRW and X locks because
+# they will also be blocked by active SW lock.
+#
+#
+# Switching to connection 'mdl_con2'.
+#
+# 2) Check compatibility for pending UNRW lock.
+#
+# Acquire SR lock in order to create pending UNRW lock.
+begin;
+select count(*) from t1;
+count(*)
+3
+#
+# Switching to connection 'default'.
+# Add pending UNRW lock.
+# Sending:
+lock table t1 write;;
+#
+# Switching to connection 'mdl_con1'.
+# Check that LOCK TABLE is waiting with pending UNRW lock.
+# Check that S and SH locks are compatible with pending UNRW
+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 UNRW
+# Sending:
+select count(*) from t1;;
+#
+# Switching to connection 'mdl_con2'.
+# Check that the above SELECT is blocked because of pending UNRW 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 UNRW 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 UNRW lock.
+# Check that SW is incompatible with pending UNRW
+# Sending:
+insert into t1 values (1);;
+#
+# Switching to connection 'mdl_con2'.
+# Check that the above INSERT is blocked because of pending UNRW lock.
+# Unblock LOCK TABLE.
+commit;
+#
+# Switching to connection 'default'.
+# Reaping LOCK TABLE.
+unlock tables;
+#
+# Switching to connection 'mdl_con1'.
+# Reaping INSERT.
+# Restore pending UNRW 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 UNRW lock.
+# Check that UNW is incompatible with pending UNRW
+# QQ: Should this be changed, to avoid starvation of ALTER TABLE?
+# Sending:
+alter table t1 add primary key (c1);;
+#
+# Switching to connection 'mdl_con2'.
+# Check that the above ALTER is blocked because of pending UNRW lock.
+# Unblock LOCK TABLE.
+commit;
+#
+# Switching to connection 'default'.
+# Reaping LOCK TABLE.
+unlock tables;
+#
+# Switching to connection 'mdl_con1'.
+# Reaping ALTER TABLE.
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+#
+# We can't do similar check for UNRW 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 UNW 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 UNRW 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 UNW 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 UNW lock on t2.
+# We have to use DEBUG_SYNC facility as otherwise UNW 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 UNW 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 UNW 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 UNW 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 UNW lock acquires SR and SW
+# locks on this table.
+#
+begin;
+insert into t1 values (1);
+#
+# Switching to connection 'mdl_con1'.
+# Create pending UNW lock on t1.
+# Sending:
+alter table t1 add primary key (c1);;
+#
+# Switching to connection 'default'.
+# Wait until ALTER TABLE starts waiting for UNW 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 UNW 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 UNW -> X upgrade from
+# completing immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+#
+# Switching to connection 'mdl_con1'.
+# Create UNW 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 UNW -> X upgrade from
+# completing immediately.
+begin;
+select count(*) from t2;
+count(*)
+3
+#
+# Switching to connection 'mdl_con1'.
+# Create UNW 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 UNW 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 UNRW 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 UNRW 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.
+#
+# TODO/FIXME: Add more test cases once MDL contexts for
+# normal and HANDLER locks are merged so we
+# can perform deadlock detection properly.
+#
+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 UNRW 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 UNRW 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 UNRW 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;
+# Clean-up.
+drop tables t1, t2;
+#
# Test coverage for basic deadlock detection in metadata
# locking subsystem.
#
@@ -173,10 +1850,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 +1886,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".
#
@@ -347,10 +1997,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 +2036,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 +2056,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 +2076,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 +2108,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 +2118,11 @@ alter table t1 add column e int, rename
#
# 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
=== modified file 'mysql-test/r/sp-threads.result'
--- a/mysql-test/r/sp-threads.result 2009-12-03 20:08:27 +0000
+++ b/mysql-test/r/sp-threads.result 2010-01-22 05:53:57 +0000
@@ -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;
=== modified file 'mysql-test/r/truncate_coverage.result'
--- a/mysql-test/r/truncate_coverage.result 2009-12-11 12:24:23 +0000
+++ b/mysql-test/r/truncate_coverage.result 2010-01-22 05:53:57 +0000
@@ -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
=== modified file 'mysql-test/suite/rpl/t/rpl_sp.test'
--- a/mysql-test/suite/rpl/t/rpl_sp.test 2009-12-29 12:19:05 +0000
+++ b/mysql-test/suite/rpl/t/rpl_sp.test 2010-01-22 05:53:57 +0000
@@ -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;
=== modified file 'mysql-test/t/debug_sync.test'
--- a/mysql-test/t/debug_sync.test 2009-09-29 15:38:40 +0000
+++ b/mysql-test/t/debug_sync.test 2010-01-22 05:53:57 +0000
@@ -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.
=== modified file 'mysql-test/t/events_2.test'
--- a/mysql-test/t/events_2.test 2008-08-18 11:05:51 +0000
+++ b/mysql-test/t/events_2.test 2010-01-22 05:53:57 +0000
@@ -283,6 +283,13 @@ drop event e2;
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
drop event e1;
unlock tables;
+--echo #
+--echo # QQ: Should we somehow detect deadlock caused by SHOW CREATE EVENT
+--echo # and SELECT FROM I_S.EVENTS below and do something about it?
+--echo # Alternatively we can try not to use separate MDL_context for
+--echo # tables and thus avoid deadlock in this case...
+--echo #
+--disable_parsing
#
--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
lock table t1 read, mysql.event write;
@@ -300,7 +307,9 @@ alter event e2 rename to e3;
drop event e3;
drop event e1;
unlock tables;
+--enable_parsing
--echo Make sure we have left no events
+drop event e1;
select event_name from information_schema.events;
--echo
--echo Events in sub-statements, events and prelocking
=== modified file 'mysql-test/t/innodb-lock.test'
--- a/mysql-test/t/innodb-lock.test 2006-11-19 18:26:36 +0000
+++ b/mysql-test/t/innodb-lock.test 2010-01-22 05:53:57 +0000
@@ -60,6 +60,12 @@ drop table t1;
# Try with old lock method (where LOCK TABLE is ignored by InnoDB)
#
+--echo #
+--echo # QQ With new semantics of LOCK TABLE WRITE we in essence ignore
+--echo # innodb_table_locks=0 option.
+--echo # Should we do anything about it?
+--echo #
+--disable_parsing
set @@innodb_table_locks=0;
create table t1 (id integer primary key, x integer) engine=INNODB;
@@ -98,5 +104,6 @@ reap;
commit;
select * from t1;
drop table t1;
+--enable_parsing
# End of 4.1 tests
=== modified file 'mysql-test/t/innodb_mysql_lock.test'
--- a/mysql-test/t/innodb_mysql_lock.test 2009-12-09 15:13:00 +0000
+++ b/mysql-test/t/innodb_mysql_lock.test 2010-01-22 05:53:57 +0000
@@ -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
=== modified file 'mysql-test/t/lock_multi.test'
--- a/mysql-test/t/lock_multi.test 2009-12-10 10:53:20 +0000
+++ b/mysql-test/t/lock_multi.test 2010-01-22 05:53:57 +0000
@@ -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 #
=== modified file 'mysql-test/t/lock_sync.test'
--- a/mysql-test/t/lock_sync.test 2009-12-03 20:08:27 +0000
+++ b/mysql-test/t/lock_sync.test 2010-01-22 05:53:57 +0000
@@ -19,6 +19,12 @@
--echo # (TL_WRITE_ALLOW_WRITE) on several instances of the same table and
--echo # statements which tried to acquire stronger write lock (TL_WRITE,
--echo # TL_WRITE_ALLOW_READ) on this table might have led to deadlock.
+--echo #
+--echo # QQ: What we should do with this test? With introduction of new
+--echo # types of locks this scenario becomes unrepeatable since InnoDB
+--echo # no longer acquire TL_WRITE lock which is not supported by
+--echo # semi-exclusive MDL lock.
+--disable_parsing
--disable_warnings
drop table if exists t1;
--enable_warnings
@@ -111,7 +117,7 @@ disconnect con_bug45143_3;
set debug_sync= 'RESET';
set @@global.general_log= @old_general_log;
drop table t1;
-
+--enable_parsing
# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
=== modified file 'mysql-test/t/mdl_sync.test'
--- a/mysql-test/t/mdl_sync.test 2010-01-21 20:43:03 +0000
+++ b/mysql-test/t/mdl_sync.test 2010-01-22 05:53:57 +0000
@@ -74,6 +74,2238 @@ 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 UNW lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail after opening the table and thus obtaining UNW metadata
+--echo # lock.
+--error ER_DUP_ENTRY
+alter table t1 add primary key (c1);
+--echo # Check that UNRW 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 UNW 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 UNRW 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 UNRW 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 UNW lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail after opening the table and thus obtaining UNW metadata
+--echo # lock.
+--error ER_DUP_ENTRY
+alter table t1 add primary key (c1);
+--echo # Check that UNRW 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 UNW 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 UNRW 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 UNRW 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 UNW lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail after opening the table and thus obtaining UNW metadata
+--echo # lock.
+--error ER_DUP_ENTRY
+alter table t1 add primary key (c1);
+--echo # Check that UNRW 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 UNW 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 UNRW to X is blocked
+--echo # by presence of SR lock because UNRW 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 UNW lock is not compatible with SW lock.
+--echo # Again we use ALTER TABLE which fails after opening
+--echo # the table to avoid upgrade of UNW -> 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 UNRW 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 UNW/UNRW to X is
+--echo # blocked by presence of SW lock because UNW/UNRW is incompatible
+--echo # with SW anyway.
+--echo #
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo #
+--echo # 5) Acquire UNW lock on the table. We have to use DEBUG_SYNC for
+--echo # this, to prevent UNW 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 UNW 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 UNW 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 UNW lock is incompatible with UNW 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 UNW 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 UNRW lock is incompatible with UNW 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 UNW 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 UNW 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 UNW 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 UNW/UNRW to X is
+--echo # blocked by presence of another UNW lock because UNW/UNRW is
+--echo # incompatible with UNW anyway.
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo #
+--echo #
+--echo # 6) Acquire UNRW 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 UNRW 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 UNRW 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 UNRW 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 UNRW 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 UNW lock is incompatible with UNRW 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 UNRW lock is incompatible with UNRW 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 UNRW 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 UNRW 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 UNRW 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 UNW/UNRW to X is
+--echo # blocked by presence of another UNRW lock because UNW/UNRW is
+--echo # incompatible with UNRW 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 UNW 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 UNRW 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 UNW lock.
+--echo #
+--echo # Acquire SW lock in order to create pending UNW lock later.
+begin;
+insert into t1 values (1);
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending UNW 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 UNW 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 UNW
+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 UNW
+--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 UNW 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 UNW, UNRW 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 UNRW lock.
+--echo #
+--echo # Acquire SR lock in order to create pending UNRW lock.
+begin;
+select count(*) from t1;
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Add pending UNRW 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 UNRW 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 UNRW
+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 UNRW
+--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 UNRW 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 UNRW 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 UNRW 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 UNRW
+--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 UNRW 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 UNRW 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 UNRW 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 UNW is incompatible with pending UNRW
+--echo # QQ: Should this be changed, to avoid starvation of ALTER TABLE?
+--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 pending UNRW 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 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 ALTER TABLE.
+--error ER_DUP_ENTRY
+--reap
+--echo #
+--echo # We can't do similar check for UNRW 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 UNW 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 UNRW 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 UNW 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 UNW lock on t2.
+--echo # We have to use DEBUG_SYNC facility as otherwise UNW 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 UNW 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 UNW 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 UNW 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 UNW 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 UNW 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 UNW 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 UNW 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 UNW -> X upgrade from
+--echo # completing immediately.
+begin;
+select count(*) from t2;
+--echo #
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create UNW 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 UNW -> X upgrade from
+--echo # completing immediately.
+begin;
+select count(*) from t2;
+--echo #
+--echo # Switching to connection 'mdl_con1'.
+connection mdl_con1;
+--echo # Create UNW 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 UNW 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 UNRW 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 UNRW 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 #
+--echo # TODO/FIXME: Add more test cases once MDL contexts for
+--echo # normal and HANDLER locks are merged so we
+--echo # can perform deadlock detection properly.
+--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 UNRW 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 UNRW 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 UNRW 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 # 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 #
@@ -328,12 +2560,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 +2613,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;
@@ -615,19 +2796,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 +2849,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 +2891,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 +2934,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 +2998,7 @@ create table t1 (c1 int primary key, c2
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 +3010,12 @@ set debug_sync='after_lock_tables_takes_
--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
=== modified file 'mysql-test/t/multi_update.test'
--- a/mysql-test/t/multi_update.test 2009-12-03 20:08:27 +0000
+++ b/mysql-test/t/multi_update.test 2010-01-22 05:53:57 +0000
@@ -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 def
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;
=== modified file 'mysql-test/t/truncate_coverage.test'
--- a/mysql-test/t/truncate_coverage.test 2009-12-11 12:24:23 +0000
+++ b/mysql-test/t/truncate_coverage.test 2010-01-22 05:53:57 +0000
@@ -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
=== modified file 'sql/mdl.cc'
--- a/sql/mdl.cc 2010-01-21 20:43:03 +0000
+++ b/sql/mdl.cc 2010-01-22 05:53:57 +0000
@@ -19,6 +19,10 @@
#include <hash.h>
#include <mysqld_error.h>
+
+void notify_shared_lock(THD *thd, MDL_ticket *conflicting_ticket);
+
+
static bool mdl_initialized= 0;
@@ -71,18 +75,6 @@ public:
public:
/** The key of the object (data) being protected. */
MDL_key key;
- /** List of granted tickets for this lock. */
- Ticket_list granted;
- /** Tickets for contexts waiting to acquire a shared lock. */
- Ticket_list waiting_shared;
- /**
- Tickets for contexts waiting to acquire an exclusive lock.
- There can be several upgraders and active exclusive
- locks belonging to the same context. E.g.
- in case of RENAME t1 to t2, t2 to t3, we attempt to
- exclusively lock t2 twice.
- */
- Ticket_list waiting_exclusive;
void *cached_object;
mdl_cached_object_release_hook cached_object_release_hook;
/** Mutex protecting this lock context. */
@@ -90,24 +82,112 @@ public:
bool is_empty() const
{
- return (granted.is_empty() && waiting_shared.is_empty() &&
- waiting_exclusive.is_empty());
+ return (granted.is_empty() && waiting.is_empty());
}
- bool has_pending_exclusive_lock()
- {
- bool has_locks;
- pthread_mutex_lock(&m_mutex);
- has_locks= ! waiting_exclusive.is_empty();
- pthread_mutex_unlock(&m_mutex);
- return has_locks;
- }
+ bool has_pending_conflicting_lock(enum_mdl_type type);
+
virtual bool can_grant_lock(const MDL_context *requestor_ctx,
enum_mdl_type type, bool is_upgrade)= 0;
- virtual void wake_up_waiters()= 0;
inline static MDL_lock *create(const MDL_key *key);
+ bool has_pending_lock(enum_mdl_type type) const
+ {
+ Ticket_iterator it(waiting);
+ MDL_ticket *ticket;
+
+ safe_mutex_assert_owner(&m_mutex);
+
+ /*
+ QQ: Should we add counters for pending/granted lock
+ types to speed up this and other similar methods?
+ */
+
+ while ((ticket= it++))
+ if (ticket->m_type == type)
+ return TRUE;
+ return FALSE;
+ }
+
+ bool has_granted_lock(enum_mdl_type type) const
+ {
+ Ticket_iterator it(granted);
+ MDL_ticket *ticket;
+
+ safe_mutex_assert_owner(&m_mutex);
+
+ while ((ticket= it++))
+ if (ticket->m_type == type)
+ return TRUE;
+ return FALSE;
+ }
+
+ void add_pending(MDL_ticket *ticket)
+ {
+ safe_mutex_assert_owner(&m_mutex);
+
+ waiting.push_front(ticket);
+ }
+
+ void remove_pending(MDL_ticket *ticket)
+ {
+ safe_mutex_assert_owner(&m_mutex);
+
+ waiting.remove(ticket);
+ }
+
+ void add_granted(MDL_ticket *ticket)
+ {
+ safe_mutex_assert_owner(&m_mutex);
+
+ granted.push_front(ticket);
+ }
+
+ void remove_granted(MDL_ticket *ticket)
+ {
+ safe_mutex_assert_owner(&m_mutex);
+
+ granted.remove(ticket);
+ }
+
+ void notify_shared_locks(MDL_context *ctx)
+ {
+ Ticket_iterator it(granted);
+ MDL_ticket *conflicting_ticket;
+
+ safe_mutex_assert_owner(&m_mutex);
+
+ while ((conflicting_ticket= it++))
+ {
+ if (conflicting_ticket->m_ctx != ctx)
+ notify_shared_lock(ctx->get_thd(), conflicting_ticket);
+ }
+ }
+
+ /**
+ Wake up contexts which are waiting to acquire lock on the object and
+ which may succeed now, when we released some lock on it or removed
+ some pending request from its waiters list (the latter can happen,
+ for example, when context trying to acquire exclusive on the object
+ lock is killed).
+ */
+ void wake_up_waiters()
+ {
+ MDL_lock::Ticket_iterator it(waiting);
+ MDL_ticket *awake_ticket;
+
+ safe_mutex_assert_owner(&m_mutex);
+
+ while ((awake_ticket= it++))
+ awake_ticket->get_ctx()->awake();
+ }
+
+ /** List of granted tickets for this lock. */
+ Ticket_list granted;
+ /** Tickets for contexts waiting to acquire a lock. */
+ Ticket_list waiting;
+
MDL_lock(const MDL_key *key_arg)
: key(key_arg),
cached_object(NULL),
@@ -166,7 +246,6 @@ public:
virtual bool can_grant_lock(const MDL_context *requestor_ctx,
enum_mdl_type type, bool is_upgrade);
- virtual void wake_up_waiters();
};
@@ -184,7 +263,6 @@ public:
virtual bool can_grant_lock(const MDL_context *requestor_ctx,
enum_mdl_type type, bool is_upgrade);
- virtual void wake_up_waiters();
};
@@ -663,28 +741,34 @@ static inline void mdl_exit_cond(THD *th
@retval TRUE - Lock request can be satisfied
@retval FALSE - There is some conflicting lock
- Here is a compatibility matrix defined by this function:
+ Here is how types of individual locks are translated to type of global lock:
- | | Satisfied or pending requests
- | | for global metadata lock
- ----------------+-------------+--------------------------------------------
+ ----------------+-------------+
Type of request | Correspond. |
- for indiv. lock | global lock | Active-S Pending-S Active-IS(**) Active-IX
- ----------------+-------------+--------------------------------------------
- S, high-prio S | IS | + + + +
- upgradable S | IX | - - + +
- X | IX | - - + +
- S upgraded to X | IX (*) | 0 + + +
+ for indiv. lock | global lock |
+ ----------------+-------------+
+ S, SH, SR, SW | IS |
+ UNW, UNRW, X | IX |
+ UNW, UNRW -> X | IX (*) |
+
+ And here is a compatibility matrix for types of global lock:
+
+ | State of MDL_global_lock |
+ Request | Active | Pending |
+ type | IS IX S | IS(**) IX S |
+ ---------+------------+--------------+
+ IS | + + + | + + + |
+ IX | + + - | + + - |
+ S | + - + | + + + |
Here: "+" -- means that request can be satisfied
"-" -- means that request can't be satisfied and should wait
- "0" -- means impossible situation.
- (*) Since for upgradable shared locks we always take intention exclusive
- global lock at the same time when obtaining the shared lock, there
- is no need to obtain such lock during the upgrade itself.
- (**) Since intention shared global locks are compatible with all other
- type of locks we don't even have any accounting for them.
+ (*) Since for upgradable locks we always take intention exclusive global
+ lock at the same time when obtaining the shared lock, there is no
+ need to obtain such lock during the upgrade itself.
+ (**) Since intention shared global locks are compatible with all other
+ type of locks we don't even have any accounting for them.
*/
bool
@@ -707,7 +791,7 @@ MDL_global_lock::can_grant_lock(const MD
break;
case MDL_INTENTION_EXCLUSIVE:
if ((! granted.is_empty() && granted.front()->m_type == MDL_SHARED) ||
- ! waiting_shared.is_empty())
+ has_pending_lock(MDL_SHARED))
{
/*
We are going to obtain intention exclusive global lock and
@@ -727,87 +811,42 @@ MDL_global_lock::can_grant_lock(const MD
/**
- Wake up contexts which are waiting to acquire the global
- metadata lock and which may succeed now, when we released it, or
- removed a blocking request for it from the waiters list.
- The latter can happen when the context trying to acquire the
- global shared lock is killed.
-*/
-
-void MDL_global_lock::wake_up_waiters()
-{
- /*
- If there are no active locks or they are of INTENTION
- EXCLUSIVE type and there are no pending requests for global
- SHARED lock, wake up contexts waiting for an INTENTION
- EXCLUSIVE lock.
- This happens when we release the global SHARED lock or abort
- or remove a pending request for it, i.e. abort the
- context waiting for it.
- */
- if ((granted.is_empty() ||
- granted.front()->m_type == MDL_INTENTION_EXCLUSIVE) &&
- waiting_shared.is_empty() && ! waiting_exclusive.is_empty())
- {
- MDL_lock::Ticket_iterator it(waiting_exclusive);
- MDL_ticket *awake_ticket;
- while ((awake_ticket= it++))
- awake_ticket->get_ctx()->awake();
- }
-
- /*
- If there are no active locks, wake up contexts waiting for
- the global shared lock (happens when an INTENTION EXCLUSIVE
- lock is released).
-
- We don't wake up contexts waiting for the global shared lock
- if there is an active global shared lock since such situation
- is transient and in it contexts marked as waiting for global
- shared lock must be already woken up and simply have not
- managed to update lock object yet.
- */
- if (granted.is_empty() &&
- ! waiting_shared.is_empty())
- {
- MDL_lock::Ticket_iterator it(waiting_shared);
- MDL_ticket *awake_ticket;
- while ((awake_ticket= it++))
- awake_ticket->get_ctx()->awake();
- }
-}
-
-
-/**
Check if request for the per-object lock can be satisfied given current
state of the lock.
@param requestor_ctx The context that identifies the owner of the request.
@param type_arg The requested lock type.
- @param is_upgrade Must be set to TRUE when we are upgrading
- a shared upgradable lock to exclusive.
+ @param is_upgrade Must be set to TRUE when we are upgrading an
+ upgradable lock to exclusive.
@retval TRUE Lock request can be satisfied
@retval FALSE There is some conflicting lock.
This function defines the following compatibility matrix for metadata locks:
- | Satisfied or pending requests which we have in MDL_lock
- ----------------+---------------------------------------------------------
- Current request | Active-S Pending-X Active-X Act-S-pend-upgrade-to-X
- ----------------+---------------------------------------------------------
- S, upgradable S | + - - (*) -
- High-prio S | + + - +
- X | - + - -
- S upgraded to X | - (**) + 0 0
+ | Satisfied or pending requests for the MDL_lock |
+ Request | Active | Pending |
+ type | S SH SR SW UNW UNRW X | S SH SR SW UNW UNRW X |
+ ----------+------------------------------+-----------------------------+
+ S | + + + + + + - | + + + + + + - |
+ SH | + + + + + + - | + + + + + + + |
+ SR | + + + + + - - | + + + + + - - |
+ SW | + + + + - - - | + + + + - - - |
+ UNW | + + + - - - - | + + + + +? - -?|
+ UNRW | + + - - - - - | + + + + +? + -?|
+ X | - - - - - - - | + + + + +? + +?|
+ UNW -> X | - - - 0 0 0 0 | + + + + + + + |
+ UNRW -> X | - - 0 0 0 0 0 | + + + + + + + |
Here: "+" -- means that request can be satisfied
"-" -- means that request can't be satisfied and should wait
"0" -- means impossible situation which will trigger assert
- (*) Unless active exclusive lock belongs to the same context as shared
- lock being requested.
- (**) Unless all active shared locks belong to the same context as one
- being upgraded.
+ ?) Choice for these cases is actually questionable. QQ?
+
+ @note In cases then current context already has "stronger" type
+ of lock on the object it will be automatically granted
+ thanks to usage of the MDL_context::find_ticket() method.
*/
bool
@@ -819,44 +858,83 @@ MDL_object_lock::can_grant_lock(const MD
switch (type_arg) {
case MDL_SHARED:
- case MDL_SHARED_UPGRADABLE:
case MDL_SHARED_HIGH_PRIO:
if (granted.is_empty() || granted.front()->is_shared())
{
/* Pending exclusive locks have higher priority over shared locks. */
- if (waiting_exclusive.is_empty() || type_arg == MDL_SHARED_HIGH_PRIO)
+ if (! has_pending_lock(MDL_EXCLUSIVE) || type_arg == MDL_SHARED_HIGH_PRIO)
can_grant= TRUE;
}
- else if (granted.front()->get_ctx() == requestor_ctx)
+ break;
+ case MDL_SHARED_READ:
+ if (granted.is_empty() ||
+ (granted.front()->is_shared() &&
+ ! has_granted_lock(MDL_UPGRADABLE_NO_READ_WRITE)))
{
- /*
- When exclusive lock comes from the same context we can satisfy our
- shared lock. This is required for CREATE TABLE ... SELECT ... and
- ALTER VIEW ... AS ....
- */
+ if (! has_pending_lock(MDL_EXCLUSIVE) &&
+ ! has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE))
+ can_grant= TRUE;
+ }
+ break;
+ case MDL_SHARED_WRITE:
+ if (granted.is_empty() ||
+ (granted.front()->is_shared() &&
+ ! has_granted_lock(MDL_UPGRADABLE_NO_WRITE) &&
+ ! has_granted_lock(MDL_UPGRADABLE_NO_READ_WRITE)))
+ {
+ if (! has_pending_lock(MDL_EXCLUSIVE) &&
+ ! has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE) &&
+ ! has_pending_lock(MDL_UPGRADABLE_NO_WRITE))
+ can_grant= TRUE;
+ }
+ break;
+ case MDL_UPGRADABLE_NO_WRITE:
+ if (granted.is_empty() ||
+ (granted.front()->is_shared() &&
+ ! has_granted_lock(MDL_UPGRADABLE_NO_WRITE) &&
+ ! has_granted_lock(MDL_UPGRADABLE_NO_READ_WRITE)))
+ {
+ if (! has_pending_lock(MDL_EXCLUSIVE) &&
+ ! has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE) &&
+ ! has_granted_lock(MDL_SHARED_WRITE))
can_grant= TRUE;
}
break;
+ case MDL_UPGRADABLE_NO_READ_WRITE:
+ if (granted.is_empty() ||
+ (granted.front()->is_shared() &&
+ ! has_granted_lock(MDL_UPGRADABLE_NO_WRITE) &&
+ ! has_granted_lock(MDL_UPGRADABLE_NO_READ_WRITE)))
+ {
+ if (! has_pending_lock(MDL_EXCLUSIVE) &&
+ ! has_granted_lock(MDL_SHARED_READ) &&
+ ! has_granted_lock(MDL_SHARED_WRITE))
+ can_grant= TRUE;
+ }
+ break;
case MDL_EXCLUSIVE:
if (is_upgrade)
{
- /* We are upgrading MDL_SHARED to MDL_EXCLUSIVE. */
MDL_ticket *conflicting_ticket;
MDL_lock::Ticket_iterator it(granted);
+ DBUG_ASSERT(has_granted_lock(MDL_UPGRADABLE_NO_WRITE) ||
+ has_granted_lock(MDL_UPGRADABLE_NO_READ_WRITE));
+
/*
- There should be no active exclusive locks since we own shared lock
- on the object.
+ Check if there are other contexts which hold locks on this object.
+
+ Note that thanks to the fact that UNW and UNRW locks are always
+ acquired before shared locks the below check is actually equivalent
+ to checking if MDL_lock has any active tickets besides of ticket
+ being upgraded.
+
+ QQ: Should we change API to pass ticket being upgraded instead of
+ "requestor_ctx" to simplify the below check?
*/
- DBUG_ASSERT(granted.front()->is_shared());
while ((conflicting_ticket= it++))
{
- /*
- When upgrading shared lock to exclusive one we can have other shared
- locks for the same object in the same context, e.g. in case when several
- instances of TABLE are open.
- */
if (conflicting_ticket->get_ctx() != requestor_ctx)
break;
}
@@ -882,40 +960,47 @@ MDL_object_lock::can_grant_lock(const MD
/**
- Wake up contexts which are waiting to acquire lock on individual object
- and which may succeed now, when we released some lock on it or removed
- some pending request from its waiters list (the latter can happen, for
- example, when context trying to acquire exclusive lock is killed).
+ Check if we have any pending locks which conflict with existing
+ shared lock.
+
+ @pre The ticket must match an acquired lock.
+
+ @return TRUE if there is a conflicting lock request, FALSE otherwise.
*/
-void MDL_object_lock::wake_up_waiters()
+bool MDL_lock::has_pending_conflicting_lock(enum_mdl_type type)
{
- /*
- There are no active locks or they are of shared type.
- We have to wake up contexts waiting for shared lock even if there is
- a pending exclusive lock as some them might be trying to acquire high
- priority shared lock.
- */
- if ((granted.is_empty() || granted.front()->is_shared()) &&
- ! waiting_shared.is_empty())
- {
- MDL_lock::Ticket_iterator it(waiting_shared);
- MDL_ticket *waiting_ticket;
- while ((waiting_ticket= it++))
- waiting_ticket->get_ctx()->awake();
- }
+ bool result;
- /*
- There are no active locks (shared or exclusive).
- Wake up contexts waiting to acquire exclusive locks.
- */
- if (granted.is_empty() && ! waiting_exclusive.is_empty())
+ safe_mutex_assert_not_owner(&LOCK_open);
+
+ pthread_mutex_lock(&m_mutex);
+ switch (type)
{
- MDL_lock::Ticket_iterator it(waiting_exclusive);
- MDL_ticket *waiting_ticket;
- while ((waiting_ticket= it++))
- waiting_ticket->get_ctx()->awake();
+ case MDL_SHARED:
+ case MDL_SHARED_HIGH_PRIO:
+ result= has_pending_lock(MDL_EXCLUSIVE);
+ break;
+ case MDL_SHARED_READ:
+ result= has_pending_lock(MDL_EXCLUSIVE) ||
+ has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE);
+ break;
+ case MDL_SHARED_WRITE:
+ result= has_pending_lock(MDL_EXCLUSIVE) ||
+ has_pending_lock(MDL_UPGRADABLE_NO_WRITE) ||
+ has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE);
+ break;
+ case MDL_UPGRADABLE_NO_WRITE:
+ case MDL_UPGRADABLE_NO_READ_WRITE:
+ case MDL_INTENTION_EXCLUSIVE:
+ case MDL_EXCLUSIVE:
+ default:
+ /* This method should not be used for these types of tickets. */
+ DBUG_ASSERT(0);
+ break;
}
+ pthread_mutex_unlock(&m_mutex);
+ return result;
}
@@ -930,6 +1015,9 @@ void MDL_object_lock::wake_up_waiters()
@param[out] is_lt_or_ha Did we pass beyond m_lt_or_ha_sentinel while
searching for ticket?
+ @note Tickets which correspond to lock types "stronger" than one
+ being requested are also considered compatible.
+
@return A pointer to the lock ticket for the object or NULL otherwise.
*/
@@ -947,7 +1035,7 @@ MDL_context::find_ticket(MDL_request *md
if (ticket == m_lt_or_ha_sentinel)
*is_lt_or_ha= TRUE;
- if (mdl_request->type == ticket->m_type &&
+ if (mdl_request->type <= ticket->m_type &&
mdl_request->key.is_equal(&ticket->m_lock->key))
break;
}
@@ -1038,10 +1126,7 @@ MDL_context::acquire_lock_impl(MDL_reque
if (! lock->can_grant_lock(this, mdl_request->type, FALSE))
{
- if (mdl_request->is_shared())
- lock->waiting_shared.push_front(ticket);
- else
- lock->waiting_exclusive.push_front(ticket);
+ lock->add_pending(ticket);
do
{
@@ -1061,10 +1146,7 @@ MDL_context::acquire_lock_impl(MDL_reque
pthread_mutex_lock(&lock->m_mutex);
/* Get rid of pending ticket. */
- if (mdl_request->is_shared())
- lock->waiting_shared.remove(ticket);
- else
- lock->waiting_exclusive.remove(ticket);
+ lock->remove_pending(ticket);
if (lock->is_empty())
mdl_locks.remove(lock);
else
@@ -1075,14 +1157,10 @@ MDL_context::acquire_lock_impl(MDL_reque
MDL_ticket::destroy(ticket);
return TRUE;
}
-
- if (mdl_request->is_shared())
- lock->waiting_shared.remove(ticket);
- else
- lock->waiting_exclusive.remove(ticket);
+ lock->remove_pending(ticket);
}
- lock->granted.push_front(ticket);
+ lock->add_granted(ticket);
MDL_EXIT_COND(m_thd, mysys_var, &lock->m_mutex, old_msg);
ticket->m_state= MDL_ACQUIRED;
@@ -1165,7 +1243,7 @@ MDL_context::try_acquire_lock_impl(MDL_r
if ((ticket= find_ticket(mdl_request, &is_lt_or_ha)))
{
DBUG_ASSERT(ticket->m_state == MDL_ACQUIRED);
- DBUG_ASSERT(ticket->m_type == mdl_request->type);
+ DBUG_ASSERT(ticket->m_type >= mdl_request->type);
/*
If the request is for a transactional lock, and we found
a transactional lock, just reuse the found ticket.
@@ -1252,7 +1330,8 @@ bool
MDL_context::try_acquire_shared_lock(MDL_request *mdl_request)
{
DBUG_ASSERT(mdl_request->is_shared());
- DBUG_ASSERT(mdl_request->type != MDL_SHARED_UPGRADABLE ||
+ DBUG_ASSERT((mdl_request->type != MDL_UPGRADABLE_NO_WRITE &&
+ mdl_request->type != MDL_UPGRADABLE_NO_READ_WRITE) ||
is_global_lock_owner(MDL_INTENTION_EXCLUSIVE));
return try_acquire_lock_impl(mdl_request);
@@ -1289,7 +1368,7 @@ MDL_context::clone_ticket(MDL_request *m
mdl_request->ticket= ticket;
pthread_mutex_lock(&ticket->m_lock->m_mutex);
- ticket->m_lock->granted.push_front(ticket);
+ ticket->m_lock->add_granted(ticket);
pthread_mutex_unlock(&ticket->m_lock->m_mutex);
m_tickets.push_front(ticket);
@@ -1309,19 +1388,21 @@ void notify_shared_lock(THD *thd, MDL_ti
{
if (conflicting_ticket->is_shared())
{
- THD *conflicting_thd= conflicting_ticket->get_ctx()->get_thd();
+ MDL_context *conflicting_ctx= conflicting_ticket->get_ctx();
+ THD *conflicting_thd= conflicting_ctx->get_thd();
DBUG_ASSERT(thd != conflicting_thd); /* Self-deadlock */
/*
If the thread that holds the conflicting lock is waiting in MDL
subsystem it has to be woken up by calling MDL_context::awake().
*/
- conflicting_ticket->get_ctx()->awake();
+ conflicting_ctx->awake();
/*
If it is waiting on table-level lock or some other non-MDL resource
we delegate its waking up to code outside of MDL.
*/
- mysql_notify_thread_having_shared_lock(thd, conflicting_thd);
+ mysql_notify_thread_having_shared_lock(thd, conflicting_thd,
+ conflicting_ticket->get_needs_thr_lock_abort());
}
}
@@ -1349,7 +1430,9 @@ bool MDL_context::acquire_exclusive_lock
st_my_thread_var *mysys_var= my_thread_var;
MDL_key *key= &mdl_request->key;
- DBUG_ASSERT(mdl_request->type == MDL_EXCLUSIVE &&
+ DBUG_ASSERT((mdl_request->type == MDL_EXCLUSIVE ||
+ mdl_request->type == MDL_UPGRADABLE_NO_READ_WRITE ||
+ mdl_request->type == MDL_UPGRADABLE_NO_WRITE) &&
mdl_request->ticket == NULL);
safe_mutex_assert_not_owner(&LOCK_open);
@@ -1364,7 +1447,6 @@ bool MDL_context::acquire_exclusive_lock
if ((ticket= find_ticket(mdl_request, ¬_used)))
{
DBUG_ASSERT(ticket->m_state == MDL_ACQUIRED);
- DBUG_ASSERT(ticket->m_type == MDL_EXCLUSIVE);
mdl_request->ticket= ticket;
return FALSE;
}
@@ -1382,7 +1464,7 @@ bool MDL_context::acquire_exclusive_lock
return TRUE;
}
- lock->waiting_exclusive.push_front(ticket);
+ lock->add_pending(ticket);
old_msg= MDL_ENTER_COND(m_thd, mysys_var, &m_ctx_wakeup_cond,
&lock->m_mutex);
@@ -1404,7 +1486,7 @@ bool MDL_context::acquire_exclusive_lock
pthread_mutex_lock(&lock->m_mutex);
/* Get rid of pending ticket. */
- lock->waiting_exclusive.remove(ticket);
+ lock->remove_pending(ticket);
if (lock->is_empty())
mdl_locks.remove(lock);
else
@@ -1421,11 +1503,7 @@ bool MDL_context::acquire_exclusive_lock
return TRUE;
}
- MDL_ticket *conflicting_ticket;
- MDL_lock::Ticket_iterator it(lock->granted);
-
- while ((conflicting_ticket= it++))
- notify_shared_lock(m_thd, conflicting_ticket);
+ lock->notify_shared_locks(this);
/* There is a shared or exclusive lock on the object. */
DEBUG_SYNC(m_thd, "mdl_acquire_exclusive_locks_wait");
@@ -1458,7 +1536,7 @@ bool MDL_context::acquire_exclusive_lock
pthread_mutex_lock(&lock->m_mutex);
/* Get rid of pending ticket. */
- lock->waiting_exclusive.remove(ticket);
+ lock->remove_pending(ticket);
if (lock->is_empty())
mdl_locks.remove(lock);
else
@@ -1475,8 +1553,8 @@ bool MDL_context::acquire_exclusive_lock
}
}
- lock->waiting_exclusive.remove(ticket);
- lock->granted.push_front(ticket);
+ lock->remove_pending(ticket);
+ lock->add_granted(ticket);
if (lock->cached_object)
(*lock->cached_object_release_hook)(lock->cached_object);
@@ -1549,6 +1627,9 @@ bool MDL_context::acquire_exclusive_lock
MDL_request **sort_buf;
uint i;
+ if (mdl_requests->is_empty())
+ return FALSE;
+
/*
Exclusive locks must always be acquired first, all at once.
*/
@@ -1556,9 +1637,6 @@ bool MDL_context::acquire_exclusive_lock
m_tickets.front()->m_lock->key.mdl_namespace() == MDL_key::GLOBAL &&
++Ticket_list::Iterator(m_tickets) == m_lt_or_ha_sentinel);
- if (mdl_requests->is_empty())
- return FALSE;
-
/* Sort requests according to MDL_key. */
if (! (sort_buf= (MDL_request **)my_malloc(mdl_requests->elements() *
sizeof(MDL_request *),
@@ -1628,8 +1706,9 @@ MDL_ticket::upgrade_shared_lock_to_exclu
if (m_type == MDL_EXCLUSIVE)
DBUG_RETURN(FALSE);
- /* Only allow upgrades from MDL_SHARED_UPGRADABLE */
- DBUG_ASSERT(m_type == MDL_SHARED_UPGRADABLE);
+ /* Only allow upgrades from MDL_UPGRADABLE_NO_WRITE/NO_READ_WRITE */
+ DBUG_ASSERT(m_type == MDL_UPGRADABLE_NO_WRITE ||
+ m_type == MDL_UPGRADABLE_NO_READ_WRITE);
/*
Since we should have already acquired an intention exclusive
@@ -1650,7 +1729,7 @@ MDL_ticket::upgrade_shared_lock_to_exclu
pthread_mutex_lock(&m_lock->m_mutex);
- m_lock->waiting_exclusive.push_front(pending_ticket);
+ m_lock->add_pending(pending_ticket);
old_msg= MDL_ENTER_COND(thd, mysys_var, &m_ctx->m_ctx_wakeup_cond,
&m_lock->m_mutex);
@@ -1660,61 +1739,7 @@ MDL_ticket::upgrade_shared_lock_to_exclu
if (m_lock->can_grant_lock(m_ctx, MDL_EXCLUSIVE, TRUE))
break;
- MDL_ticket *conflicting_ticket;
- MDL_lock::Ticket_iterator it(m_lock->granted);
-
- /*
- If m_ctx->lt_or_ha_sentinel(), and this sentinel is for HANDLER,
- we can deadlock. However, HANDLER is not allowed under
- LOCK TABLES, and apart from LOCK TABLES there are only
- two cases of lock upgrade: ALTER TABLE and CREATE/DROP
- TRIGGER (*). This leaves us with the following scenario
- for deadlock:
-
- connection 1 connection 2
- handler t1 open; handler t2 open;
- alter table t2 ... alter table t1 ...
-
- This scenario is quite remote, since ALTER
- (and CREATE/DROP TRIGGER) performs mysql_ha_flush() in
- the beginning, and thus closes open HANDLERS against which
- there is a pending lock upgrade. Still, two ALTER statements
- can interleave and not notice each other's pending lock
- (e.g. if both upgrade their locks at the same time).
- This, however, is quite unlikely, so we do nothing to
- address it.
-
- (*) There is no requirement to upgrade lock in
- CREATE/DROP TRIGGER, it's used there just for convenience.
-
- A temporary work-around to avoid deadlocks/livelocks in
- a situation when in one connection ALTER TABLE tries to
- upgrade its metadata lock and in another connection
- the active transaction already got this lock in some
- of its earlier statements.
- In such case this transaction always succeeds with getting
- a metadata lock on the table -- it already has one.
- But later on it may block on the table level lock, since ALTER
- got TL_WRITE_ALLOW_READ, and subsequently get aborted
- by notify_shared_lock().
- An abort will lead to a back off, and a second attempt to
- get an MDL lock (successful), and a table lock (-> livelock).
-
- The call below breaks this loop by forcing transactions to call
- tdc_wait_for_old_versions() (even if the transaction doesn't need
- any new metadata locks), which in turn will check if someone
- is waiting on the owned MDL lock, and produce ER_LOCK_DEADLOCK.
-
- TODO: Long-term such deadlocks/livelock will be resolved within
- MDL subsystem and thus this call will become unnecessary.
- */
- mysql_abort_transactions_with_shared_lock(&m_lock->key);
-
- while ((conflicting_ticket= it++))
- {
- if (conflicting_ticket->m_ctx != m_ctx)
- notify_shared_lock(thd, conflicting_ticket);
- }
+ m_lock->notify_shared_locks(m_ctx);
/* There is a shared or exclusive lock on the object. */
DEBUG_SYNC(thd, "mdl_upgrade_shared_lock_to_exclusive_wait");
@@ -1737,7 +1762,7 @@ MDL_ticket::upgrade_shared_lock_to_exclu
if (mysys_var->abort)
{
- m_lock->waiting_exclusive.remove(pending_ticket);
+ m_lock->remove_pending(pending_ticket);
/*
If there are no other pending requests for exclusive locks
we need to wake up threads waiting for a chance to acquire
@@ -1753,8 +1778,7 @@ MDL_ticket::upgrade_shared_lock_to_exclu
/* Set the new type of lock in the ticket. */
m_type= MDL_EXCLUSIVE;
- /* Remove and destroy the auxiliary pending ticket. */
- m_lock->waiting_exclusive.remove(pending_ticket);
+ m_lock->remove_pending(pending_ticket);
if (m_lock->cached_object)
(*m_lock->cached_object_release_hook)(m_lock->cached_object);
@@ -1947,10 +1971,7 @@ MDL_context::wait_for_locks(MDL_request_
pthread_mutex_unlock(&lock->m_mutex);
return TRUE;
}
- if (mdl_request->is_shared())
- lock->waiting_shared.push_front(pending_ticket);
- else
- lock->waiting_exclusive.push_front(pending_ticket);
+ lock->add_pending(pending_ticket);
old_msg= MDL_ENTER_COND(m_thd, mysys_var, &m_ctx_wakeup_cond,
&lock->m_mutex);
@@ -1965,10 +1986,7 @@ MDL_context::wait_for_locks(MDL_request_
MDL_EXIT_COND(m_thd, mysys_var, &lock->m_mutex, old_msg);
pthread_mutex_lock(&lock->m_mutex);
- if (mdl_request->is_shared())
- lock->waiting_shared.remove(pending_ticket);
- else
- lock->waiting_exclusive.remove(pending_ticket);
+ lock->remove_pending(pending_ticket);
if (lock->is_empty())
mdl_locks.remove(lock);
else
@@ -2008,7 +2026,7 @@ void MDL_context::release_lock(MDL_ticke
pthread_mutex_lock(&lock->m_mutex);
- lock->granted.remove(ticket);
+ lock->remove_granted(ticket);
if (lock->is_empty())
mdl_locks.remove(lock);
@@ -2109,16 +2127,15 @@ void MDL_ticket::downgrade_exclusive_loc
return;
pthread_mutex_lock(&m_lock->m_mutex);
- m_type= MDL_SHARED_UPGRADABLE;
-
- if (! m_lock->waiting_shared.is_empty())
- {
- MDL_lock::Ticket_iterator it(m_lock->waiting_shared);
- MDL_ticket *ticket;
- while ((ticket= it++))
- ticket->get_ctx()->awake();
- }
+ /*
+ UNRW type of lock is used here since downgrade of metadata locks
+ happens in most cases (QQ) under LOCK TABLES.
+ QQ: What about CREATE TABLE SELECT, what kind of lock should we
+ use there?
+ */
+ m_type= MDL_UPGRADABLE_NO_READ_WRITE;
+ m_lock->wake_up_waiters();
pthread_mutex_unlock(&m_lock->m_mutex);
}
@@ -2206,8 +2223,7 @@ MDL_context::is_lock_owner(MDL_key::enum
/**
- Check if we have any pending exclusive locks which conflict with
- existing shared lock.
+ Check if we have any pending locks which conflict with existing shared lock.
@pre The ticket must match an acquired lock.
@@ -2216,10 +2232,8 @@ MDL_context::is_lock_owner(MDL_key::enum
bool MDL_ticket::has_pending_conflicting_lock() const
{
- safe_mutex_assert_not_owner(&LOCK_open);
- DBUG_ASSERT(is_shared());
-
- return m_lock->has_pending_exclusive_lock();
+ DBUG_ASSERT(m_state== MDL_ACQUIRED);
+ return m_lock->has_pending_conflicting_lock(m_type);
}
=== modified file 'sql/mdl.h'
--- a/sql/mdl.h 2010-01-21 20:43:03 +0000
+++ b/sql/mdl.h 2010-01-22 05:53:57 +0000
@@ -30,19 +30,117 @@ class MDL_ticket;
/**
Type of metadata lock request.
- - High-priority shared locks differ from ordinary shared locks by
- that they ignore pending requests for exclusive locks.
- - Upgradable shared locks can be later upgraded to exclusive
- (because of that their acquisition involves implicit
- acquisition of global intention-exclusive lock).
-
@sa Comments for MDL_object_lock::can_grant_lock() and
- MDL_global_lock::can_grant_lock() for details.
+ MDL_global_lock::can_grant_lock() for details.
+
+ @note Order of types of locks is important as MDL_context::find_ticket()
+ we assume that "stronger" lock types correspond to enum elements
+ with greater numeric values.
*/
-enum enum_mdl_type {MDL_SHARED=0, MDL_SHARED_HIGH_PRIO,
- MDL_SHARED_UPGRADABLE, MDL_INTENTION_EXCLUSIVE,
- MDL_EXCLUSIVE};
+enum enum_mdl_type {
+ /*
+ A shared metadata lock.
+ To be used in cases when we are interested in object metadata only
+ and there is no intention to access object data (e.g. for stored
+ routines or during preparing prepared statements).
+ We also mis-use this type of lock for open HANDLERs, since lock
+ acquired by this statement has to be compatible with lock acquired
+ by LOCK TABLES ... WRITE statement, i.e. UNRW (We can't get by by
+ acquiring S lock at HANDLER ... OPEN time and upgrading it to SR
+ lock for HANDLER ... READ as it doesn't solve problem with need
+ to abort DML statements which wait on table level lock while having
+ open HANDLER in the same connection).
+ To avoid deadlock which may occur when UNRW lock is being upgraded to
+ X lock for table on which there is an active S lock which is owned by
+ thread which waits in its turn for table-level lock owned by thread
+ performing upgrade we have to use thr_abort_locks_for_thread()
+ facility in such situation.
+ This problem does not arise for locks on stored routines as we don't
+ use UNRW locks for them. It also does not arise when S locks are used
+ during PREPARE calls as table-level locks are not acquired in this
+ case.
+ */
+ MDL_SHARED= 0,
+ /*
+ A high priority shared metadata lock.
+ Used for cases when there is no intention to access object data (i.e.
+ data in the table).
+ "High priority" means that, unlike other shared locks, it is granted
+ ignoring pending requests for exclusive locks. Intended for use in
+ cases when we only need to access metadata and not data, e.g. when
+ filling an INFORMATION_SCHEMA table.
+ Since SH lock is compatible with UNRW lock, the connection that
+ holds SH lock lock should not try to acquire any kind of table-level
+ or row-level lock, as this can lead to a deadlock. Moreover, after
+ acquiring SH lock, the connection should not wait for any other
+ resource, as it might cause starvation for X locks and a potential
+ deadlock during upgrade of UNW or UNRW to X lock (e.g. if the
+ upgrading connection holds the resource that is being waited for).
+ */
+ MDL_SHARED_HIGH_PRIO,
+ /*
+ A shared metadata lock for cases when there is an intention to read data
+ from table.
+ A connection holding this kind of lock can read table metadata and read
+ table data (after acquiring appropriate table and row-level locks).
+ This means that one can only acquire TL_READ, TL_READ_NO_INSERT, and
+ similar table-level locks on table if one holds SR MDL lock on it.
+ To be used for tables in SELECTs, subqueries, and LOCK TABLE ... READ
+ statements.
+ */
+ MDL_SHARED_READ,
+ /*
+ A shared metadata lock for cases when there is an intention to modify
+ (and not just read) data in the table.
+ A connection holding SW lock can read table metadata and modify or read
+ table data (after acquiring appropriate table and row-level locks).
+ To be used for tables to be modified by INSERT, UPDATE, DELETE
+ statements, but not LOCK TABLE ... WRITE or DDL). Also taken by
+ SELECT ... FOR UPDATE.
+ */
+ MDL_SHARED_WRITE,
+ /*
+ An upgradable shared metadata lock which blocks all attempts to update
+ table data, allowing reads.
+ A connection holding this kind of lock can read table metadata and read
+ table data.
+ Can be upgraded to X metadata lock.
+ Note, that since this type of lock is not compatible with UNRW or SW
+ lock types, acquiring appropriate engine-level locks for reading
+ (TL_READ* for MyISAM, shared row locks in InnoDB) should be
+ contention-free.
+ To be used for the first phase of ALTER TABLE, when copying data between
+ tables, to allow concurrent SELECTs from the table, but not UPDATEs.
+ */
+ MDL_UPGRADABLE_NO_WRITE,
+ /*
+ An upgradable semi-exclusive metadata lock which allows other connections
+ to access table metadata, but not data.
+ It blocks all attempts to read or update table data, while allowing
+ INFORMATION_SCHEMA and SHOW queries.
+ A connection holding this kind of lock can read table metadata modify and
+ read table data.
+ Can be upgraded to X metadata lock.
+ To be used for LOCK TABLES WRITE statement.
+ Not compatible with any other lock type except S and SH.
+ */
+ MDL_UPGRADABLE_NO_READ_WRITE,
+ /*
+ An intention exclusive metadata lock. Used only for global locks.
+ Owner of this type of lock can acquire upgradable exclusive locks on
+ individual objects.
+ Compatible with other IX locks, but is incompatible with global S lock.
+ */
+ MDL_INTENTION_EXCLUSIVE,
+ /*
+ An exclusive metadata lock.
+ A connection holding this lock can modify both table's metadata and data.
+ No other type of metadata lock can be granted while this lock is held.
+ To be used for CREATE/DROP/RENAME TABLE statements and for execution of
+ certain phases of other DDL statements.
+ */
+ MDL_EXCLUSIVE};
/** States which a metadata lock ticket can have. */
@@ -300,12 +398,28 @@ public:
bool is_shared() const { return m_type < MDL_INTENTION_EXCLUSIVE; }
bool is_upgradable_or_exclusive() const
{
- return m_type == MDL_SHARED_UPGRADABLE || m_type == MDL_EXCLUSIVE;
+ return m_type == MDL_UPGRADABLE_NO_WRITE ||
+ m_type == MDL_UPGRADABLE_NO_READ_WRITE ||
+ m_type == MDL_EXCLUSIVE;
}
bool upgrade_shared_lock_to_exclusive();
void downgrade_exclusive_lock();
+ void set_needs_thr_lock_abort()
+ {
+ /*
+ QQ: In theory this should be done under protection of MDL_lock::m_mutex.
+ But it is not convinient in practice and probably does not matter.
+ Should we do anything about it?
+ */
+ m_needs_thr_lock_abort= TRUE;
+ }
+ bool get_needs_thr_lock_abort() const
+ {
+ return m_needs_thr_lock_abort;
+ }
private:
friend class MDL_context;
+ friend class MDL_lock;
friend class MDL_global_lock;
friend class MDL_object_lock;
@@ -313,7 +427,8 @@ private:
: m_type(type_arg),
m_state(MDL_PENDING),
m_ctx(ctx_arg),
- m_lock(NULL)
+ m_lock(NULL),
+ m_needs_thr_lock_abort(FALSE)
{}
static MDL_ticket *create(MDL_context *ctx_arg, enum_mdl_type type_arg);
@@ -331,6 +446,17 @@ private:
/** Pointer to the lock object for this lock ticket. Context private. */
MDL_lock *m_lock;
+ /**
+ TRUE - if for this ticket we will break protocol and try to
+ acquire table-level locks while having S lock on some
+ table.
+ To avoid deadlocks which might occur during concurrent
+ upgrade of UNRW lock on such object to X lock we have to
+ abort waits for table-level locks for such connections.
+ FALSE - Otherwise.
+
+ */
+ bool m_needs_thr_lock_abort;
private:
MDL_ticket(const MDL_ticket &); /* not implemented */
MDL_ticket &operator=(const MDL_ticket &); /* not implemented */
@@ -436,9 +562,11 @@ public:
bool is_global_lock_owner(enum_mdl_type type_arg)
{
MDL_request mdl_request;
+ MDL_ticket *ticket;
bool not_used;
mdl_request.init(MDL_key::GLOBAL, "", "", type_arg);
- return find_ticket(&mdl_request, ¬_used);
+ return (ticket= find_ticket(&mdl_request, ¬_used)) &&
+ (ticket->m_type == type_arg);
}
void init(THD *thd_arg) { m_thd= thd_arg; }
@@ -463,6 +591,7 @@ private:
queue of waiters).
*/
pthread_cond_t m_ctx_wakeup_cond;
+
private:
MDL_ticket *find_ticket(MDL_request *mdl_req,
bool *is_lt_or_ha);
@@ -473,6 +602,8 @@ private:
bool acquire_exclusive_lock_impl(MDL_request *mdl_request);
friend bool MDL_ticket::upgrade_shared_lock_to_exclusive();
+ friend void notify_shared_lock(THD *thd, MDL_ticket *conflicting_ticket);
+
private:
MDL_context(const MDL_context &rhs); /* not implemented */
MDL_context &operator=(MDL_context &rhs); /* not implemented */
@@ -487,9 +618,9 @@ void mdl_destroy();
Functions in the server's kernel used by metadata locking subsystem.
*/
-extern bool mysql_notify_thread_having_shared_lock(THD *thd, THD *in_use);
+extern bool mysql_notify_thread_having_shared_lock(THD *thd, THD *in_use,
+ bool needs_thr_lock_abort);
extern void mysql_ha_flush(THD *thd);
-extern void mysql_abort_transactions_with_shared_lock(const MDL_key *mdl_key);
extern "C" const char *set_thd_proc_info(THD *thd, const char *info,
const char *calling_function,
const char *calling_file,
=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h 2010-01-21 20:43:03 +0000
+++ b/sql/mysql_priv.h 2010-01-22 05:53:57 +0000
@@ -2078,6 +2078,8 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd,
#define MYSQL_OPEN_SKIP_TEMPORARY 0x0100
/** Fail instead of waiting when conficting metadata lock is discovered. */
#define MYSQL_OPEN_FAIL_ON_MDL_CONFLICT 0x0200
+/** Open tables using MDL_SHARED lock instead of one specified in parser. */
+#define MYSQL_OPEN_FORCE_SHARED_MDL 0x0400
/** Please refer to the internals manual. */
#define MYSQL_OPEN_REOPEN (MYSQL_LOCK_IGNORE_FLUSH |\
=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc 2010-01-12 11:32:55 +0000
+++ b/sql/sp_head.cc 2010-01-22 05:53:57 +0000
@@ -3966,7 +3966,8 @@ sp_head::add_used_tables_to_table_list(T
table->belong_to_view= belong_to_view;
table->trg_event_map= stab->trg_event_map;
table->mdl_request.init(MDL_key::TABLE, table->db, table->table_name,
- MDL_SHARED);
+ table->lock_type >= TL_WRITE_ALLOW_WRITE ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
/* Everyting else should be zeroed */
@@ -4009,7 +4010,8 @@ sp_add_to_query_tables(THD *thd, LEX *le
table->select_lex= lex->current_select;
table->cacheable_table= 1;
table->mdl_request.init(MDL_key::TABLE, table->db, table->table_name,
- MDL_SHARED);
+ table->lock_type >= TL_WRITE_ALLOW_WRITE ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
lex->add_to_query_tables(table);
return table;
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2010-01-21 20:43:03 +0000
+++ b/sql/sql_base.cc 2010-01-22 05:53:57 +0000
@@ -2369,20 +2369,46 @@ open_table_get_mdl_lock(THD *thd, TABLE_
}
else
{
- /*
- There is no MDL_SHARED_UPGRADABLE_HIGH_PRIO type of metadata lock so we
- want to be sure that caller doesn't pass us both flags simultaneously.
- */
- DBUG_ASSERT(!(flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) ||
- !(flags & MYSQL_LOCK_IGNORE_FLUSH));
+ if (flags & MYSQL_OPEN_FORCE_SHARED_MDL)
+ {
+ /*
+ While executing PREPARE for prepared statement we override
+ type-of-operation aware type of shared metadata lock which
+ was set in the parser with simple shared metadata lock.
+ This is necessary to allow concurrent execution of PREPARE
+ and LOCK TABLES WRITE statement which locks one of the tables
+ used in the statement being prepared.
+ */
+ DBUG_ASSERT(!(flags & (MYSQL_OPEN_TAKE_UPGRADABLE_MDL |
+ MYSQL_LOCK_IGNORE_FLUSH)));
+
+ mdl_request->set_type(MDL_SHARED);
+ }
+ else if (flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL)
+ {
+ DBUG_ASSERT(!(flags & MYSQL_LOCK_IGNORE_FLUSH));
- if (flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL &&
- table_list->lock_type >= TL_WRITE_ALLOW_WRITE)
- mdl_request->set_type(MDL_SHARED_UPGRADABLE);
- if (flags & MYSQL_LOCK_IGNORE_FLUSH)
+ if (table_list->lock_type >= TL_WRITE_ALLOW_WRITE)
+ {
+ /*
+ When executing LOCK TABLES ... WRITE statement in addition to
+ upgradable locks which were pre-acquired in open_tables() we
+ want to acquire upgradable locks on tables which are implicitly
+ locked for write.
+
+ QQ: Should we try to get rid of this thing by disallowing DDL
+ on implicitly locked tables?
+ */
+ mdl_request->set_type(table_list->lock_type > TL_WRITE_ALLOW_READ ?
+ MDL_UPGRADABLE_NO_READ_WRITE :
+ MDL_UPGRADABLE_NO_WRITE);
+ }
+ }
+ else if (flags & MYSQL_LOCK_IGNORE_FLUSH)
mdl_request->set_type(MDL_SHARED_HIGH_PRIO);
- if (mdl_request->type == MDL_SHARED_UPGRADABLE)
+ if (mdl_request->type == MDL_UPGRADABLE_NO_READ_WRITE ||
+ mdl_request->type == MDL_UPGRADABLE_NO_WRITE)
{
MDL_request *global_request;
@@ -4419,6 +4445,65 @@ restart:
thd_proc_info(thd, "Opening tables");
/*
+ If we are executing LOCK TABLES statement or a DDL statement
+ (in non-LOCK TABLES mode) we might have to acquire upgradable
+ semi-exclusive metadata locks (UNW or UNRW) on some of the
+ tables to be opened.
+ So we acquire all such locks at once here as doing this in one
+ by one fashion may lead to deadlocks or starvation. Later when
+ we will be opening corresponding table pre-acquired metadata
+ lock will be reused (thanks to the fact that in recursive case
+ metadata locks are acquired without waiting).
+
+ QQ: Is there a better place or a way to do this?
+ I am reluctant to put code into Prelocking_strategy as this
+ has nothing to do with extending of prelocking set and is
+ rather about the order in which we acquire MDL locks...
+ */
+ if ((flags & MYSQL_OPEN_TAKE_UPGRADABLE_MDL) &&
+ ! thd->locked_tables_mode)
+ {
+ MDL_request_list mdl_requests;
+
+ for (tables= *start; tables && tables != thd->lex->first_not_own_table();
+ tables= tables->next_global)
+ {
+ if (tables->lock_type >= TL_WRITE_ALLOW_WRITE)
+ {
+ tables->mdl_request.set_type(tables->lock_type > TL_WRITE_ALLOW_READ ?
+ MDL_UPGRADABLE_NO_READ_WRITE :
+ MDL_UPGRADABLE_NO_WRITE);
+ mdl_requests.push_front(&tables->mdl_request);
+ }
+ }
+
+ if (! mdl_requests.is_empty())
+ {
+ MDL_request *global_request;
+
+ if (!(global_request= ot_ctx.get_global_mdl_request(thd)))
+ return 1;
+ if (! global_request->ticket)
+ {
+ if (thd->mdl_context.acquire_global_intention_exclusive_lock(global_request))
+ return 1;
+ }
+ }
+
+ thd->mdl_context.acquire_exclusive_locks(&mdl_requests);
+
+ for (tables= *start; tables && tables != thd->lex->first_not_own_table();
+ tables= tables->next_global)
+ {
+ if (tables->lock_type >= TL_WRITE_ALLOW_WRITE)
+ {
+ tables->mdl_request.ticket= NULL;
+ tables->mdl_request.set_type(MDL_SHARED_WRITE);
+ }
+ }
+ }
+
+ /*
Perform steps of prelocking algorithm until there are unprocessed
elements in prelocking list/set.
*/
@@ -8459,15 +8544,19 @@ void flush_tables()
@param thd Current thread context
@param in_use The thread to wake up
+ @param needs_thr_lock_abort Indicates that to wake up thread
+ this call needs to abort its waiting
+ on table-level lock.
@retval TRUE if the thread was woken up
- @retval FALSE otherwise (e.g. it was not waiting for a table-level lock).
+ @retval FALSE otherwise.
@note It is one of two places where border between MDL and the
rest of the server is broken.
*/
-bool mysql_notify_thread_having_shared_lock(THD *thd, THD *in_use)
+bool mysql_notify_thread_having_shared_lock(THD *thd, THD *in_use,
+ bool needs_thr_lock_abort)
{
bool signalled= FALSE;
if ((in_use->system_thread & SYSTEM_THREAD_DELAYED_INSERT) &&
@@ -8481,19 +8570,23 @@ bool mysql_notify_thread_having_shared_l
signalled= TRUE;
}
pthread_mutex_lock(&LOCK_open);
- for (TABLE *thd_table= in_use->open_tables;
- thd_table ;
- thd_table= thd_table->next)
+
+ if (needs_thr_lock_abort)
{
- /*
- Check for TABLE::needs_reopen() is needed since in some places we call
- handler::close() for table instance (and set TABLE::db_stat to 0)
- and do not remove such instances from the THD::open_tables
- for some time, during which other thread can see those instances
- (e.g. see partitioning code).
- */
- if (!thd_table->needs_reopen())
- signalled|= mysql_lock_abort_for_thread(thd, thd_table);
+ for (TABLE *thd_table= in_use->open_tables;
+ thd_table ;
+ thd_table= thd_table->next)
+ {
+ /*
+ Check for TABLE::needs_reopen() is needed since in some places we call
+ handler::close() for table instance (and set TABLE::db_stat to 0)
+ and do not remove such instances from the THD::open_tables
+ for some time, during which other thread can see those instances
+ (e.g. see partitioning code).
+ */
+ if (!thd_table->needs_reopen())
+ signalled|= mysql_lock_abort_for_thread(thd, thd_table);
+ }
}
/*
Wake up threads waiting in tdc_wait_for_old_versions().
@@ -8512,28 +8605,6 @@ bool mysql_notify_thread_having_shared_l
/**
- Force transactions holding shared metadata lock on the table to call
- MDL_context::can_wait_lead_to_deadlock() even if they don't need any
- new metadata locks so they can detect potential deadlocks between
- metadata locking subsystem and table-level locks.
-
- @param mdl_key MDL key for the table on which we are upgrading
- metadata lock.
-*/
-
-void mysql_abort_transactions_with_shared_lock(const MDL_key *mdl_key)
-{
- if (mdl_key->mdl_namespace() == MDL_key::TABLE)
- {
- pthread_mutex_lock(&LOCK_open);
- tdc_remove_table(NULL, TDC_RT_REMOVE_UNUSED, mdl_key->db_name(),
- mdl_key->name());
- pthread_mutex_unlock(&LOCK_open);
- }
-}
-
-
-/**
Remove all or some (depending on parameter) instances of TABLE and
TABLE_SHARE from the table definition cache.
=== modified file 'sql/sql_handler.cc'
--- a/sql/sql_handler.cc 2009-12-22 16:09:15 +0000
+++ b/sql/sql_handler.cc 2010-01-22 05:53:57 +0000
@@ -293,13 +293,16 @@ bool mysql_ha_open(THD *thd, TABLE_LIST
error= TRUE;
}
if (!error &&
- hash_tables->mdl_request.ticket &&
- thd->mdl_context.has_lock(mdl_savepoint,
- hash_tables->mdl_request.ticket))
- {
- /* The ticket returned is within a savepoint. Make a copy. */
- error= thd->mdl_context.clone_ticket(&hash_tables->mdl_request);
- hash_tables->table->mdl_ticket= hash_tables->mdl_request.ticket;
+ hash_tables->mdl_request.ticket)
+ {
+ if (thd->mdl_context.has_lock(mdl_savepoint,
+ hash_tables->mdl_request.ticket))
+ {
+ /* The ticket returned is within a savepoint. Make a copy. */
+ error= thd->mdl_context.clone_ticket(&hash_tables->mdl_request);
+ hash_tables->table->mdl_ticket= hash_tables->mdl_request.ticket;
+ }
+ hash_tables->mdl_request.ticket->set_needs_thr_lock_abort();
}
if (error)
{
@@ -774,6 +777,7 @@ void mysql_ha_flush(THD *thd)
TABLE::mdl_ticket is 0 for temporary tables so we need extra check.
*/
if (hash_tables->table &&
+ hash_tables->table->s->tmp_table == NO_TMP_TABLE &&
((hash_tables->table->mdl_ticket &&
hash_tables->table->mdl_ticket->has_pending_conflicting_lock()) ||
(!hash_tables->table->s->tmp_table &&
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2010-01-21 20:43:03 +0000
+++ b/sql/sql_parse.cc 2010-01-22 05:53:57 +0000
@@ -2397,8 +2397,7 @@ case SQLCOM_PREPARE:
goto end_with_restore_list;
}
- if (!(res= open_and_lock_tables_derived(thd, lex->query_tables, TRUE,
- MYSQL_OPEN_TAKE_UPGRADABLE_MDL)))
+ if (!(res= open_and_lock_tables_derived(thd, lex->query_tables, TRUE, 0)))
{
/*
Is table which we are changing used somewhere in other parts
@@ -5971,7 +5970,9 @@ TABLE_LIST *st_select_lex::add_table_to_
ptr->next_name_resolution_table= NULL;
/* Link table in global list (all used tables) */
lex->add_to_query_tables(ptr);
- ptr->mdl_request.init(MDL_key::TABLE, ptr->db, ptr->table_name, MDL_SHARED);
+ ptr->mdl_request.init(MDL_key::TABLE, ptr->db, ptr->table_name,
+ (ptr->lock_type >= TL_WRITE_ALLOW_WRITE) ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
DBUG_RETURN(ptr);
}
@@ -6207,6 +6208,8 @@ void st_select_lex::set_lock_for_tables(
{
tables->lock_type= lock_type;
tables->updating= for_update;
+ tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
}
DBUG_VOID_RETURN;
}
=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc 2010-01-12 11:32:55 +0000
+++ b/sql/sql_prepare.cc 2010-01-22 05:53:57 +0000
@@ -1204,7 +1204,8 @@ static bool mysql_test_insert(Prepared_s
If we would use locks, then we have to ensure we are not using
TL_WRITE_DELAYED as having two such locks can cause table corruption.
*/
- if (open_normal_and_derived_tables(thd, table_list, 0))
+ if (open_normal_and_derived_tables(thd, table_list,
+ MYSQL_OPEN_FORCE_SHARED_MDL))
goto error;
if ((values= its++))
@@ -1285,7 +1286,7 @@ static int mysql_test_update(Prepared_st
DBUG_ENTER("mysql_test_update");
if (update_precheck(thd, table_list) ||
- open_tables(thd, &table_list, &table_count, 0))
+ open_tables(thd, &table_list, &table_count, MYSQL_OPEN_FORCE_SHARED_MDL))
goto error;
if (table_list->multitable_view)
@@ -1362,7 +1363,8 @@ static bool mysql_test_delete(Prepared_s
DBUG_ENTER("mysql_test_delete");
if (delete_precheck(thd, table_list) ||
- open_normal_and_derived_tables(thd, table_list, 0))
+ open_normal_and_derived_tables(thd, table_list,
+ MYSQL_OPEN_FORCE_SHARED_MDL))
goto error;
if (!table_list->table)
@@ -1420,7 +1422,7 @@ static int mysql_test_select(Prepared_st
goto error;
}
- if (open_normal_and_derived_tables(thd, tables, 0))
+ if (open_normal_and_derived_tables(thd, tables, MYSQL_OPEN_FORCE_SHARED_MDL))
goto error;
thd->used_tables= 0; // Updated by setup_fields
@@ -1481,7 +1483,7 @@ static bool mysql_test_do_fields(Prepare
UINT_MAX, FALSE))
DBUG_RETURN(TRUE);
- if (open_normal_and_derived_tables(thd, tables, 0))
+ if (open_normal_and_derived_tables(thd, tables, MYSQL_OPEN_FORCE_SHARED_MDL))
DBUG_RETURN(TRUE);
DBUG_RETURN(setup_fields(thd, 0, *values, MARK_COLUMNS_NONE, 0, 0));
}
@@ -1511,7 +1513,7 @@ static bool mysql_test_set_fields(Prepar
if ((tables && check_table_access(thd, SELECT_ACL, tables, FALSE,
UINT_MAX, FALSE)) ||
- open_normal_and_derived_tables(thd, tables, 0))
+ open_normal_and_derived_tables(thd, tables, MYSQL_OPEN_FORCE_SHARED_MDL))
goto error;
while ((var= it++))
@@ -1548,7 +1550,7 @@ static bool mysql_test_call_fields(Prepa
if ((tables && check_table_access(thd, SELECT_ACL, tables, FALSE,
UINT_MAX, FALSE)) ||
- open_normal_and_derived_tables(thd, tables, 0))
+ open_normal_and_derived_tables(thd, tables, MYSQL_OPEN_FORCE_SHARED_MDL))
goto err;
while ((item= it++))
@@ -1631,7 +1633,8 @@ select_like_stmt_test_with_open(Prepared
prepared EXPLAIN yet so derived tables will clean up after
themself.
*/
- if (open_normal_and_derived_tables(stmt->thd, tables, 0))
+ if (open_normal_and_derived_tables(stmt->thd, tables,
+ MYSQL_OPEN_FORCE_SHARED_MDL))
DBUG_RETURN(TRUE);
DBUG_RETURN(select_like_stmt_test(stmt, specific_prepare,
@@ -1675,7 +1678,8 @@ static bool mysql_test_create_table(Prep
if (select_lex->item_list.elements)
{
- if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0))
+ if (open_normal_and_derived_tables(stmt->thd, lex->query_tables,
+ MYSQL_OPEN_FORCE_SHARED_MDL))
DBUG_RETURN(TRUE);
select_lex->context.resolve_in_select_list= TRUE;
@@ -1694,7 +1698,8 @@ static bool mysql_test_create_table(Prep
we validate metadata of all CREATE TABLE statements,
which keeps metadata validation code simple.
*/
- if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0))
+ if (open_normal_and_derived_tables(stmt->thd, lex->query_tables,
+ MYSQL_OPEN_FORCE_SHARED_MDL))
DBUG_RETURN(TRUE);
}
@@ -1727,7 +1732,7 @@ static bool mysql_test_create_view(Prepa
if (create_view_precheck(thd, tables, view, lex->create_view_mode))
goto err;
- if (open_normal_and_derived_tables(thd, tables, 0))
+ if (open_normal_and_derived_tables(thd, tables, MYSQL_OPEN_FORCE_SHARED_MDL))
goto err;
lex->view_prepare_mode= 1;
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2010-01-21 20:43:03 +0000
+++ b/sql/sql_table.cc 2010-01-22 05:53:57 +0000
@@ -4142,7 +4142,7 @@ bool mysql_create_table(THD *thd, TABLE_
Open or obtain an exclusive metadata lock on table being created.
*/
if (open_and_lock_tables_derived(thd, thd->lex->query_tables, FALSE,
- MYSQL_OPEN_TAKE_UPGRADABLE_MDL))
+ 0))
{
result= TRUE;
goto unlock;
=== modified file 'sql/table.cc'
--- a/sql/table.cc 2009-12-16 08:33:54 +0000
+++ b/sql/table.cc 2010-01-22 05:53:57 +0000
@@ -4583,11 +4583,12 @@ void TABLE_LIST::reinit_before_use(THD *
mdl_request.ticket= NULL;
/*
- Not strictly necessary, but we manipulate with the type in open_table(),
- so it's "safe" to reset the lock request type to the parser default, to
- restore things back to first-execution state.
+ Since we manipulate with the metadata lock type in open_table(),
+ we need to reset it to the parser default, to restore things back
+ to first-execution state.
*/
- mdl_request.set_type(MDL_SHARED);
+ mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
}
/*
@@ -4821,7 +4822,8 @@ void init_mdl_requests(TABLE_LIST *table
for ( ; table_list ; table_list= table_list->next_global)
table_list->mdl_request.init(MDL_key::TABLE,
table_list->db, table_list->table_name,
- MDL_SHARED);
+ table_list->lock_type >= TL_WRITE_ALLOW_WRITE ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
}
=== modified file 'sql/table.h'
--- a/sql/table.h 2009-12-15 19:03:56 +0000
+++ b/sql/table.h 2010-01-22 05:53:57 +0000
@@ -1103,7 +1103,9 @@ struct TABLE_LIST
table_name_length= table_name_length_arg;
alias= (char*) alias_arg;
lock_type= lock_type_arg;
- mdl_request.init(MDL_key::TABLE, db, table_name, MDL_SHARED);
+ mdl_request.init(MDL_key::TABLE, db, table_name,
+ (lock_type >= TL_WRITE_ALLOW_WRITE) ?
+ MDL_SHARED_WRITE : MDL_SHARED_READ);
}
/*
Attachment: [text/bzr-bundle] bzr/dlenev@mysql.com-20100122055357-qmybdvyvt00pl3n8.bundle
| Thread |
|---|
| • bzr commit into mysql-5.6-next-mr branch (dlenev:3055) Bug#46272 | Dmitry Lenev | 22 Jan |
