Bug #38804 Query deadlock causes all tables to be inaccessible.
Submitted: 14 Aug 2008 16:27 Modified: 17 Oct 2008 18:16
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:5.1-bzr OS:Any
Assigned to: Mattias Jonsson
Triage: Triaged: D1 (Critical)

[14 Aug 2008 16:27] Philip Stoev
Description:
If there is a deadlock between two transactions that requires a timeout to resolve, then all tables on the server are not accessible for the duration of the deadlock. Threads which have nothing to do with the deadlock are hung like this:

#1  0x00583829 in __lll_lock_wait () from /lib/libpthread.so.0
#2  0x0057edb5 in _L_lock_758 () from /lib/libpthread.so.0
#3  0x0057ecce in pthread_mutex_lock () from /lib/libpthread.so.0
#4  0x08589e61 in safe_mutex_lock (mp=0xa380804, try_lock=0 '\0', file=0x86ab82d "sql_base.cc", line=396) at thr_mutex.c:149
#5  0x082a5ba8 in get_table_share (thd=0xa37c9d8, table_list=0xb2b4a58c, key=0xb2b4a76e "test", key_length=48, db_flags=8192, error=0xb2b4a8f8)
    at sql_base.cc:396
#6  0x083a6377 in fill_schema_table_from_frm (thd=0xa37c9d8, table=0xb28085c0, schema_table=0x8743938, db_name=0xb2b4aba4, table_name=0xa356738,
    schema_table_idx=SCH_COLUMNS) at sql_show.cc:3028
#7  0x083b62bc in get_all_tables (thd=0xa37c9d8, tables=0xa354db8, cond=0xa3550b8) at sql_show.cc:3257
#8  0x083a32eb in get_schema_tables_result (join=0xb280a8c0, executed_place=PROCESSED_BY_JOIN_EXEC) at sql_show.cc:5911
#9  0x082e00ec in JOIN::exec (this=0xb280a8c0) at sql_select.cc:1712
#10 0x082dccc5 in mysql_select (thd=0xa37c9d8, rref_pointer_array=0xa37de5c, tables=0xa354db8, wild_num=0, fields=@0xa37ddf8, conds=0xa3550b8, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2686208513, result=0xa355f70, unit=0xa37daec, select_lex=0xa37dd64)
    at sql_select.cc:2360
#11 0x082e202c in handle_select (thd=0xa37c9d8, lex=0xa37da90, result=0xa355f70, setup_tables_done_option=0) at sql_select.cc:269
#12 0x0825997f in execute_sqlcom_select (thd=0xa37c9d8, all_tables=0xa354db8) at sql_parse.cc:4765
#13 0x0825b655 in mysql_execute_command (thd=0xa37c9d8) at sql_parse.cc:2073
#14 0x08264375 in mysql_parse (thd=0xa37c9d8,
    inBuf=0xa354bc8 "SELECT DISTINCT COLUMN_NAME\n\t\tFROM INFORMATION_SCHEMA.COLUMNS\n\t\tWHERE TABLE_SCHEMA = DATABASE()", length=95,
    found_semicolon=0xb2b4c25c) at sql_parse.cc:5656
#15 0x08264f70 in dispatch_command (command=COM_QUERY, thd=0xa37c9d8,
    packet=0xa35ec31 "\n\t\tSELECT DISTINCT COLUMN_NAME\n\t\tFROM INFORMATION_SCHEMA.COLUMNS\n\t\tWHERE TABLE_SCHEMA = DATABASE()\n\t", packet_length=100)
    at sql_parse.cc:1137
#16 0x0826612e in do_command (thd=0xa37c9d8) at sql_parse.cc:794
#17 0x082535ae in handle_one_connection (arg=0xa37c9d8) at sql_connect.cc:1115
#18 0x0057d32f in start_thread () from /lib/libpthread.so.0
#19 0x0049a27e in clone () from /lib/libc.so.6

or

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00583829 in __lll_lock_wait () from /lib/libpthread.so.0
#2  0x0057ed75 in _L_lock_87 () from /lib/libpthread.so.0
#3  0x0057e7fa in pthread_mutex_lock () from /lib/libpthread.so.0
#4  0x08589e61 in safe_mutex_lock (mp=0x884ae40, try_lock=0 '\0',
    file=0x86ab82d "sql_base.cc", line=2702) at thr_mutex.c:149
#5  0x082a8787 in open_table (thd=0xa39f530, table_list=0xa43e760,
    mem_root=0xb2960ddc, refresh=0xb2960e1f, flags=0) at sql_base.cc:2702
#6  0x082a9cc3 in open_tables (thd=0xa39f530, start=0xb2960e94,
    counter=0xb2960e80, flags=0) at sql_base.cc:4515
