Backport of:

------------------------------------------------------------
revno: 2617.68.7
committer: Dmitry Lenev <dlenev@mysql.com>
branch nick: mysql-next-bg46044
timestamp: Thu 2009-08-27 10:22:17 +0400
message:
  Fix for bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY
  FOR UPDATE".

  Deadlock occured when during execution of query to I_S we tried to open
  a table or its .FRM in order to get information about it and had to wait
  because we have encountered exclusive metadata lock on this table held by
  a DDL operation from another connection which in its turn waited for some
  resource currently owned by connection executing this I_S query.
  For example, this might have happened if one under LOCK TABLES executed I_S
  query targeted to particular table (which was not among locked) and also
  concurrently tried to create this table using CREATE TABLE SELECT which
  had to wait for one of tables locked by the first connection.
  Another situation in which deadlock might have occured is when I_S query,
  which was executed as part of transaction, tried to get information about
  table which just has been dropped by concurrent DROP TABLES executed under
  LOCK TABLES and this DROP TABLES for its completion also had to wait
  transaction from the first connection.

  This problem stemmed from the fact that opening of tables/.FRMs for I_S
  filling is happening outside of connection's main MDL_context so code
  which tries to detect deadlocks due to conflicting metadata locks doesn't
  work in this case. Indeed, this led to deadlocks when during I_S filling
  we tried to wait for conflicting metadata lock to go away, while its owner
  was waiting for some resource held by connection executing I_S query.

  This patch solves this problem by avoiding waiting in such situation.
  Instead we skip this table and produce warning that information about
  it was omitted from I_S due to concurrent DDL operation. We still wait
  for conflicting metadata lock to go away when it is known that deadlock
  is not possible (i.e. when connection executing I_S query does not hold
  any metadata or table-level locks).
  Basically, we apply our standard deadlock avoidance technique for metadata
  locks to the process of filling of I_S tables but replace ER_LOCK_DEADLOCK
  error with a warning.
  Note that this change is supposed to be safe for 'mysqldump' since the
  only its mode which is affected by this change is --single-transaction mode
  is not safe in the presence of concurrent DDL anyway (and this fact is
  documented). Other modes are unaffected because they either use
  SHOW TABLES/SELECT * FROM I_S.TABLE_NAMES which do not take any metadata
  locks in the process of I_S table filling and thus cannot skip tables or
  execute I_S queries for tables which were previously locked by LOCK TABLES
  (or in the presence of global read lock) which excludes possibility of
  encountering conflicting metadata lock.


mysql-test/r/mdl_sync.result:
  Added test for bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE
  HIGH_PRIORITY FOR UPDATE".
mysql-test/t/mdl_sync.test:
  Added test for bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE
  HIGH_PRIORITY FOR UPDATE".
sql/mysql_priv.h:
  Added a new flag for open_table() call which allows it to fail
  with an error in cases when conflicting metadata lock is discovered
  instead of waiting until this lock goes away.
sql/share/errmsg-utf8.txt:
  Added error/warning message to be generated in cases when information
  about table is omitted from I_S since there is conflicting metadata lock
  on the table.
sql/share/errmsg.txt:
  Added error/warning message to be generated in cases when information
  about table is omitted from I_S since there is conflicting metadata lock
  on the table.
sql/sql_base.cc:
  Added a new flag for open_table() call which allows it to fail
  with an error in cases when conflicting metadata lock is discovered
  instead of waiting until this lock goes away.
sql/sql_show.cc:
  When we are opening a table (or just .FRM) in order to fill I_S with
  information about this table and encounter conflicting metadata lock
  waiting for this lock to go away can lead to a deadlock in some
  situations (under LOCK TABLES, within transaction, etc.). To avoid
  these deadlocks we detect such situations and don't do waiting.
  Instead, we skip table for which we have conflicting metadata lock,
  thus omitting information about it from I_S table, and produce an
  appropriate warning.

1 files changed, 111 insertions, 0 deletions

diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result
index 36451985a86..f63179b893a 100644
--- a/mysql-test/r/mdl_sync.result
+++ b/mysql-test/r/mdl_sync.result
@@ -62,3 +62,114 @@ unlock tables;
 # Reap INSERT.
 # Clean-up.
 drop tables t1, t2, t3, t5;
