Bug #48724 Deadlock between INSERT DELAYED and FLUSH TABLES
Submitted: 12 Nov 2009 12:33 Modified: 7 Mar 2010 1:35
Reporter: Jon Olav Hauglid Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:6.0.14-bzr, 6.0-codebase-bugfixing OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Tags: regression

[12 Nov 2009 12:33] Jon Olav Hauglid
Description:
Concurrent execution of INSERT DELAYED and FLUSH TABLES can lead to deadlock.

INSERT DELAYED:

#0  __lll_lock_wait () at ../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:136
#1  0x00007f3b3cc9ab30 in _L_cond_lock_1191 () from /lib/libpthread.so.0
#2  0x00007f3b3cc9aa7b in __pthread_mutex_cond_lock (mutex=0x7f3b34007690)
    at ../nptl/pthread_mutex_lock.c:101
#3  0x00007f3b3cc95644 in pthread_cond_wait@@GLIBC_2.3.2 ()
    at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_wait.S:333
#4  0x0000000000ba0e29 in safe_cond_wait (cond=0x7f3b34007738, mp=0x7f3b34007668, 
    file=0xdace6a "sql_insert.cc", line=2094) at thr_mutex.c:423
#5  0x00000000007c733f in Delayed_insert::get_local_table (this=0x7f3b34004780, 
    client_thd=0x1906360) at sql_insert.cc:2094
#6  0x00000000007c716e in delayed_get_table (thd=0x1906360, table_list=0x7f3b34035a78)
    at sql_insert.cc:2043
#7  0x00000000007c36df in open_and_lock_for_insert_delayed (thd=0x1906360, 
    table_list=0x7f3b34035a78) at sql_insert.cc:523
#8  0x00000000007c3a76 in mysql_insert (thd=0x1906360, table_list=0x7f3b34035a78, 
    fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, 
    ignore=false) at sql_insert.cc:630
#9  0x00000000007137c7 in mysql_execute_command (thd=0x1906360) at sql_parse.cc:3267
#10 0x000000000071b742 in mysql_parse (thd=0x1906360, 
    inBuf=0x7f3b34036418 "INSERT DELAYED INTO t1 VALUES (1)", length=33, 
    found_semicolon=0x7f3b3d1688c8) at sql_parse.cc:5979

FLUSH TABLES:

#0  pthread_cond_wait@@GLIBC_2.3.2 ()
    at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_wait.S:261
#1  0x0000000000ba0e29 in safe_cond_wait (cond=0x12d6500, mp=0x12d5820, 
    file=0xda4482 "sql_base.cc", line=1062) at thr_mutex.c:423
#2  0x00000000007656d9 in close_cached_tables (thd=0x1971020, tables=0x0, have_lock=false, 
    wait_for_refresh=true) at sql_base.cc:1062
#3  0x000000000071d9ab in reload_acl_and_cache (thd=0x1971020, options=4, tables=0x0, 
    write_to_binlog=0x7f3b3d126370) at sql_parse.cc:6966
#4  0x000000000071641f in mysql_execute_command (thd=0x1971020) at sql_parse.cc:4084
#5  0x000000000071b742 in mysql_parse (thd=0x1971020, inBuf=0x1a88f98 "FLUSH TABLES", 
    length=12, found_semicolon=0x7f3b3d1278c8) at sql_parse.cc:5979

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

let $try = 1000;

connect (con2, localhost, root);

while ($try)
{
        connection default;
        --send INSERT DELAYED INTO t1 VALUES (1)
        connection con2;
        --send FLUSH TABLES
        connection default;
        --reap
        connection con2;
        --reap
        dec $try;
}

DROP TABLE t1;
[12 Nov 2009 12:39] Jon Olav Hauglid
Also see bug#48725
[12 Nov 2009 13:33] Valeriy Kravchuk
With recent mysql-6.0-codebase debug binaries I've got assertion failure on Mac OS X:

Version: '6.0.14-alpha-debug-log'  socket: '/Users/openxs/dbs/6.0-codebase/mysql-test/var/tmp/master.sock'  port: 9306  Source distribution
Assertion failed: (! thd->is_error()), function delayed_get_table, file sql_insert.cc, line 2047.
...

So, we definitely have a problem here.
[12 Nov 2009 13:39] Valeriy Kravchuk
Problem is NOT repeatable with recent 5.1.42 from bzr, so this is a regression bug.
[12 Nov 2009 14:08] Jon Olav Hauglid
I reported the assertion as bug#48725.
This deadlock happens less frequently.
[20 Nov 2009 10:31] 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/91051

3714 Jon Olav Hauglid	2009-11-20
      Bug #48724 Deadlock between INSERT DELAYED and FLUSH TABLES
      
      The problem was that if the handler thread was killed at the wrong moment
      (using e.g. FLUSH TABLES), this would not be properly noticed by the
      INSERT DELAYED connection thread. It would be stuck waiting for the handler
      thread to lock its table, while the handler thread would be looping, trying
      to get the connection thread to notice the error.
      
      The root of the problem was that insert delayed had an extra variable "dead"
      used to indicate if the handler thread had been killed. This in addition to
      the usual "thd->killed". Most places both were set, but some only set
      "thd->killed". And Delayed_insert::get_local_table() only checked "dead"
      while waiting for the table to be locked.
      
      This patch removes the "dead" variable and replaces its usage with
      "thd->killed", thereby resolving the issue.
[17 Dec 2009 12:43] 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/94732

3041 Jon Olav Hauglid	2009-12-17
      Bug #48724 Deadlock between INSERT DELAYED and FLUSH TABLES
      
      If the handler (or delayed insert) thread failed to lock a table due
      to being killed, the "dead" flag was used to notify the connection thread
      of this failure. However, with the changes introduced by Bug#45949, 
      the handler thread will no longer try to lock the table if it was killed.
      This meant that the "dead" flag would not be set, and the connection
      thread would not notice that the handler thread had failed.
      
      This could happen with concurrent INSERT DELAYED and FLUSH TABLES.
      FLUSH TABLES would kill any active INSERT DELAYED that had opened any
      table(s) to be flushed. This could cause the INSERT DELAYED connection
      thread to be stuck waiting for the handler thread to lock its table,
      while the handler thread would be looping, trying to get the connection
      thread to notice the error.
      
      The root of the problem was that the handler thread had both the "dead"
      flag and "thd->killed" to indicate that it had been killed. Most places
      both were set, but some only set "thd->killed". And 
      Delayed_insert::get_local_table() only checked "dead" while waiting for
      the table to be locked.
      
      This patch removes the "dead" variable and replaces its usage with
      "thd->killed", thereby resolving the issue.
[17 Dec 2009 14:39] Jon Olav Hauglid
Pushed to mysql-next-4284 and merged to mysql-6.0-codebase-4284.
[16 Feb 2010 16:45] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:jon.hauglid@sun.com-20091217143610-t3t1lz1jcda1pney) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:55] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20091217124307-glwitkbztcmh8ajr) (pib:16)
[25 Feb 2010 21:02] Paul DuBois
Noted in 6.0.14 changelog.

Concurrent execution of INSERT DELAYED and FLUSH TABLES could lead to
deadlock. 

Setting report to Need Merge pending push of Celosia to release tree.
[6 Mar 2010 11:08] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 1:35] Paul DuBois
Noted in 5.5.3 changelog.
[13 Apr 2010 4:55] Paul DuBois
Correction: Not in any released version. No changelog entry needed.