#7  0x082aa4b4 in open_and_lock_tables_derived (thd=0xa39f530,
    tables=0xa43e760, derived=true) at sql_base.cc:4915
#8  0x08266e2b in open_and_lock_tables (thd=0xa39f530, tables=0xa43e760)
    at mysql_priv.h:1531
#9  0x08259702 in execute_sqlcom_select (thd=0xa39f530, all_tables=0xa43e760)
    at sql_parse.cc:4730
#10 0x0825b655 in mysql_execute_command (thd=0xa39f530) at sql_parse.cc:2073
#11 0x08264375 in mysql_parse (thd=0xa39f530,
    inBuf=0xa43e638 "select * from a", length=15, found_semicolon=0xb296225c)
    at sql_parse.cc:5656
#12 0x08264f70 in dispatch_command (command=COM_QUERY, thd=0xa39f530,
    packet=0xa4375f1 "select * from a", packet_length=15) at sql_parse.cc:1137
#13 0x0826612e in do_command (thd=0xa39f530) at sql_parse.cc:794
#14 0x082535ae in handle_one_connection (arg=0xa39f530) at sql_connect.cc:1115
#15 0x0057d32f in start_thread () from /lib/libpthread.so.0
#16 0x0049a27e in clone () from /lib/libc.so.6

How to repeat:
1. Trigger a deadlock that is resolved only via timeout, and not with a deadlock detector. This would require using both a transactional storage engine (can cause deadlocks) and partitioning (causes deadlocks that would otherwise be resolved with a detector to be resolved via timeout). Such an example would be the test case from bug #38797. Use --innodb-lock-wait-timeout=500 for comfort.

2. Using SHOW PROCESSLIST observe that threads executing, for example, an I_S SELECT query, hang with status "checking permissions".

3. Using a mysql client, observe that queries like "SELECT * FROM foo" hang in open_table().
[15 Aug 2008 7:43] Sveta Smirnova
Thank you for the report.

Verified as described.
[28 Aug 2008 16:01] Mattias Jonsson
Is this repeatable in 6.0? I think that the auto_increment patch that fixed bug#33479 might solve this bug too (since it does no longer use the table_share->mutex). (that patch has only been included in 6.0, not in 5.1)
[29 Aug 2008 9:47] Kristofer Pettersson
Bug has its origin from:
http://bugs.mysql.com/bug.php?id=27405
[1 Sep 2008 14:15] 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/53007

2728 Kristofer Pettersson	2008-09-01
      Bug#38804 Query deadlock causes all tables to be inaccessible.
      
      Backport 6.0-> 5.1 of b ug 33479: auto_increment failures in partitioning
      
      Several problems with auto_increment in partitioning
      (with MyISAM, InnoDB and Falcon. Locking issues, not handling
      multi-row INSERTs properly etc.)
        
      This is a full patch which I have updated according to sergs and
      guilhems comments.
      Changed the auto_increment handling for partitioning:
      Added a ha_data variable in table_share for storage engine specific data
      such as auto_increment value handling in partitioning, also see WL 4305
      and using the ha_data->mutex to lock around read + update.
      
      The idea is this:
      Store the table's reserved auto_increment value in
      the TABLE_SHARE and use a mutex to, lock it for reading and updating it
      and unlocking it, in one block. Only accessing all partitions
      when it is not initialized.
      Also allow reservations of ranges, and if no one has done a reservation
      afterwards, lower the reservation to what was actually used after
      the statement is done (via release_auto_increment from WL 3146).
      The lock is kept from the first reservation if it is statement based
      replication and a multi-row INSERT statement where the number of
      candidate rows to insert is not known in advance (like INSERT SELECT,
      LOAD DATA, unlike INSERT VALUES (row1), (row2),,(rowN)).
      
      This should also lead to better concurrancy (no need to have a mutex
      protection around write_row in all cases)
      and work with any local storage engine.
[2 Sep 2008 13:29] 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/53061

2732 Mattias Jonsson	2008-09-02
      Bug#38804: Query deadlock causes all tables to be inaccessible.
      
      Problem was a mutex added in bug n 27405 for solving a problem
      with auto_increment in partitioned innodb tables.
      (in ha_partition::write_row over partitions file->ha_write_row)
      
      Solution is to use the patch for bug#33479, which refines the
      usage of mutexes for auto_increment.
      
      Backport of bug-33479 from 6.0:
      
      Bug-33479: auto_increment failures in partitioning
      
      Several problems with auto_increment in partitioning
      (with MyISAM, InnoDB. Locking issues, not handling
      multi-row INSERTs properly etc.)
      
      Changed the auto_increment handling for partitioning:
      Added a ha_data variable in table_share for storage engine specific data
      such as auto_increment value handling in partitioning, also see WL 4305
      and using the ha_data->mutex to lock around read + update.
      
      The idea is this:
      Store the table's reserved auto_increment value in
      the TABLE_SHARE and use a mutex to, lock it for reading and updating it
      and unlocking it, in one block. Only accessing all partitions
      when it is not initialized.
      Also allow reservations of ranges, and if no one has done a reservation
      afterwards, lower the reservation to what was actually used after
      the statement is done (via release_auto_increment from WL 3146).
      The lock is kept from the first reservation if it is statement based
      replication and a multi-row INSERT statement where the number of
      candidate rows to insert is not known in advance (like INSERT SELECT,
      LOAD DATA, unlike INSERT VALUES (row1), (row2),,(rowN)).
      
      This should also lead to better concurrancy (no need to have a mutex
      protection around write_row in all cases)
      and work with any local storage engine.