+#
+# Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY
+#             FOR UPDATE"
+#
+drop tables if exists t1, t2;
+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;
+# Switching to connection 'con46044'.
+# Sending:
+create table t2 select * from t1;;
+# Switching to connection 'default'.
+# Waiting until CREATE TABLE ... SELECT ... is blocked.
+# First let us check that SHOW FIELDS/DESCRIBE doesn't
+# gets blocked and emits and error.
+show fields from t2;
+ERROR HY000: Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
+# Now test for I_S query which reads only .FRMs.
+#
+# Query below should only emit a warning.
+select column_name from information_schema.columns
+where table_schema='test' and table_name='t2';
+column_name
+Warnings:
+Warning	1652	Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
+# Finally, test for I_S query which does full-blown table open.
+#
+# Query below should not be blocked. Warning message should be
+# stored in the 'table_comment' column.
+select table_name, table_type, auto_increment, table_comment
+from information_schema.tables where table_schema='test' and table_name='t2';
+table_name	table_type	auto_increment	table_comment
+t2	BASE TABLE	NULL	Table 'test'.'t2' was skipped since its definition is being modified by concurre
+# Switching to connection 'default'.
+unlock tables;
+# Switching to connection 'con46044'.
+# Reaping CREATE TABLE ... SELECT ... .
+drop table t2;
+#
+# Let us also check that queries to I_S wait for conflicting metadata
+# locks to go away instead of skipping table with a warning in cases
+# when deadlock is not possible. This is a nice thing from compatibility
+# and ease of use points of view.
+#
+# We check same three queries to I_S in this new situation.
+# Switching to connection 'con46044_2'.
+lock tables t1 write;
+# Switching to connection 'con46044'.
+# Sending:
+create table t2 select * from t1;;
+# Switching to connection 'default'.
+# Waiting until CREATE TABLE ... SELECT ... is blocked.
+# Let us check that SHOW FIELDS/DESCRIBE gets blocked.
+# Sending:
+show fields from t2;;
+# Switching to connection 'con46044_2'.
+# Wait until SHOW FIELDS gets blocked.
+unlock tables;
+# Switching to connection 'con46044'.
+# Reaping CREATE TABLE ... SELECT ... .
+# Switching to connection 'default'.
+# Reaping SHOW FIELDS ...
+Field	Type	Null	Key	Default	Extra
+i	int(11)	YES		NULL	
+drop table t2;
+# Switching to connection 'con46044_2'.
+lock tables t1 write;
+# Switching to connection 'con46044'.
+# Sending:
+create table t2 select * from t1;;
+# Switching to connection 'default'.
+# Waiting until CREATE TABLE ... SELECT ... is blocked.
+# Check that I_S query which reads only .FRMs gets blocked.
+# Sending:
+select column_name from information_schema.columns where table_schema='test' and table_name='t2';;
+# Switching to connection 'con46044_2'.
+# Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS  gets blocked.
+unlock tables;
+# Switching to connection 'con46044'.
+# Reaping CREATE TABLE ... SELECT ... .
+# Switching to connection 'default'.
+# Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS
+column_name
+i
+drop table t2;
+# Switching to connection 'con46044_2'.
+lock tables t1 write;
+# Switching to connection 'con46044'.
+# Sending:
+create table t2 select * from t1;;
+# Switching to connection 'default'.
+# Waiting until CREATE TABLE ... SELECT ... is blocked.
+# Finally, check that I_S query which does full-blown table open
+# also gets blocked.
+# Sending:
+select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2';;
+# Switching to connection 'con46044_2'.
+# Wait until SELECT ... FROM I_S.TABLES gets blocked.
+unlock tables;
+# Switching to connection 'con46044'.
+# Reaping CREATE TABLE ... SELECT ... .
+# Switching to connection 'default'.
+# Reaping SELECT ... FROM I_S.TABLES
+table_name	table_type	auto_increment	table_comment
+t2	BASE TABLE	NULL	
+drop table t2;
+# Switching to connection 'default'.
+# Clean-up.
+drop table t1;