Debian Codegraph @

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

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