Bug #54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER
Submitted: 9 Jun 2010 9:26 Modified: 15 Oct 2010 10:40
Reporter: Jon Olav Hauglid Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1, 5.5 OS:Any
Assigned to: Jon Olav Hauglid
Triage: Triaged: D1 (Critical)

[9 Jun 2010 9:26] Jon Olav Hauglid
Description:
If a connection tries to drop a database which contains a table with open HANDLERs held by another connection, any DATABASE command executed by the latter connection produces a deadlock.

How to repeat:
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT);
INSERT INTO db1.t1 VALUES (1), (2);

connect (con1, localhost, root);
HANDLER db1.t1 OPEN;

connection default;
# This will hold LOCK_mysql_create_db mutex while waiting for HANDLER to close.
--send DROP DATABASE db1

connection con1;
--sleep 1
# The next command can be any variation of CREATE/ALTER/DROP DATABASE as
# they all will try to get LOCK_mysql_create_db
ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
[9 Jun 2010 13:20] 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/110631

3050 Jon Olav Hauglid	2010-06-09
      Bug #54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER
      
      This deadlock happened if DROP DATABASE was blocked due to an open
      HANDLER table from a different connection. While DROP DATABASE
      is blocked, it holds the LOCK_mysql_create_db mutex. This results
      in a deadlock if the connection with the open HANDLER table tries
      to execute a CREATE/ALTER/DROP DATABASE statement as they all
      try to acquire LOCK_mysql_create_db.
      
      This patch prevents the deadlock from occuring by closing and
      marking for re-open all HANDLER tables for which there are pending
      conflicing locks, before LOCK_mysql_create_db is acquired.
      
      Test case added to schema.test.
