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 files changed, 48 insertions, 2 deletions

diff --git a/mysql-test/r/schema.result b/mysql-test/r/schema.result
index 2919606d74a..890f53669b5 100644
--- a/mysql-test/r/schema.result
+++ b/mysql-test/r/schema.result
@@ -16,19 +16,21 @@ drop schema foo;
 # Bug #48940 MDL deadlocks against mysql_rm_db
 #
 DROP SCHEMA IF EXISTS schema1;
+DROP SCHEMA IF EXISTS schema2;
 # Connection default
 CREATE SCHEMA schema1;
+CREATE SCHEMA schema2;
 CREATE TABLE schema1.t1 (a INT);
 SET autocommit= FALSE;
 INSERT INTO schema1.t1 VALUES (1);
 # Connection 2
 DROP SCHEMA schema1;
 # Connection default
-ALTER SCHEMA schema1 DEFAULT CHARACTER SET utf8;
-Got one of the listed errors
+ALTER SCHEMA schema2 DEFAULT CHARACTER SET utf8;
 SET autocommit= TRUE;
 # Connection 2
 # Connection default
+DROP SCHEMA schema2;
 #
 # Bug #49988 MDL deadlocks with mysql_create_db, reload_acl_and_cache
 #
@@ -48,3 +50,47 @@ ERROR HY000: Can't execute the given command because you have active locked tabl
 UNLOCK TABLES;
 # Connection con2
 # Connection default
+#
+# Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER
+#
+CREATE DATABASE db1;
+CREATE TABLE db1.t1 (a INT);
+INSERT INTO db1.t1 VALUES (1), (2);
+# Connection con1
+HANDLER db1.t1 OPEN;
+# Connection default
+# Sending:
+DROP DATABASE db1;
+# Connection con2
+# Connection con1
+CREATE DATABASE db2;
+ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
+DROP DATABASE db2;
+# Connection default
+# Reaping: DROP DATABASE db1
+#
+# Tests for increased CREATE/ALTER/DROP DATABASE concurrency with
+# database name locks.
+# 
+DROP DATABASE IF EXISTS db1;
+DROP DATABASE IF EXISTS db2;
+# Connection default
+CREATE DATABASE db1;
+CREATE TABLE db1.t1 (id INT);
+START TRANSACTION;
+INSERT INTO db1.t1 VALUES (1);
+# Connection 2
+# DROP DATABASE should block due to the active transaction
+# Sending:
+DROP DATABASE db1;
+# Connection 3
+# But it should still be possible to CREATE/ALTER/DROP other databases.
+CREATE DATABASE db2;
+ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
+DROP DATABASE db2;
+# Connection default
+# End the transaction so DROP DATABASE db1 can continue
+COMMIT;
+# Connection 2
+# Reaping: DROP DATABASE db1
+# Connection default;