Bug #19815 CREATE/RENAME/DROP DATABASE can deadlock on a global read lock
Submitted: 15 May 2006 13:17 Modified: 10 Jun 2006 15:44
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:5.0.21 OS:Linux (Linux)
Assigned to: Ingo Strüwing CPU Architecture:Any

[15 May 2006 13:17] Ingo Strüwing 
Description:
connection con1;
CREATE DATABASE mysqltest_1;
FLUSH TABLES WITH READ LOCK;
#
# With bug in place: acquire LOCK_mysql_create_table and
# wait in wait_if_global_read_lock().
connection con2;
send DROP DATABASE mysqltest_1;
--sleep 1
#
# With bug in place: try to acquire LOCK_mysql_create_table...
# When fixed: Reject dropping db because of the read lock.
connection con1;
--error ER_CANT_UPDATE_WITH_READLOCK
DROP DATABASE mysqltest_1;

How to repeat:
See above.

Suggested fix:
Exchange the order of acquiring LOCK_mysql_create_table and wait_if_global_read_lock() for CREATE, RENAME, and DROP DATABASE.
[15 May 2006 14: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/6387
[30 May 2006 12:45] Ingo Strüwing 
The order of acquiring LOCK_mysql_create_db
  and wait_if_global_read_lock() was wrong. It could happen
  that a thread held LOCK_mysql_create_db while waiting for
  the global read lock to be released. The thread with the
  global read lock could try to administrate a database too.
  It would first try to lock LOCK_mysql_create_db and hang...
  
  The check if the current thread has the global read lock
  is done in wait_if_global_read_lock(), which could not be
  reached because of the hang in LOCK_mysql_create_db.
  
  Now I exchanged the order of acquiring LOCK_mysql_create_db
  and wait_if_global_read_lock(). This makes 
  wait_if_global_read_lock() fail with an error message for
  the thread with the global read lock. No deadlock happens.

Pushed to 5.0.22 and 5.1.11.
[31 May 2006 1:24] Paul DuBois 
Noted in 5.0.23, 5.1.11 changelogs.

If there is a global read lock, CREATE DATABASE, RENAME DATABASE,
and DROP DATABASE could deadlock.
[10 Jun 2006 14:49] Ingo Strüwing 
Due to a mistake in team tree handling this patch didn't make it into 5.1.11. It will now be in 5.1.12. (and 5.0.23.) I apologize for the confusion.
[10 Jun 2006 15:44] Paul DuBois 
5.1 changelog entry moved from 5.1.11 to 5.1.12.