Skip to content

Commit bcc0978

Browse files
author
Jon Olav Hauglid
committed
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. --BZR-- revision-id: [email protected] property-branch-nick: mysql-trunk-runtime-bug54360 testament3-sha1: 7fb5e97c3d1ca888afc0bff76dea59899ce10495
1 parent f20072a commit bcc0978

29 files changed

+1300
-652
lines changed

‎.bzrfileids‎

60 Bytes
Binary file not shown.

‎mysql-test/r/mdl_sync.result‎

Lines changed: 237 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2527,3 +2527,240 @@ SET DEBUG_SYNC= "now SIGNAL completed";
25272527
Field Type Collation Null Key Default Extra Privileges Comment
25282528
a char(255) latin1_swedish_ci YES NULL #
25292529
DROP TABLE t1;
2530+
#
2531+
# Tests for schema-scope locks
2532+
#
2533+
DROP DATABASE IF EXISTS db1;
2534+
DROP DATABASE IF EXISTS db2;
2535+
# Test 1:
2536+
# CREATE DATABASE blocks database DDL on the same database, but
2537+
# not database DDL on different databases. Tests X vs X lock.
2538+
#
2539+
# Connection default
2540+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2541+
# Sending:
2542+
CREATE DATABASE db1;
2543+
# Connection con2
2544+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2545+
# Sending:
2546+
CREATE DATABASE db1;
2547+
# Connection con3
2548+
CREATE DATABASE db2;
2549+
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
2550+
DROP DATABASE db2;
2551+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2552+
# Connection default
2553+
# Reaping: CREATE DATABASE db1
2554+
# Connection con2
2555+
# Reaping: CREATE DATABASE db1
2556+
ERROR HY000: Can't create database 'db1'; database exists
2557+
# Test 2:
2558+
# ALTER DATABASE blocks database DDL on the same database, but
2559+
# not database DDL on different databases. Tests X vs X lock.
2560+
#
2561+
# Connection default
2562+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2563+
# Sending:
2564+
ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
2565+
# Connection con2
2566+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2567+
# Sending:
2568+
ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
2569+
# Connection con3
2570+
CREATE DATABASE db2;
2571+
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
2572+
DROP DATABASE db2;
2573+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2574+
# Connection default
2575+
# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
2576+
# Connection con2
2577+
# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
2578+
# Connection default
2579+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2580+
# Sending:
2581+
ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
2582+
# Connection con2
2583+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2584+
# Sending:
2585+
DROP DATABASE db1;
2586+
# Connection con3
2587+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2588+
# Connection default
2589+
# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
2590+
# Connection con2
2591+
# Reaping: DROP DATABASE db1
2592+
CREATE DATABASE db1;
2593+
# Test 3:
2594+
# Two ALTER..UPGRADE of the same database are mutually exclusive, but
2595+
# two ALTER..UPGRADE of different databases are not. Tests X vs X lock.
2596+
#
2597+
# Connection default
2598+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2599+
# Sending:
2600+
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
2601+
# Connection con2
2602+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2603+
# Sending:
2604+
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
2605+
# Connection con3
2606+
ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME;
2607+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2608+
# Connection default
2609+
# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME
2610+
# Connection con2
2611+
# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME
2612+
ERROR 42000: Unknown database '#mysql50#a-b-c'
2613+
DROP DATABASE `a-b-c`;
2614+
DROP DATABASE `a-b-c-d`;
2615+
# Test 4:
2616+
# DROP DATABASE blocks database DDL on the same database, but
2617+
# not database DDL on different databases. Tests X vs X lock.
2618+
#
2619+
# Connection default
2620+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2621+
# Sending:
2622+
DROP DATABASE db1;
2623+
# Connection con2
2624+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2625+
# Sending:
2626+
DROP DATABASE db1;
2627+
# Connection con3
2628+
CREATE DATABASE db2;
2629+
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
2630+
DROP DATABASE db2;
2631+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2632+
# Connection default
2633+
# Reaping: DROP DATABASE db1
2634+
# Connection con2
2635+
# Reaping: DROP DATABASE db1
2636+
ERROR HY000: Can't drop database 'db1'; database doesn't exist
2637+
# Connection default
2638+
CREATE DATABASE db1;
2639+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2640+
# Sending:
2641+
DROP DATABASE db1;
2642+
# Connection con2
2643+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2644+
# Sending:
2645+
ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
2646+
# Connection con3
2647+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2648+
# Connection default
2649+
# Reaping: DROP DATABASE db1
2650+
# Connection con2
2651+
# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
2652+
ERROR HY000: Can't create/write to file './db1/db.opt' (Errcode: 2)
2653+
# Test 5:
2654+
# Locked database name prevents CREATE of tables in that database.
2655+
# Tests X vs IX lock.
2656+
#
2657+
# Connection default
2658+
CREATE DATABASE db1;
2659+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2660+
# Sending:
2661+
DROP DATABASE db1;
2662+
# Connection con2
2663+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2664+
# Sending:
2665+
CREATE TABLE db1.t1 (a INT);
2666+
# Connection con3
2667+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2668+
# Connection default
2669+
# Reaping: DROP DATABASE db1
2670+
# Connection con2
2671+
# Reaping: CREATE TABLE db1.t1 (a INT)
2672+
ERROR 42000: Unknown database 'db1'
2673+
# Test 6:
2674+
# Locked database name prevents RENAME of tables to/from that database.
2675+
# Tests X vs IX lock.
2676+
#
2677+
# Connection default
2678+
CREATE DATABASE db1;
2679+
CREATE TABLE db1.t1 (a INT);
2680+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2681+
# Sending:
2682+
DROP DATABASE db1;
2683+
# Connection con2
2684+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2685+
# Sending:
2686+
RENAME TABLE db1.t1 TO test.t1;
2687+
# Connection con3
2688+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2689+
# Connection default
2690+
# Reaping: DROP DATABASE db1
2691+
# Connection con2
2692+
# Reaping: RENAME TABLE db1.t1 TO test.t1
2693+
ERROR HY000: Can't find file: './db1/t1.frm' (errno: 2)
2694+
# Connection default
2695+
CREATE DATABASE db1;
2696+
CREATE TABLE test.t2 (a INT);
2697+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2698+
# Sending:
2699+
DROP DATABASE db1;
2700+
# Connection con2
2701+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2702+
# Sending:
2703+
RENAME TABLE test.t2 TO db1.t2;
2704+
# Connection con3
2705+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2706+
# Connection default
2707+
# Reaping: DROP DATABASE db1
2708+
# Connection con2
2709+
# Reaping: RENAME TABLE test.t2 TO db1.t2
2710+
ERROR HY000: Error on rename of './test/t2.MYI' to './db1/t2.MYI' (Errcode: 2)
2711+
DROP TABLE test.t2;
2712+
# Test 7:
2713+
# Locked database name prevents DROP of tables in that database.
2714+
# Tests X vs IX lock.
2715+
#
2716+
# Connection default
2717+
CREATE DATABASE db1;
2718+
CREATE TABLE db1.t1 (a INT);
2719+
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
2720+
# Sending:
2721+
DROP DATABASE db1;
2722+
# Connection con2
2723+
SET DEBUG_SYNC= 'now WAIT_FOR locked';
2724+
# Sending:
2725+
DROP TABLE db1.t1;
2726+
# Connection con3
2727+
SET DEBUG_SYNC= 'now SIGNAL blocked';
2728+
# Connection default
2729+
# Reaping: DROP DATABASE db1
2730+
# Connection con2
2731+
# Reaping: DROP TABLE db1.t1
2732+
ERROR 42S02: Unknown table 't1'
2733+
# Connection default
2734+
SET DEBUG_SYNC= 'RESET';
2735+
#
2736+
# End of tests for schema-scope locks
2737+
#
2738+
#
2739+
# Tests of granted global S lock (FLUSH TABLE WITH READ LOCK)
2740+
#
2741+
CREATE DATABASE db1;
2742+
CREATE TABLE db1.t1(a INT);
2743+
# Connection default
2744+
FLUSH TABLE WITH READ LOCK;
2745+
# Connection con2
2746+
CREATE TABLE db1.t2(a INT);
2747+
# Connection default
2748+
UNLOCK TABLES;
2749+
# Connection con2
2750+
# Reaping CREATE TABLE db1.t2(a INT)
2751+
# Connection default
2752+
FLUSH TABLE WITH READ LOCK;
2753+
# Connection con2
2754+
ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;
2755+
# Connection default
2756+
UNLOCK TABLES;
2757+
# Connection con2
2758+
# Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
2759+
# Connection default
2760+
FLUSH TABLE WITH READ LOCK;
2761+
# Connection con2
2762+
FLUSH TABLE WITH READ LOCK;
2763+
UNLOCK TABLES;
2764+
# Connection default
2765+
UNLOCK TABLES;
2766+
DROP DATABASE db1;

