Fix for bug #56251 "Deadlock with INSERT DELAYED and MERGE

tables".

Attempting to issue an INSERT DELAYED statement for a MERGE
table might have caused a deadlock if it happened as part of
a transaction or under LOCK TABLES, and there was a concurrent
DDL or LOCK TABLES ... WRITE statement which tried to lock one
of its underlying tables.

The problem occurred when a delayed insert handler thread tried
to open a MERGE table and discovered that to do this it had also
to open all underlying tables and hence acquire metadata
locks on them. Since metadata locks on the underlying tables were
not pre-acquired by the connection thread executing INSERT DELAYED,
attempts to do so might lead to waiting. In this case the
connection thread had to wait for the delayed insert thread.
If the thread which was preventing the lock on the underlying table
from being acquired had to wait for the connection thread (due to
this or other metadata locks), a deadlock occurred. 
This deadlock was not detected by the MDL deadlock detector since 
waiting for the handler thread by the connection thread is not
represented in the wait-for graph.

This patch solves the problem by ensuring that the delayed
insert handler thread never tries to open underlying tables 
of a MERGE table. Instead open_tables() is aborted right after
the parent table is opened and a ER_DELAYED_NOT_SUPPORTED 
error is emitted (which is passed to the connection thread and
ultimately to the user).

mysql-test/r/merge.result:
  Added test for bug #56251 "Deadlock with INSERT DELAYED and
  MERGE tables".
mysql-test/t/merge.test:
  Added test for bug #56251 "Deadlock with INSERT DELAYED and
  MERGE tables".
sql/sql_base.cc:
  Changed open_n_lock_single_table() to take prelocking strategy
  as an argument instead of always using DML_prelocking_strategy.
sql/sql_base.h:
  Changed open_n_lock_single_table() to take prelocking strategy
  as an argument instead of always using DML_prelocking_strategy.
  Added a version of this function which is compatible with old
  signature.
sql/sql_insert.cc:
  When opening MERGE table in delayed insert thread stop and emit
  ER_DELAYED_NOT_SUPPORTED right after opening main table and
  before opening underlying tables. This ensures that we won't
  try to acquire metadata lock on underlying tables which might
  lead to a deadlock.
  This is achieved by using special prelocking strategy which
  abort open_tables() process as soon as we discover that we
  have opened table with engine which doesn't support delayed
  inserts.

1 files changed, 12 insertions, 1 deletions

diff --git a/sql/sql_base.h b/sql/sql_base.h
index b1a730ad277..7ae3971942b 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -246,7 +246,8 @@ bool open_and_lock_tables(THD *thd, TABLE_LIST *tables,
 int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived);
 /* simple open_and_lock_tables without derived handling for single table */
 TABLE *open_n_lock_single_table(THD *thd, TABLE_LIST *table_l,
-                                thr_lock_type lock_type, uint flags);
+                                thr_lock_type lock_type, uint flags,
+                                Prelocking_strategy *prelocking_strategy);
 bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags);
 bool lock_tables(THD *thd, TABLE_LIST *tables, uint counter, uint flags);
 int decide_logging_format(THD *thd, TABLE_LIST *tables);
@@ -455,6 +456,16 @@ open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags)
 }
 
 
+inline TABLE *open_n_lock_single_table(THD *thd, TABLE_LIST *table_l,
+                                       thr_lock_type lock_type, uint flags)
+{
+  DML_prelocking_strategy prelocking_strategy;
+
+  return open_n_lock_single_table(thd, table_l, lock_type, flags,
+                                  &prelocking_strategy);
+}
+
+
 /* open_and_lock_tables with derived handling */
 inline bool open_and_lock_tables(THD *thd, TABLE_LIST *tables,
                                  bool derived, uint flags)