Bug #25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
Submitted: 26 Jan 2007 8:27 Modified: 6 Mar 2010 19:56
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.36-bk, 5.1.15-bk OS:Linux (Linux Suse 10.1)
Assigned to: Davi Arnaut CPU Architecture:Any

[26 Jan 2007 8:27] Dmitry Lenev
Description:
In connections that has some tables locked with LOCK TABLES MySQL carelessly allows to issue DROP TABLE for any table. And this can easily lead to deadlocks.
See "How to repeat" section for more info.

How to repeat:
# This small script for 'mysqltest' shows the problem
connect (addconroot, localhost, root,,);
connection default;
create table t1 (i int);
create table t2 (i int);
lock tables t1 read;
connection addconroot;
lock tables t2 read;
--send drop table t1;
connection default;
# This will hang !
drop table t2;
connection addconroot;
--reap

Suggested fix:
Probably it should be disallowed to DROP TABLE under LOCK TABLES, unless the table being dropped was locked for WRITE (aka exclusively) by this LOCK TABLES
statement.
[26 Jan 2007 9:32] Hartmut Holzgraefe
verified, will upload a complete test case (the current one is missing DROP TABLE IF EXISTS ...)
[26 Jan 2007 9:33] Hartmut Holzgraefe
mysqltest test case

Attachment: bug25858.tgz (application/x-gtar, text), 635 bytes.

[28 Aug 2007 10:47] Konstantin Osipov
Davi, please contact me before starting work on this bug.
[6 Sep 2007 1:37] 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/33773

ChangeSet@1.2605, 2007-09-05 22:37:26-03:00, davi@moksha.local +19 -0
  Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
  
  When a client (connection) holds a lock on a table and attempts to
  drop (obtain a exclusive lock) on a second table that is already held
  by a second client and the second client then attempts to drop the table
  that is held by the first client, leads to a circular wait deadlock. This
  scenario is very similar to trying to drop (or rename) a table while
  holding read locks and are correctly forbidden.
  
  The solution is to allow a drop table operation to continue only if the
  table being dropped is write (exclusively) locked, or if the table is
  temporary, or if the client is not holding any locks. Using this scheme
  prevents the creation of a circular chain in which each client is waiting
  for one table that the next client in the chain is holding.
  
  This is incompatible change, as can be seen by number of tests cases
  that needed to be fixed, but is consistent with respect to behavior of
  the different scenarios in which the circular wait might happen.
[6 Sep 2007 3:57] 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/33778

ChangeSet@1.2605, 2007-09-06 00:54:06-03:00, davi@moksha.local +21 -0
  Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
  
  When a client (connection) holds a lock on a table and attempts to
  drop (obtain a exclusive lock) on a second table that is already held
  by a second client and the second client then attempts to drop the table
  that is held by the first client, leads to a circular wait deadlock. This
  scenario is very similar to trying to drop (or rename) a table while
  holding read locks and are correctly forbidden.
  
  The solution is to allow a drop table operation to continue only if the
  table being dropped is write (exclusively) locked, or if the table is
  temporary, or if the client is not holding any locks. Using this scheme
  prevents the creation of a circular chain in which each client is waiting
  for one table that the next client in the chain is holding.
  
  This is incompatible change, as can be seen by number of tests cases
  that needed to be fixed, but is consistent with respect to behavior of
  the different scenarios in which the circular wait might happen.
[27 Sep 2007 20:45] Konstantin Osipov
Sent review by email.
[3 Oct 2007 0:27] 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/34795

ChangeSet@1.2609, 2007-10-02 21:27:31-03:00, davi@moksha.local +21 -0
  Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
  
  When a client (connection) holds a lock on a table and attempts to
  drop (obtain a exclusive lock) on a second table that is already held
  by a second client and the second client then attempts to drop the table
  that is held by the first client, leads to a circular wait deadlock. This
  scenario is very similar to trying to drop (or rename) a table while
  holding read locks and are correctly forbidden.
  
  The solution is to allow a drop table operation to continue only if the
  table being dropped is write (exclusively) locked, or if the table is
  temporary, or if the client is not holding any locks. Using this scheme
  prevents the creation of a circular chain in which each client is waiting
  for one table that the next client in the chain is holding.
  
  This is incompatible change, as can be seen by number of tests cases
  that needed to be fixed, but is consistent with respect to behavior of
  the different scenarios in which the circular wait might happen.
[3 Oct 2007 11:17] Konstantin Osipov
Approved the second version of the patch by email.
[5 Oct 2007 13:51] Davi Arnaut
Pushed into 5.2-runtime
[19 Oct 2007 20:13] Davi Arnaut
Pushed in 5.2.6
[29 Oct 2007 18:20] Paul Dubois
Noted in 6.0.3 changelog (5.2.6 changes will appear in 6.0.3):

DROP TABLE now is allowed only if you have acquired a WRITE lock with
LOCK TABLES, or if you hold no locks, or if the table is a TEMPORARY
table.