‎mysql-test/r/partition_debug_sync.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,7 @@ ENGINE = MYISAM
4747
PARTITION p1 VALUES LESS THAN (20),
4848
PARTITION p2 VALUES LESS THAN (100),
4949
PARTITION p3 VALUES LESS THAN MAXVALUE ) */;
50-
SET DEBUG_SYNC= 'open_tables_acquire_upgradable_mdl SIGNAL removing_partitions WAIT_FOR waiting_for_alter';
50+
SET DEBUG_SYNC= 'alter_table_before_open_tables SIGNAL removing_partitions WAIT_FOR waiting_for_alter';
5151
SET DEBUG_SYNC= 'alter_table_before_rename_result_table WAIT_FOR delete_done';
5252
ALTER TABLE t2 REMOVE PARTITIONING;
5353
# Con default

‎mysql-test/r/schema.result‎

Lines changed: 48 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -16,19 +16,21 @@ drop schema foo;
1616
# Bug #48940 MDL deadlocks against mysql_rm_db
1717
#
1818
DROP SCHEMA IF EXISTS schema1;
19+
DROP SCHEMA IF EXISTS schema2;
1920
# Connection default
2021
CREATE SCHEMA schema1;
22+
CREATE SCHEMA schema2;
2123
CREATE TABLE schema1.t1 (a INT);
2224
SET autocommit= FALSE;
2325
INSERT INTO schema1.t1 VALUES (1);
2426
# Connection 2
2527
DROP SCHEMA schema1;
2628
# Connection default
27-
ALTER SCHEMA schema1 DEFAULT CHARACTER SET utf8;
28-
Got one of the listed errors
29+
ALTER SCHEMA schema2 DEFAULT CHARACTER SET utf8;
2930
SET autocommit= TRUE;
3031
# Connection 2
3132
# Connection default
33+
DROP SCHEMA schema2;
3234
#
3335
# Bug #49988 MDL deadlocks with mysql_create_db, reload_acl_and_cache
3436
#
@@ -48,3 +50,47 @@ ERROR HY000: Can't execute the given command because you have active locked tabl
4850
UNLOCK TABLES;
4951
# Connection con2
5052
# Connection default
53+
#
54+
# Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER
55+
#
56+
CREATE DATABASE db1;
57+
CREATE TABLE db1.t1 (a INT);
58+
INSERT INTO db1.t1 VALUES (1), (2);
59+
# Connection con1
60+
HANDLER db1.t1 OPEN;
61+
# Connection default
62+
# Sending:
63+
DROP DATABASE db1;
64+
# Connection con2
65+
# Connection con1
66+
CREATE DATABASE db2;
67+
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
68+
DROP DATABASE db2;
69+
# Connection default
70+
# Reaping: DROP DATABASE db1
71+
#
72+
# Tests for increased CREATE/ALTER/DROP DATABASE concurrency with
73+
# database name locks.
74+
#
75+
DROP DATABASE IF EXISTS db1;
76+
DROP DATABASE IF EXISTS db2;
77+
# Connection default
78+
CREATE DATABASE db1;
79+
CREATE TABLE db1.t1 (id INT);
80+
START TRANSACTION;
81+
INSERT INTO db1.t1 VALUES (1);
82+
# Connection 2
83+
# DROP DATABASE should block due to the active transaction
84+
# Sending:
85+
DROP DATABASE db1;
86+
# Connection 3
87+
# But it should still be possible to CREATE/ALTER/DROP other databases.
88+
CREATE DATABASE db2;
89+
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
90+
DROP DATABASE db2;
91+
# Connection default
92+
# End the transaction so DROP DATABASE db1 can continue
93+
COMMIT;
94+
# Connection 2
95+
# Reaping: DROP DATABASE db1
96+
# Connection default;

