Bug #23312 server hangs 'closing tables' with insert delayed,flush tables,alter table
Submitted: 15 Oct 2006 22:59 Modified: 16 Jan 2007 7:27
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:5.1.12, 5.0.26,5.0.27-debug, 4.1.21,4.0.27 OS:Linux (suse 9.3 x86/Win2000)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: insert delayed closing tables opening tables hang alter table

[15 Oct 2006 22:59] Shane Bester 
Description:
Sometimes most the queries hang in 'closing tables' state when many threads are running 'insert delayed into ..', 'flush tables', and 'alter table ...' statements.

Here is a snippet of the processlist during such a hangup.

+------+--------------------------+-----------
| Time | State                    | Info      
+------+--------------------------+-----------
|    0 | NULL                     | show full 
|  597 | waiting for delay_list   | insert del
|  597 | waiting for delay_list   | insert del
|  597 | waiting for delay_list   | insert del
|  597 | waiting for delay_list   | insert del
|  597 | NULL                     | flush tabl
|  597 | setup                    | alter tabl
|  597 | Opening tables           | select `id
|  598 | closing tables           | insert del
|  597 | waiting for delay_list   | insert del
|  597 | waiting for delay_list   | insert del
|  597 | closing tables           | select `id
|  597 | waiting for delay_list   | insert del
|  597 | waiting for handler lock | insert del
|  597 | waiting for delay_list   | insert del

Complete processlist is attached as a file, to preserve formatting.

How to repeat:
Just compile the attached testcase.c and let it run.  It runs at 25 threads for 60 minutes, in which case a hangup should happen.  Monitor it in the processlist.
I noticed many 'server shutdown in progress' errors - just ignore those for the purpose of this bug.

Suggested fix:
.
[15 Oct 2006 23:03] MySQL Verification Team 
testcase to recreate hang

Attachment: testcase.c (text/x-csrc), 5.73 KiB.
[15 Oct 2006 23:12] MySQL Verification Team 
show full processlist during a hangup

Attachment: processlist.txt (plain/text, text), 11.11 KiB.
[15 Oct 2006 23:42] MySQL Verification Team 
happens on windows too, so it cannot be nptl related

Attachment: stacktraces_windows.txt (plain/text, text), 15.10 KiB.
[15 Oct 2006 23:48] MySQL Verification Team 
On Windows, I received many of these errors in the logs:

061016  1:29:52 [ERROR] Delayed insert thread couldn't get requested lock for table bug_2
061016  1:29:52 [ERROR] Delayed insert thread couldn't get requested lock for table bug_2
061016  1:29:53 [ERROR] Delayed insert thread couldn't get requested lock for table bug
061016  1:29:53 [ERROR] Delayed insert thread couldn't get requested lock for table bug_2

But not on Linux.

When reading sql_insert.cc, searching for ER_DELAYED_CANT_CHANGE_LOCK , I see this should never happen anyway ...
[16 Oct 2006 0:23] MySQL Verification Team 
affects 4.0.27 too

Attachment: processlist_4.0.27.txt (plain/text, text), 6.26 KiB.
[16 Oct 2006 8:02] MySQL Verification Team 
stack traces 5.0.26-windows with more debug information

Attachment: 5.0.26_vs.net_threads.txt (plain/text, text), 59.60 KiB.
[16 Oct 2006 8:04] MySQL Verification Team 
only way to stop server once in hung state is kill -9
[18 Oct 2006 16:34] MySQL Verification Team 
Sergey, if you have difficulty reproducing a hang in under 15 minutes, then try increasing or decreasing the NUMTHREADS #define by 10 or so.  It really depends how fast your server is.
[27 Oct 2006 11:40] 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/14474

ChangeSet@1.2534, 2006-10-27 16:42:18+05:00, svoj@mysql.com +1 -0
  BUG#23312 - server hangs 'closing tables' with insert delayed,flush
              tables,alter table
  Fixed a deadlock when delayed insert + flush tables + alter table running
  concurrently.
[1 Nov 2006 20:03] 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/14708

ChangeSet@1.2534, 2006-11-02 00:05:02+04:00, svoj@mysql.com +1 -0
  BUG#23312 - server hangs 'closing tables' with insert delayed,flush
              tables,alter table
  Deadlock could happen if there are delayed insert + flush tables + alter table
  running concurrently.
  
  This is fixed by removing a redundant mutex lock when killing a delayed thread.
[27 Dec 2006 0:12] Antony Curtis 
Pushed to 5.1.15-beta repository
[27 Dec 2006 2:25] Antony Curtis 
Pushed to 5.0.34 repository
[27 Dec 2006 10:00] Antony Curtis 
Pushed to 4.1.23 repository
[16 Jan 2007 7:27] Jon Stephens 
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.1.23, 5.0.34, and 5.1.15 changelogs.