| 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: | |
| Category: | MySQL Server: Locking | Severity: | S2 (Serious) |
| Version: | 5.1, 5.5 | OS: | Any |
| Assigned to: | Jon Olav Hauglid | CPU Architecture: | Any |
[9 Jun 2010 9:26]
Jon Olav Hauglid
[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.
