MySQL Bugs: #56251: Deadlock with INSERT DELAYED and MERGE tables

Bug #56251	Deadlock with INSERT DELAYED and MERGE tables
Submitted:	25 Aug 2010 13:21	Modified:	20 Nov 2010 22:59
Reporter:	Jon Olav Hauglid	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Even with the fix for Bug#54332, it is possible to get a deadlock
with the combination of INSERT DELAYED and MERGE tables.
In 5.1, the test case below works - INSERT DELAYED fails with ER_DELAYED_NOT_SUPPORTED

How to repeat:
CREATE TABLE t1 (a INT) engine=MyISAM;
CREATE TABLE t2 (a INT) engine=MyISAM;
CREATE TABLE m1 (a INT) engine=MERGE UNION=(t1, t2);
connect (con1, localhost, root);

connection default;
START TRANSACTION;
SELECT * FROM t1;

connection con1;
--send ALTER TABLE t1 COMMENT 'test'

connection default;
--sleep 1
INSERT DELAYED INTO m1 VALUES (1);

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/118115

3136 Dmitry Lenev	2010-09-14
      Fix for bug #56251 "Deadlock with INSERT DELAYED and MERGE
      tables".
      
      Attempt to issue INSERT DELAYED statement for MERGE table
      might have ended with a deadlock if it has happened as part
      of 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 delayed insert handler thread tried
      to open MERGE table and discovered that to do this it has also
      to open all underlying tables as well and hence acquire metadata
      locks on them. Since metadata locks on underlying tables were
      not pre-acquired by a connection thread executing INSERT DELAYED
      attempt to do so might have led to waiting. In this case
      connection thread had to wait for delayed insert thread.
      If thread which was preventing lock on underlying table from 
      being acquired had to wait for the connection thread (due to
      this or other metadata lock it had) a deadlock has occurred. 
      This deadlock was not detected by MDL deadlock detector since 
      wait for the handler thread by connection thread is not
      represented in wait-for graph.
      
      This patch solves this problem by ensuring that the delayed
      insert handler thread never tries to open underlying tables 
      of a MERGE table. Instead open_tables() process is aborted 
      right after parent table is open and ER_DELAYED_NOT_SUPPORTED 
      error is emitted (which is passed to connection thread and
      ultimately to 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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/118312

3137 Dmitry Lenev	2010-09-15
      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.

Noted in 5.5.7 changelog.

An INSERT DELAYED statement for a MERGE table could cause deadlock if
it occurred as part of a transaction or under LOCK TABLES, and there
was a concurrent DDL or LOCK TABLES ... WRITE statement that tried to
lock one of its underlying tables.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (pib:21)