[3 Sep 2008 8:22] Kristofer Pettersson
Dead lock visualized

Attachment: bug38804.swf (application/x-shockwave-flash, text), 64.78 KiB.

[3 Sep 2008 21:06] 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/53206

2733 Mattias Jonsson	2008-09-03
      Bug#38804: Query deadlock causes all tables to be inaccessible.
      Bug-33479: auto_increment failures in partitioning
      
      fix for update of auto_increment column
      
      (Same bug exists in non partitioned InnoDB, bug-39207)
[8 Sep 2008 13:30] 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/53529

2732 Mattias Jonsson	2008-09-08
      Bug#38804: Query deadlock causes all tables to be inaccessible.
      
      Problem was a mutex added in bug n 27405 for solving a problem
      with auto_increment in partitioned innodb tables.
      (in ha_partition::write_row over partitions file->ha_write_row)
      
      Solution is to use the patch for bug#33479, which refines the
      usage of mutexes for auto_increment.
      
      Backport of bug-33479 from 6.0:
      
      Bug-33479: auto_increment failures in partitioning
      
      Several problems with auto_increment in partitioning
      (with MyISAM, InnoDB. Locking issues, not handling
      multi-row INSERTs properly etc.)
      
      Changed the auto_increment handling for partitioning:
      Added a ha_data variable in table_share for storage engine specific data
      such as auto_increment value handling in partitioning, also see WL 4305
      and using the ha_data->mutex to lock around read + update.
      
      The idea is this:
      Store the table's reserved auto_increment value in
      the TABLE_SHARE and use a mutex to, lock it for reading and updating it
      and unlocking it, in one block. Only accessing all partitions
      when it is not initialized.
      Also allow reservations of ranges, and if no one has done a reservation
      afterwards, lower the reservation to what was actually used after
      the statement is done (via release_auto_increment from WL 3146).
      The lock is kept from the first reservation if it is statement based
      replication and a multi-row INSERT statement where the number of
      candidate rows to insert is not known in advance (like INSERT SELECT,
      LOAD DATA, unlike INSERT VALUES (row1), (row2),,(rowN)).
      
      This should also lead to better concurrancy (no need to have a mutex
      protection around write_row in all cases)
      and work with any local storage engine.
[29 Sep 2008 9:06] Mattias Jonsson
Estimated push to 5.1.29 is 2008-10-03.
[1 Oct 2008 10:41] 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/54916

2694 Mattias Jonsson	2008-10-01
      pre push fix for bug#38804
[1 Oct 2008 12: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/54920

2844 Mattias Jonsson	2008-10-01 [merge]
      merge (basically a null merge, since it contains a backport of bug#33479 as a fix for Bug#38804)
[1 Oct 2008 19:28] Mattias Jonsson
pushed into mysql-6.0-5.1.29-rc and mysql-5.1-5.1.29-rc
[3 Oct 2008 9: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/55199

2686 Mattias Jonsson	2008-10-03
      post push fix for bug#38804 (back port of bug#33479)
      Removes the regression bug#38751.
[3 Oct 2008 12:35] 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/55246

2841 Mattias Jonsson	2008-10-03 [merge]
      merge and update of post push fix for bug#38804
      There had also been a small miss in the previous
      merge between the 5.1.29-rc and -bugteam trees
[7 Oct 2008 19:21] Paul Dubois
Noted in 5.1.29 changelog.

For deadlock between two transactions that required a timeout to
resolve, all server tables became inaccessible for the duration of
the deadlock.  

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[9 Oct 2008 17:51] Bugs System
Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:kgeorge@mysql.com-20081007082452-gk4l86zq8k53wwyo) (pib:4)
[9 Oct 2008 18:39] Paul Dubois
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:46] Bugs System
Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081008085925-xh27vrkzh2gx1k3u) (version source revid:kgeorge@mysql.com-20081008092313-4wzrak2duhsz7ylm) (pib:5)
[17 Oct 2008 18:16] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:01] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:20] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:44] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)