Bug #54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
Submitted: 8 Jun 2010 11:31 Modified: 20 Nov 2010 22:58
Reporter: Jon Olav Hauglid Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.5-m3 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[8 Jun 2010 11:31] Jon Olav Hauglid
Description:
If two connections have a table each locked by LOCK TABLE ... WRITE,
executing INSERT DELAYED on the other table produces a deadlock.

In 5.1. INSERT DELAYED returns and the values are inserted after the tables have been unlocked.

How to repeat:
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);

connect (con1, localhost, root);
LOCK TABLE t1 WRITE;

connection default;
LOCK TABLE t2 WRITE;
--send INSERT DELAYED INTO t1 VALUES (1)

connection con1;
--sleep 1
INSERT DELAYED INTO t2 VALUES (1);
[8 Jun 2010 14:25] Jon Olav Hauglid
Alternative test case that also give deadlock without LOCK TABLE and with only one INSERT DELAYED:

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = new.a + 1;

connect (con1, localhost, root);
connect (con2, localhost, root);

connection default;
START TRANSACTION;
SELECT * FROM t1;

connection con1;
START TRANSACTION;
INSERT INTO t2 VALUES (1);

connection con2;
--send DROP TRIGGER t1_bi;

connection con1;
--sleep 1
--send UPDATE t1 SET a = 2 WHERE a = 1;

connection default;
--sleep 1
INSERT DELAYED INTO t1 VALUES (1);
[20 Aug 2010 14:55] Bugs System
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/116425

3117 Jon Olav Hauglid	2010-08-20
      Bug #54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
      
      The problem was that deadlocks involving INSERT DELAYED were not detected.
      
      The reason for this is that two threads are involved in INSERT DELAYED:
      the connection thread and the handler thread. The connection thread would
      wait while the handler thread acquired locks and opened the table.
      In essence, this adds an edge to the wait-for-graph between the 
      connection thread and the handler thread that the deadlock detector is
      unaware of. Therefore any deadlocks involving INSERT DELAYED were not 
      detected.
      
      This patch fixes the problem by having the connection thread lock on the
      table before starting the handler thread. This allows the deadlock detector
      to detect any possible deadlocks resulting from trying to lock the table.
      If a lock is successfully acquired, the handler thread is started and
      given a copy of the ticket representing the lock. The connection thread
      then releases any locks it has taken during the process before continuing.
      When the handler thread then tries to lock and open the table, it will
      find that it already has the lock and therefore not acquire any new locks.
      
      Test cases added to delayed.test.
[23 Aug 2010 13:25] Bugs System
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/116503

3117 Jon Olav Hauglid	2010-08-23
      Bug #54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
      
      The problem was that deadlocks involving INSERT DELAYED were not detected.
      
      The reason for this is that two threads are involved in INSERT DELAYED:
      the connection thread and the handler thread. The connection thread would
      wait while the handler thread acquired locks and opened the table.
      In essence, this adds an edge to the wait-for-graph between the 
      connection thread and the handler thread that the deadlock detector is
      unaware of. Therefore any deadlocks involving INSERT DELAYED were not 
      detected.
      
      This patch fixes the problem by having the connection thread lock on the
      table before starting the handler thread. This allows the deadlock detector
      to detect any possible deadlocks resulting from trying to lock the table.
      If a lock is successfully acquired, the handler thread is started and
      given a copy of the ticket representing the lock. The connection thread
      then releases any locks it has taken during the process before continuing.
      When the handler thread then tries to lock and open the table, it will
      find that it already has the lock and therefore not acquire any new locks.
      
      Test cases added to delayed.test.
[23 Aug 2010 14:49] Bugs System
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/116532

3193 Jon Olav Hauglid	2010-08-23
      Bug #54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
      
      The problem was that deadlocks involving INSERT DELAYED were not detected.
      
      The reason for this is that two threads are involved in INSERT DELAYED:
      the connection thread and the handler thread. The connection thread would
      wait while the handler thread acquired locks and opened the table.
      In essence, this adds an edge to the wait-for-graph between the 
      connection thread and the handler thread that the deadlock detector is
      unaware of. Therefore many deadlocks involving INSERT DELAYED were not 
      detected.
      
      This patch fixes the problem by having the connection thread acquire the
      metadata lock the table before starting the handler thread. This allows the
      deadlock detector to detect any possible deadlocks resulting from trying to
      acquire a metadata lock the table. If a metadata lock is successfully acquired,
      the handler thread is started and given a copy of the ticket representing the
      metadata lock. When the handler thread then tries to lock and open the table,
      it will find that it already has the metadata lock and therefore not acquire
      any new metadata locks.
      
      Test cases added to delayed.test.
