Debian Codegraph @

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

raw
1 # Prevent background threads to acquire mdl locks,
2 # which makes the result unstable
3 SET GLOBAL innodb_fast_shutdown=0;
4 # restart: --debug=d,do_not_meta_lock_in_background
5 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
6 UPDATE performance_schema.setup_instruments SET enabled = 'YES'
7 WHERE name in ('wait/io/table/sql/handler',
8 'wait/lock/table/sql/handler',
9 'wait/lock/metadata/sql/mdl');
10 create user user1@localhost, user2@localhost;
11 grant ALL on *.* to user1@localhost;
12 grant ALL on *.* to user2@localhost;
13 insert into performance_schema.setup_objects (object_type, object_schema, object_name, enabled, timed)
14 values ('TABLE', 'mtr', '%', 'NO', 'NO');
15 # Switch to (con1, localhost, user1, , )
16 # Switch to (con2, localhost, user2, , )
17 drop function if exists thread_id_name;
18 create function thread_id_name(THREAD_ID int)
19 returns varchar(16)
20 begin
21 if (THREAD_ID IS NULL)
22 then
23 return NULL;
24 end if;
25 if (THREAD_ID = @user1_tid)
26 then
27 return "USER1";
28 end if;
29 if (THREAD_ID = @user2_tid)
30 then
31 return "USER2";
32 end if;
33 return "OTHER";
34 end;
35 //
36 select thread_id_name(NULL);
37 thread_id_name(NULL)
38 NULL
39 select thread_id_name(@user1_tid);
40 thread_id_name(@user1_tid)
41 USER1
42 select thread_id_name(@user2_tid);
43 thread_id_name(@user2_tid)
44 USER2
45 prepare dump_metadata_locks from
46 "select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COLUMN_NAME,
47 LOCK_TYPE, LOCK_DURATION, LOCK_STATUS,
48 thread_id_name(OWNER_THREAD_ID) as OWNER_THREAD_ID
49 from performance_schema.metadata_locks
50 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
51 LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID;";
52 prepare dump_table_handles from
53 "select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
54 INTERNAL_LOCK, EXTERNAL_LOCK,
55 thread_id_name(OWNER_THREAD_ID) as OWNER_THREAD_ID
56 from performance_schema.table_handles
57 order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
58 INTERNAL_LOCK, EXTERNAL_LOCK, OWNER_THREAD_ID;";
59 prepare dump_waits_current from
60 "select thread_id_name(THREAD_ID) as THREAD_ID,
61 EVENT_NAME,
62 TIMER_START is not NULL as TIMER_START_SET,
63 TIMER_END is not NULL as TIMER_END_SET,
64 OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
65 OPERATION
66 from performance_schema.events_waits_current
67 where event_name = \'wait/lock/metadata/sql/mdl\';";
68 prepare dump_waits_history_long from
69 "select thread_id_name(THREAD_ID) as THREAD_ID,
70 EVENT_NAME,
71 TIMER_START is not NULL as TIMER_START_SET,
72 TIMER_END is not NULL as TIMER_END_SET,
73 OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
74 OPERATION
75 from performance_schema.events_waits_history_long
76 where event_name = \'wait/lock/metadata/sql/mdl\';";
77 truncate table performance_schema.events_statements_summary_by_digest;
78 flush status;
79 flush tables;
80 DROP TABLE IF EXISTS t1;
81 CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value');
82 INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
83 TRUNCATE TABLE performance_schema.events_waits_history_long;
84 TRUNCATE TABLE performance_schema.events_waits_history;
85 TRUNCATE TABLE performance_schema.events_waits_current;
86 BEGIN;
87 SELECT * from t1 where id=1;
88 id b
89 1 initial value
90 "---- Marker 1 ----"
91 execute dump_metadata_locks;
92 OBJECT_TYPE FUNCTION
93 OBJECT_SCHEMA test
94 OBJECT_NAME thread_id_name
95 COLUMN_NAME NULL
96 LOCK_TYPE SHARED
97 LOCK_DURATION TRANSACTION
98 LOCK_STATUS GRANTED
99 OWNER_THREAD_ID OTHER
100 OBJECT_TYPE TABLE
101 OBJECT_SCHEMA performance_schema
102 OBJECT_NAME metadata_locks
103 COLUMN_NAME NULL
104 LOCK_TYPE SHARED_READ
105 LOCK_DURATION TRANSACTION
106 LOCK_STATUS GRANTED
107 OWNER_THREAD_ID OTHER
108 OBJECT_TYPE TABLE
109 OBJECT_SCHEMA test
110 OBJECT_NAME t1
111 COLUMN_NAME NULL
112 LOCK_TYPE SHARED_READ
113 LOCK_DURATION TRANSACTION
114 LOCK_STATUS GRANTED
115 OWNER_THREAD_ID USER1
116 execute dump_table_handles;
117 OBJECT_TYPE TABLE
118 OBJECT_SCHEMA test
119 OBJECT_NAME t1
120 INTERNAL_LOCK NULL
121 EXTERNAL_LOCK NULL
122 OWNER_THREAD_ID NULL
123 OBJECT_TYPE TABLE
124 OBJECT_SCHEMA test
125 OBJECT_NAME t1
126 INTERNAL_LOCK NULL
127 EXTERNAL_LOCK NULL
128 OWNER_THREAD_ID NULL
129 execute dump_waits_current;
130 execute dump_waits_history_long;
131 COMMIT;
132 "---- Marker 2 ----"
133 execute dump_metadata_locks;
134 OBJECT_TYPE FUNCTION
135 OBJECT_SCHEMA test
136 OBJECT_NAME thread_id_name
137 COLUMN_NAME NULL
138 LOCK_TYPE SHARED
139 LOCK_DURATION TRANSACTION
140 LOCK_STATUS GRANTED
141 OWNER_THREAD_ID OTHER
142 OBJECT_TYPE TABLE
143 OBJECT_SCHEMA performance_schema
144 OBJECT_NAME metadata_locks
145 COLUMN_NAME NULL
146 LOCK_TYPE SHARED_READ
147 LOCK_DURATION TRANSACTION
148 LOCK_STATUS GRANTED
149 OWNER_THREAD_ID OTHER
150 execute dump_table_handles;
151 OBJECT_TYPE TABLE
152 OBJECT_SCHEMA test
153 OBJECT_NAME t1
154 INTERNAL_LOCK NULL
155 EXTERNAL_LOCK NULL
156 OWNER_THREAD_ID NULL
157 OBJECT_TYPE TABLE
158 OBJECT_SCHEMA test
159 OBJECT_NAME t1
160 INTERNAL_LOCK NULL
161 EXTERNAL_LOCK NULL
162 OWNER_THREAD_ID NULL
163 execute dump_waits_current;
164 execute dump_waits_history_long;
165 BEGIN;
166 UPDATE t1 set b="new value" where id=2;
167 "---- Marker 3 ----"
168 execute dump_metadata_locks;
169 OBJECT_TYPE FUNCTION
170 OBJECT_SCHEMA test
171 OBJECT_NAME thread_id_name
172 COLUMN_NAME NULL
173 LOCK_TYPE SHARED
174 LOCK_DURATION TRANSACTION
175 LOCK_STATUS GRANTED
176 OWNER_THREAD_ID OTHER
177 OBJECT_TYPE TABLE
178 OBJECT_SCHEMA performance_schema
179 OBJECT_NAME metadata_locks
180 COLUMN_NAME NULL
181 LOCK_TYPE SHARED_READ
182 LOCK_DURATION TRANSACTION
183 LOCK_STATUS GRANTED
184 OWNER_THREAD_ID OTHER
185 OBJECT_TYPE TABLE
186 OBJECT_SCHEMA test
187 OBJECT_NAME t1
188 COLUMN_NAME NULL
189 LOCK_TYPE SHARED_WRITE
190 LOCK_DURATION TRANSACTION
191 LOCK_STATUS GRANTED
192 OWNER_THREAD_ID USER1
193 execute dump_table_handles;
194 OBJECT_TYPE TABLE
195 OBJECT_SCHEMA test
196 OBJECT_NAME t1
197 INTERNAL_LOCK NULL
198 EXTERNAL_LOCK NULL
199 OWNER_THREAD_ID NULL
200 OBJECT_TYPE TABLE
201 OBJECT_SCHEMA test
202 OBJECT_NAME t1
203 INTERNAL_LOCK NULL
204 EXTERNAL_LOCK NULL
205 OWNER_THREAD_ID NULL
206 execute dump_waits_current;
207 execute dump_waits_history_long;
208 BEGIN;
209 DROP TABLE t1;;
210 "---- Marker 4 ----"
211 execute dump_metadata_locks;
212 OBJECT_TYPE BACKUP LOCK
213 OBJECT_SCHEMA NULL
214 OBJECT_NAME NULL
215 COLUMN_NAME NULL
216 LOCK_TYPE INTENTION_EXCLUSIVE
217 LOCK_DURATION TRANSACTION
218 LOCK_STATUS GRANTED
219 OWNER_THREAD_ID USER2
220 OBJECT_TYPE FUNCTION
221 OBJECT_SCHEMA test
222 OBJECT_NAME thread_id_name
223 COLUMN_NAME NULL
224 LOCK_TYPE SHARED
225 LOCK_DURATION TRANSACTION
226 LOCK_STATUS GRANTED
227 OWNER_THREAD_ID OTHER
228 OBJECT_TYPE GLOBAL
229 OBJECT_SCHEMA NULL
230 OBJECT_NAME NULL
231 COLUMN_NAME NULL
232 LOCK_TYPE INTENTION_EXCLUSIVE
233 LOCK_DURATION STATEMENT
234 LOCK_STATUS GRANTED
235 OWNER_THREAD_ID USER2
236 OBJECT_TYPE SCHEMA
237 OBJECT_SCHEMA test
238 OBJECT_NAME NULL
239 COLUMN_NAME NULL
240 LOCK_TYPE INTENTION_EXCLUSIVE
241 LOCK_DURATION TRANSACTION
242 LOCK_STATUS GRANTED
243 OWNER_THREAD_ID USER2
244 OBJECT_TYPE TABLE
245 OBJECT_SCHEMA performance_schema
246 OBJECT_NAME metadata_locks
247 COLUMN_NAME NULL
248 LOCK_TYPE SHARED_READ
249 LOCK_DURATION TRANSACTION
250 LOCK_STATUS GRANTED
251 OWNER_THREAD_ID OTHER
252 OBJECT_TYPE TABLE
253 OBJECT_SCHEMA test
254 OBJECT_NAME t1
255 COLUMN_NAME NULL
256 LOCK_TYPE EXCLUSIVE
257 LOCK_DURATION TRANSACTION
258 LOCK_STATUS PENDING
259 OWNER_THREAD_ID USER2
260 OBJECT_TYPE TABLE
261 OBJECT_SCHEMA test
262 OBJECT_NAME t1
263 COLUMN_NAME NULL
264 LOCK_TYPE SHARED_WRITE
265 LOCK_DURATION TRANSACTION
266 LOCK_STATUS GRANTED
267 OWNER_THREAD_ID USER1
268 execute dump_table_handles;
269 OBJECT_TYPE TABLE
270 OBJECT_SCHEMA test
271 OBJECT_NAME t1
272 INTERNAL_LOCK NULL
273 EXTERNAL_LOCK NULL
274 OWNER_THREAD_ID NULL
275 OBJECT_TYPE TABLE
276 OBJECT_SCHEMA test
277 OBJECT_NAME t1
278 INTERNAL_LOCK NULL
279 EXTERNAL_LOCK NULL
280 OWNER_THREAD_ID NULL
281 execute dump_waits_current;
282 THREAD_ID USER2
283 EVENT_NAME wait/lock/metadata/sql/mdl
284 TIMER_START_SET 1
285 TIMER_END_SET 1
286 OBJECT_TYPE TABLE
287 OBJECT_SCHEMA test
288 OBJECT_NAME t1
289 INDEX_NAME NULL
290 OPERATION metadata lock
291 execute dump_waits_history_long;
292 COMMIT;
293 "---- Marker 5 ----"
294 execute dump_metadata_locks;
295 OBJECT_TYPE FUNCTION
296 OBJECT_SCHEMA test
297 OBJECT_NAME thread_id_name
298 COLUMN_NAME NULL
299 LOCK_TYPE SHARED
300 LOCK_DURATION TRANSACTION
301 LOCK_STATUS GRANTED
302 OWNER_THREAD_ID OTHER
303 OBJECT_TYPE TABLE
304 OBJECT_SCHEMA performance_schema
305 OBJECT_NAME metadata_locks
306 COLUMN_NAME NULL
307 LOCK_TYPE SHARED_READ
308 LOCK_DURATION TRANSACTION
309 LOCK_STATUS GRANTED
310 OWNER_THREAD_ID OTHER
311 execute dump_table_handles;
312 execute dump_waits_current;
313 THREAD_ID USER2
314 EVENT_NAME wait/lock/metadata/sql/mdl
315 TIMER_START_SET 1
316 TIMER_END_SET 1
317 OBJECT_TYPE TABLE
318 OBJECT_SCHEMA test
319 OBJECT_NAME t1
320 INDEX_NAME NULL
321 OPERATION metadata lock
322 execute dump_waits_history_long;
323 THREAD_ID USER2
324 EVENT_NAME wait/lock/metadata/sql/mdl
325 TIMER_START_SET 1
326 TIMER_END_SET 1
327 OBJECT_TYPE TABLE
328 OBJECT_SCHEMA test
329 OBJECT_NAME t1
330 INDEX_NAME NULL
331 OPERATION metadata lock
332 TRUNCATE TABLE performance_schema.events_waits_history_long;
333 TRUNCATE TABLE performance_schema.events_waits_history;
334 TRUNCATE TABLE performance_schema.events_waits_current;
335 create table t1 (a int);
336 LOCK TABLE t1 READ;
337 "---- Marker 6 ----"
338 execute dump_metadata_locks;
339 OBJECT_TYPE COLUMN STATISTICS
340 OBJECT_SCHEMA test
341 OBJECT_NAME t1
342 COLUMN_NAME a
343 LOCK_TYPE SHARED_READ
344 LOCK_DURATION STATEMENT
345 LOCK_STATUS GRANTED
346 OWNER_THREAD_ID USER1
347 OBJECT_TYPE FUNCTION
348 OBJECT_SCHEMA test
349 OBJECT_NAME thread_id_name
350 COLUMN_NAME NULL
351 LOCK_TYPE SHARED
352 LOCK_DURATION TRANSACTION
353 LOCK_STATUS GRANTED
354 OWNER_THREAD_ID OTHER
355 OBJECT_TYPE SCHEMA
356 OBJECT_SCHEMA test
357 OBJECT_NAME NULL
358 COLUMN_NAME NULL
359 LOCK_TYPE INTENTION_EXCLUSIVE
360 LOCK_DURATION TRANSACTION
361 LOCK_STATUS GRANTED
362 OWNER_THREAD_ID USER1
363 OBJECT_TYPE TABLE
364 OBJECT_SCHEMA performance_schema
365 OBJECT_NAME metadata_locks
366 COLUMN_NAME NULL
367 LOCK_TYPE SHARED_READ
368 LOCK_DURATION TRANSACTION
369 LOCK_STATUS GRANTED
370 OWNER_THREAD_ID OTHER
371 OBJECT_TYPE TABLE
372 OBJECT_SCHEMA test
373 OBJECT_NAME t1
374 COLUMN_NAME NULL
375 LOCK_TYPE SHARED_READ_ONLY
376 LOCK_DURATION TRANSACTION
377 LOCK_STATUS GRANTED
378 OWNER_THREAD_ID USER1
379 execute dump_table_handles;
380 OBJECT_TYPE TABLE
381 OBJECT_SCHEMA test
382 OBJECT_NAME t1
383 INTERNAL_LOCK NULL
384 EXTERNAL_LOCK READ EXTERNAL
385 OWNER_THREAD_ID USER1
386 execute dump_waits_current;
387 execute dump_waits_history_long;
388 LOCK TABLE t1 write;;
389 "---- Marker 7 ----"
390 execute dump_metadata_locks;
391 OBJECT_TYPE COLUMN STATISTICS
392 OBJECT_SCHEMA test
393 OBJECT_NAME t1
394 COLUMN_NAME a
395 LOCK_TYPE SHARED_READ
396 LOCK_DURATION STATEMENT
397 LOCK_STATUS GRANTED
398 OWNER_THREAD_ID USER1
399 OBJECT_TYPE FUNCTION
400 OBJECT_SCHEMA test
401 OBJECT_NAME thread_id_name
402 COLUMN_NAME NULL
403 LOCK_TYPE SHARED
404 LOCK_DURATION TRANSACTION
405 LOCK_STATUS GRANTED
406 OWNER_THREAD_ID OTHER
407 OBJECT_TYPE GLOBAL
408 OBJECT_SCHEMA NULL
409 OBJECT_NAME NULL
410 COLUMN_NAME NULL
411 LOCK_TYPE INTENTION_EXCLUSIVE
412 LOCK_DURATION STATEMENT
413 LOCK_STATUS GRANTED
414 OWNER_THREAD_ID USER2
415 OBJECT_TYPE SCHEMA
416 OBJECT_SCHEMA test
417 OBJECT_NAME NULL
418 COLUMN_NAME NULL
419 LOCK_TYPE INTENTION_EXCLUSIVE
420 LOCK_DURATION TRANSACTION
421 LOCK_STATUS GRANTED
422 OWNER_THREAD_ID USER1
423 OBJECT_TYPE SCHEMA
424 OBJECT_SCHEMA test
425 OBJECT_NAME NULL
426 COLUMN_NAME NULL
427 LOCK_TYPE INTENTION_EXCLUSIVE
428 LOCK_DURATION TRANSACTION
429 LOCK_STATUS GRANTED
430 OWNER_THREAD_ID USER2
431 OBJECT_TYPE TABLE
432 OBJECT_SCHEMA performance_schema
433 OBJECT_NAME metadata_locks
434 COLUMN_NAME NULL
435 LOCK_TYPE SHARED_READ
436 LOCK_DURATION TRANSACTION
437 LOCK_STATUS GRANTED
438 OWNER_THREAD_ID OTHER
439 OBJECT_TYPE TABLE
440 OBJECT_SCHEMA test
441 OBJECT_NAME t1
442 COLUMN_NAME NULL
443 LOCK_TYPE SHARED_NO_READ_WRITE
444 LOCK_DURATION TRANSACTION
445 LOCK_STATUS PENDING
446 OWNER_THREAD_ID USER2
447 OBJECT_TYPE TABLE
448 OBJECT_SCHEMA test
449 OBJECT_NAME t1
450 COLUMN_NAME NULL
451 LOCK_TYPE SHARED_READ_ONLY
452 LOCK_DURATION TRANSACTION
453 LOCK_STATUS GRANTED
454 OWNER_THREAD_ID USER1
455 execute dump_table_handles;
456 OBJECT_TYPE TABLE
457 OBJECT_SCHEMA test
458 OBJECT_NAME t1
459 INTERNAL_LOCK NULL
460 EXTERNAL_LOCK READ EXTERNAL
461 OWNER_THREAD_ID USER1
462 execute dump_waits_current;
463 THREAD_ID USER2
464 EVENT_NAME wait/lock/metadata/sql/mdl
465 TIMER_START_SET 1
466 TIMER_END_SET 1
467 OBJECT_TYPE TABLE
468 OBJECT_SCHEMA test
469 OBJECT_NAME t1
470 INDEX_NAME NULL
471 OPERATION metadata lock
472 execute dump_waits_history_long;
473 UNLOCK TABLES;
474 "---- Marker 8 ----"
475 execute dump_metadata_locks;
476 OBJECT_TYPE FUNCTION
477 OBJECT_SCHEMA test
478 OBJECT_NAME thread_id_name
479 COLUMN_NAME NULL
480 LOCK_TYPE SHARED
481 LOCK_DURATION TRANSACTION
482 LOCK_STATUS GRANTED
483 OWNER_THREAD_ID OTHER
484 OBJECT_TYPE GLOBAL
485 OBJECT_SCHEMA NULL
486 OBJECT_NAME NULL
487 COLUMN_NAME NULL
488 LOCK_TYPE INTENTION_EXCLUSIVE
489 LOCK_DURATION STATEMENT
490 LOCK_STATUS GRANTED
491 OWNER_THREAD_ID USER2
492 OBJECT_TYPE SCHEMA
493 OBJECT_SCHEMA test
494 OBJECT_NAME NULL
495 COLUMN_NAME NULL
496 LOCK_TYPE INTENTION_EXCLUSIVE
497 LOCK_DURATION TRANSACTION
498 LOCK_STATUS GRANTED
499 OWNER_THREAD_ID USER2
500 OBJECT_TYPE TABLE
501 OBJECT_SCHEMA performance_schema
502 OBJECT_NAME metadata_locks
503 COLUMN_NAME NULL
504 LOCK_TYPE SHARED_READ
505 LOCK_DURATION TRANSACTION
506 LOCK_STATUS GRANTED
507 OWNER_THREAD_ID OTHER
508 OBJECT_TYPE TABLE
509 OBJECT_SCHEMA test
510 OBJECT_NAME t1
511 COLUMN_NAME NULL
512 LOCK_TYPE SHARED_NO_READ_WRITE
513 LOCK_DURATION TRANSACTION
514 LOCK_STATUS GRANTED
515 OWNER_THREAD_ID USER2
516 OBJECT_TYPE TABLESPACE
517 OBJECT_SCHEMA NULL
518 OBJECT_NAME test/t1
519 COLUMN_NAME NULL
520 LOCK_TYPE INTENTION_EXCLUSIVE
521 LOCK_DURATION TRANSACTION
522 LOCK_STATUS GRANTED
523 OWNER_THREAD_ID USER2
524 execute dump_table_handles;
525 OBJECT_TYPE TABLE
526 OBJECT_SCHEMA test
527 OBJECT_NAME t1
528 INTERNAL_LOCK NULL
529 EXTERNAL_LOCK NULL
530 OWNER_THREAD_ID NULL
531 OBJECT_TYPE TABLE
532 OBJECT_SCHEMA test
533 OBJECT_NAME t1
534 INTERNAL_LOCK NULL
535 EXTERNAL_LOCK WRITE EXTERNAL
536 OWNER_THREAD_ID USER2
537 execute dump_waits_current;
538 execute dump_waits_history_long;
539 THREAD_ID USER2
540 EVENT_NAME wait/lock/metadata/sql/mdl
541 TIMER_START_SET 1
542 TIMER_END_SET 1
543 OBJECT_TYPE TABLE
544 OBJECT_SCHEMA test
545 OBJECT_NAME t1
546 INDEX_NAME NULL
547 OPERATION metadata lock
548 LOCK TABLES t1 read;;
549 "---- Marker 9 ----"
550 execute dump_metadata_locks;
551 OBJECT_TYPE FUNCTION
552 OBJECT_SCHEMA test
553 OBJECT_NAME thread_id_name
554 COLUMN_NAME NULL
555 LOCK_TYPE SHARED
556 LOCK_DURATION TRANSACTION
557 LOCK_STATUS GRANTED
558 OWNER_THREAD_ID OTHER
559 OBJECT_TYPE GLOBAL
560 OBJECT_SCHEMA NULL
561 OBJECT_NAME NULL
562 COLUMN_NAME NULL
563 LOCK_TYPE INTENTION_EXCLUSIVE
564 LOCK_DURATION STATEMENT
565 LOCK_STATUS GRANTED
566 OWNER_THREAD_ID USER2
567 OBJECT_TYPE SCHEMA
568 OBJECT_SCHEMA test
569 OBJECT_NAME NULL
570 COLUMN_NAME NULL
571 LOCK_TYPE INTENTION_EXCLUSIVE
572 LOCK_DURATION TRANSACTION
573 LOCK_STATUS GRANTED
574 OWNER_THREAD_ID USER2
575 OBJECT_TYPE TABLE
576 OBJECT_SCHEMA performance_schema
577 OBJECT_NAME metadata_locks
578 COLUMN_NAME NULL
579 LOCK_TYPE SHARED_READ
580 LOCK_DURATION TRANSACTION
581 LOCK_STATUS GRANTED
582 OWNER_THREAD_ID OTHER
583 OBJECT_TYPE TABLE
584 OBJECT_SCHEMA test
585 OBJECT_NAME t1
586 COLUMN_NAME NULL
587 LOCK_TYPE SHARED_NO_READ_WRITE
588 LOCK_DURATION TRANSACTION
589 LOCK_STATUS GRANTED
590 OWNER_THREAD_ID USER2
591 OBJECT_TYPE TABLE
592 OBJECT_SCHEMA test
593 OBJECT_NAME t1
594 COLUMN_NAME NULL
595 LOCK_TYPE SHARED_READ_ONLY
596 LOCK_DURATION TRANSACTION
597 LOCK_STATUS PENDING
598 OWNER_THREAD_ID USER1
599 OBJECT_TYPE TABLESPACE
600 OBJECT_SCHEMA NULL
601 OBJECT_NAME test/t1
602 COLUMN_NAME NULL
603 LOCK_TYPE INTENTION_EXCLUSIVE
604 LOCK_DURATION TRANSACTION
605 LOCK_STATUS GRANTED
606 OWNER_THREAD_ID USER2
607 execute dump_table_handles;
608 OBJECT_TYPE TABLE
609 OBJECT_SCHEMA test
610 OBJECT_NAME t1
611 INTERNAL_LOCK NULL
612 EXTERNAL_LOCK NULL
613 OWNER_THREAD_ID NULL
614 OBJECT_TYPE TABLE
615 OBJECT_SCHEMA test
616 OBJECT_NAME t1
617 INTERNAL_LOCK NULL
618 EXTERNAL_LOCK WRITE EXTERNAL
619 OWNER_THREAD_ID USER2
620 execute dump_waits_current;
621 THREAD_ID USER1
622 EVENT_NAME wait/lock/metadata/sql/mdl
623 TIMER_START_SET 1
624 TIMER_END_SET 1
625 OBJECT_TYPE TABLE
626 OBJECT_SCHEMA test
627 OBJECT_NAME t1
628 INDEX_NAME NULL
629 OPERATION metadata lock
630 execute dump_waits_history_long;
631 THREAD_ID USER2
632 EVENT_NAME wait/lock/metadata/sql/mdl
633 TIMER_START_SET 1
634 TIMER_END_SET 1
635 OBJECT_TYPE TABLE
636 OBJECT_SCHEMA test
637 OBJECT_NAME t1
638 INDEX_NAME NULL
639 OPERATION metadata lock
640 UNLOCK TABLES;
641 "---- Marker 10 ----"
642 execute dump_metadata_locks;
643 OBJECT_TYPE FUNCTION
644 OBJECT_SCHEMA test
645 OBJECT_NAME thread_id_name
646 COLUMN_NAME NULL
647 LOCK_TYPE SHARED
648 LOCK_DURATION TRANSACTION
649 LOCK_STATUS GRANTED
650 OWNER_THREAD_ID OTHER
651 OBJECT_TYPE TABLE
652 OBJECT_SCHEMA performance_schema
653 OBJECT_NAME metadata_locks
654 COLUMN_NAME NULL
655 LOCK_TYPE SHARED_READ
656 LOCK_DURATION TRANSACTION
657 LOCK_STATUS GRANTED
658 OWNER_THREAD_ID OTHER
659 OBJECT_TYPE TABLE
660 OBJECT_SCHEMA test
661 OBJECT_NAME t1
662 COLUMN_NAME NULL
663 LOCK_TYPE SHARED_READ_ONLY
664 LOCK_DURATION TRANSACTION
665 LOCK_STATUS GRANTED
666 OWNER_THREAD_ID USER1
667 execute dump_table_handles;
668 OBJECT_TYPE TABLE
669 OBJECT_SCHEMA test
670 OBJECT_NAME t1
671 INTERNAL_LOCK NULL
672 EXTERNAL_LOCK NULL
673 OWNER_THREAD_ID NULL
674 OBJECT_TYPE TABLE
675 OBJECT_SCHEMA test
676 OBJECT_NAME t1
677 INTERNAL_LOCK NULL
678 EXTERNAL_LOCK READ EXTERNAL
679 OWNER_THREAD_ID USER1
680 execute dump_waits_current;
681 execute dump_waits_history_long;
682 THREAD_ID USER2
683 EVENT_NAME wait/lock/metadata/sql/mdl
684 TIMER_START_SET 1
685 TIMER_END_SET 1
686 OBJECT_TYPE TABLE
687 OBJECT_SCHEMA test
688 OBJECT_NAME t1
689 INDEX_NAME NULL
690 OPERATION metadata lock
691 THREAD_ID USER1
692 EVENT_NAME wait/lock/metadata/sql/mdl
693 TIMER_START_SET 1
694 TIMER_END_SET 1
695 OBJECT_TYPE TABLE
696 OBJECT_SCHEMA test
697 OBJECT_NAME t1
698 INDEX_NAME NULL
699 OPERATION metadata lock
700 UNLOCK TABLES;
701 "---- Marker 11 ----"
702 execute dump_metadata_locks;
703 OBJECT_TYPE FUNCTION
704 OBJECT_SCHEMA test
705 OBJECT_NAME thread_id_name
706 COLUMN_NAME NULL
707 LOCK_TYPE SHARED
708 LOCK_DURATION TRANSACTION
709 LOCK_STATUS GRANTED
710 OWNER_THREAD_ID OTHER
711 OBJECT_TYPE TABLE
712 OBJECT_SCHEMA performance_schema
713 OBJECT_NAME metadata_locks
714 COLUMN_NAME NULL
715 LOCK_TYPE SHARED_READ
716 LOCK_DURATION TRANSACTION
717 LOCK_STATUS GRANTED
718 OWNER_THREAD_ID OTHER
719 execute dump_table_handles;
720 OBJECT_TYPE TABLE
721 OBJECT_SCHEMA test
722 OBJECT_NAME t1
723 INTERNAL_LOCK NULL
724 EXTERNAL_LOCK NULL
725 OWNER_THREAD_ID NULL
726 OBJECT_TYPE TABLE
727 OBJECT_SCHEMA test
728 OBJECT_NAME t1
729 INTERNAL_LOCK NULL
730 EXTERNAL_LOCK NULL
731 OWNER_THREAD_ID NULL
732 execute dump_waits_current;
733 execute dump_waits_history_long;
734 THREAD_ID USER2
735 EVENT_NAME wait/lock/metadata/sql/mdl
736 TIMER_START_SET 1
737 TIMER_END_SET 1
738 OBJECT_TYPE TABLE
739 OBJECT_SCHEMA test
740 OBJECT_NAME t1
741 INDEX_NAME NULL
742 OPERATION metadata lock
743 THREAD_ID USER1
744 EVENT_NAME wait/lock/metadata/sql/mdl
745 TIMER_START_SET 1
746 TIMER_END_SET 1
747 OBJECT_TYPE TABLE
748 OBJECT_SCHEMA test
749 OBJECT_NAME t1
750 INDEX_NAME NULL
751 OPERATION metadata lock
752 TRUNCATE TABLE performance_schema.events_waits_history_long;
753 TRUNCATE TABLE performance_schema.events_waits_history;
754 TRUNCATE TABLE performance_schema.events_waits_current;
755 BEGIN;
756 UPDATE LOW_PRIORITY t1 SET a=8;
757 "---- Marker 12 ----"
758 execute dump_metadata_locks;
759 OBJECT_TYPE FUNCTION
760 OBJECT_SCHEMA test
761 OBJECT_NAME thread_id_name
762 COLUMN_NAME NULL
763 LOCK_TYPE SHARED
764 LOCK_DURATION TRANSACTION
765 LOCK_STATUS GRANTED
766 OWNER_THREAD_ID OTHER
767 OBJECT_TYPE TABLE
768 OBJECT_SCHEMA performance_schema
769 OBJECT_NAME metadata_locks
770 COLUMN_NAME NULL
771 LOCK_TYPE SHARED_READ
772 LOCK_DURATION TRANSACTION
773 LOCK_STATUS GRANTED
774 OWNER_THREAD_ID OTHER
775 OBJECT_TYPE TABLE
776 OBJECT_SCHEMA test
777 OBJECT_NAME t1
778 COLUMN_NAME NULL
779 LOCK_TYPE SHARED_WRITE_LOW_PRIO
780 LOCK_DURATION TRANSACTION
781 LOCK_STATUS GRANTED
782 OWNER_THREAD_ID USER1
783 execute dump_table_handles;
784 OBJECT_TYPE TABLE
785 OBJECT_SCHEMA test
786 OBJECT_NAME t1
787 INTERNAL_LOCK NULL
788 EXTERNAL_LOCK NULL
789 OWNER_THREAD_ID NULL
790 OBJECT_TYPE TABLE
791 OBJECT_SCHEMA test
792 OBJECT_NAME t1
793 INTERNAL_LOCK NULL
794 EXTERNAL_LOCK NULL
795 OWNER_THREAD_ID NULL
796 execute dump_waits_current;
797 execute dump_waits_history_long;
798 COMMIT;
799 TRUNCATE TABLE performance_schema.events_waits_history_long;
800 TRUNCATE TABLE performance_schema.events_waits_history;
801 TRUNCATE TABLE performance_schema.events_waits_current;
802 SELECT GET_LOCK('test', 0);
803 GET_LOCK('test', 0)
804 1
805 "---- Marker 13 ----"
806 execute dump_metadata_locks;
807 OBJECT_TYPE FUNCTION
808 OBJECT_SCHEMA test
809 OBJECT_NAME thread_id_name
810 COLUMN_NAME NULL
811 LOCK_TYPE SHARED
812 LOCK_DURATION TRANSACTION
813 LOCK_STATUS GRANTED
814 OWNER_THREAD_ID OTHER
815 OBJECT_TYPE TABLE
816 OBJECT_SCHEMA performance_schema
817 OBJECT_NAME metadata_locks
818 COLUMN_NAME NULL
819 LOCK_TYPE SHARED_READ
820 LOCK_DURATION TRANSACTION
821 LOCK_STATUS GRANTED
822 OWNER_THREAD_ID OTHER
823 OBJECT_TYPE USER LEVEL LOCK
824 OBJECT_SCHEMA NULL
825 OBJECT_NAME test
826 COLUMN_NAME NULL
827 LOCK_TYPE EXCLUSIVE
828 LOCK_DURATION EXPLICIT
829 LOCK_STATUS GRANTED
830 OWNER_THREAD_ID USER1
831 execute dump_table_handles;
832 OBJECT_TYPE TABLE
833 OBJECT_SCHEMA test
834 OBJECT_NAME t1
835 INTERNAL_LOCK NULL
836 EXTERNAL_LOCK NULL
837 OWNER_THREAD_ID NULL
838 OBJECT_TYPE TABLE
839 OBJECT_SCHEMA test
840 OBJECT_NAME t1
841 INTERNAL_LOCK NULL
842 EXTERNAL_LOCK NULL
843 OWNER_THREAD_ID NULL
844 execute dump_waits_current;
845 execute dump_waits_history_long;
846 SELECT GET_LOCK('test', 120);;
847 "---- Marker 14 ----"
848 execute dump_metadata_locks;
849 OBJECT_TYPE FUNCTION
850 OBJECT_SCHEMA test
851 OBJECT_NAME thread_id_name
852 COLUMN_NAME NULL
853 LOCK_TYPE SHARED
854 LOCK_DURATION TRANSACTION
855 LOCK_STATUS GRANTED
856 OWNER_THREAD_ID OTHER
857 OBJECT_TYPE TABLE
858 OBJECT_SCHEMA performance_schema
859 OBJECT_NAME metadata_locks
860 COLUMN_NAME NULL
861 LOCK_TYPE SHARED_READ
862 LOCK_DURATION TRANSACTION
863 LOCK_STATUS GRANTED
864 OWNER_THREAD_ID OTHER
865 OBJECT_TYPE USER LEVEL LOCK
866 OBJECT_SCHEMA NULL
867 OBJECT_NAME test
868 COLUMN_NAME NULL
869 LOCK_TYPE EXCLUSIVE
870 LOCK_DURATION EXPLICIT
871 LOCK_STATUS GRANTED
872 OWNER_THREAD_ID USER1
873 OBJECT_TYPE USER LEVEL LOCK
874 OBJECT_SCHEMA NULL
875 OBJECT_NAME test
876 COLUMN_NAME NULL
877 LOCK_TYPE EXCLUSIVE
878 LOCK_DURATION EXPLICIT
879 LOCK_STATUS PENDING
880 OWNER_THREAD_ID USER2
881 execute dump_table_handles;
882 OBJECT_TYPE TABLE
883 OBJECT_SCHEMA test
884 OBJECT_NAME t1
885 INTERNAL_LOCK NULL
886 EXTERNAL_LOCK NULL
887 OWNER_THREAD_ID NULL
888 OBJECT_TYPE TABLE
889 OBJECT_SCHEMA test
890 OBJECT_NAME t1
891 INTERNAL_LOCK NULL
892 EXTERNAL_LOCK NULL
893 OWNER_THREAD_ID NULL
894 execute dump_waits_current;
895 THREAD_ID USER2
896 EVENT_NAME wait/lock/metadata/sql/mdl
897 TIMER_START_SET 1
898 TIMER_END_SET 1
899 OBJECT_TYPE USER LEVEL LOCK
900 OBJECT_SCHEMA NULL
901 OBJECT_NAME test
902 INDEX_NAME NULL
903 OPERATION metadata lock
904 execute dump_waits_history_long;
905 SELECT RELEASE_LOCK('test');
906 RELEASE_LOCK('test')
907 1
908 GET_LOCK('test', 120)
909 1
910 "---- Marker 15 ----"
911 execute dump_metadata_locks;
912 OBJECT_TYPE FUNCTION
913 OBJECT_SCHEMA test
914 OBJECT_NAME thread_id_name
915 COLUMN_NAME NULL
916 LOCK_TYPE SHARED
917 LOCK_DURATION TRANSACTION
918 LOCK_STATUS GRANTED
919 OWNER_THREAD_ID OTHER
920 OBJECT_TYPE TABLE
921 OBJECT_SCHEMA performance_schema
922 OBJECT_NAME metadata_locks
923 COLUMN_NAME NULL
924 LOCK_TYPE SHARED_READ
925 LOCK_DURATION TRANSACTION
926 LOCK_STATUS GRANTED
927 OWNER_THREAD_ID OTHER
928 OBJECT_TYPE USER LEVEL LOCK
929 OBJECT_SCHEMA NULL
930 OBJECT_NAME test
931 COLUMN_NAME NULL
932 LOCK_TYPE EXCLUSIVE
933 LOCK_DURATION EXPLICIT
934 LOCK_STATUS GRANTED
935 OWNER_THREAD_ID USER2
936 execute dump_table_handles;
937 OBJECT_TYPE TABLE
938 OBJECT_SCHEMA test
939 OBJECT_NAME t1
940 INTERNAL_LOCK NULL
941 EXTERNAL_LOCK NULL
942 OWNER_THREAD_ID NULL
943 OBJECT_TYPE TABLE
944 OBJECT_SCHEMA test
945 OBJECT_NAME t1
946 INTERNAL_LOCK NULL
947 EXTERNAL_LOCK NULL
948 OWNER_THREAD_ID NULL
949 execute dump_waits_current;
950 THREAD_ID USER2
951 EVENT_NAME wait/lock/metadata/sql/mdl
952 TIMER_START_SET 1
953 TIMER_END_SET 1
954 OBJECT_TYPE USER LEVEL LOCK
955 OBJECT_SCHEMA NULL
956 OBJECT_NAME test
957 INDEX_NAME NULL
958 OPERATION metadata lock
959 execute dump_waits_history_long;
960 THREAD_ID USER2
961 EVENT_NAME wait/lock/metadata/sql/mdl
962 TIMER_START_SET 1
963 TIMER_END_SET 1
964 OBJECT_TYPE USER LEVEL LOCK
965 OBJECT_SCHEMA NULL
966 OBJECT_NAME test
967 INDEX_NAME NULL
968 OPERATION metadata lock
969 SELECT RELEASE_LOCK('test');
970 RELEASE_LOCK('test')
971 1
972 "---- Marker 16 ----"
973 execute dump_metadata_locks;
974 OBJECT_TYPE FUNCTION
975 OBJECT_SCHEMA test
976 OBJECT_NAME thread_id_name
977 COLUMN_NAME NULL
978 LOCK_TYPE SHARED
979 LOCK_DURATION TRANSACTION
980 LOCK_STATUS GRANTED
981 OWNER_THREAD_ID OTHER
982 OBJECT_TYPE TABLE
983 OBJECT_SCHEMA performance_schema
984 OBJECT_NAME metadata_locks
985 COLUMN_NAME NULL
986 LOCK_TYPE SHARED_READ
987 LOCK_DURATION TRANSACTION
988 LOCK_STATUS GRANTED
989 OWNER_THREAD_ID OTHER
990 execute dump_table_handles;
991 OBJECT_TYPE TABLE
992 OBJECT_SCHEMA test
993 OBJECT_NAME t1
994 INTERNAL_LOCK NULL
995 EXTERNAL_LOCK NULL
996 OWNER_THREAD_ID NULL
997 OBJECT_TYPE TABLE
998 OBJECT_SCHEMA test
999 OBJECT_NAME t1
1000 INTERNAL_LOCK NULL
1001 EXTERNAL_LOCK NULL
1002 OWNER_THREAD_ID NULL
1003 execute dump_waits_current;
1004 THREAD_ID USER2
1005 EVENT_NAME wait/lock/metadata/sql/mdl
1006 TIMER_START_SET 1
1007 TIMER_END_SET 1
1008 OBJECT_TYPE USER LEVEL LOCK
1009 OBJECT_SCHEMA NULL
1010 OBJECT_NAME test
1011 INDEX_NAME NULL
1012 OPERATION metadata lock
1013 execute dump_waits_history_long;
1014 THREAD_ID USER2
1015 EVENT_NAME wait/lock/metadata/sql/mdl
1016 TIMER_START_SET 1
1017 TIMER_END_SET 1
1018 OBJECT_TYPE USER LEVEL LOCK
1019 OBJECT_SCHEMA NULL
1020 OBJECT_NAME test
1021 INDEX_NAME NULL
1022 OPERATION metadata lock
1023 CREATE TABLE t30065213 (a int);
1024 LOCK TABLE t30065213 WRITE;
1025 SELECT GET_LOCK("ULL-FOO", 0);
1026 GET_LOCK("ULL-FOO", 0)
1027 1
1028 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1029 FROM performance_schema.metadata_locks
1030 WHERE OBJECT_TYPE = "TABLE"
1031 AND OBJECT_SCHEMA = "test"
1032 AND OBJECT_NAME = "t30065213";
1033 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1034 TABLE test t30065213 SHARED_NO_READ_WRITE TRANSACTION GRANTED
1035 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1036 FROM performance_schema.metadata_locks
1037 WHERE OBJECT_TYPE = "USER LEVEL LOCK";
1038 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1039 USER LEVEL LOCK NULL ull-foo EXCLUSIVE EXPLICIT GRANTED
1040 RENAME TABLE t30065213 TO t30065213b;
1041 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1042 FROM performance_schema.metadata_locks
1043 WHERE OBJECT_TYPE = "TABLE"
1044 AND OBJECT_SCHEMA = "test"
1045 AND OBJECT_NAME = "t30065213b";
1046 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1047 TABLE test t30065213b SHARED_NO_READ_WRITE EXPLICIT GRANTED
1048 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
1049 FROM performance_schema.metadata_locks
1050 WHERE OBJECT_TYPE = "USER LEVEL LOCK";
1051 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
1052 USER LEVEL LOCK NULL ull-foo EXCLUSIVE EXPLICIT GRANTED
1053 UNLOCK TABLES;
1054 SELECT RELEASE_LOCK("ULL-FOO");
1055 RELEASE_LOCK("ULL-FOO")
1056 1
1057 DROP TABLE t30065213b;
1058 drop table t1;
1059 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
1060 revoke all privileges, grant option from user1@localhost;
1061 revoke all privileges, grant option from user2@localhost;
1062 drop user user1@localhost;
1063 drop user user2@localhost;
1064 drop function thread_id_name;
1065 drop prepare dump_metadata_locks;
1066 drop prepare dump_table_handles;
1067 drop prepare dump_waits_current;
1068 drop prepare dump_waits_history_long;
1069 delete from performance_schema.setup_objects
1070 where object_schema='mtr';
1071 show global status like "performance_schema%_lost";
1072 Variable_name Value
1073 Performance_schema_accounts_lost 0
1074 Performance_schema_cond_classes_lost 0
1075 Performance_schema_cond_instances_lost 0
1076 Performance_schema_digest_lost 0
1077 Performance_schema_file_classes_lost 0
1078 Performance_schema_file_handles_lost 0
1079 Performance_schema_file_instances_lost 0
1080 Performance_schema_hosts_lost 0
1081 Performance_schema_index_stat_lost 0
1082 Performance_schema_locker_lost 0
1083 Performance_schema_memory_classes_lost 0
1084 Performance_schema_metadata_lock_lost 0
1085 Performance_schema_mutex_classes_lost 0
1086 Performance_schema_mutex_instances_lost 0
1087 Performance_schema_nested_statement_lost 0
1088 Performance_schema_prepared_statements_lost 0
1089 Performance_schema_program_lost 0
1090 Performance_schema_rwlock_classes_lost 0
1091 Performance_schema_rwlock_instances_lost 0
1092 Performance_schema_session_connect_attrs_lost 0
1093 Performance_schema_socket_classes_lost 0
1094 Performance_schema_socket_instances_lost 0
1095 Performance_schema_stage_classes_lost 0
1096 Performance_schema_statement_classes_lost 0
1097 Performance_schema_table_handles_lost 0
1098 Performance_schema_table_instances_lost 0
1099 Performance_schema_table_lock_stat_lost 0
1100 Performance_schema_thread_classes_lost 0
1101 Performance_schema_thread_instances_lost 0
1102 Performance_schema_users_lost 0
1103 # restart