1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
#
# MDEV-12882 - Assertion `mdl_ticket->m_type == MDL_SHARED_UPGRADABLE ||
# mdl_ticket->m_type == MDL_SHARED_NO_WRITE ||
# mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE ||
# mdl_ticket->m_type == MDL_SHARED_READ'
# failed in MDL_context::upgrade_shared_lock
#
CREATE TABLE t1(a INT) ENGINE=InnoDB;
CREATE TABLE t3(a INT) ENGINE=myisam;
LOCK TABLES t1 WRITE CONCURRENT, t1 AS t2 READ;
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_TRANS_DML Backup lock
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
UNLOCK TABLES;
LOCK TABLES t1 AS t2 READ, t1 WRITE CONCURRENT;
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_TRANS_DML Backup lock
MDL_SHARED_WRITE Table metadata lock test t1
MDL_SHARED_READ_ONLY Table metadata lock test t1
UNLOCK TABLES;
LOCK TABLES t1 WRITE CONCURRENT, t3 WRITE;
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_SHARED_WRITE Table metadata lock test t1
MDL_SHARED_NO_READ_WRITE Table metadata lock test t3
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
UNLOCK TABLES;
LOCK TABLES t3 WRITE, t1 WRITE CONCURRENT;
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_BACKUP_DML Backup lock
MDL_SHARED_WRITE Table metadata lock test t1
MDL_SHARED_NO_READ_WRITE Table metadata lock test t3
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
UNLOCK TABLES;
LOCK TABLES t1 WRITE, mysql.user WRITE;
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_DDL Backup lock
MDL_SHARED_NO_READ_WRITE Table metadata lock mysql user
MDL_SHARED_NO_READ_WRITE Table metadata lock test t1
MDL_INTENTION_EXCLUSIVE Schema metadata lock mysql
MDL_SHARED_NO_READ_WRITE Table metadata lock mysql global_priv
MDL_INTENTION_EXCLUSIVE Schema metadata lock test
UNLOCK TABLES;
LOCK TABLES mysql.general_log WRITE;
ERROR HY000: You can't use locks with log tables
LOCK TABLES t1 WRITE,information_schema.tables READ;
UNLOCK TABLES;
DROP TABLE t1,t3;
#
# Check MDL locks taken for different kind of tables by open
#
CREATE TABLE t1(a INT) ENGINE=InnoDB;
CREATE TABLE t3(a INT) ENGINE=myisam;
connect purge_control,localhost,root,,;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connect locker,localhost,root,,;
connection default;
FLUSH TABLES WITH READ LOCK;
connection locker;
insert into t1 values (1);
connection default;
connection default;
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_FTWRL2 Backup lock
MDL_SHARED_WRITE Table metadata lock test t1
unlock tables;
connection locker;
unlock tables;
connection default;
FLUSH TABLES WITH READ LOCK;
connection locker;
insert into t3 values (2);
connection default;
connection default;
SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME
MDL_BACKUP_FTWRL2 Backup lock
MDL_SHARED_WRITE Table metadata lock test t3
unlock tables;
connection locker;
unlock tables;
disconnect purge_control;
connection default;
disconnect locker;
DROP TABLE t1,t3;
|
