Debian Codegraph @

mysql-test/suite/perfschema/t/idx_data_lock_waits.test

raw
1 # Performance schema tests generally depend on specific query plans
2 # and behavior, and much of the perfschema suite is therefore disabled
3 # if the hypergraph optimizer is active.
4 --source include/not_hypergraph.inc
5
6 # Tests for PERFORMANCE_SCHEMA
7
8 show create table performance_schema.data_lock_waits;
9
10 # Debug
11 let $debug_script = 0;
12
13 #setup
14 let $select_column = COUNT(*);
15 let $table = performance_schema.data_lock_waits;
16
17 connect(con1, localhost, root,,);
18
19 --echo # Connection con1
20 --connection con1
21
22 create database explain_test_db;
23 create table explain_test_db.explain_test_table(a int, b int, PRIMARY KEY (a, b)) engine=innodb
24 PARTITION BY RANGE( a )
25 SUBPARTITION BY HASH( b ) (
26 PARTITION p0 VALUES LESS THAN (1990) (
27 SUBPARTITION s0,
28 SUBPARTITION s1
29 ),
30 PARTITION p1 VALUES LESS THAN (2000) (
31 SUBPARTITION s2,
32 SUBPARTITION s3
33 ),
34 PARTITION p2 VALUES LESS THAN MAXVALUE (
35 SUBPARTITION s4,
36 SUBPARTITION s5
37 )
38 );
39
40 insert into explain_test_db.explain_test_table values (1, 100), (1995, 200);
41
42 start transaction;
43 select * from explain_test_db.explain_test_table where a <= 1990 for update;
44
45 connect(con2, localhost, root,,);
46
47 --echo # Connection con2
48 --connection con2
49
50 start transaction;
51 --send
52 insert into explain_test_db.explain_test_table values (2, 150), (1998, 250);
53
54 --echo # Connection default
55 --connection default
56
57 # Wait for con2 to block
58 -- connection default
59 let $wait_condition= SELECT COUNT(*)>=1 FROM performance_schema.data_locks
60 where OBJECT_NAME='explain_test_table' and LOCK_STATUS='WAITING';
61 --source include/wait_condition.inc
62
63 connect(con3, localhost, root,,);
64
65 --echo # Connection con3
66 --connection con3
67
68 create table explain_test_db.noise_table(a int, b int, PRIMARY KEY (a, b)) engine=innodb;
69 insert into explain_test_db.noise_table
70 values (1, 100), (2, 200), (3, 300), (5, 500);
71 start transaction;
72 select * from explain_test_db.noise_table where a >= 2 for update;
73
74 connect(con4, localhost, root,,);
75
76 --echo # Connection con4
77 --connection con4
78
79 start transaction;
80 --send
81 insert into explain_test_db.noise_table values (4, 400);
82
83 --connection default
84
85 # Wait for con4 to block
86 -- connection default
87 let $wait_condition= SELECT COUNT(*)>=1 FROM performance_schema.data_locks
88 where OBJECT_NAME='noise_table' and LOCK_STATUS='WAITING';
89 --source include/wait_condition.inc
90 if (!$success)
91 {
92 # wait_condition failed, dumping data for debug
93 select * from performance_schema.threads;
94 select * from performance_schema.data_locks;
95 select * from performance_schema.data_lock_waits;
96 }
97
98 IF ($debug_script == 1)
99 {
100 select * from performance_schema.data_locks;
101 select * from performance_schema.data_lock_waits;
102 }
103
104 # Make sure there are enough records for the test to be valid
105 select count(*) >= 2 from performance_schema.data_lock_waits;
106
107 # Find requesting data lock on hold
108 select ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID
109 from performance_schema.data_locks
110 where OBJECT_NAME='explain_test_table' and LOCK_STATUS='WAITING'
111 into @engine, @requesting_lock_id, @requesting_trx_id,
112 @requesting_thread_id, @requesting_event_id;
113
114 # Find blocking data lock
115 # use upper(ENGINE) to prevent the optimizer from using an index
116 select
117 BLOCKING_ENGINE_LOCK_ID,
118 BLOCKING_ENGINE_TRANSACTION_ID,
119 BLOCKING_THREAD_ID,
120 BLOCKING_EVENT_ID
121 from performance_schema.data_lock_waits
122 where (upper(ENGINE) = upper(@engine))
123 and (REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id)
124 into @blocking_lock_id, @blocking_trx_id, @blocking_thread_id, @blocking_event_id;
125
126 IF ($debug_script == 1)
127 {
128 select @engine,
129 @requesting_lock_id,
130 @requesting_trx_id,
131 @requesting_thread_id,
132 @requesting_event_id,
133 @blocking_lock_id,
134 @blocking_trx_id,
135 @blocking_thread_id,
136 @blocking_event_id;
137 }
138
139 # Make sure this test found a record.
140 select @engine,
141 @requesting_lock_id is null,
142 @requesting_trx_id is null,
143 @requesting_thread_id is null,
144 @requesting_event_id is null,
145 @blocking_lock_id is null,
146 @blocking_trx_id is null,
147 @blocking_thread_id is null,
148 @blocking_event_id is null;
149
150 ###########################################################################
151 # Test index on REQUESTING_ENGINE_LOCK_ID, BLOCKING_ENGINE_LOCK_ID, ENGINE
152 ###########################################################################
153
154 let $table = performance_schema.data_lock_waits;
155
156 let $column_count = 3;
157 let $col1 = REQUESTING_ENGINE_LOCK_ID;
158 let $col2 = BLOCKING_ENGINE_LOCK_ID;
159 let $col3 = ENGINE;
160 let $col1_act = @requesting_lock_id;
161 let $col2_act = @blocking_lock_id;
162 let $col3_act = @engine;
163
164 --source ../include/idx_explain_test.inc
165
166 # verify actual data
167 select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id,
168 REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id,
169 ENGINE = @engine
170 from performance_schema.data_lock_waits
171 where (ENGINE = @engine)
172 and (REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id)
173 and (BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id);
174
175 ###########################################################################
176 # Test index on REQUESTING_ENGINE_LOCK_ID, ENGINE
177 ###########################################################################
178
179 let $table = performance_schema.data_lock_waits;
180
181 let $column_count = 2;
182 let $col1 = REQUESTING_ENGINE_LOCK_ID;
183 let $col2 = ENGINE;
184 let $col1_act = @requesting_lock_id;
185 let $col2_act = @engine;
186
187 --source ../include/idx_explain_test.inc
188
189 # verify actual data
190 select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
191 from performance_schema.data_lock_waits
192 where (ENGINE = @engine)
193 and (REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id);
194
195 ###########################################################################
196 # Test index on BLOCKING_ENGINE_LOCK_ID, ENGINE
197 ###########################################################################
198
199 let $table = performance_schema.data_lock_waits;
200
201 let $column_count = 2;
202 let $col1 = BLOCKING_ENGINE_LOCK_ID;
203 let $col2 = ENGINE;
204 let $col1_act = @blocking_lock_id;
205 let $col2_act = @engine;
206
207 --source ../include/idx_explain_test.inc
208
209 # verify actual data
210 select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
211 from performance_schema.data_lock_waits
212 where (ENGINE = @engine)
213 and (BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id);
214
215 ###########################################################################
216 # Test index on REQUESTING_ENGINE_TRANSACTION_ID, ENGINE
217 ###########################################################################
218
219 let $table = performance_schema.data_lock_waits;
220
221 let $column_count = 2;
222 let $col1 = REQUESTING_ENGINE_TRANSACTION_ID;
223 let $col2 = ENGINE;
224 let $col1_act = @requesting_trx_id;
225 let $col2_act = @engine;
226
227 --source ../include/idx_explain_test.inc
228
229 # verify actual data
230 select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
231 from performance_schema.data_lock_waits
232 where (ENGINE = @engine)
233 and (REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id);
234
235 ###########################################################################
236 # Test index on BLOCKING_ENGINE_TRANSACTION_ID, ENGINE
237 ###########################################################################
238
239 let $table = performance_schema.data_lock_waits;
240
241 let $column_count = 2;
242 let $col1 = BLOCKING_ENGINE_TRANSACTION_ID;
243 let $col2 = ENGINE;
244 let $col1_act = @blocking_trx_id;
245 let $col2_act = @engine;
246
247 --source ../include/idx_explain_test.inc
248
249 # verify actual data
250 select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
251 from performance_schema.data_lock_waits
252 where (ENGINE = @engine)
253 and (BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id);
254
255 ###########################################################################
256 # Test index on REQUESTING_THREAD_ID, REQUESTING_EVENT_ID
257 ###########################################################################
258
259 let $table = performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID);
260
261 let $column_count = 2;
262 let $col1 = REQUESTING_THREAD_ID;
263 let $col2 = REQUESTING_EVENT_ID;
264 let $col1_act = @requesting_thread_id;
265 let $col2_act = @requesting_event_id;
266
267 --source ../include/idx_explain_test.inc
268
269 # verify actual data
270 select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
271 from performance_schema.data_lock_waits
272 where (REQUESTING_THREAD_ID = @requesting_thread_id)
273 and (REQUESTING_EVENT_ID = @requesting_event_id);
274
275 ###########################################################################
276 # Test index on BLOCKING_THREAD_ID, BLOCKING_EVENT_ID
277 ###########################################################################
278
279 let $table = performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID);
280
281 let $column_count = 2;
282 let $col1 = BLOCKING_THREAD_ID;
283 let $col2 = BLOCKING_EVENT_ID;
284 let $col1_act = @blocking_thread_id;
285 let $col2_act = @blocking_event_id;
286
287 --source ../include/idx_explain_test.inc
288
289 # verify actual data
290 select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
291 from performance_schema.data_lock_waits
292 where (BLOCKING_THREAD_ID = @blocking_thread_id)
293 and (BLOCKING_EVENT_ID = @blocking_event_id);
294
295 # Cleanup
296
297 --echo # Connection con1
298 --connection con1
299 commit;
300
301 --echo # Connection con2
302 --connection con2
303 --reap
304 commit;
305
306 --echo # Connection con3
307 --connection con3
308 commit;
309
310 --echo # Connection con4
311 --connection con4
312 --reap
313 commit;
314
315 --echo # Connection default
316 --connection default
317
318 drop table explain_test_db.explain_test_table;
319 drop database explain_test_db;
320
321 --disconnect con1
322 --disconnect con2
323 --disconnect con3
324 --disconnect con4