#At file:///home/dlenev/src/bzr/mysql-6.0-codebase-new-locks/ based on revid:dlenev@stripped
3628 Dmitry Lenev 2009-12-21
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".
The first bug has manifested itself as unwarranted abort of transaction
with ER_LOCK_DEADLOCK error in cases when transaction tried to use table
being currently altered even although the same transaction has already
used the same table in the same fashion.
The second bug showed up as a deadlock between table-level locks
and InnoDB row-locks, which was "detected" only after
innodb_lock_wait_timeout timeout. The deadlock occured when ALTER TABLE
tried to change table which was already updated by some transaction
(and on which this transaction held X row-lock) and so had to wait for
this transaction to complete while holding table-level lock preventing
further updates on the table even those which were coming from the
the transaction for which it was waiting.
Both these bugs stemmed from inadequate solutions to problem of
deadlocks occuring between different subsystems.
In the first case we tried to avoid deadlocks between metadata
locking and table-level locking subsystems, when upgrading shared
metadata lock to exclusive one, and aborted transactions holding
shared lock on table and waiting for some table-level lock too
eagerly. We also allowed ALTER TABLE to proceed and acquire
TL_WRITE_ALLOW_READ lock in cases when there were some transactions
which already managed to update table which inevitably led to
necessity to abort such transactions once they tried to perform
yet another update on the table.
The second bug occurred simply because we didn't have any means to
handle deadlocks between table-level in the core server and row-level
locks in InnoDB other than timeout.
This patch tries to solve both this problems by moving all conflicts
which are causing these deadlocks into metadata locking subsystem,
thus making it possible to avoid or detect such deadlocks by its
means.
To do this we introduce new type-of-operation-aware metadata locks,
which allow MDL subsystem to know not only the fact that transaction
has used or going to use some object but also what kind of operation
it has carried out or going to carry out on the object.
This, along with addition of special kind of upgradable metadata lock,
allows ALTER TABLE to wait before doing anything else until all
transactions which has updated the table go away. This solves the
the second issue.
And along with addition of special kind of upgradable metadata lock
to be acquired by LOCK TABLES WRITE it allows to solve the first issue
as well, since aborting of table-level locks becomes unnecessary (see
comment for sql/sql_base.cc for detailed explanation).
Here are the list of incompatible changes introduced by this patch:
- Now ALTER TABLE and CREATE/DROP TRIGGER statements (i.e. those
which acquire TL_WRITE_ALLOW_READ lock) before doing anything
else wait until all transactions which has updated the table
being changed complete.
- LOCK TABLES ... WRITE, REPAIR/OPTIMIZE TABLE (i.e. all statements
which acquire upgradable metadata lock and TL_WRITE table-level
lock) now wait before doing anything else untill all transactions
which somehow used table to be locked or changed complete.
As a consequence innodb_table_locks=0 option no longer applies
to LOCK TABLES ... WRITE.
- DROP TABLES/DATABASE and RENAME TABLE no longer abort statements
or transactions which use tables being changed and are waiting for
some table-level locks.
- Transactions which try to change some table for the first time or
use some table for the first time may fail with ER_LOCK_DEADLOCK
error if there is a concurrent LOCK TABLES ... WRITE statement
for this table and transaction is likely to create deadlock by
waiting for it to go away (note that unlike before this also
applies to transactions involving non-InnoDB tables or even
using only non-InnoDB tables).
- Finally, this patch temporarily disables support of LOW_PRIORITY
option for LOCK TABLES ... WRITE.
As usual questions for reviewer are marked by QQ.
@ mysql-test/include/handler.inc
Adjusted test case to trigger execution path on which bug 41110 "crash
with handler command when used concurrently with alter table" and bug
41112 "crash in mysql_ha_close_table/get_lock_data with alter table"
were originally discovered. Left old test case which no longer triggers
this execution path for the sake of coverage.
@ mysql-test/include/locktrans.inc
Adjusted test case to take into account facts:
- that now waiting for table locked by LOCK TABLES ... WRITE happens
within metadata locking subsystem and therefore different status is
shown in I_S.PROCESSLIST in this case.
- that now LOCK TABLES ... WRITE has to wait until transactions using
the table being locked are commited or rolled back.
Also temporarily disabled part of the test which uses LOCK TABLES ...
LOW_PRIORITY WRITE - feature which is not yet supported by this
version of this patch.
@ mysql-test/r/events_2.result
Disabled part of the test which started to cause deadlocks after
changing LOCK TABLES WRITE to be a metadata level lock. We need
to make decision how to handle such situation.
@ mysql-test/r/handler_innodb.result
Adjusted test case to trigger execution path on which bug 41110 "crash
with handler command when used concurrently with alter table" and bug
41112 "crash in mysql_ha_close_table/get_lock_data with alter table"
were originally discovered. Left old test case which no longer triggers
this execution path for the sake of coverage.
@ mysql-test/r/handler_myisam.result
Adjusted test case to trigger execution path on which bug 41110 "crash
with handler command when used concurrently with alter table" and bug
41112 "crash in mysql_ha_close_table/get_lock_data with alter table"
were originally discovered. Left old test case which no longer triggers
this execution path for the sake of coverage.
@ mysql-test/r/innodb-lock.result
Disabled part of test case which uses innodb_table_locks=0 option.
It is unlikely that we will be able to support this option with
LOCK TABLES ... WRITE implemented on metadata locking level.
@ mysql-test/r/innodb_mysql_lock.result
Added test for bug #37346 "innodb does not detect deadlock between
update and alter table".
@ mysql-test/r/lock_multi.result
Added test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock".
Adjusted other test cases to take into account the fact that waiting
for LOCK TABLES ... WRITE now happens within metadata locking subsystem.
@ mysql-test/r/locktrans_innodb.result
Adjusted test case to take into account fact that now LOCK TABLES ...
WRITE has to wait until transactions using the table being locked are
commited or rolled back.
Also temporarily disabled part of the test which uses LOCK TABLES ...
LOW_PRIORITY WRITE - feature which is not yet supported by this
version of this patch.
@ mysql-test/r/locktrans_myisam.result
Adjusted test case to take into account fact that now LOCK TABLES ...
WRITE has to wait until transactions using the table being locked are
commited or rolled back.
Also temporarily disabled part of the test which uses LOCK TABLES ...
LOW_PRIORITY WRITE - feature which is not yet supported by this
version of this patch.
@ mysql-test/r/mdl_sync.result
Added basic test coverage for type-of-operation aware metadata locks.
Also covered with tests some use cases involving HANDLER statements
in which deadlock might have arisen.
@ mysql-test/r/merge-sync.result
Use LOCK TABLES READ instead of LOCK TABLES WRITE in order to trigger
the same execution patch as before.
@ mysql-test/r/sp-threads.result
SHOW PROCESSLIST has changed due to fact that waiting for LOCK TABLES
WRITE now happens within metadata locking subsystem.
@ mysql-test/r/truncate_coverage.result
Adjusted test case after making LOCK TABLES WRITE to wait until
transactions using the table to be locked are completed.
@ mysql-test/suite/rpl/r/rpl_locktrans_innodb.result
Adjusted test case to take into account fact that now LOCK TABLES ...
WRITE has to wait until transactions using the table being locked are
commited or rolled back.
Also temporarily disabled part of the test which uses LOCK TABLES ...
LOW_PRIORITY WRITE - feature which is not yet supported by this
version of this patch.
@ mysql-test/suite/rpl/r/rpl_locktrans_myisam.result
Adjusted test case to take into account fact that now LOCK TABLES ...
WRITE has to wait until transactions using the table being locked are
commited or rolled back.
Also temporarily disabled part of the test which uses LOCK TABLES ...
LOW_PRIORITY WRITE - feature which is not yet supported by this
version of this patch.
@ mysql-test/t/events_2.test
Disabled part of the test which started to cause deadlocks after
changing LOCK TABLES WRITE to be a metadata level lock. We need
to make decision how to handle such situation.
@ mysql-test/t/innodb-lock.test
Disabled part of test case which uses innodb_table_locks=0 option.
It is unlikely that we will be able to support this option with
LOCK TABLES ... WRITE implemented on metadata locking level.
@ mysql-test/t/innodb_mysql_lock.test
Added test for bug #37346 "innodb does not detect deadlock between
update and alter table".
@ mysql-test/t/lock_multi.test
Added test for bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock".
Adjusted other test cases to take into account the fact that waiting
for LOCK TABLES ... WRITE now happens within metadata locking subsystem.
@ mysql-test/t/mdl_sync.test
Added basic test coverage for type-of-operation aware metadata locks.
Also covered with tests some use cases involving HANDLER statements
in which deadlock might have arisen.
@ mysql-test/t/merge-sync.test
Use LOCK TABLES READ instead of LOCK TABLES WRITE in order to trigger
the same execution patch as before.
@ mysql-test/t/multi_update.test
Changed test case due to fact that waiting for LOCK TABLES WRITE now
happens within metadata locking subsystem.
@ mysql-test/t/truncate_coverage.test
Adjusted test case after making LOCK TABLES WRITE to wait until
transactions using the table to be locked are completed.
@ sql/mdl.cc
Introduced new type-of-operation-aware metadata locks. To do this:
- Changed MDL_lock to use one list for waiting requests and one
list for granted requests. Added auxiliary methods to manipulate
with those lists while keeping the rest of the MDL_lock's state
in sync with their content.
- Adjusted compatibility matrices which are implemented by
MDL_global_lock::is_lock_type_compatible() and
MDL_lock::can_grant_lock() methods.
- Adjusted rest of MDL subsystem implementation to take into account
new types of locks (e.g. to the fact that now we have two types of
upgradable locks).
- Removed call to mysql_abort_transactions_with_shared_lock() from
MDL_ticket::upgrade_shared_lock_to_exclusive() since with advent
of new locks deadlocks between deadlocks between table-level locks
and MDL subsystem should not be possible (since thread which has
managed to acquire metadata lock compatible with UNW, should be
able to acquire table-level lock without waiting, and thread which
has lock compatible with UNRW lock should not acquire table-level
locks at all (S lock acquired by HANDLER statement being an
exception, which is handled through aborts of table-level locks)).
@ sql/mdl.h
Introduced new type-of-operation-aware metadata locks.
Added MDL_context::m_needs_thr_lock_abort flag to be able to
distinguish contexts used for opening HANDLERs.
Got rid of mysql_abort_transactions_with_shared_lock() function
which became unnecessary with advent of new locks.
@ sql/mysql_priv.h
Added MYSQL_OPEN_FORCE_SHARED_MDL flag which forces open_tables() to
take MDL_SHARED on tables instead of metadata locks specified in parser.
We use this to allow PREPARE statements not to wait for LOCK TABLES ...
WRITE on tables used in the prepared statements.
@ sql/sp_head.cc
When creating table list elements for prelocking or system tables set
type of request for metadata lock according to the type of table-level
lock which we are going to acquire and thus operation we are going to
perform on the table.
@ sql/sql_base.cc
wait_while_table_is_used()/mysql_notify_thread_having_shared_lock():
When upgrading metadata lock or acquiring an exclusive metadata lock
we no longer abort threads which hold conflicting metadata lock and
are waiting on table-level lock (in general case). Doing so led to
aborts of transactions which were not causing deadlocks and should
have continued their execution (see bug#46272).
It became possible to remove these aborts without opening gap for
deadlock situations after introduction of type-of-operation-aware
metadata locks.
Note that even before this change deadlock could have not arisen
in cases when one tried to acquire X metadata lock, since in it
all waiting happened within MDL subsystem and thus deadlocks were
successfully detected and reported by our trivial, empiric-based
deadlock detector.
But this change helps to avoid deadlock in scenarios when shared
metadata lock is upgraded to X lock.
Particularly, in case when UNW lock is being upgraded there
cannot be any threads waiting for thread performing upgrade due
to table-level lock and therefore possible deadlocks limited to
MDL subsystem and should be detected. This is because any thread
which managed to acquire metadata lock compatible with UNW, i.e.
SR lock, can request only read table-level locks which will be
granted without any waiting (NB: this means that thread that
acquires UNW locks should not mix them with any other kinds of
metadata locks).
In case when UNRW lock is upgraded to X lock, thread preventing
upgrade can hold only S or SH types of lock on the table. The
latter type is used only for filling of I_S tables during which
no table-level locks are acquired so no deadlocks outside of MDL
can occur. The former type is used in PREPARE statement which
also does not aacquire table-level locks and by open HANDLERs
for which we still abort table-level locks.
open_table_get_mdl_lock():
Adjusted code to the fact that now we have two kind of upgradable
metadata locks which exact type is determined according to type of
table-level lock to be acquired (for TL_WRITE table-level lock we
acquire UNRW metadata lock and for TL_WRITE_ALLOW_READ we acquire
UNW lock).
open_tables():
In order to avoid starvation pre-acquire upgradable metadata locks
using acquire_exclusive_locks() method instead of acquiring them in
one-by-one fashion using try_acquire_shared_lock().
mysql_abort_transactions_with_shared_lock():
We no longer need this function since after introduction
of type-of-operation-aware metadata locks deadlocks between
table-level locks and MDL subsystem should not be possible
(since thread which has managed to acquire metadata lock
compatible with UNW, should be able to acquire table-level
lock without waiting, and thread which has lock compatible
with UNRW lock should not acquire table-level locks at all
(S lock acquired by HANDLER statement being an exception,
which is handled through aborts of table-level locks)).
@ sql/sql_class.h
Mark MDL_context used for open HANDLERs as breaking metadata locking
protocol and thus requiring aborting of waits on table-level locks.
@ sql/sql_handler.cc
Don't try flush HANDLERs for temporary tables. Such tables are not
visible outside of this connection anyway. This is temporary fix
which will go away once Kostja pushes his patch refactoring use
of metadata locks for HANDLERs.
@ sql/sql_parse.cc
Don't take upgradable metadata locks when opening tables for
CREATE TABLE ... SELECT statement. This is not needed until support
for engine-agnostic foreign keys is implemented and creates problems
with acquiring exclusive metadata lock on target table and with
tables from SELECT part if FOR UPDATE clause is specified.
When initializing metadata lock request for table list element set
metadata lock type according to the type of table-level lock to be
acquired and thus operation we are going to perform on the table.
@ sql/sql_prepare.cc
Use MYSQL_OPEN_FORCE_SHARED_MDL flag when calling open_tables() during
PREPARE of prepared statement in order to not be blocked by concurrent
LOCK TABLES WRITE.
@ sql/sql_table.cc
Don't take upgradable metadata locks when opening tables for
CREATE TABLE statement. This is not needed until support for
engine-agnostic foreign keys is implemented and creates problems
with acquiring exclusive metadata lock on target table.
@ sql/table.cc
When initializing or resetting metadata lock request for table list
element set metadata lock type according to the type of table-level
lock to be acquired and thus operation we are going to perform on
the table.
@ sql/table.h
When initializing metadata lock request for table list element set
metadata lock type according to the type of table-level lock to be
acquired and thus operation we are going to perform on the table.
modified:
mysql-test/include/handler.inc
mysql-test/include/locktrans.inc
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/locktrans_innodb.result
mysql-test/r/locktrans_myisam.result
mysql-test/r/mdl_sync.result
mysql-test/r/merge-sync.result
mysql-test/r/sp-threads.result
mysql-test/r/truncate_coverage.result
mysql-test/suite/rpl/r/rpl_locktrans_innodb.result
mysql-test/suite/rpl/r/rpl_locktrans_myisam.result
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/mdl_sync.test
mysql-test/t/merge-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_class.h
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 2009-10-29 09:13:48 +0000
+++ b/mysql-test/include/handler.inc 2009-12-21 14:09:47 +0000
@@ -717,10 +717,13 @@ connection default;
--disable_warnings
drop table if exists t1;
--enable_warnings
+--echo # First test case which is supposed trigger the execution
+--echo # patch 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=
@@ -733,6 +736,29 @@ 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 patch 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
=== modified file 'mysql-test/include/locktrans.inc'
--- a/mysql-test/include/locktrans.inc 2009-09-16 13:31:23 +0000
+++ b/mysql-test/include/locktrans.inc 2009-12-21 14:09:47 +0000
@@ -493,7 +493,7 @@ LOCK TABLE t1 READ;
connection default;
--echo # Wait for the helper thread to sit on its lock.
while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST
- WHERE STATE LIKE '%lock%'`)
+ WHERE STATE LIKE '%Waiting for table%'`)
{
--sleep 0.1
}
@@ -508,7 +508,7 @@ while (`SELECT COUNT(*) < 1 FROM INFORMA
connection default;
--echo # Wait for the helper threads to sit on their locks.
while (`SELECT COUNT(*) < 2 FROM INFORMATION_SCHEMA.PROCESSLIST
- WHERE STATE LIKE '%lock%'`)
+ WHERE STATE LIKE '%Waiting for table%'`)
{
--sleep 0.1
}
@@ -533,6 +533,10 @@ UNLOCK TABLES;
--echo # connection default.
connection default;
TRUNCATE t1;
+--echo # QQ Part of test below is disabled because it requires proper
+--echo # support of LOCK TABLES ... LOW_PRIORITY WRITE. What should
+--echo # we do about it?
+--disable_parsing
--echo #
--echo # LOW_PRIORITY WRITE locks wait for readers (autocommit).
--echo # Insert a value.
@@ -579,6 +583,11 @@ UNLOCK TABLES;
--echo # connection default.
connection default;
TRUNCATE t1;
+--enable_parsing
+ --echo # connection conn1.
+ connection conn1;
+ SET AUTOCOMMIT= 0;
+connection default;
SET AUTOCOMMIT= 0;
COMMIT;
@@ -600,7 +609,7 @@ LOCK TABLE t1 READ;
connection default;
--echo # Wait for the helper thread to sit on its lock.
while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST
- WHERE STATE LIKE '%lock%'`)
+ WHERE STATE LIKE '%Waiting for table%'`)
{
--sleep 0.1
}
@@ -613,7 +622,7 @@ while (`SELECT COUNT(*) < 1 FROM INFORMA
connection default;
--echo # Wait for the helper threads to sit on their locks.
while (`SELECT COUNT(*) < 2 FROM INFORMATION_SCHEMA.PROCESSLIST
- WHERE STATE LIKE '%lock%'`)
+ WHERE STATE LIKE '%Waiting for table%'`)
{
--sleep 0.1
}
@@ -666,7 +675,7 @@ LOCK TABLE t1 READ;
connection default;
--echo # Wait for the helper thread to sit on its lock.
while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST
- WHERE STATE LIKE '%lock%'`)
+ WHERE STATE LIKE '%Waiting for table%'`)
{
--sleep 0.1
}
@@ -679,7 +688,7 @@ while (`SELECT COUNT(*) < 1 FROM INFORMA
connection default;
--echo # Wait for the helper threads to sit on their locks.
while (`SELECT COUNT(*) < 2 FROM INFORMATION_SCHEMA.PROCESSLIST
- WHERE STATE LIKE '%lock%'`)
+ WHERE STATE LIKE '%Waiting for table%'`)
{
--sleep 0.1
}
@@ -1024,43 +1033,52 @@ COMMIT;
DELIMITER //;
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
BEGIN
- # This will time out. So use a small value.
- INSERT INTO t2 SELECT GET_LOCK("mysqltest1", 1);
+ DO GET_LOCK("mysqltest1", 10);
UPDATE t2 SET c2= c2 + 111;
- INSERT INTO t2 SELECT RELEASE_LOCK("mysqltest1");
+ DO RELEASE_LOCK("mysqltest1");
END//
DELIMITER ;//
---echo # Take an SQL lock which blocks the trigger.
-SELECT GET_LOCK("mysqltest1", 10);
+ --echo # connection conn2.
+ connection conn2;
+ --echo # Take an SQL lock which will block the trigger.
+ SELECT GET_LOCK("mysqltest1", 10);
--echo # connection conn1.
connection conn1;
--echo # Insert into t1 to fire trigger. This waits on GET_LOCK.
send INSERT INTO t1 VALUES(111222);
--echo # connection default.
connection default;
---echo # Wait for the helper thread to sit on its lock.
+--echo # Wait for the conn1 thread to sit on user lock.
while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST
- WHERE STATE LIKE '%lock%'`)
+ WHERE STATE = 'User lock'`)
{
--sleep 0.1
}
---echo # Take a write lock. This waits until the trigger times out.
-LOCK TABLE t2 WRITE;
---echo # Use the lock for insert.
-INSERT INTO t2 VALUES (111333);
---echo # Release the lock again.
-UNLOCK TABLES;
---echo # Release the SQL lock.
-SELECT RELEASE_LOCK("mysqltest1");
+--echo # Try to take a write lock. Since it will allow DDL on t2 it has
+--echo # to wait until transaction in conn1 commits.
+--send LOCK TABLE t2 WRITE;
+ --echo # connection conn2.
+ connection conn2;
+ --echo # Check that LOCK TABLES is blocked.
+ while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+ WHERE STATE = 'Waiting for table' AND INFO = 'LOCK TABLE t2 WRITE'`)
+ {
+ --sleep 0.1
+ }
+ --echo # Unblock trigger in conn1.
+ SELECT RELEASE_LOCK("mysqltest1");
--echo # connection conn1.
connection conn1;
- --echo # Trigger timed out.
- --error ER_LOCK_WAIT_TIMEOUT
reap;
- --echo # Commit.
+ --echo # Commit. Should unblock LOCK TABLES.
COMMIT;
--echo # connection default.
connection default;
+--reap
+--echo # Use the lock for insert.
+INSERT INTO t2 VALUES (111333);
+--echo # Release the lock again.
+UNLOCK TABLES;
--echo # Commit.
COMMIT;
--echo # Show the results.
=== modified file 'mysql-test/r/events_2.result'
--- a/mysql-test/r/events_2.result 2009-03-26 06:15:10 +0000
+++ b/mysql-test/r/events_2.result 2009-12-21 14:09:47 +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 2009-10-29 09:13:48 +0000
+++ b/mysql-test/r/handler_innodb.result 2009-12-21 14:09:47 +0000
@@ -733,13 +733,32 @@ drop table t1;
handler t1 read a next;
ERROR 42S02: Unknown table 't1' in HANDLER
drop table if exists t1;
+# First test case which is supposed trigger the execution
+# patch 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 patch 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;
drop table t1;
USE information_schema;
HANDLER COLUMNS OPEN;
=== modified file 'mysql-test/r/handler_myisam.result'
--- a/mysql-test/r/handler_myisam.result 2009-10-29 09:13:48 +0000
+++ b/mysql-test/r/handler_myisam.result 2009-12-21 14:09:47 +0000
@@ -731,13 +731,32 @@ drop table t1;
handler t1 read a next;
ERROR 42S02: Unknown table 't1' in HANDLER
drop table if exists t1;
+# First test case which is supposed trigger the execution
+# patch 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 patch 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;
drop table t1;
USE information_schema;
HANDLER COLUMNS OPEN;
=== 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 2009-12-21 14:09:47 +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-09-09 13:01:25 +0000
+++ b/mysql-test/r/innodb_mysql_lock.result 2009-12-21 14:09:47 +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-09-23 06:32:02 +0000
+++ b/mysql-test/r/lock_multi.result 2009-12-21 14:09:47 +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/locktrans_innodb.result'
--- a/mysql-test/r/locktrans_innodb.result 2009-09-25 10:48:44 +0000
+++ b/mysql-test/r/locktrans_innodb.result 2009-12-21 14:09:47 +0000
@@ -493,42 +493,11 @@ c1
UNLOCK TABLES;
# connection default.
TRUNCATE t1;
-#
-# LOW_PRIORITY WRITE locks wait for readers (autocommit).
-# Insert a value.
-INSERT INTO t1 VALUES(111);
-# Take a non-transactional lock.
-LOCK TABLE t1 READ;
-# connection conn1.
-# Take a non-transactional LOW_PRIORITY WRITE lock,
-# which waits in background until all read locks are released.
-LOCK TABLE t1 LOW_PRIORITY WRITE;
-# connection default.
-# Wait for the helper thread to sit on its lock.
-# connection conn2.
-# Take a non-transactional READ lock,
-# which goes before the LOW_PRIORITY WRITE lock.
-LOCK TABLE t1 READ;
-# The READ lock could be taken immediately.
-# Select from the table.
-SELECT * FROM t1;
-c1
-111
-# Unlock table.
-UNLOCK TABLES;
-SET AUTOCOMMIT= 0;
-# connection default.
-# Unlock this connections non-transactional lock.
-UNLOCK TABLES;
+# QQ Part of test below is disabled because it requires proper
+# support of LOCK TABLES ... LOW_PRIORITY WRITE. What should
+# we do about it?
# connection conn1.
-# Now the LOW_PRIORITY WRITE lock is taken.
-# Insert a value.
-INSERT INTO t1 VALUES(1111);
-# Unlock table.
-UNLOCK TABLES;
SET AUTOCOMMIT= 0;
-# connection default.
-TRUNCATE t1;
SET AUTOCOMMIT= 0;
COMMIT;
#
@@ -876,12 +845,12 @@ COMMIT;
# Create a trigger on t1 that updates t2.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
BEGIN
-# This will time out. So use a small value.
-INSERT INTO t2 SELECT GET_LOCK("mysqltest1", 1);
+DO GET_LOCK("mysqltest1", 10);
UPDATE t2 SET c2= c2 + 111;
-INSERT INTO t2 SELECT RELEASE_LOCK("mysqltest1");
+DO RELEASE_LOCK("mysqltest1");
END//
-# Take an SQL lock which blocks the trigger.
+# connection conn2.
+# Take an SQL lock which will block the trigger.
SELECT GET_LOCK("mysqltest1", 10);
GET_LOCK("mysqltest1", 10)
1
@@ -889,32 +858,34 @@ GET_LOCK("mysqltest1", 10)
# Insert into t1 to fire trigger. This waits on GET_LOCK.
INSERT INTO t1 VALUES(111222);
# connection default.
-# Wait for the helper thread to sit on its lock.
-# Take a write lock. This waits until the trigger times out.
-LOCK TABLE t2 WRITE;
-# Use the lock for insert.
-INSERT INTO t2 VALUES (111333);
-# Release the lock again.
-UNLOCK TABLES;
-# Release the SQL lock.
+# Wait for the conn1 thread to sit on user lock.
+# Try to take a write lock. Since it will allow DDL on t2 it has
+# to wait until transaction in conn1 commits.
+LOCK TABLE t2 WRITE;;
+# connection conn2.
+# Check that LOCK TABLES is blocked.
+# Unblock trigger in conn1.
SELECT RELEASE_LOCK("mysqltest1");
RELEASE_LOCK("mysqltest1")
1
# connection conn1.
-# Trigger timed out.
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
-# Commit.
+# Commit. Should unblock LOCK TABLES.
COMMIT;
# connection default.
+# Use the lock for insert.
+INSERT INTO t2 VALUES (111333);
+# Release the lock again.
+UNLOCK TABLES;
# Commit.
COMMIT;
# Show the results.
SELECT * FROM t1;
c1
111
+111222
SELECT * FROM t2;
c2
-222
+333
111333
TRUNCATE t1;
TRUNCATE t2;
=== modified file 'mysql-test/r/locktrans_myisam.result'
--- a/mysql-test/r/locktrans_myisam.result 2009-07-31 17:53:36 +0000
+++ b/mysql-test/r/locktrans_myisam.result 2009-12-21 14:09:47 +0000
@@ -344,42 +344,11 @@ c1
UNLOCK TABLES;
# connection default.
TRUNCATE t1;
-#
-# LOW_PRIORITY WRITE locks wait for readers (autocommit).
-# Insert a value.
-INSERT INTO t1 VALUES(111);
-# Take a non-transactional lock.
-LOCK TABLE t1 READ;
-# connection conn1.
-# Take a non-transactional LOW_PRIORITY WRITE lock,
-# which waits in background until all read locks are released.
-LOCK TABLE t1 LOW_PRIORITY WRITE;
-# connection default.
-# Wait for the helper thread to sit on its lock.
-# connection conn2.
-# Take a non-transactional READ lock,
-# which goes before the LOW_PRIORITY WRITE lock.
-LOCK TABLE t1 READ;
-# The READ lock could be taken immediately.
-# Select from the table.
-SELECT * FROM t1;
-c1
-111
-# Unlock table.
-UNLOCK TABLES;
-SET AUTOCOMMIT= 0;
-# connection default.
-# Unlock this connections non-transactional lock.
-UNLOCK TABLES;
+# QQ Part of test below is disabled because it requires proper
+# support of LOCK TABLES ... LOW_PRIORITY WRITE. What should
+# we do about it?
# connection conn1.
-# Now the LOW_PRIORITY WRITE lock is taken.
-# Insert a value.
-INSERT INTO t1 VALUES(1111);
-# Unlock table.
-UNLOCK TABLES;
SET AUTOCOMMIT= 0;
-# connection default.
-TRUNCATE t1;
SET AUTOCOMMIT= 0;
COMMIT;
#
=== modified file 'mysql-test/r/mdl_sync.result'
--- a/mysql-test/r/mdl_sync.result 2009-10-29 09:13:48 +0000
+++ b/mysql-test/r/mdl_sync.result 2009-12-21 14:09:47 +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/merge-sync.result'
--- a/mysql-test/r/merge-sync.result 2008-07-09 14:27:18 +0000
+++ b/mysql-test/r/merge-sync.result 2009-12-21 14:09:47 +0000
@@ -56,7 +56,7 @@ c1
SET DEBUG_SYNC= 'RESET';
DROP TABLE m1, t1;
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
-LOCK TABLE t1 WRITE;
+LOCK TABLE t1 READ;
connection con1
SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked';
INSERT INTO t1 VALUES (1);
=== modified file 'mysql-test/r/sp-threads.result'
--- a/mysql-test/r/sp-threads.result 2007-12-20 15:57:11 +0000
+++ b/mysql-test/r/sp-threads.result 2009-12-21 14:09:47 +0000
@@ -35,7 +35,7 @@ call bug9486();
show processlist;
Id User Host db Command Time State Info
# root localhost test Sleep # NULL 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 NULL
unlock tables;
=== modified file 'mysql-test/r/truncate_coverage.result'
--- a/mysql-test/r/truncate_coverage.result 2009-07-21 16:53:40 +0000
+++ b/mysql-test/r/truncate_coverage.result 2009-12-21 14:09:47 +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/r/rpl_locktrans_innodb.result'
--- a/mysql-test/suite/rpl/r/rpl_locktrans_innodb.result 2009-09-16 13:31:23 +0000
+++ b/mysql-test/suite/rpl/r/rpl_locktrans_innodb.result 2009-12-21 14:09:47 +0000
@@ -499,42 +499,11 @@ c1
UNLOCK TABLES;
# connection default.
TRUNCATE t1;
-#
-# LOW_PRIORITY WRITE locks wait for readers (autocommit).
-# Insert a value.
-INSERT INTO t1 VALUES(111);
-# Take a non-transactional lock.
-LOCK TABLE t1 READ;
-# connection conn1.
-# Take a non-transactional LOW_PRIORITY WRITE lock,
-# which waits in background until all read locks are released.
-LOCK TABLE t1 LOW_PRIORITY WRITE;
-# connection default.
-# Wait for the helper thread to sit on its lock.
-# connection conn2.
-# Take a non-transactional READ lock,
-# which goes before the LOW_PRIORITY WRITE lock.
-LOCK TABLE t1 READ;
-# The READ lock could be taken immediately.
-# Select from the table.
-SELECT * FROM t1;
-c1
-111
-# Unlock table.
-UNLOCK TABLES;
-SET AUTOCOMMIT= 0;
-# connection default.
-# Unlock this connections non-transactional lock.
-UNLOCK TABLES;
+# QQ Part of test below is disabled because it requires proper
+# support of LOCK TABLES ... LOW_PRIORITY WRITE. What should
+# we do about it?
# connection conn1.
-# Now the LOW_PRIORITY WRITE lock is taken.
-# Insert a value.
-INSERT INTO t1 VALUES(1111);
-# Unlock table.
-UNLOCK TABLES;
SET AUTOCOMMIT= 0;
-# connection default.
-TRUNCATE t1;
SET AUTOCOMMIT= 0;
COMMIT;
#
@@ -882,12 +851,12 @@ COMMIT;
# Create a trigger on t1 that updates t2.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
BEGIN
-# This will time out. So use a small value.
-INSERT INTO t2 SELECT GET_LOCK("mysqltest1", 1);
+DO GET_LOCK("mysqltest1", 10);
UPDATE t2 SET c2= c2 + 111;
-INSERT INTO t2 SELECT RELEASE_LOCK("mysqltest1");
+DO RELEASE_LOCK("mysqltest1");
END//
-# Take an SQL lock which blocks the trigger.
+# connection conn2.
+# Take an SQL lock which will block the trigger.
SELECT GET_LOCK("mysqltest1", 10);
GET_LOCK("mysqltest1", 10)
1
@@ -895,32 +864,34 @@ GET_LOCK("mysqltest1", 10)
# Insert into t1 to fire trigger. This waits on GET_LOCK.
INSERT INTO t1 VALUES(111222);
# connection default.
-# Wait for the helper thread to sit on its lock.
-# Take a write lock. This waits until the trigger times out.
-LOCK TABLE t2 WRITE;
-# Use the lock for insert.
-INSERT INTO t2 VALUES (111333);
-# Release the lock again.
-UNLOCK TABLES;
-# Release the SQL lock.
+# Wait for the conn1 thread to sit on user lock.
+# Try to take a write lock. Since it will allow DDL on t2 it has
+# to wait until transaction in conn1 commits.
+LOCK TABLE t2 WRITE;;
+# connection conn2.
+# Check that LOCK TABLES is blocked.
+# Unblock trigger in conn1.
SELECT RELEASE_LOCK("mysqltest1");
RELEASE_LOCK("mysqltest1")
1
# connection conn1.
-# Trigger timed out.
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
-# Commit.
+# Commit. Should unblock LOCK TABLES.
COMMIT;
# connection default.
+# Use the lock for insert.
+INSERT INTO t2 VALUES (111333);
+# Release the lock again.
+UNLOCK TABLES;
# Commit.
COMMIT;
# Show the results.
SELECT * FROM t1;
c1
111
+111222
SELECT * FROM t2;
c2
-222
+333
111333
TRUNCATE t1;
TRUNCATE t2;
=== modified file 'mysql-test/suite/rpl/r/rpl_locktrans_myisam.result'
--- a/mysql-test/suite/rpl/r/rpl_locktrans_myisam.result 2009-07-31 17:53:36 +0000
+++ b/mysql-test/suite/rpl/r/rpl_locktrans_myisam.result 2009-12-21 14:09:47 +0000
@@ -391,42 +391,11 @@ c1
UNLOCK TABLES;
# connection default.
TRUNCATE t1;
-#
-# LOW_PRIORITY WRITE locks wait for readers (autocommit).
-# Insert a value.
-INSERT INTO t1 VALUES(111);
-# Take a non-transactional lock.
-LOCK TABLE t1 READ;
-# connection conn1.
-# Take a non-transactional LOW_PRIORITY WRITE lock,
-# which waits in background until all read locks are released.
-LOCK TABLE t1 LOW_PRIORITY WRITE;
-# connection default.
-# Wait for the helper thread to sit on its lock.
-# connection conn2.
-# Take a non-transactional READ lock,
-# which goes before the LOW_PRIORITY WRITE lock.
-LOCK TABLE t1 READ;
-# The READ lock could be taken immediately.
-# Select from the table.
-SELECT * FROM t1;
-c1
-111
-# Unlock table.
-UNLOCK TABLES;
-SET AUTOCOMMIT= 0;
-# connection default.
-# Unlock this connections non-transactional lock.
-UNLOCK TABLES;
+# QQ Part of test below is disabled because it requires proper
+# support of LOCK TABLES ... LOW_PRIORITY WRITE. What should
+# we do about it?
# connection conn1.
-# Now the LOW_PRIORITY WRITE lock is taken.
-# Insert a value.
-INSERT INTO t1 VALUES(1111);
-# Unlock table.
-UNLOCK TABLES;
SET AUTOCOMMIT= 0;
-# connection default.
-TRUNCATE t1;
SET AUTOCOMMIT= 0;
COMMIT;
#
=== 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 2009-12-21 14:09:47 +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 2009-12-21 14:09:47 +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-09-09 13:01:25 +0000
+++ b/mysql-test/t/innodb_mysql_lock.test 2009-12-21 14:09:47 +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-09-28 16:34:26 +0000
+++ b/mysql-test/t/lock_multi.test 2009-12-21 14:09:47 +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/mdl_sync.test'
--- a/mysql-test/t/mdl_sync.test 2009-10-29 09:13:48 +0000
+++ b/mysql-test/t/mdl_sync.test 2009-12-21 14:09:47 +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/merge-sync.test'
--- a/mysql-test/t/merge-sync.test 2008-07-09 14:27:18 +0000
+++ b/mysql-test/t/merge-sync.test 2009-12-21 14:09:47 +0000
@@ -384,7 +384,7 @@ if (0)
# Coverage test for wait_for_lock.
#
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
-LOCK TABLE t1 WRITE;
+LOCK TABLE t1 READ;
#
--echo connection con1
connect (con1,localhost,root,,);
=== modified file 'mysql-test/t/multi_update.test'
--- a/mysql-test/t/multi_update.test 2009-02-11 16:48:50 +0000
+++ b/mysql-test/t/multi_update.test 2009-12-21 14:09:47 +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-07-21 16:53:40 +0000
+++ b/mysql-test/t/truncate_coverage.test 2009-12-21 14:09:47 +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 2009-10-29 09:13:48 +0000
+++ b/sql/mdl.cc 2009-12-21 14:09:47 +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;
/**
@@ -39,26 +43,8 @@ public:
typedef Ticket_list::Iterator Ticket_iterator;
- /** The type of lock (shared or exclusive). */
- enum
- {
- MDL_LOCK_SHARED,
- MDL_LOCK_EXCLUSIVE,
- } type;
/** 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 shared lock. */
- Ticket_list waiting_shared;
- /**
- Tickets for contexts waiting to acquire 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. */
@@ -66,8 +52,7 @@ 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 can_grant_lock(const MDL_context *requestor_ctx,
@@ -75,12 +60,152 @@ public:
inline static MDL_lock *create(const MDL_key *key);
inline static void destroy(MDL_lock *lock);
+
+ bool has_pending_lock(enum_mdl_type type) const
+ {
+ Ticket_iterator it(waiting);
+ MDL_ticket *ticket;
+
+ safe_mutex_assert_owner(&lock);
+
+ /*
+ 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(&lock);
+
+ while ((ticket= it++))
+ if (ticket->m_type == type)
+ return TRUE;
+ return FALSE;
+ }
+
+ void add_pending(MDL_ticket *ticket)
+ {
+ safe_mutex_assert_owner(&lock);
+
+ waiting.push_front(ticket);
+ }
+
+ void remove_pending(MDL_ticket *ticket)
+ {
+ safe_mutex_assert_owner(&lock);
+
+ waiting.remove(ticket);
+ }
+
+ void add_granted(MDL_ticket *ticket)
+ {
+ safe_mutex_assert_owner(&lock);
+
+ granted.push_front(ticket);
+ switch (ticket->m_type)
+ {
+ case MDL_UPGRADABLE_NO_WRITE:
+ type= MDL_LOCK_NO_WRITE;
+ break;
+ case MDL_UPGRADABLE_NO_READ_WRITE:
+ type= MDL_LOCK_NO_READ_WRITE;
+ break;
+ case MDL_EXCLUSIVE:
+ type= MDL_LOCK_EXCLUSIVE;
+ break;
+ default:
+ /*
+ Some of requests for shared type of lock can be satisfied even
+ if current lock type is UNW or UNRW. So we leave current type
+ of lock unchanged in this case.
+ */
+ break;
+ }
+ }
+
+ void remove_granted(MDL_ticket *ticket)
+ {
+ safe_mutex_assert_owner(&lock);
+
+ granted.remove(ticket);
+ switch (ticket->m_type)
+ {
+ case MDL_UPGRADABLE_NO_WRITE:
+ case MDL_UPGRADABLE_NO_READ_WRITE:
+ case MDL_EXCLUSIVE:
+ type= MDL_LOCK_SHARED;
+ break;
+ default:
+ /*
+ If we are releasing ticket of one of shared types there still
+ can be an active UNW or UNRW lock. So we leave current type of
+ lock unchanged.
+ */
+ break;
+ }
+ }
+
+ void notify_shared_locks(MDL_context *ctx)
+ {
+ Ticket_iterator it(granted);
+ MDL_ticket *conflicting_ticket;
+
+ safe_mutex_assert_owner(&lock);
+
+ while ((conflicting_ticket= it++))
+ {
+ if (conflicting_ticket->m_ctx != ctx)
+ notify_shared_lock(ctx->get_thd(), conflicting_ticket);
+ }
+ }
+
+ void wake_up_waiters()
+ {
+ Ticket_iterator it(waiting);
+ MDL_ticket *waiting_ticket;
+
+ safe_mutex_assert_owner(&lock);
+
+ /*
+ We wake up threads waiting for shared lock even if there is a
+ pending exclusive lock as some them might be trying to acquire
+ high priority shared lock.
+
+ QQ: Should we implement selective waking up?
+ */
+ while ((waiting_ticket= it++))
+ waiting_ticket->get_ctx()->wake_up();
+ }
+
private:
+ /** The type of lock. */
+ enum
+ {
+ MDL_LOCK_SHARED,
+ MDL_LOCK_NO_WRITE,
+ MDL_LOCK_NO_READ_WRITE,
+ MDL_LOCK_EXCLUSIVE,
+ } type;
+
+ /** 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)
- : type(MDL_LOCK_SHARED),
- key(key_arg),
+ : key(key_arg),
cached_object(NULL),
- cached_object_release_hook(NULL)
+ cached_object_release_hook(NULL),
+ type(MDL_LOCK_SHARED)
{
pthread_mutex_init(&lock, NULL);
}
@@ -126,7 +251,7 @@ public:
waiting_intention_exclusive.is_empty() &&
active_shared == 0 && active_intention_exclusive == 0);
}
- bool is_lock_type_compatible(enum_mdl_type type, bool is_upgrade) const;
+ bool is_lock_type_compatible(enum_mdl_type type) const;
};
@@ -206,11 +331,12 @@ void mdl_destroy()
This is to be called when a new server connection is created.
*/
-void MDL_context::init(THD *thd_arg)
+void MDL_context::init(THD *thd_arg, bool needs_thr_lock_abort)
{
m_has_global_shared_lock= FALSE;
m_global_intention_exclusive_locks= 0;
m_thd= thd_arg;
+ m_needs_thr_lock_abort= needs_thr_lock_abort;
/*
FIXME: In reset_n_backup_open_tables_state,
we abuse "init" as a reset, i.e. call it on an already
@@ -263,6 +389,8 @@ void MDL_context::backup_and_reset(MDL_c
backup->m_has_global_shared_lock= m_has_global_shared_lock;
backup->m_global_intention_exclusive_locks=
m_global_intention_exclusive_locks;
+
+ backup->m_needs_thr_lock_abort= m_needs_thr_lock_abort;
/*
When the main context is swapped out, one can not take
the global shared lock, and one can not rely on it:
@@ -271,6 +399,7 @@ void MDL_context::backup_and_reset(MDL_c
*/
m_has_global_shared_lock= FALSE;
m_global_intention_exclusive_locks= 0;
+ m_needs_thr_lock_abort= FALSE;
}
@@ -288,6 +417,7 @@ void MDL_context::restore_from_backup(MD
m_global_intention_exclusive_locks=
backup->m_global_intention_exclusive_locks;
m_has_global_shared_lock= backup->m_has_global_shared_lock;
+ m_needs_thr_lock_abort= backup->m_needs_thr_lock_abort;
}
@@ -519,41 +649,51 @@ 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 which will trigger assert
- (*) 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.
+ (***) This method doesn't handle requests for global shared lock.
*/
bool
-MDL_global_lock::is_lock_type_compatible(enum_mdl_type type,
- bool is_upgrade) const
+MDL_global_lock::is_lock_type_compatible(enum_mdl_type type) const
{
switch (type)
{
case MDL_SHARED:
case MDL_SHARED_HIGH_PRIO:
+ case MDL_SHARED_READ:
+ case MDL_SHARED_WRITE:
return TRUE;
break;
- case MDL_SHARED_UPGRADABLE:
+ case MDL_UPGRADABLE_NO_WRITE:
+ case MDL_UPGRADABLE_NO_READ_WRITE:
+ case MDL_EXCLUSIVE:
if (active_shared || ! waiting_shared.is_empty())
{
/*
@@ -565,32 +705,6 @@ MDL_global_lock::is_lock_type_compatible
else
return TRUE;
break;
- case MDL_EXCLUSIVE:
- if (is_upgrade)
- {
- /*
- We are upgrading MDL_SHARED to MDL_EXCLUSIVE.
-
- There should be no conflicting global locks since for each upgradable
- shared lock we obtain intention exclusive global lock first.
- */
- DBUG_ASSERT(active_shared == 0 && active_intention_exclusive);
- return TRUE;
- }
- else
- {
- if (active_shared || ! waiting_shared.is_empty())
- {
- /*
- We are going to obtain intention exclusive global lock and
- there is active or pending shared global lock.
- */
- return FALSE;
- }
- else
- return TRUE;
- }
- break;
default:
DBUG_ASSERT(0);
}
@@ -603,31 +717,37 @@ MDL_global_lock::is_lock_type_compatible
@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
@@ -638,44 +758,71 @@ MDL_lock::can_grant_lock(const MDL_conte
switch (type_arg) {
case MDL_SHARED:
- case MDL_SHARED_UPGRADABLE:
+ if (type != MDL_lock::MDL_LOCK_EXCLUSIVE &&
+ ! has_pending_lock(MDL_EXCLUSIVE))
+ can_grant= TRUE;
+ break;
case MDL_SHARED_HIGH_PRIO:
+ if (type != MDL_lock::MDL_LOCK_EXCLUSIVE)
+ can_grant= TRUE;
+ break;
+ case MDL_SHARED_READ:
+ if (type == MDL_LOCK_SHARED || type == MDL_LOCK_NO_WRITE)
+ {
+ if (! has_pending_lock(MDL_EXCLUSIVE) &&
+ ! has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE))
+ can_grant= TRUE;
+ }
+ break;
+ case MDL_SHARED_WRITE:
if (type == MDL_lock::MDL_LOCK_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) &&
+ ! has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE) &&
+ ! has_pending_lock(MDL_UPGRADABLE_NO_WRITE))
can_grant= TRUE;
}
- else if (granted.head()->get_ctx() == requestor_ctx)
+ break;
+ case MDL_UPGRADABLE_NO_WRITE:
+ if (type == MDL_lock::MDL_LOCK_SHARED)
{
- /*
- 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) &&
+ ! has_granted_lock(MDL_SHARED_WRITE))
can_grant= TRUE;
}
break;
+ case MDL_UPGRADABLE_NO_READ_WRITE:
+ if (type == MDL_lock::MDL_LOCK_SHARED)
+ {
+ 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(type == MDL_lock::MDL_LOCK_NO_WRITE ||
+ type == MDL_lock::MDL_LOCK_NO_READ_WRITE);
+
/*
- There should be no active exclusive locks since we own shared lock
- on the object.
- */
- DBUG_ASSERT(type == MDL_lock::MDL_LOCK_SHARED);
+ 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?
+ */
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;
}
@@ -702,6 +849,9 @@ MDL_lock::can_grant_lock(const MDL_conte
@param mdl_request Lock request object for lock to be acquired
+ @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.
*/
@@ -713,7 +863,7 @@ MDL_context::find_ticket(MDL_request *md
while ((ticket= it++))
{
- if (mdl_request->type == ticket->m_type &&
+ if (mdl_request->type <= ticket->m_type &&
mdl_request->key.is_equal(&ticket->m_lock->key))
break;
}
@@ -740,7 +890,8 @@ MDL_context::
try_acquire_global_intention_exclusive_lock(MDL_request *mdl_request,
bool *acquired)
{
- 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 ||
mdl_request->type == MDL_EXCLUSIVE);
*acquired= FALSE;
@@ -754,7 +905,7 @@ try_acquire_global_intention_exclusive_l
if (! m_global_intention_exclusive_locks)
{
pthread_mutex_lock(&LOCK_mdl_global);
- if (!global_lock.is_lock_type_compatible(mdl_request->type, FALSE))
+ if (! global_lock.is_lock_type_compatible(mdl_request->type))
{
pthread_mutex_unlock(&LOCK_mdl_global);
return FALSE;
@@ -785,7 +936,9 @@ MDL_context::acquire_global_intention_ex
const char *old_msg;
st_my_thread_var *mysys_var= my_thread_var;
- 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);
if (m_has_global_shared_lock)
{
@@ -820,7 +973,7 @@ MDL_context::acquire_global_intention_ex
old_msg= MDL_ENTER_COND(m_thd, mysys_var, &m_cond, &LOCK_mdl_global);
- if (unlikely(! global_lock.is_lock_type_compatible(mdl_request->type, FALSE)))
+ if (unlikely(! global_lock.is_lock_type_compatible(mdl_request->type)))
{
MDL_ticket *pending_ticket;
@@ -839,7 +992,7 @@ MDL_context::acquire_global_intention_ex
{
pthread_cond_wait(&this->m_cond, &LOCK_mdl_global);
}
- while (! global_lock.is_lock_type_compatible(mdl_request->type, FALSE) &&
+ while (! global_lock.is_lock_type_compatible(mdl_request->type) &&
! mysys_var->abort);
global_lock.waiting_intention_exclusive.remove(pending_ticket);
@@ -933,12 +1086,12 @@ MDL_context::try_acquire_shared_lock(MDL
if ((ticket= find_ticket(mdl_request)))
{
DBUG_ASSERT(ticket->m_state == MDL_ACQUIRED);
- DBUG_ASSERT(ticket->is_shared());
mdl_request->ticket= ticket;
return FALSE;
}
- if (mdl_request->type == MDL_SHARED_UPGRADABLE)
+ if (mdl_request->type == MDL_UPGRADABLE_NO_WRITE ||
+ mdl_request->type == MDL_UPGRADABLE_NO_READ_WRITE)
{
bool acquired;
@@ -950,7 +1103,8 @@ MDL_context::try_acquire_shared_lock(MDL
if (!(ticket= MDL_ticket::create(this, mdl_request->type)))
{
- if (mdl_request->type == MDL_SHARED_UPGRADABLE)
+ if (mdl_request->type == MDL_UPGRADABLE_NO_WRITE ||
+ mdl_request->type == MDL_UPGRADABLE_NO_READ_WRITE)
release_global_intention_exclusive_lock();
return TRUE;
}
@@ -966,7 +1120,8 @@ MDL_context::try_acquire_shared_lock(MDL
MDL_lock::destroy(lock);
pthread_mutex_unlock(&LOCK_mdl_hash);
MDL_ticket::destroy(ticket);
- if (mdl_request->type == MDL_SHARED_UPGRADABLE)
+ if (mdl_request->type == MDL_UPGRADABLE_NO_WRITE ||
+ mdl_request->type == MDL_UPGRADABLE_NO_READ_WRITE)
release_global_intention_exclusive_lock();
return TRUE;
}
@@ -979,7 +1134,7 @@ MDL_context::try_acquire_shared_lock(MDL
if (lock->can_grant_lock(this, mdl_request->type, FALSE))
{
mdl_request->ticket= ticket;
- lock->granted.push_front(ticket);
+ lock->add_granted(ticket);
m_tickets.push_front(ticket);
ticket->m_state= MDL_ACQUIRED;
ticket->m_lock= lock;
@@ -991,7 +1146,8 @@ MDL_context::try_acquire_shared_lock(MDL
DBUG_ASSERT(! lock->is_empty());
pthread_mutex_unlock(&lock->lock);
MDL_ticket::destroy(ticket);
- if (mdl_request->type == MDL_SHARED_UPGRADABLE)
+ if (mdl_request->type == MDL_UPGRADABLE_NO_WRITE ||
+ mdl_request->type == MDL_UPGRADABLE_NO_READ_WRITE)
release_global_intention_exclusive_lock();
}
@@ -1011,19 +1167,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 thread which holds conflicting lock is waiting inside of MDL
subsystem it has to be woken up by calling MDL_context::wake_up().
*/
- conflicting_ticket->get_ctx()->wake_up();
+ conflicting_ctx->wake_up();
/*
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_ctx->m_needs_thr_lock_abort);
}
}
@@ -1050,7 +1208,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);
@@ -1065,7 +1225,6 @@ bool MDL_context::acquire_exclusive_lock
if ((ticket= find_ticket(mdl_request)))
{
DBUG_ASSERT(ticket->m_state == MDL_ACQUIRED);
- DBUG_ASSERT(ticket->m_type == MDL_EXCLUSIVE);
mdl_request->ticket= ticket;
return FALSE;
}
@@ -1099,18 +1258,14 @@ bool MDL_context::acquire_exclusive_lock
pthread_mutex_unlock(&LOCK_mdl_hash);
mdl_request->ticket= ticket;
- lock->waiting_exclusive.push_front(ticket);
+ lock->add_pending(ticket);
ticket->m_lock= lock;
old_msg= MDL_ENTER_COND(m_thd, mysys_var, &m_cond, &lock->lock);
while (!lock->can_grant_lock(this, mdl_request->type, FALSE))
{
- 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");
@@ -1143,22 +1298,8 @@ bool MDL_context::acquire_exclusive_lock
pthread_mutex_lock(&LOCK_mdl_hash);
pthread_mutex_lock(&lock->lock);
/* Get rid of pending ticket. */
- lock->waiting_exclusive.remove(ticket);
- /*
- If there are no active/pending exclusive locks wake up contexts
- waiting for shared lock.
- */
- if (lock->type == MDL_lock::MDL_LOCK_SHARED &&
- ! lock->waiting_shared.is_empty() &&
- lock->waiting_exclusive.is_empty())
- {
- MDL_lock::Ticket_iterator it(lock->waiting_shared);
- MDL_ticket *wake_up_ticket;
- while ((ticket= it++))
- pthread_cond_signal(&wake_up_ticket->get_ctx()->m_cond);
- pthread_mutex_unlock(&lock->lock);
- }
- else if (lock->is_empty())
+ lock->remove_pending(ticket);
+ if (lock->is_empty())
{
my_hash_delete(&mdl_locks, (uchar *)lock);
if (lock->cached_object)
@@ -1166,6 +1307,15 @@ bool MDL_context::acquire_exclusive_lock
pthread_mutex_unlock(&lock->lock);
MDL_lock::destroy(lock);
}
+ else
+ {
+ /*
+ If there are no active/pending exclusive locks wake up contexts
+ waiting for shared lock.
+ */
+ lock->wake_up_waiters();
+ pthread_mutex_unlock(&lock->lock);
+ }
pthread_mutex_unlock(&LOCK_mdl_hash);
MDL_ticket::destroy(ticket);
release_global_intention_exclusive_lock();
@@ -1174,10 +1324,8 @@ bool MDL_context::acquire_exclusive_lock
}
}
- lock->type= MDL_lock::MDL_LOCK_EXCLUSIVE;
-
- lock->waiting_exclusive.remove(ticket);
- lock->granted.push_front(ticket);
+ lock->remove_pending(ticket);
+ lock->add_granted(ticket);
m_tickets.push_front(ticket);
ticket->m_state= MDL_ACQUIRED;
@@ -1314,8 +1462,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
@@ -1335,7 +1484,7 @@ MDL_ticket::upgrade_shared_lock_to_exclu
DBUG_RETURN(TRUE);
}
pending_ticket->m_lock= m_lock;
- 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_cond, &m_lock->lock);
@@ -1344,35 +1493,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);
-
- /*
- A temporary work-around to avoid deadlocks/livelocks in situation
- when we in one connection ALTER TABLE tries to upgrade metadata
- lock on the table and in another connection we have a transaction
- which already has obtained metadata lock on the table in one of
- its previous statement and now tries to update table being altered.
- In such case this transaction will try to get metadata lock on
- table first. It will succeed since it already has one. Then it will
- try to get table-level lock and will get aborted in the process.
- After that the loop will be started again.
-
- The call below breaks this loop by forcing transactions to call
- MDL_context::wait_for_locks() (even if transaction doesn't need
- any new metadata locks) which will emit ER_LOCK_DEADLOCK error
- in such situation.
-
- 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");
@@ -1395,31 +1516,25 @@ MDL_ticket::upgrade_shared_lock_to_exclu
if (mysys_var->abort)
{
/* Get rid of auxiliary pending ticket. */
- m_lock->waiting_exclusive.remove(pending_ticket);
+ m_lock->remove_pending(pending_ticket);
MDL_ticket::destroy(pending_ticket);
/*
If there are no other pending requests for exclusive locks
wake up threads waiting for chance to acquire shared lock.
*/
- if (! m_lock->waiting_shared.is_empty() &&
- m_lock->waiting_exclusive.is_empty())
- {
- MDL_lock::Ticket_iterator it(m_lock->waiting_shared);
- MDL_ticket *wake_up_ticket;
- while ((wake_up_ticket= it++))
- wake_up_ticket->get_ctx()->wake_up();
- }
+ m_lock->wake_up_waiters();
MDL_EXIT_COND(thd, mysys_var, &m_lock->lock, old_msg);
DBUG_RETURN(TRUE);
}
}
- m_lock->type= MDL_lock::MDL_LOCK_EXCLUSIVE;
+ m_lock->remove_granted(this);
/* Set the new type of lock in the ticket. */
m_type= MDL_EXCLUSIVE;
+ m_lock->add_granted(this);
/* Get rid of auxiliary pending ticket. */
- m_lock->waiting_exclusive.remove(pending_ticket);
+ m_lock->remove_pending(pending_ticket);
MDL_ticket::destroy(pending_ticket);
if (m_lock->cached_object)
@@ -1498,8 +1613,7 @@ MDL_context::try_acquire_exclusive_lock(
pthread_mutex_lock(&lock->lock);
pthread_mutex_unlock(&LOCK_mdl_hash);
mdl_request->ticket= ticket;
- lock->type= MDL_lock::MDL_LOCK_EXCLUSIVE;
- lock->granted.push_front(ticket);
+ lock->add_granted(ticket);
m_tickets.push_front(ticket);
ticket->m_state= MDL_ACQUIRED;
ticket->m_lock= lock;
@@ -1673,7 +1787,7 @@ MDL_context::wait_for_locks(MDL_request_
DBUG_ASSERT(mdl_request->ticket == NULL);
pthread_mutex_lock(&LOCK_mdl_global);
- if (unlikely (!global_lock.is_lock_type_compatible(mdl_request->type, FALSE)))
+ if (unlikely (!global_lock.is_lock_type_compatible(mdl_request->type)))
{
MDL_ticket *pending_ticket;
if (! (pending_ticket= MDL_ticket::create(this, mdl_request->type)))
@@ -1725,7 +1839,7 @@ MDL_context::wait_for_locks(MDL_request_
pthread_mutex_unlock(&lock->lock);
return TRUE;
}
- lock->waiting_shared.push_front(pending_ticket);
+ lock->add_pending(pending_ticket);
old_msg= MDL_ENTER_COND(m_thd, mysys_var, &m_cond, &lock->lock);
@@ -1735,7 +1849,7 @@ MDL_context::wait_for_locks(MDL_request_
pthread_mutex_lock(&LOCK_mdl_hash);
pthread_mutex_lock(&lock->lock);
- lock->waiting_shared.remove(pending_ticket);
+ lock->remove_pending(pending_ticket);
if (lock->is_empty())
{
my_hash_delete(&mdl_locks, (uchar *)lock);
@@ -1784,20 +1898,11 @@ void MDL_context::release_lock(MDL_ticke
pthread_mutex_lock(&LOCK_mdl_hash);
pthread_mutex_lock(&lock->lock);
- switch (ticket->m_type)
- {
- case MDL_SHARED_UPGRADABLE:
- case MDL_SHARED:
- case MDL_SHARED_HIGH_PRIO:
- lock->granted.remove(ticket);
- break;
- case MDL_EXCLUSIVE:
- lock->type= MDL_lock::MDL_LOCK_SHARED;
- lock->granted.remove(ticket);
- break;
- default:
- DBUG_ASSERT(0);
- }
+ /*
+ Removal of ticket from MDL_lock::granted list also sets
+ MDL_lock::type appropriately.
+ */
+ lock->remove_granted(ticket);
if (lock->is_empty())
{
@@ -1811,27 +1916,14 @@ void MDL_context::release_lock(MDL_ticke
}
else
{
- if (lock->type == MDL_lock::MDL_LOCK_SHARED)
- {
- MDL_lock::Ticket_iterator it(lock->waiting_shared);
- MDL_lock::Ticket_iterator it2(lock->waiting_exclusive);
- MDL_ticket *waiting_ticket;
- /*
- We wake up threads waiting for shared lock even if there is a
- pending exclusive lock as some them might be trying to acquire
- high priority shared lock.
- */
- while ((waiting_ticket= it++))
- waiting_ticket->get_ctx()->wake_up();
- while ((waiting_ticket= it2++))
- waiting_ticket->get_ctx()->wake_up();
- }
+ lock->wake_up_waiters();
pthread_mutex_unlock(&lock->lock);
}
pthread_mutex_unlock(&LOCK_mdl_hash);
- if (ticket->m_type == MDL_SHARED_UPGRADABLE ||
+ if (ticket->m_type == MDL_UPGRADABLE_NO_WRITE ||
+ ticket->m_type == MDL_UPGRADABLE_NO_READ_WRITE ||
ticket->m_type == MDL_EXCLUSIVE)
release_global_intention_exclusive_lock();
@@ -1913,17 +2005,17 @@ void MDL_ticket::downgrade_exclusive_loc
return;
pthread_mutex_lock(&m_lock->lock);
- m_lock->type= MDL_lock::MDL_LOCK_SHARED;
- 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()->wake_up();
- }
+ m_lock->remove_granted(this);
+ /*
+ 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->add_granted(this);
+ m_lock->wake_up_waiters();
pthread_mutex_unlock(&m_lock->lock);
}
@@ -2027,10 +2119,32 @@ bool MDL_ticket::has_pending_conflicting
bool result;
safe_mutex_assert_not_owner(&LOCK_open);
- DBUG_ASSERT(is_shared());
+ DBUG_ASSERT(m_state == MDL_ACQUIRED);
pthread_mutex_lock(&m_lock->lock);
- result= !m_lock->waiting_exclusive.is_empty();
+ switch (m_type)
+ {
+ case MDL_SHARED:
+ case MDL_SHARED_HIGH_PRIO:
+ result= m_lock->has_pending_lock(MDL_EXCLUSIVE);
+ break;
+ case MDL_SHARED_READ:
+ result= m_lock->has_pending_lock(MDL_EXCLUSIVE) ||
+ m_lock->has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE);
+ break;
+ case MDL_SHARED_WRITE:
+ result= m_lock->has_pending_lock(MDL_EXCLUSIVE) ||
+ m_lock->has_pending_lock(MDL_UPGRADABLE_NO_WRITE) ||
+ m_lock->has_pending_lock(MDL_UPGRADABLE_NO_READ_WRITE);
+ break;
+ case MDL_UPGRADABLE_NO_WRITE:
+ case MDL_UPGRADABLE_NO_READ_WRITE:
+ case MDL_EXCLUSIVE:
+ default:
+ /* This method should not be used for these types of tickets. */
+ DBUG_ASSERT(0);
+ break;
+ }
pthread_mutex_unlock(&m_lock->lock);
return result;
}
=== modified file 'sql/mdl.h'
--- a/sql/mdl.h 2009-10-29 09:13:48 +0000
+++ b/sql/mdl.h 2009-12-21 14:09:47 +0000
@@ -30,17 +30,110 @@ 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).
+ @see Comments for can_grant_lock() and can_grant_global_lock() for
+ lock compatibility matrices.
- @see Comments for can_grant_lock() and can_grant_global_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_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 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. */
@@ -282,12 +375,15 @@ public:
bool is_shared() const { return m_type < MDL_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();
private:
friend class MDL_context;
+ friend class MDL_lock;
MDL_ticket(MDL_context *ctx_arg, enum_mdl_type type_arg)
: m_type(type_arg),
@@ -338,7 +434,7 @@ public:
typedef Ticket_list::Iterator Ticket_iterator;
- void init(THD *thd);
+ void init(THD *thd, bool needs_thr_lock_abort);
void destroy();
void backup_and_reset(MDL_context *backup);
@@ -402,6 +498,16 @@ private:
queue of waiters).
*/
pthread_cond_t m_cond;
+ /**
+ TRUE - if this context breaks protocol and tries to acquire
+ table-level locks while having S lock on some the 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 *find_ticket(MDL_request *mdl_req);
@@ -417,6 +523,7 @@ private:
}
friend bool MDL_ticket::upgrade_shared_lock_to_exclusive();
+ friend void notify_shared_lock(THD *thd, MDL_ticket *conflicting_ticket);
};
@@ -428,9 +535,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 2009-10-16 08:24:11 +0000
+++ b/sql/mysql_priv.h 2009-12-21 14:09:47 +0000
@@ -2119,6 +2119,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 2009-09-25 14:15:30 +0000
+++ b/sql/sp_head.cc 2009-12-21 14:09:47 +0000
@@ -3970,7 +3970,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 */
@@ -4015,7 +4016,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 2009-10-16 08:24:11 +0000
+++ b/sql/sql_base.cc 2009-12-21 14:09:47 +0000
@@ -2115,39 +2115,13 @@ bool rename_temporary_table(THD* thd, TA
bool wait_while_table_is_used(THD *thd, TABLE *table,
enum ha_extra_function function)
{
- enum thr_lock_type old_lock_type;
DBUG_ENTER("wait_while_table_is_used");
DBUG_PRINT("enter", ("table: '%s' share: %p db_stat: %u version: %lu",
table->s->table_name.str, table->s,
table->db_stat, table->s->version));
- /*
- Wake-up threads waiting on the table-level lock for the table.
-
- TODO: Since the same effect could be achieved by calls to
- mysql_lock_abort_for_thread() which happen during metadata
- lock upgrade this call is redundant to some extent.
- But before removing it one should consider/fix the following
- issues:
- - mysql_notify_thread_having_shared_lock() which calls
- mysql_lock_abort_for_thread() doesn't properly handle
- tables open by HANDLER statement.
- - Code which uses mysql_lock_abort_for_thread() is not very
- good at handling threads which already managed to acquire
- metadata lock but has not yet tried to acquire table-level
- lock. The timeout (10 secs) which is used to catch such
- threads is probably too big. Moreover sometimes such thread
- will have to wait until some other thread releases metadata
- lock.
- */
- old_lock_type= table->reginfo.lock_type;
- mysql_lock_abort(thd, table, TRUE);
-
if (table->mdl_ticket->upgrade_shared_lock_to_exclusive())
- {
- mysql_lock_downgrade_write(thd, table, old_lock_type);
DBUG_RETURN(TRUE);
- }
pthread_mutex_lock(&LOCK_open);
tdc_remove_table(thd, TDC_RT_REMOVE_NOT_OWN,
@@ -2356,17 +2330,42 @@ 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 (thd->mdl_context.try_acquire_shared_lock(mdl_request))
@@ -4173,6 +4172,53 @@ bool open_tables(THD *thd, TABLE_LIST **
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())
+ 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.
*/
@@ -8211,15 +8257,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) &&
@@ -8233,19 +8283,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 holding shared metadata locks while waiting
@@ -8263,28 +8317,6 @@ bool mysql_notify_thread_having_shared_l
/**
- Force transactions holding shared metadata lock on the table to call
- MDL_context::wait_for_locks() 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_class.h'
--- a/sql/sql_class.h 2009-09-25 14:15:30 +0000
+++ b/sql/sql_class.h 2009-12-21 14:09:47 +0000
@@ -1034,8 +1034,8 @@ public:
locked_tables_mode= LTM_NONE;
state_flags= 0U;
m_reprepare_observer= NULL;
- mdl_context.init(thd);
- handler_mdl_context.init(thd);
+ mdl_context.init(thd, FALSE);
+ handler_mdl_context.init(thd, TRUE);
}
};
=== modified file 'sql/sql_handler.cc'
--- a/sql/sql_handler.cc 2009-09-22 11:44:58 +0000
+++ b/sql/sql_handler.cc 2009-12-21 14:09:47 +0000
@@ -819,6 +819,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->needs_reopen()))
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2009-09-25 14:15:30 +0000
+++ b/sql/sql_parse.cc 2009-12-21 14:09:47 +0000
@@ -2645,8 +2645,7 @@ mysql_execute_command(THD *thd)
!thd->locked_tables_mode)
global_schema_lock_guard.lock();
- 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
@@ -6370,7 +6369,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);
}
@@ -6606,6 +6607,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 2009-09-25 14:15:30 +0000
+++ b/sql/sql_prepare.cc 2009-12-21 14:09:47 +0000
@@ -1208,7 +1208,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++))
@@ -1289,7 +1290,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)
@@ -1366,7 +1367,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)
@@ -1424,7 +1426,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
@@ -1485,7 +1487,7 @@ static bool mysql_test_do_fields(Prepare
if (tables && check_table_access(thd, SELECT_ACL, tables, FALSE, FALSE, UINT_MAX))
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));
}
@@ -1514,7 +1516,7 @@ static bool mysql_test_set_fields(Prepar
set_var_base *var;
if ((tables && check_table_access(thd, SELECT_ACL, tables, FALSE, FALSE, UINT_MAX)) ||
- 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++))
@@ -1551,7 +1553,7 @@ static bool mysql_test_call_fields(Prepa
if ((tables && check_table_access(thd, SELECT_ACL, tables, FALSE, FALSE,
UINT_MAX)) ||
- 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++))
@@ -1634,7 +1636,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,
@@ -1678,7 +1681,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;
@@ -1697,7 +1701,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);
}
@@ -1730,7 +1735,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 2009-09-25 14:15:30 +0000
+++ b/sql/sql_table.cc 2009-12-21 14:09:47 +0000
@@ -4105,7 +4105,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-09-25 14:15:30 +0000
+++ b/sql/table.cc 2009-12-21 14:09:47 +0000
@@ -4716,11 +4716,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);
}
/*
@@ -4939,7 +4940,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-09-28 15:58:30 +0000
+++ b/sql/table.h 2009-12-21 14:09:47 +0000
@@ -1093,7 +1093,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-20091221140947-hvc4fw4shzi7rgik.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0-codebase-bugfixing branch (dlenev:3628)Bug#46272 | Dmitry Lenev | 21 Dec |