[23 Jun 2010 17: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/111979

3061 Konstantin Osipov	2010-06-23 [merge]
      A 5.5 version of the fix for Bug #54360 "Deadlock 
      DROP/ALTER/CREATE DATABASE with open HANDLER"
      
      Remove LOCK_create_db, database name locks,
      and use metadata locks instead.
      This exposes CREATE/DROP/ALTER DATABASE
      statements to the graph-based deadlock detector in MDL,
      and paves the way for a safe, deadlock-free implementation
      of RENAME DATABASE.
      
      This patch also adds a simple template to help
      work with mysys HASH data structure.
[24 Jun 2010 15: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/112115

3061 Konstantin Osipov	2010-06-24 [merge]
      A 5.5 version of the fix for Bug #54360 "Deadlock 
      DROP/ALTER/CREATE DATABASE with open HANDLER"
      
      Remove LOCK_create_db, database name locks,
      and use metadata locks instead.
      This exposes CREATE/DROP/ALTER DATABASE
      statements to the graph-based deadlock detector in MDL,
      and paves the way for a safe, deadlock-free implementation
      of RENAME DATABASE.
      
      This patch also adds a simple template to help
      work with mysys HASH data structure.
[24 Jun 2010 17:24] 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/112128

3061 Konstantin Osipov	2010-06-24 [merge]
      A 5.5 version of the fix for Bug #54360 "Deadlock 
      DROP/ALTER/CREATE DATABASE with open HANDLER"
      
      Remove LOCK_create_db, database name locks,
      and use metadata locks instead.
      This exposes CREATE/DROP/ALTER DATABASE
      statements to the graph-based deadlock detector in MDL,
      and paves the way for a safe, deadlock-free implementation
      of RENAME DATABASE.
      
      This patch also adds a simple template to help
      work with mysys HASH data structure.
[26 Jun 2010 17:36] 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/112268

3445 Jon Olav Hauglid	2010-06-26
      Bug #54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER
      
      This deadlock happened if DROP DATABASE was blocked due to an open
      HANDLER table from a different connection. While DROP DATABASE
      is blocked, it holds the LOCK_mysql_create_db mutex. This results
      in a deadlock if the connection with the open HANDLER table tries
      to execute a CREATE/ALTER/DROP DATABASE statement as they all
      try to acquire LOCK_mysql_create_db.
      
      This patch makes this deadlock scenario very unlikely by closing and
      marking for re-open all HANDLER tables for which there are pending
      conflicing locks, before LOCK_mysql_create_db is acquired.
      However, there is still a very slight possibility that a connection
      could access one of these HANDLER tables between closing/marking for
      re-open and the acquisition of LOCK_mysql_create_db.
      
      This patch is for 5.1 only, a separate and complete fix will be
      made for 5.5+.
      
      Test case added to schema.test.
[26 Jun 2010 20:35] Jon Olav Hauglid
5.1 version of patch pushed to mysql-5.1-bugteam (5.1.48) and null-merged to mysql-trunk-merge.

Setting bug back to "In progress" pending 5.5 version of the patch.
[28 Jun 2010 12: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/112342

3065 Jon Olav Hauglid	2010-06-28
      A 5.5 version of the fix for Bug #54360 "Deadlock DROP/ALTER/CREATE
      DATABASE with open HANDLER"
      
      Remove LOCK_create_db, database name locks, and use metadata locks instead.
      This exposes CREATE/DROP/ALTER DATABASE statements to the graph-based
      deadlock detector in MDL, and paves the way for a safe, deadlock-free
      implementation of RENAME DATABASE.
      
      Database DDL statements will now take exclusive metadata locks on
      the database name, while table/view/routine DDL statements take
      intention exclusive locks on the database name. This prevents race
      conditions between database DDL and table/view/routine DDL.
      (e.g. DROP DATABASE with concurrent CREATE/ALTER/DROP TABLE)
      
      By adding database name locks, this patch implements
      WL#4450 "DDL locking: CREATE/DROP DATABASE must use database locks" and
      WL#4985 "DDL locking: namespace/hierarchical locks".
      
      This patch also adds a simple template to help work with 
      the mysys HASH data structure.
      
      Most of the patch was written by Konstantin Osipov.
[30 Jun 2010 12:12] 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/112571

3069 Jon Olav Hauglid	2010-06-30
      A 5.5 version of the fix for Bug #54360 "Deadlock DROP/ALTER/CREATE
      DATABASE with open HANDLER"
      
      Remove LOCK_create_db, database name locks, and use metadata locks instead.
      This exposes CREATE/DROP/ALTER DATABASE statements to the graph-based
      deadlock detector in MDL, and paves the way for a safe, deadlock-free
      implementation of RENAME DATABASE.
      
      Database DDL statements will now take exclusive metadata locks on
      the database name, while table/view/routine DDL statements take
      intention exclusive locks on the database name. This prevents race
      conditions between database DDL and table/view/routine DDL.
      (e.g. DROP DATABASE with concurrent CREATE/ALTER/DROP TABLE)
      
      By adding database name locks, this patch implements
      WL#4450 "DDL locking: CREATE/DROP DATABASE must use database locks" and
      WL#4985 "DDL locking: namespace/hierarchical locks".
      
      This patch also adds a simple template to help work with 
      the mysys HASH data structure.
      
      Most of the patch was written by Konstantin Osipov.
[1 Jul 2010 11:05] 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/112660

3069 Jon Olav Hauglid	2010-07-01
      A 5.5 version of the fix for Bug #54360 "Deadlock DROP/ALTER/CREATE
      DATABASE with open HANDLER"
      
      Remove LOCK_create_db, database name locks, and use metadata locks instead.
      This exposes CREATE/DROP/ALTER DATABASE statements to the graph-based
      deadlock detector in MDL, and paves the way for a safe, deadlock-free
      implementation of RENAME DATABASE.
      
      Database DDL statements will now take exclusive metadata locks on
      the database name, while table/view/routine DDL statements take
      intention exclusive locks on the database name. This prevents race
      conditions between database DDL and table/view/routine DDL.
      (e.g. DROP DATABASE with concurrent CREATE/ALTER/DROP TABLE)
      
      By adding database name locks, this patch implements
      WL#4450 "DDL locking: CREATE/DROP DATABASE must use database locks" and
      WL#4985 "DDL locking: namespace/hierarchical locks".
      
      The patch also changes code to use init_one_table() where appropriate.
      The new lock_table_names() function requires TABLE_LIST::db_length to
      be set correctly, and this is taken care of by init_one_table().
      
      This patch also adds a simple template to help work with 
      the mysys HASH data structure.
      
      Most of the patch was written by Konstantin Osipov.
[1 Jul 2010 13:54] 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/112673

3069 Jon Olav Hauglid	2010-07-01
      A 5.5 version of the fix for Bug #54360 "Deadlock DROP/ALTER/CREATE
      DATABASE with open HANDLER"
      
      Remove LOCK_create_db, database name locks, and use metadata locks instead.
      This exposes CREATE/DROP/ALTER DATABASE statements to the graph-based
      deadlock detector in MDL, and paves the way for a safe, deadlock-free
      implementation of RENAME DATABASE.
      
      Database DDL statements will now take exclusive metadata locks on
      the database name, while table/view/routine DDL statements take
      intention exclusive locks on the database name. This prevents race
      conditions between database DDL and table/view/routine DDL.
      (e.g. DROP DATABASE with concurrent CREATE/ALTER/DROP TABLE)
      
      By adding database name locks, this patch implements
      WL#4450 "DDL locking: CREATE/DROP DATABASE must use database locks" and
      WL#4985 "DDL locking: namespace/hierarchical locks".
      
      The patch also changes code to use init_one_table() where appropriate.
      The new lock_table_names() function requires TABLE_LIST::db_length to
      be set correctly, and this is taken care of by init_one_table().
      
      This patch also adds a simple template to help work with 
      the mysys HASH data structure.
      
      Most of the patch was written by Konstantin Osipov.
[1 Jul 2010 13:54] Jon Olav Hauglid
Patch pushed to mysql-trunk-runtime (5.5.6).
[1 Jul 2010 14:59] 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/112689

3070 Dmitry Lenev	2010-07-01
      A follow-up for 5.5 version of fix for bug#54360 "Deadlock 
      DROP/ALTER/CREATE DATABASE with open HANDLER".
      
      Remove wait_for_condition() which became unused after 
      database locks were replaced with MDL scoped locks.
      If one needs functionality provided by this call one can 
      always use THD::enter_cond()/exit_cond() methods.
      
      Also removed an unused include from sql_db.cc and updated 
      comment describing one of used includes to reflect current
      situation.
[1 Jul 2010 15:49] 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/112695

3071 Dmitry Lenev	2010-07-01
      Another follow-up for 5.5 version of fix for bug#54360 
      "Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER".
      
      Fixes production build which was broken by the fix for
      bug#54360 due to missing instantiation of some Hash_set 
      template's methods.
      
      Circumvent requirement of explicit instantiation of 
      non-inline methods by making all Hash_set methods
      inline.
[2 Jul 2010 9: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/112721

3072 Jon Olav Hauglid	2010-07-02
      Followup to Bug #54360 Deadlock DROP/ALTER/CREATE DATABASE
                             with open HANDLER
      
      Fixes problem which caused mdl_sync.test to fail on Solaris and
      Windows due to path name differences in error messages in the
      result file.
[2 Jul 2010 9:54] 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/112722

3073 Jon Olav Hauglid	2010-07-02
      Followup for Bug #54360 Deadlock DROP/ALTER/CREATE DATABASE
                              with open HANDLER
      
      Fixes a problem with schema.test visible using embedded server.
      The HANDLER was not closed which caused the test to hang.
      
      The problem was not visible if the test was run on a normal server
      as the the handler there was implicitly closed by DATABASE DDL
      statements doing Events::drop_schema_events().
[19 Jul 2010 14:37] Bugs System
Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:jon.hauglid@sun.com-20100626173600-oskpaipjt0d9z1d5) (merge vers: 5.1.48) (pib:16)
[20 Jul 2010 2:09] Paul Dubois
Noted in 5.1.49 changelog.

If a session tried to drop a database containing a table opened with
HANDLER in another session, any DATABASE statement (CREATE, DROP,
ALTER) executed by the that session produced a deadlock.

Setting report to Need Merge pending further pushes.
[23 Jul 2010 12:27] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[26 Jul 2010 15:26] Paul Dubois
Noted in 5.5.6 changelog.
[4 Aug 2010 7:51] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:08] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:24] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 9:03] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)
[4 Aug 2010 23:08] Paul Dubois
Bug does not appear in any released 5.6.x version.
[10 Aug 2010 10: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/115392

3098 Jon Olav Hauglid	2010-08-10
      Followup for Bug #54360 Deadlock DROP/ALTER/CREATE DATABASE
                              with open HANDLER
      
      This patch changes the code for table renames to not drop metadata
      locks. Since table renames are done as a part of ALTER DATABASE ...
      UPGRADE, dropping metadata locks in the middle of execution can
      result in wrong binlog order since it means that no locks are held
      when the binlog is written to.
      
      The RENAME TABLE statement is unafffected since it auto commits and
      therefore already drops metadata locks at the end of execution.
      
      This patch also reverts the regression test for Bug#48940 back to
      its original version. The test was temporarily changed due to the
      issue mentioned above.
[10 Aug 2010 11:17] 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/115394

3098 Jon Olav Hauglid	2010-08-10
      Followup for Bug #54360 Deadlock DROP/ALTER/CREATE DATABASE
                              with open HANDLER
      
      This patch changes the code for table renames to not drop metadata
      locks. Since table renames are done as a part of ALTER DATABASE ...
      UPGRADE, dropping metadata locks in the middle of execution can
      result in wrong binlog order since it means that no locks are held
      when the binlog is written to.
      
      The RENAME TABLE statement is unafffected since it auto commits and
      therefore already drops metadata locks at the end of execution.
      
      This patch also reverts the regression test for Bug#48940 back to
      its original version. The test was temporarily changed due to the
      issue mentioned above.
[10 Aug 2010 11:19] Jon Olav Hauglid
Followup pushed to mysql-5.5-runtime (5.5.6).
[25 Aug 2010 9:23] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)
[30 Aug 2010 8:31] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[10 Sep 2010 2:57] Paul Dubois
Noted in 5.5.7, 5.6.1 changelog.

If one session attempted to drop a database containing a table which
another session had opened with HANDLER, any instance of ALTER 
DATABASE, CREATE DATABASE, or DROP DATABASE issued by the latter
session produced a deadlock.
[14 Oct 2010 8:32] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:47] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 9:01] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[15 Oct 2010 10:40] Jon Stephens
Already documented in the 5.1.49 changelog. No new changelog entries required. Setting back to Closed.