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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
#
# 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;
#
# MDEV-28820 MyISAM wrong server status flags
#
create table t1 (a int);
set autocommit=0;
select @@in_transaction;
@@in_transaction
0
select * from t1;
a
select @@in_transaction;
@@in_transaction
0
connect foo,localhost,root;
drop table t1;
connection default;
set autocommit=1;
create table t1 (a int);
create table t2 (b int) engine=innodb;
set autocommit=0;
select @@in_transaction;
@@in_transaction
0
select * from t2;
b
select @@in_transaction;
@@in_transaction
1
select * from t1;
a
connection foo;
drop table t1;
connection default;
select * from t1;
a
commit;
connection foo;
disconnect foo;
connection default;
set autocommit=default;
drop table t2;
#
# End of 10.4 tests
#
|
