Debian Codegraph @

mysql-test/suite/perfschema/r/idx_data_lock_waits.result

raw
1 show create table performance_schema.data_lock_waits;
2 Table Create Table
3 data_lock_waits CREATE TABLE `data_lock_waits` (
4 `ENGINE` varchar(32) NOT NULL,
5 `REQUESTING_ENGINE_LOCK_ID` varchar(128) NOT NULL,
6 `REQUESTING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL,
7 `REQUESTING_THREAD_ID` bigint unsigned DEFAULT NULL,
8 `REQUESTING_EVENT_ID` bigint unsigned DEFAULT NULL,
9 `REQUESTING_OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL,
10 `BLOCKING_ENGINE_LOCK_ID` varchar(128) NOT NULL,
11 `BLOCKING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL,
12 `BLOCKING_THREAD_ID` bigint unsigned DEFAULT NULL,
13 `BLOCKING_EVENT_ID` bigint unsigned DEFAULT NULL,
14 `BLOCKING_OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL,
15 PRIMARY KEY (`REQUESTING_ENGINE_LOCK_ID`,`BLOCKING_ENGINE_LOCK_ID`,`ENGINE`),
16 KEY `REQUESTING_ENGINE_LOCK_ID` (`REQUESTING_ENGINE_LOCK_ID`,`ENGINE`),
17 KEY `BLOCKING_ENGINE_LOCK_ID` (`BLOCKING_ENGINE_LOCK_ID`,`ENGINE`),
18 KEY `REQUESTING_ENGINE_TRANSACTION_ID` (`REQUESTING_ENGINE_TRANSACTION_ID`,`ENGINE`),
19 KEY `BLOCKING_ENGINE_TRANSACTION_ID` (`BLOCKING_ENGINE_TRANSACTION_ID`,`ENGINE`),
20 KEY `REQUESTING_THREAD_ID` (`REQUESTING_THREAD_ID`,`REQUESTING_EVENT_ID`),
21 KEY `BLOCKING_THREAD_ID` (`BLOCKING_THREAD_ID`,`BLOCKING_EVENT_ID`)
22 ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
23 # Connection con1
24 create database explain_test_db;
25 create table explain_test_db.explain_test_table(a int, b int, PRIMARY KEY (a, b)) engine=innodb
26 PARTITION BY RANGE( a )
27 SUBPARTITION BY HASH( b ) (
28 PARTITION p0 VALUES LESS THAN (1990) (
29 SUBPARTITION s0,
30 SUBPARTITION s1
31 ),
32 PARTITION p1 VALUES LESS THAN (2000) (
33 SUBPARTITION s2,
34 SUBPARTITION s3
35 ),
36 PARTITION p2 VALUES LESS THAN MAXVALUE (
37 SUBPARTITION s4,
38 SUBPARTITION s5
39 )
40 );
41 insert into explain_test_db.explain_test_table values (1, 100), (1995, 200);
42 start transaction;
43 select * from explain_test_db.explain_test_table where a <= 1990 for update;
44 a b
45 1 100
46 # Connection con2
47 start transaction;
48 insert into explain_test_db.explain_test_table values (2, 150), (1998, 250);
49 # Connection default
50 # Connection con3
51 create table explain_test_db.noise_table(a int, b int, PRIMARY KEY (a, b)) engine=innodb;
52 insert into explain_test_db.noise_table
53 values (1, 100), (2, 200), (3, 300), (5, 500);
54 start transaction;
55 select * from explain_test_db.noise_table where a >= 2 for update;
56 a b
57 2 200
58 3 300
59 5 500
60 # Connection con4
61 start transaction;
62 insert into explain_test_db.noise_table values (4, 400);
63 select count(*) >= 2 from performance_schema.data_lock_waits;
64 count(*) >= 2
65 1
66 select ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, EVENT_ID
67 from performance_schema.data_locks
68 where OBJECT_NAME='explain_test_table' and LOCK_STATUS='WAITING'
69 into @engine, @requesting_lock_id, @requesting_trx_id,
70 @requesting_thread_id, @requesting_event_id;
71 select
72 BLOCKING_ENGINE_LOCK_ID,
73 BLOCKING_ENGINE_TRANSACTION_ID,
74 BLOCKING_THREAD_ID,
75 BLOCKING_EVENT_ID
76 from performance_schema.data_lock_waits
77 where (upper(ENGINE) = upper(@engine))
78 and (REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id)
79 into @blocking_lock_id, @blocking_trx_id, @blocking_thread_id, @blocking_event_id;
80 select @engine,
81 @requesting_lock_id is null,
82 @requesting_trx_id is null,
83 @requesting_thread_id is null,
84 @requesting_event_id is null,
85 @blocking_lock_id is null,
86 @blocking_trx_id is null,
87 @blocking_thread_id is null,
88 @blocking_event_id is null;
89 @engine @requesting_lock_id is null @requesting_trx_id is null @requesting_thread_id is null @requesting_event_id is null @blocking_lock_id is null @blocking_trx_id is null @blocking_thread_id is null @blocking_event_id is null
90 INNODB 0 0 0 0 0 0 0 0
91
92 ====================================================================
93 Testing index for columns REQUESTING_ENGINE_LOCK_ID, BLOCKING_ENGINE_LOCK_ID, ENGINE
94 ====================================================================
95 ############ Explain for Query ####################################
96 explain select COUNT(*)
97 from performance_schema.data_lock_waits
98 where REQUESTING_ENGINE_LOCK_ID = "impossible";
99 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
100 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 100.00 NULL
101 ############ Explain for Query ####################################
102 explain select COUNT(*)
103 from performance_schema.data_lock_waits
104 where REQUESTING_ENGINE_LOCK_ID > "impossible";
105 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
106 1 SIMPLE data_lock_waits NULL ALL PRIMARY,REQUESTING_ENGINE_LOCK_ID NULL NULL NULL # 33.33 Using where
107 ############ Explain for Query ####################################
108 explain select COUNT(*)
109 from performance_schema.data_lock_waits
110 where REQUESTING_ENGINE_LOCK_ID < "2";
111 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
112 1 SIMPLE data_lock_waits NULL ALL PRIMARY,REQUESTING_ENGINE_LOCK_ID NULL NULL NULL # 33.33 Using where
113 ############ Explain for Query ####################################
114 explain select COUNT(*)
115 from performance_schema.data_lock_waits
116 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id;
117 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
118 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 100.00 NULL
119 ############ Explain for Query ####################################
120 explain select COUNT(*)
121 from performance_schema.data_lock_waits
122 where REQUESTING_ENGINE_LOCK_ID = "impossible"
123 and BLOCKING_ENGINE_LOCK_ID = "impossible";
124 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
125 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID PRIMARY 1028 const,const # 100.00 NULL
126 ############ Explain for Query ####################################
127 explain select COUNT(*)
128 from performance_schema.data_lock_waits
129 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
130 and BLOCKING_ENGINE_LOCK_ID = "impossible";
131 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
132 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID PRIMARY 1028 const,const # 100.00 NULL
133 ############ Explain for Query ####################################
134 explain select COUNT(*)
135 from performance_schema.data_lock_waits
136 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
137 and BLOCKING_ENGINE_LOCK_ID > "impossible";
138 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
139 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID PRIMARY 514 const # 33.33 Using where
140 ############ Explain for Query ####################################
141 explain select COUNT(*)
142 from performance_schema.data_lock_waits
143 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
144 and BLOCKING_ENGINE_LOCK_ID < "2";
145 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
146 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID PRIMARY 514 const # 33.33 Using where
147 ############ Explain for Query ####################################
148 explain select COUNT(*)
149 from performance_schema.data_lock_waits
150 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
151 and BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id;
152 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
153 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID PRIMARY 1028 const,const # 100.00 NULL
154 ############ Explain for Query ####################################
155 explain select COUNT(*)
156 from performance_schema.data_lock_waits
157 where REQUESTING_ENGINE_LOCK_ID = "impossible"
158 and BLOCKING_ENGINE_LOCK_ID = "impossible"
159 and ENGINE = "impossible";
160 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
161 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # NULL no matching row in const table
162 ############ Explain for Query ####################################
163 explain select COUNT(*)
164 from performance_schema.data_lock_waits
165 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
166 and BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
167 and ENGINE = "impossible";
168 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
169 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL # NULL no matching row in const table
170 ############ Explain for Query ####################################
171 explain select COUNT(*)
172 from performance_schema.data_lock_waits
173 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
174 and BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
175 and ENGINE > "impossible";
176 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
177 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID PRIMARY 1028 const,const # 33.33 Using where
178 ############ Explain for Query ####################################
179 explain select COUNT(*)
180 from performance_schema.data_lock_waits
181 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
182 and BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
183 and ENGINE < "2";
184 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
185 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID PRIMARY 1028 const,const # 33.33 Using where
186 ############ Explain for Query ####################################
187 explain select COUNT(*)
188 from performance_schema.data_lock_waits
189 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
190 and BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
191 and ENGINE = @engine;
192 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
193 1 SIMPLE data_lock_waits NULL const PRIMARY,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID PRIMARY 1158 const,const,const # 100.00 NULL
194 ############# Explain End #########################################
195 flush status;
196 select COUNT(*)
197 from performance_schema.data_lock_waits
198 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
199 and BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
200 and ENGINE = @engine;
201 COUNT(*)
202 #
203 OK: handler_read_key incremented
204 select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id,
205 REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id,
206 ENGINE = @engine
207 from performance_schema.data_lock_waits
208 where (ENGINE = @engine)
209 and (REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id)
210 and (BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id);
211 BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id ENGINE = @engine
212 1 1 1
213
214 ====================================================================
215 Testing index for columns REQUESTING_ENGINE_LOCK_ID, ENGINE
216 ====================================================================
217 ############ Explain for Query ####################################
218 explain select COUNT(*)
219 from performance_schema.data_lock_waits
220 where REQUESTING_ENGINE_LOCK_ID = "impossible";
221 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
222 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 100.00 NULL
223 ############ Explain for Query ####################################
224 explain select COUNT(*)
225 from performance_schema.data_lock_waits
226 where REQUESTING_ENGINE_LOCK_ID > "impossible";
227 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
228 1 SIMPLE data_lock_waits NULL ALL PRIMARY,REQUESTING_ENGINE_LOCK_ID NULL NULL NULL # 33.33 Using where
229 ############ Explain for Query ####################################
230 explain select COUNT(*)
231 from performance_schema.data_lock_waits
232 where REQUESTING_ENGINE_LOCK_ID < "2";
233 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
234 1 SIMPLE data_lock_waits NULL ALL PRIMARY,REQUESTING_ENGINE_LOCK_ID NULL NULL NULL # 33.33 Using where
235 ############ Explain for Query ####################################
236 explain select COUNT(*)
237 from performance_schema.data_lock_waits
238 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id;
239 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
240 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 100.00 NULL
241 ############ Explain for Query ####################################
242 explain select COUNT(*)
243 from performance_schema.data_lock_waits
244 where REQUESTING_ENGINE_LOCK_ID = "impossible"
245 and ENGINE = "impossible";
246 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
247 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 10.00 Using where
248 ############ Explain for Query ####################################
249 explain select COUNT(*)
250 from performance_schema.data_lock_waits
251 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
252 and ENGINE = "impossible";
253 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
254 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 10.00 Using where
255 ############ Explain for Query ####################################
256 explain select COUNT(*)
257 from performance_schema.data_lock_waits
258 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
259 and ENGINE > "impossible";
260 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
261 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 33.33 Using where
262 ############ Explain for Query ####################################
263 explain select COUNT(*)
264 from performance_schema.data_lock_waits
265 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
266 and ENGINE < "2";
267 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
268 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 33.33 Using where
269 ############ Explain for Query ####################################
270 explain select COUNT(*)
271 from performance_schema.data_lock_waits
272 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
273 and ENGINE = @engine;
274 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
275 1 SIMPLE data_lock_waits NULL ref PRIMARY,REQUESTING_ENGINE_LOCK_ID PRIMARY 514 const # 10.00 Using where
276 ############# Explain End #########################################
277 flush status;
278 select COUNT(*)
279 from performance_schema.data_lock_waits
280 where REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
281 and ENGINE = @engine;
282 COUNT(*)
283 #
284 OK: handler_read_key incremented
285 select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
286 from performance_schema.data_lock_waits
287 where (ENGINE = @engine)
288 and (REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id);
289 BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
290 1
291
292 ====================================================================
293 Testing index for columns BLOCKING_ENGINE_LOCK_ID, ENGINE
294 ====================================================================
295 ############ Explain for Query ####################################
296 explain select COUNT(*)
297 from performance_schema.data_lock_waits
298 where BLOCKING_ENGINE_LOCK_ID = "impossible";
299 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
300 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_LOCK_ID BLOCKING_ENGINE_LOCK_ID 514 const # 100.00 NULL
301 ############ Explain for Query ####################################
302 explain select COUNT(*)
303 from performance_schema.data_lock_waits
304 where BLOCKING_ENGINE_LOCK_ID > "impossible";
305 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
306 1 SIMPLE data_lock_waits NULL ALL BLOCKING_ENGINE_LOCK_ID NULL NULL NULL # 33.33 Using where
307 ############ Explain for Query ####################################
308 explain select COUNT(*)
309 from performance_schema.data_lock_waits
310 where BLOCKING_ENGINE_LOCK_ID < "2";
311 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
312 1 SIMPLE data_lock_waits NULL ALL BLOCKING_ENGINE_LOCK_ID NULL NULL NULL # 33.33 Using where
313 ############ Explain for Query ####################################
314 explain select COUNT(*)
315 from performance_schema.data_lock_waits
316 where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id;
317 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
318 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_LOCK_ID BLOCKING_ENGINE_LOCK_ID 514 const # 100.00 NULL
319 ############ Explain for Query ####################################
320 explain select COUNT(*)
321 from performance_schema.data_lock_waits
322 where BLOCKING_ENGINE_LOCK_ID = "impossible"
323 and ENGINE = "impossible";
324 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
325 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_LOCK_ID BLOCKING_ENGINE_LOCK_ID 644 const,const # 100.00 NULL
326 ############ Explain for Query ####################################
327 explain select COUNT(*)
328 from performance_schema.data_lock_waits
329 where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
330 and ENGINE = "impossible";
331 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
332 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_LOCK_ID BLOCKING_ENGINE_LOCK_ID 644 const,const # 100.00 NULL
333 ############ Explain for Query ####################################
334 explain select COUNT(*)
335 from performance_schema.data_lock_waits
336 where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
337 and ENGINE > "impossible";
338 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
339 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_LOCK_ID BLOCKING_ENGINE_LOCK_ID 514 const # 33.33 Using where
340 ############ Explain for Query ####################################
341 explain select COUNT(*)
342 from performance_schema.data_lock_waits
343 where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
344 and ENGINE < "2";
345 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
346 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_LOCK_ID BLOCKING_ENGINE_LOCK_ID 514 const # 33.33 Using where
347 ############ Explain for Query ####################################
348 explain select COUNT(*)
349 from performance_schema.data_lock_waits
350 where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
351 and ENGINE = @engine;
352 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
353 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_LOCK_ID BLOCKING_ENGINE_LOCK_ID 644 const,const # 100.00 NULL
354 ############# Explain End #########################################
355 flush status;
356 select COUNT(*)
357 from performance_schema.data_lock_waits
358 where BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
359 and ENGINE = @engine;
360 COUNT(*)
361 #
362 OK: handler_read_key incremented
363 select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
364 from performance_schema.data_lock_waits
365 where (ENGINE = @engine)
366 and (BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id);
367 REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
368 1
369
370 ====================================================================
371 Testing index for columns REQUESTING_ENGINE_TRANSACTION_ID, ENGINE
372 ====================================================================
373 ############ Explain for Query ####################################
374 explain select COUNT(*)
375 from performance_schema.data_lock_waits
376 where REQUESTING_ENGINE_TRANSACTION_ID = "impossible";
377 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
378 1 SIMPLE data_lock_waits NULL ref REQUESTING_ENGINE_TRANSACTION_ID REQUESTING_ENGINE_TRANSACTION_ID 9 const # 100.00 Using where
379 ############ Explain for Query ####################################
380 explain select COUNT(*)
381 from performance_schema.data_lock_waits
382 where REQUESTING_ENGINE_TRANSACTION_ID > "impossible";
383 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
384 1 SIMPLE data_lock_waits NULL ALL REQUESTING_ENGINE_TRANSACTION_ID NULL NULL NULL # 33.33 Using where
385 ############ Explain for Query ####################################
386 explain select COUNT(*)
387 from performance_schema.data_lock_waits
388 where REQUESTING_ENGINE_TRANSACTION_ID < "2";
389 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
390 1 SIMPLE data_lock_waits NULL ALL REQUESTING_ENGINE_TRANSACTION_ID NULL NULL NULL # 33.33 Using where
391 ############ Explain for Query ####################################
392 explain select COUNT(*)
393 from performance_schema.data_lock_waits
394 where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id;
395 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
396 1 SIMPLE data_lock_waits NULL ref REQUESTING_ENGINE_TRANSACTION_ID REQUESTING_ENGINE_TRANSACTION_ID 9 const # 100.00 NULL
397 ############ Explain for Query ####################################
398 explain select COUNT(*)
399 from performance_schema.data_lock_waits
400 where REQUESTING_ENGINE_TRANSACTION_ID = "impossible"
401 and ENGINE = "impossible";
402 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
403 1 SIMPLE data_lock_waits NULL ref REQUESTING_ENGINE_TRANSACTION_ID REQUESTING_ENGINE_TRANSACTION_ID 139 const,const # 100.00 Using where
404 ############ Explain for Query ####################################
405 explain select COUNT(*)
406 from performance_schema.data_lock_waits
407 where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
408 and ENGINE = "impossible";
409 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
410 1 SIMPLE data_lock_waits NULL ref REQUESTING_ENGINE_TRANSACTION_ID REQUESTING_ENGINE_TRANSACTION_ID 139 const,const # 100.00 NULL
411 ############ Explain for Query ####################################
412 explain select COUNT(*)
413 from performance_schema.data_lock_waits
414 where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
415 and ENGINE > "impossible";
416 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
417 1 SIMPLE data_lock_waits NULL ref REQUESTING_ENGINE_TRANSACTION_ID REQUESTING_ENGINE_TRANSACTION_ID 9 const # 33.33 Using where
418 ############ Explain for Query ####################################
419 explain select COUNT(*)
420 from performance_schema.data_lock_waits
421 where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
422 and ENGINE < "2";
423 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
424 1 SIMPLE data_lock_waits NULL ref REQUESTING_ENGINE_TRANSACTION_ID REQUESTING_ENGINE_TRANSACTION_ID 9 const # 33.33 Using where
425 ############ Explain for Query ####################################
426 explain select COUNT(*)
427 from performance_schema.data_lock_waits
428 where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
429 and ENGINE = @engine;
430 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
431 1 SIMPLE data_lock_waits NULL ref REQUESTING_ENGINE_TRANSACTION_ID REQUESTING_ENGINE_TRANSACTION_ID 139 const,const # 100.00 NULL
432 ############# Explain End #########################################
433 flush status;
434 select COUNT(*)
435 from performance_schema.data_lock_waits
436 where REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id
437 and ENGINE = @engine;
438 COUNT(*)
439 #
440 OK: handler_read_key incremented
441 select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
442 from performance_schema.data_lock_waits
443 where (ENGINE = @engine)
444 and (REQUESTING_ENGINE_TRANSACTION_ID = @requesting_trx_id);
445 BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
446 1
447
448 ====================================================================
449 Testing index for columns BLOCKING_ENGINE_TRANSACTION_ID, ENGINE
450 ====================================================================
451 ############ Explain for Query ####################################
452 explain select COUNT(*)
453 from performance_schema.data_lock_waits
454 where BLOCKING_ENGINE_TRANSACTION_ID = "impossible";
455 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
456 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_TRANSACTION_ID BLOCKING_ENGINE_TRANSACTION_ID 9 const # 100.00 Using where
457 ############ Explain for Query ####################################
458 explain select COUNT(*)
459 from performance_schema.data_lock_waits
460 where BLOCKING_ENGINE_TRANSACTION_ID > "impossible";
461 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
462 1 SIMPLE data_lock_waits NULL ALL BLOCKING_ENGINE_TRANSACTION_ID NULL NULL NULL # 33.33 Using where
463 ############ Explain for Query ####################################
464 explain select COUNT(*)
465 from performance_schema.data_lock_waits
466 where BLOCKING_ENGINE_TRANSACTION_ID < "2";
467 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
468 1 SIMPLE data_lock_waits NULL ALL BLOCKING_ENGINE_TRANSACTION_ID NULL NULL NULL # 33.33 Using where
469 ############ Explain for Query ####################################
470 explain select COUNT(*)
471 from performance_schema.data_lock_waits
472 where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id;
473 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
474 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_TRANSACTION_ID BLOCKING_ENGINE_TRANSACTION_ID 9 const # 100.00 NULL
475 ############ Explain for Query ####################################
476 explain select COUNT(*)
477 from performance_schema.data_lock_waits
478 where BLOCKING_ENGINE_TRANSACTION_ID = "impossible"
479 and ENGINE = "impossible";
480 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
481 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_TRANSACTION_ID BLOCKING_ENGINE_TRANSACTION_ID 139 const,const # 100.00 Using where
482 ############ Explain for Query ####################################
483 explain select COUNT(*)
484 from performance_schema.data_lock_waits
485 where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
486 and ENGINE = "impossible";
487 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
488 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_TRANSACTION_ID BLOCKING_ENGINE_TRANSACTION_ID 139 const,const # 100.00 NULL
489 ############ Explain for Query ####################################
490 explain select COUNT(*)
491 from performance_schema.data_lock_waits
492 where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
493 and ENGINE > "impossible";
494 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
495 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_TRANSACTION_ID BLOCKING_ENGINE_TRANSACTION_ID 9 const # 33.33 Using where
496 ############ Explain for Query ####################################
497 explain select COUNT(*)
498 from performance_schema.data_lock_waits
499 where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
500 and ENGINE < "2";
501 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
502 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_TRANSACTION_ID BLOCKING_ENGINE_TRANSACTION_ID 9 const # 33.33 Using where
503 ############ Explain for Query ####################################
504 explain select COUNT(*)
505 from performance_schema.data_lock_waits
506 where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
507 and ENGINE = @engine;
508 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
509 1 SIMPLE data_lock_waits NULL ref BLOCKING_ENGINE_TRANSACTION_ID BLOCKING_ENGINE_TRANSACTION_ID 139 const,const # 100.00 NULL
510 ############# Explain End #########################################
511 flush status;
512 select COUNT(*)
513 from performance_schema.data_lock_waits
514 where BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id
515 and ENGINE = @engine;
516 COUNT(*)
517 #
518 OK: handler_read_key incremented
519 select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
520 from performance_schema.data_lock_waits
521 where (ENGINE = @engine)
522 and (BLOCKING_ENGINE_TRANSACTION_ID = @blocking_trx_id);
523 REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
524 1
525
526 ====================================================================
527 Testing index for columns REQUESTING_THREAD_ID, REQUESTING_EVENT_ID
528 ====================================================================
529 ############ Explain for Query ####################################
530 explain select COUNT(*)
531 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
532 where REQUESTING_THREAD_ID = "impossible";
533 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
534 1 SIMPLE data_lock_waits NULL ref REQUESTING_THREAD_ID REQUESTING_THREAD_ID 9 const # 100.00 Using where
535 ############ Explain for Query ####################################
536 explain select COUNT(*)
537 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
538 where REQUESTING_THREAD_ID > "impossible";
539 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
540 1 SIMPLE data_lock_waits NULL ALL REQUESTING_THREAD_ID NULL NULL NULL # 33.33 Using where
541 ############ Explain for Query ####################################
542 explain select COUNT(*)
543 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
544 where REQUESTING_THREAD_ID < "2";
545 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
546 1 SIMPLE data_lock_waits NULL ALL REQUESTING_THREAD_ID NULL NULL NULL # 33.33 Using where
547 ############ Explain for Query ####################################
548 explain select COUNT(*)
549 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
550 where REQUESTING_THREAD_ID = @requesting_thread_id;
551 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
552 1 SIMPLE data_lock_waits NULL ref REQUESTING_THREAD_ID REQUESTING_THREAD_ID 9 const # 100.00 NULL
553 ############ Explain for Query ####################################
554 explain select COUNT(*)
555 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
556 where REQUESTING_THREAD_ID = "impossible"
557 and REQUESTING_EVENT_ID = "impossible";
558 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
559 1 SIMPLE data_lock_waits NULL ref REQUESTING_THREAD_ID REQUESTING_THREAD_ID 18 const,const # 100.00 Using where
560 ############ Explain for Query ####################################
561 explain select COUNT(*)
562 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
563 where REQUESTING_THREAD_ID = @requesting_thread_id
564 and REQUESTING_EVENT_ID = "impossible";
565 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
566 1 SIMPLE data_lock_waits NULL ref REQUESTING_THREAD_ID REQUESTING_THREAD_ID 18 const,const # 100.00 Using where
567 ############ Explain for Query ####################################
568 explain select COUNT(*)
569 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
570 where REQUESTING_THREAD_ID = @requesting_thread_id
571 and REQUESTING_EVENT_ID > "impossible";
572 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
573 1 SIMPLE data_lock_waits NULL ref REQUESTING_THREAD_ID REQUESTING_THREAD_ID 9 const # 33.33 Using where
574 ############ Explain for Query ####################################
575 explain select COUNT(*)
576 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
577 where REQUESTING_THREAD_ID = @requesting_thread_id
578 and REQUESTING_EVENT_ID < "2";
579 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
580 1 SIMPLE data_lock_waits NULL ref REQUESTING_THREAD_ID REQUESTING_THREAD_ID 9 const # 33.33 Using where
581 ############ Explain for Query ####################################
582 explain select COUNT(*)
583 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
584 where REQUESTING_THREAD_ID = @requesting_thread_id
585 and REQUESTING_EVENT_ID = @requesting_event_id;
586 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
587 1 SIMPLE data_lock_waits NULL ref REQUESTING_THREAD_ID REQUESTING_THREAD_ID 18 const,const # 100.00 NULL
588 ############# Explain End #########################################
589 flush status;
590 select COUNT(*)
591 from performance_schema.data_lock_waits force index(REQUESTING_THREAD_ID)
592 where REQUESTING_THREAD_ID = @requesting_thread_id
593 and REQUESTING_EVENT_ID = @requesting_event_id;
594 COUNT(*)
595 #
596 OK: handler_read_key incremented
597 select BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
598 from performance_schema.data_lock_waits
599 where (REQUESTING_THREAD_ID = @requesting_thread_id)
600 and (REQUESTING_EVENT_ID = @requesting_event_id);
601 BLOCKING_ENGINE_LOCK_ID = @blocking_lock_id
602 1
603
604 ====================================================================
605 Testing index for columns BLOCKING_THREAD_ID, BLOCKING_EVENT_ID
606 ====================================================================
607 ############ Explain for Query ####################################
608 explain select COUNT(*)
609 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
610 where BLOCKING_THREAD_ID = "impossible";
611 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
612 1 SIMPLE data_lock_waits NULL ref BLOCKING_THREAD_ID BLOCKING_THREAD_ID 9 const # 100.00 Using where
613 ############ Explain for Query ####################################
614 explain select COUNT(*)
615 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
616 where BLOCKING_THREAD_ID > "impossible";
617 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
618 1 SIMPLE data_lock_waits NULL ALL BLOCKING_THREAD_ID NULL NULL NULL # 33.33 Using where
619 ############ Explain for Query ####################################
620 explain select COUNT(*)
621 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
622 where BLOCKING_THREAD_ID < "2";
623 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
624 1 SIMPLE data_lock_waits NULL ALL BLOCKING_THREAD_ID NULL NULL NULL # 33.33 Using where
625 ############ Explain for Query ####################################
626 explain select COUNT(*)
627 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
628 where BLOCKING_THREAD_ID = @blocking_thread_id;
629 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
630 1 SIMPLE data_lock_waits NULL ref BLOCKING_THREAD_ID BLOCKING_THREAD_ID 9 const # 100.00 NULL
631 ############ Explain for Query ####################################
632 explain select COUNT(*)
633 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
634 where BLOCKING_THREAD_ID = "impossible"
635 and BLOCKING_EVENT_ID = "impossible";
636 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
637 1 SIMPLE data_lock_waits NULL ref BLOCKING_THREAD_ID BLOCKING_THREAD_ID 18 const,const # 100.00 Using where
638 ############ Explain for Query ####################################
639 explain select COUNT(*)
640 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
641 where BLOCKING_THREAD_ID = @blocking_thread_id
642 and BLOCKING_EVENT_ID = "impossible";
643 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
644 1 SIMPLE data_lock_waits NULL ref BLOCKING_THREAD_ID BLOCKING_THREAD_ID 18 const,const # 100.00 Using where
645 ############ Explain for Query ####################################
646 explain select COUNT(*)
647 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
648 where BLOCKING_THREAD_ID = @blocking_thread_id
649 and BLOCKING_EVENT_ID > "impossible";
650 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
651 1 SIMPLE data_lock_waits NULL ref BLOCKING_THREAD_ID BLOCKING_THREAD_ID 9 const # 33.33 Using where
652 ############ Explain for Query ####################################
653 explain select COUNT(*)
654 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
655 where BLOCKING_THREAD_ID = @blocking_thread_id
656 and BLOCKING_EVENT_ID < "2";
657 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
658 1 SIMPLE data_lock_waits NULL ref BLOCKING_THREAD_ID BLOCKING_THREAD_ID 9 const # 33.33 Using where
659 ############ Explain for Query ####################################
660 explain select COUNT(*)
661 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
662 where BLOCKING_THREAD_ID = @blocking_thread_id
663 and BLOCKING_EVENT_ID = @blocking_event_id;
664 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
665 1 SIMPLE data_lock_waits NULL ref BLOCKING_THREAD_ID BLOCKING_THREAD_ID 18 const,const # 100.00 NULL
666 ############# Explain End #########################################
667 flush status;
668 select COUNT(*)
669 from performance_schema.data_lock_waits force index(BLOCKING_THREAD_ID)
670 where BLOCKING_THREAD_ID = @blocking_thread_id
671 and BLOCKING_EVENT_ID = @blocking_event_id;
672 COUNT(*)
673 #
674 OK: handler_read_key incremented
675 select REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
676 from performance_schema.data_lock_waits
677 where (BLOCKING_THREAD_ID = @blocking_thread_id)
678 and (BLOCKING_EVENT_ID = @blocking_event_id);
679 REQUESTING_ENGINE_LOCK_ID = @requesting_lock_id
680 1
681 # Connection con1
682 commit;
683 # Connection con2
684 commit;
685 # Connection con3
686 commit;
687 # Connection con4
688 commit;
689 # Connection default
690 drop table explain_test_db.explain_test_table;
691 drop database explain_test_db;