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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
|
SET DEBUG_SYNC= 'RESET';
drop table if exists t1,t2,t3;
create table t1 (i int);
create table t2 (i int);
connection: default
lock tables t2 read;
connection: con1
set debug_sync='mdl_upgrade_shared_lock_to_exclusive SIGNAL parked WAIT_FOR go';
alter table t1 rename t3;
connection: default
set debug_sync= 'now WAIT_FOR parked';
connection: con2
set debug_sync='mdl_acquire_exclusive_locks_wait SIGNAL go';
drop table t1,t2;
connection: con1
connection: default
unlock tables;
connection: con2
ERROR 42S02: Unknown table 't1'
drop table t3;
SET DEBUG_SYNC= 'RESET';
#
# Test for bug #46748 "Assertion in MDL_context::wait_for_locks()
# on INSERT + CREATE TRIGGER".
#
drop tables if exists t1, t2, t3, t4, t5;
# Let us simulate scenario in which we open some tables from extended
# part of prelocking set but then encounter conflicting metadata lock,
# so have to back-off and wait for it to go away.
create table t1 (i int);
create table t2 (j int);
create table t3 (k int);
create table t4 (l int);
create trigger t1_bi before insert on t1 for each row
insert into t2 values (new.i);
create trigger t2_bi before insert on t2 for each row
insert into t3 values (new.j);
#
# Switching to connection 'con1root'.
lock tables t4 read;
#
# Switching to connection 'con2root'.
# Send :
rename table t3 to t5, t4 to t3;;
#
# Switching to connection 'default'.
# Wait until the above RENAME TABLE adds pending requests for exclusive
# metadata lock on its tables and blocks due to 't4' being used by LOCK
# TABLES.
# Send :
insert into t1 values (1);;
#
# Switching to connection 'con1root'.
# Wait until INSERT statement waits due to encountering pending
# exclusive metadata lock on 't3'.
unlock tables;
#
# Switching to connection 'con2root'.
# Reap RENAME TABLE.
#
# Switching to connection 'default'.
# Reap INSERT.
# Clean-up.
drop tables t1, t2, t3, t5;
#
# Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY
# FOR UPDATE"
#
drop tables if exists t1, t2;
create table t1 (i int);
# Let us check that we won't deadlock if during filling
# of I_S table we encounter conflicting metadata lock
# which owner is in its turn waiting for our connection.
lock tables t1 write;
# Switching to connection 'con46044'.
# Sending:
create table t2 select * from t1;;
# Switching to connection 'default'.
# Waiting until CREATE TABLE ... SELECT ... is blocked.
# First let us check that SHOW FIELDS/DESCRIBE doesn't
# gets blocked and emits and error.
show fields from t2;
ERROR HY000: Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
# Now test for I_S query which reads only .FRMs.
#
# Query below should only emit a warning.
select column_name from information_schema.columns
where table_schema='test' and table_name='t2';
column_name
Warnings:
Warning 1652 Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement
# Finally, test for I_S query which does full-blown table open.
#
# Query below should not be blocked. Warning message should be
# stored in the 'table_comment' column.
select table_name, table_type, auto_increment, table_comment
from information_schema.tables where table_schema='test' and table_name='t2';
table_name table_type auto_increment table_comment
t2 BASE TABLE NULL Table 'test'.'t2' was skipped since its definition is being modified by concurre
# Switching to connection 'default'.
unlock tables;
# Switching to connection 'con46044'.
# Reaping CREATE TABLE ... SELECT ... .
drop table t2;
#
# Let us also check that queries to I_S wait for conflicting metadata
# locks to go away instead of skipping table with a warning in cases
# when deadlock is not possible. This is a nice thing from compatibility
# and ease of use points of view.
#
# We check same three queries to I_S in this new situation.
# Switching to connection 'con46044_2'.
lock tables t1 write;
# Switching to connection 'con46044'.
# Sending:
create table t2 select * from t1;;
# Switching to connection 'default'.
# Waiting until CREATE TABLE ... SELECT ... is blocked.
# Let us check that SHOW FIELDS/DESCRIBE gets blocked.
# Sending:
show fields from t2;;
# Switching to connection 'con46044_2'.
# Wait until SHOW FIELDS gets blocked.
unlock tables;
# Switching to connection 'con46044'.
# Reaping CREATE TABLE ... SELECT ... .
# Switching to connection 'default'.
# Reaping SHOW FIELDS ...
Field Type Null Key Default Extra
i int(11) YES NULL
drop table t2;
# Switching to connection 'con46044_2'.
lock tables t1 write;
# Switching to connection 'con46044'.
# Sending:
create table t2 select * from t1;;
# Switching to connection 'default'.
# Waiting until CREATE TABLE ... SELECT ... is blocked.
# Check that I_S query which reads only .FRMs gets blocked.
# Sending:
select column_name from information_schema.columns where table_schema='test' and table_name='t2';;
# Switching to connection 'con46044_2'.
# Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS gets blocked.
unlock tables;
# Switching to connection 'con46044'.
# Reaping CREATE TABLE ... SELECT ... .
# Switching to connection 'default'.
# Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS
column_name
i
drop table t2;
# Switching to connection 'con46044_2'.
lock tables t1 write;
# Switching to connection 'con46044'.
# Sending:
create table t2 select * from t1;;
# Switching to connection 'default'.
# Waiting until CREATE TABLE ... SELECT ... is blocked.
# Finally, check that I_S query which does full-blown table open
# also gets blocked.
# Sending:
select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2';;
# Switching to connection 'con46044_2'.
# Wait until SELECT ... FROM I_S.TABLES gets blocked.
unlock tables;
# Switching to connection 'con46044'.
# Reaping CREATE TABLE ... SELECT ... .
# Switching to connection 'default'.
# Reaping SELECT ... FROM I_S.TABLES
table_name table_type auto_increment table_comment
t2 BASE TABLE NULL
drop table t2;
# Switching to connection 'default'.
# Clean-up.
drop table t1;
#
# Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK
# and DML".
#
drop tables if exists t1;
create table t1 (i int);
# Switching to connection 'con46673'.
begin;
insert into t1 values (1);
# Switching to connection 'default'.
# Statement below should not get blocked. And if after some
# changes to code it is there should not be a deadlock between
# it and transaction from connection 'con46673'.
flush tables with read lock;
unlock tables;
# Switching to connection 'con46673'.
delete from t1 where i = 1;
commit;
# Switching to connection 'default'.
# Clean-up
drop table t1;
|