‎mysql-test/suite/perfschema/r/server_init.result‎

Lines changed: 0 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -40,18 +40,10 @@ where name like "wait/synch/cond/mysys/THR_COND_threads";
4040
count(name)
4141
1
4242
select count(name) from MUTEX_INSTANCES
43-
where name like "wait/synch/mutex/sql/LOCK_mysql_create_db";
44-
count(name)
45-
1
46-
select count(name) from MUTEX_INSTANCES
4743
where name like "wait/synch/mutex/sql/LOCK_open";
4844
count(name)
4945
1
5046
select count(name) from MUTEX_INSTANCES
51-
where name like "wait/synch/mutex/sql/LOCK_lock_db";
52-
count(name)
53-
1
54-
select count(name) from MUTEX_INSTANCES
5547
where name like "wait/synch/mutex/sql/LOCK_thread_count";
5648
count(name)
5749
1

‎mysql-test/suite/perfschema/t/server_init.test‎

Lines changed: 0 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -68,15 +68,9 @@ select count(name) from COND_INSTANCES
6868

6969
# Verify that these global mutexes have been properly initilized in sql
7070

71-
select count(name) from MUTEX_INSTANCES
72-
where name like "wait/synch/mutex/sql/LOCK_mysql_create_db";
73-
7471
select count(name) from MUTEX_INSTANCES
7572
where name like "wait/synch/mutex/sql/LOCK_open";
7673

77-
select count(name) from MUTEX_INSTANCES
78-
where name like "wait/synch/mutex/sql/LOCK_lock_db";
79-
8074
select count(name) from MUTEX_INSTANCES
8175
where name like "wait/synch/mutex/sql/LOCK_thread_count";
8276

0 commit comments

Comments
 (0)