MySQL Bugs: #25858: Some DROP TABLE under LOCK TABLES can cause deadlocks

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

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.

verified, will upload a complete test case (the current one is missing DROP TABLE IF EXISTS ...)

mysqltest test case

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

Davi, please contact me before starting work on this bug.

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.

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.

Sent review by email.

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.

Approved the second version of the patch by email.

Pushed into 5.2-runtime

Pushed in 5.2.6

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.

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

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.

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.

Queued into 5.6 (next-mr-runtime).

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)

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)

Noted in 5.6.0 changelog.

Already fixed in 6.0.x.

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)

Moved 5.6.0 changelog entry to 5.5.3.