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: | |
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
[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)