Previously, if other tables were locked, you could drop a table with
a read lock or no lock, which could lead to deadlocks between
clients. The new stricter behavior means that some usage scenarios 
will fail when previously they did not. 

The change is also noted in the 5.1->6.0 upgrading, DROP TABLE, and LOCK TABLES sections.
[11 Aug 2008 15:29] Michael Widenius
The bug fix is wrong as it causes a behavior change, which can be seen by that a lot of test cases had to be changes.

In general, any bug fix that causes old test cases to be changed have to be approved by the architecture board  or at least by some architects.

Proposed better fix:

- Change any existing read lock to a write lock in drop table. This fixes the race condition
[12 Aug 2008 3:40] Davi Arnaut
I'm closing this bug again because this issue has been fixed for over 9 months and 14 days and released many times over. If the fix is disputed, a new bug should be opened and that bug should follow the usual procedures even if the said bug is a request to revert the documented incompatible changes introduced by this one.
[20 Nov 2009 19:51] 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/91160

2926 Konstantin Osipov	2009-11-20
      Backport of:
      ------------------------------------------------------------
      revno: 2476.784.3
      committer: davi@moksha.local
      timestamp: Tue 2007-10-02 21:27:31 -0300
      message:
      Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks
              
      When a client (connection) holds a lock on a table and attempts to
      drop (obtain a exclusive lock) on a second table that is already
      held by a second client and the second client then attempts to
      drop the table that is held by the first client, leads to a
      circular wait deadlock. This scenario is very similar to trying to
      drop (or rename) a table while holding read locks and are
      correctly forbidden.
              
      The solution is to allow a drop table operation to continue only
      if the table being dropped is write (exclusively) locked, or if
      the table is temporary, or if the client is not holding any
      locks. Using this scheme prevents the creation of a circular
      chain in which each client is waiting for one table that the
      next client in the chain is holding.
                  
      This is incompatible change, as can be seen by number of tests
      cases that needed to be fixed, but is consistent with respect to
      behavior of the different scenarios in which the circular wait
      might happen.
     @ mysql-test/r/drop.result
        Test case result for Bug#25858
     @ mysql-test/r/group_by.result
        Fix test case result wrt drop table under lock tables -- unlock tables
        before dropping table.
     @ mysql-test/r/insert_notembedded.result
        Fix test case result wrt drop table under lock tables -- unlock tables
        before dropping table
     @ mysql-test/r/lock.result
        Fix test case result wrt drop table under lock tables -- unlock tables
        before dropping table.
     @ mysql-test/r/lock_multi.result
        Fix test case result wrt drop table under lock tables -- unlock tables
        before dropping table.
     @ mysql-test/r/myisam.result
        Fix test case result wrt drop table under lock tables -- unlock tables
        before dropping table.
     @ mysql-test/r/view.result
        Fix test case result wrt drop table under lock tables -- unlock tables
        before dropping table.
     @ mysql-test/t/drop.test
        Add test case for Bug#25858
     @ mysql-test/t/group_by.test
        Fix test case: unlock tables in preparation for a drop table. In some
        circumstances, dropping tables while holding locks leads to a deadlock.
     @ mysql-test/t/insert_notembedded.test
        Fix test case: unlock tables in preparation for a drop table. In some
        circumstances, dropping tables while holding locks leads to a deadlock.
     @ mysql-test/t/lock.test
        Fix test case: unlock tables in preparation for a drop table. In some
        circumstances, dropping tables while holding locks leads to a deadlock.
     @ mysql-test/t/lock_multi.test
        Fix test case: unlock tables in preparation for a drop table. In some
        circumstances, dropping tables while holding locks leads to a deadlock.
     @ mysql-test/t/myisam.test
        Fix test case: unlock tables in preparation for a drop table. In some
        circumstances, dropping tables while holding locks leads to a deadlock.
     @ mysql-test/t/query_cache_notembedded.test
        Fix test case: unlock tables in preparation for a drop table. In some
        circumstances, dropping tables while holding locks leads to a deadlock.
     @ mysql-test/t/view.test
        Fix test case: unlock tables in preparation for a drop table. In some
        circumstances, dropping tables while holding locks leads to a deadlock.
     @ sql/lock.cc
        When trying to obtain a name lock under lock tables, ensure that the table is properly exclusively locked and fail otherwise.
[20 Nov 2009 19:52] Konstantin Osipov
Queued into 5.6 (next-mr-runtime).
[25 Nov 2009 13:33] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (version source revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (merge vers: 6.0.14-alpha) (pib:13)
[25 Nov 2009 13:34] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091124193905-3iyzegd75k4givuz) (version source revid:kostja@sun.com-20091120195112-qntdy86torkjh1j0) (merge vers: 5.6.0-beta) (pib:13)
[25 Nov 2009 14:45] Paul Dubois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[6 Mar 2010 11:02] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091125142014-7asc9sj33gzki0ym) (merge vers: 5.6.0-beta) (pib:16)
[6 Mar 2010 19:56] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.