[23 Aug 2010 15:45] Bugs System
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/116535

3193 Jon Olav Hauglid	2010-08-23
      Bug #54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
      
      The problem was that deadlocks involving INSERT DELAYED were not detected.
      
      The reason for this is that two threads are involved in INSERT DELAYED:
      the connection thread and the handler thread. The connection thread would
      wait while the handler thread acquired locks and opened the table.
      In essence, this adds an edge to the wait-for-graph between the 
      connection thread and the handler thread that the deadlock detector is
      unaware of. Therefore many deadlocks involving INSERT DELAYED were not 
      detected.
      
      This patch fixes the problem by having the connection thread acquire the
      metadata lock the table before starting the handler thread. This allows the
      deadlock detector to detect any possible deadlocks resulting from trying to
      acquire a metadata lock the table. If a metadata lock is successfully acquired,
      the handler thread is started and given a copy of the ticket representing the
      metadata lock. When the handler thread then tries to lock and open the table,
      it will find that it already has the metadata lock and therefore not acquire
      any new metadata locks.
      
      Test cases added to delayed.test.
[23 Aug 2010 15:48] Jon Olav Hauglid
Pushed to mysql-5.5-bugfixing (5.5.6-m3).
[24 Aug 2010 11:46] Bugs System
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/116616

3119 Jon Olav Hauglid	2010-08-24
      Follow-up to Bug #54332 Deadlock with two connections doing
                   LOCK TABLE+INSERT DELAYED
      
      The problem was that the server could crash if the insert delayed
      handler thread was killed due to a conflicting shared metadata
      lock. This could happen because the metadata lock ticket was
      added to the handler thread before it was properly initialized.
      
      This patch moves the cloning of the acquired metadata lock ticket
      until after the handler thread has been properly initialized.
[24 Aug 2010 12:51] Bugs System
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/116631

3197 Jon Olav Hauglid	2010-08-24
      Follow-up to Bug #54332 Deadlock with two connections doing
                   LOCK TABLE+INSERT DELAYED
      
      The problem was that the server could crash if the insert delayed
      handler thread was killed due to a conflicting shared metadata
      lock. This could happen because the metadata lock ticket was
      added to the handler thread before it was properly initialized.
      
      This patch moves the cloning of the acquired metadata lock ticket
      until after the handler thread has been properly initialized.
[24 Aug 2010 14:02] Bugs System
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/116650

3197 Jon Olav Hauglid	2010-08-24
      Follow-up to Bug #54332 Deadlock with two connections doing
                   LOCK TABLE+INSERT DELAYED
      
      The problem was that the server could crash if the insert delayed
      handler thread was killed due to a conflicting shared metadata
      lock. This could happen because the metadata lock ticket was
      added to the handler thread before it was properly initialized.
      
      This patch moves the cloning of the acquired metadata lock ticket
      until after the handler thread has been properly initialized.
[24 Aug 2010 14:32] Jon Olav Hauglid
Follow-up pushed to mysql-5.5-bugfixing (5.5.6-m3) and merged to mysql-trunk-bugfixing and mysql-next-mr-bugfixing.
[24 Aug 2010 15:58] Bugs System
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/116678

3120 Dmitry Lenev	2010-08-24
      Fixed --ps-protocol failures of test for bug#54332 "Deadlock
      with two connections doing LOCK TABLE+INSERT DELAYED".
      
      Disabled --ps-protocol for this part of the test as INSERT
      DELAYED simply doesn't work with it under LOCK TABLES.
[24 Aug 2010 16:20] Bugs System
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/116684

3198 Dmitry Lenev	2010-08-24
      Fixed --ps-protocol failures of test for bug#54332 "Deadlock
      with two connections doing LOCK TABLE+INSERT DELAYED".
      
      Disabled --ps-protocol for this part of the test as INSERT
      DELAYED simply doesn't work with it under LOCK TABLES.
[25 Aug 2010 6:37] Bugs System
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/116703

3120 Dmitry Lenev	2010-08-25 [merge]
      Merged follow-up for bug#54332 "Deadlock with two connections
      doing LOCK TABLE+INSERT DELAYED" from mysql-5.5-bugfixing
      into mysql-5.5-runtime.
[25 Aug 2010 9:22] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)
[30 Aug 2010 8:30] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[22 Sep 2010 20:31] Paul DuBois
Noted in 5.5.6, 5.6.1 changelogs.

Deadlocks involving INSERT DELAYED statements were not detected. The
server could crash if the delayed handler thread was killed due to a
conflicting shared metadata lock.
[9 Nov 2010 19:44] Bugs System
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)
[13 Nov 2010 16:11] Bugs System
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)
[13 Nov 2010 16:32] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (pib:21)