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 9:27	Modified:	12 Aug 5:40
Reporter:	Dmitri Lenev
Status:	Closed
Category:	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	Target Version:

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.