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:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1,6.0.14 OS:Any
Assigned to: Jon Olav Hauglid
Triage: Triaged: D3 (Medium)

[28 Sep 2009 16:12] Matthias Leich
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
[28 Sep 2009 16:55] Valerii 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)