| Bug #47682 | strange behaviour of INSERT DELAYED | ||
|---|---|---|---|
| Submitted: | 28 Sep 2009 16:12 | Modified: | 12 Mar 2010 17:19 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.1,6.0.14 | OS: | Any |
| Assigned to: | Jon Olav Hauglid | CPU Architecture: | Any |
[28 Sep 2009 16:55]
Valeriy Kravchuk
Thank you for the problem report. Verified just as described also with recent 5.1.40 from mysql-5.1 bzr tree on Mac OS X.
[6 Nov 2009 14:47]
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/89607 3198 Jon Olav Hauglid 2009-11-06 Bug #47682 strange behaviour of INSERT DELAYED The problem was a "self-deadlock" if the connection issuing INSERT DELAYED had both the global read lock (FLUSH TABLES WITH READ LOCK) and LOCK TABLES mode active. The table being inserted into had to be different from the table(s) locked by LOCK TABLES. For INSERT DELAYED, the connection thread waits until the handler thread has opened and locked its table before returning. But since the global read lock was active, the handler thread would be unable to lock and would wait for the global read lock to go away. So the handler thread would be waiting for the connection thread to release the global read lock while the connection thread was waiting for the handler thread to lock the table. This gave a "self-deadlock" (same connection, different threads). The deadlock would only happen if we also had LOCK TABLES mode since the INSERT otherwise will try to get protection against global read lock before starting the handler thread. It will then notice that the global read lock is owned by the same connection and report ER_CANT_UPDATE_WITH_READLOCK. This patch removes the deadlock by reporting ER_CANT_UPDATE_WITH_READLOCK also if we are inside LOCK TABLES mode. Test case added to delayed.test.
[18 Nov 2009 9: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/90801 3203 Jon Olav Hauglid 2009-11-18 Bug #47682 strange behaviour of INSERT DELAYED The problem was a "self-deadlock" if the connection issuing INSERT DELAYED had both the global read lock (FLUSH TABLES WITH READ LOCK) and LOCK TABLES mode active. The table being inserted into had to be different from the table(s) locked by LOCK TABLES. For INSERT DELAYED, the connection thread waits until the handler thread has opened and locked its table before returning. But since the global read lock was active, the handler thread would be unable to lock and would wait for the global read lock to go away. So the handler thread would be waiting for the connection thread to release the global read lock while the connection thread was waiting for the handler thread to lock the table. This gave a "self-deadlock" (same connection, different threads). The deadlock would only happen if we also had LOCK TABLES mode since the INSERT otherwise will try to get protection against global read lock before starting the handler thread. It will then notice that the global read lock is owned by the same connection and report ER_CANT_UPDATE_WITH_READLOCK. This patch removes the deadlock by reporting ER_CANT_UPDATE_WITH_READLOCK also if we are inside LOCK TABLES mode. Test case added to delayed.test.
[18 Nov 2009 9:30]
Jon Olav Hauglid
Pushed to mysql-5.1-bugteam (5.1.41) and merged to mysql-pe (6.0.14-alpha)
[18 Nov 2009 12:50]
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/90840 3206 Jon Olav Hauglid 2009-11-18 Postfix for Bug #47682 strange behaviour of INSERT DELAYED Fixed a problem with the test case when executed with ps-protocol. There the conflicing lock would be noticed during prepare, not during execution of the insert - leading to a different (but equally appropriate) error message.
[2 Dec 2009 8:04]
Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[16 Dec 2009 1:57]
Paul DuBois
Noted in 5.1.42 changelog. If a session held a global read lock acquired with FLUSH TABLES WITH READ LOCK, a table for one table acquired with LOCK TABLES, and issued an INSERT DELAYED statement for another table, deadlock could occur. Setting report to NDI pending push to 5.5.x+.
[16 Dec 2009 8:35]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:42]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:49]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 1:44]
Paul DuBois
Noted in 5.5.1, 6.0.14 changelogs.
[18 Dec 2009 3:11]
Paul DuBois
Corrected changelog entry:
If a session held a global read lock acquired with FLUSH TABLES WITH
READ LOCK, a lock for one table acquired with LOCK TABLES, and issued
an INSERT DELAYED statement for another table, deadlock could occur.
("a table for one table" -> "a lock for one table")
[12 Mar 2010 14:06]
Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:22]
Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:36]
Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
Description: My script: ---------- --disable_abort_on_error CREATE TABLE t1 (f1 integer); INSERT IGNORE INTO t1 VALUES (1); CREATE TABLE t2 AS SELECT 1 AS f1; FLUSH TABLES WITH READ LOCK; LOCK TABLES t2 AS a READ; send INSERT DELAYED INTO t1 VALUES (2); connect (con0,localhost,root,,test); connection con0; sleep 1; SHOW PROCESSLIST; connection default; reap; The result: ----------- CREATE TABLE t1 (f1 integer); INSERT IGNORE INTO t1 VALUES (1); CREATE TABLE t2 AS SELECT 1 AS f1; FLUSH TABLES WITH READ LOCK; LOCK TABLES t2 AS a READ; INSERT DELAYED INTO t1 VALUES (2); connect(...); SHOW PROCESSLIST; Id User Host db Command Time State Info 1 root localhost test Query 1 waiting for handler open INSERT DELAYED INTO t1 VALUES (2) 2 root localhost test Query 0 NULL SHOW PROCESSLIST 3 DELAYED localhost test Delayed insert 1 Waiting for release of readlock !! endless waiting for the "reap" Observations + What is strange: ------------------------------- 1. (only) FLUSH TABLES WITH READ LOCK removed --> no waiting for the reap, no error message or warning 2. (only) LOCK TABLES t2 AS a READ removed --> no waiting for the reap, ERROR HY000: Can't execute the query because you have a conflicting read lock 3. (only) DELAYED removed --> no waiting for the reap, ERROR HY000: Table 't1' was not locked with LOCK TABLES The manual says in http://dev.mysql.com/doc/refman/5.4/en/insert-delayed.html ... When a client uses INSERT DELAYED, it gets an okay from the server at once ... Because the INSERT DELAYED statement returns immediately, ... Therefore I have the impression that any "waiting" should never happen. Either the statement - does not fit to the current situation and gets immediate an error message or - gets immediate return code zero and the delayed execution of the INSERT is with whatever outcome (success, failure because of constraint violation ...) I am unsure if the current behaviour could be called a bug but it looks like as if it violates the intention associated with "DELAYED". My environment: --------------- - mysql-5.1-bugteam 2009-09-16 - mysql-6.0-codebase-bugfixing 2009-09-28 - ./BUILD/compile-pentium64-debug-max - Linux OpenSuSE 11.0 (64 Bit) - Intel Core2Duo How to repeat: See above