# restart UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES'; UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name in ('wait/io/table/sql/handler', 'wait/lock/table/sql/handler', 'wait/lock/metadata/sql/mdl'); set @orig_sql_mode= @@sql_mode; set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); grant ALL on *.* to user1@localhost; grant ALL on *.* to user2@localhost; set sql_mode= @orig_sql_mode; insert into performance_schema.setup_objects (object_type, object_schema, object_name, enabled, timed) values ('TABLE', 'mtr', '%', 'NO', 'NO'); # Switch to (con1, localhost, user1, , ) connect con1, localhost, user1, , ; # Switch to (con2, localhost, user2, , ) connect con2, localhost, user2, , ; connection default; drop function if exists thread_id_name; create function thread_id_name(THREAD_ID int) returns varchar(16) begin if (THREAD_ID IS NULL) then return NULL; end if; if (THREAD_ID = @user1_tid) then return "USER1"; end if; if (THREAD_ID = @user2_tid) then return "USER2"; end if; return "OTHER"; end; // select thread_id_name(NULL); thread_id_name(NULL) NULL select thread_id_name(@user1_tid); thread_id_name(@user1_tid) USER1 select thread_id_name(@user2_tid); thread_id_name(@user2_tid) USER2 prepare dump_metadata_locks from "select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, thread_id_name(OWNER_THREAD_ID) as OWNER_THREAD_ID from performance_schema.metadata_locks order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID;"; prepare dump_table_handles from "select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INTERNAL_LOCK, EXTERNAL_LOCK, thread_id_name(OWNER_THREAD_ID) as OWNER_THREAD_ID from performance_schema.table_handles order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INTERNAL_LOCK, EXTERNAL_LOCK, OWNER_THREAD_ID;"; prepare dump_waits_current from "select thread_id_name(THREAD_ID) as THREAD_ID, EVENT_NAME, TIMER_START is not NULL as TIMER_START_SET, TIMER_END is not NULL as TIMER_END_SET, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, OPERATION from performance_schema.events_waits_current where event_name = \'wait/lock/metadata/sql/mdl\';"; prepare dump_waits_history_long from "select thread_id_name(THREAD_ID) as THREAD_ID, EVENT_NAME, TIMER_START is not NULL as TIMER_START_SET, TIMER_END is not NULL as TIMER_END_SET, OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, OPERATION from performance_schema.events_waits_history_long where event_name = \'wait/lock/metadata/sql/mdl\';"; truncate table performance_schema.events_statements_summary_by_digest; flush status; flush tables; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value'); INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8); TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_waits_history; TRUNCATE TABLE performance_schema.events_waits_current; connection con1; BEGIN; SELECT * from t1 where id=1; id b 1 initial value connection default; "---- Marker 1 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK READ EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; execute dump_waits_history_long; connection con1; COMMIT; connection default; "---- Marker 2 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK READ EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; execute dump_waits_history_long; connection con1; BEGIN; UPDATE t1 set b="new value" where id=2; connection default; "---- Marker 3 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_WRITE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; execute dump_waits_history_long; connection con2; BEGIN; DROP TABLE t1;; connection default; "---- Marker 4 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE SCHEMA OBJECT_SCHEMA test OBJECT_NAME NULL LOCK_TYPE INTENTION_EXCLUSIVE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE EXCLUSIVE LOCK_DURATION TRANSACTION LOCK_STATUS PENDING OWNER_THREAD_ID USER2 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_WRITE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock execute dump_waits_history_long; connection con1; COMMIT; connection con2; connection default; "---- Marker 5 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER execute dump_table_handles; execute dump_waits_current; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock execute dump_waits_history_long; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock connection default; TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_waits_history; TRUNCATE TABLE performance_schema.events_waits_current; create table t1 (a int); connection con1; LOCK TABLE t1 READ; connection default; "---- Marker 6 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK READ NO INSERT EXTERNAL_LOCK READ EXTERNAL OWNER_THREAD_ID USER1 execute dump_waits_current; execute dump_waits_history_long; connection con2; LOCK TABLE t1 write;; connection default; "---- Marker 7 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE SCHEMA OBJECT_SCHEMA test OBJECT_NAME NULL LOCK_TYPE INTENTION_EXCLUSIVE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_NO_READ_WRITE LOCK_DURATION TRANSACTION LOCK_STATUS PENDING OWNER_THREAD_ID USER2 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK READ NO INSERT EXTERNAL_LOCK READ EXTERNAL OWNER_THREAD_ID USER1 execute dump_waits_current; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock execute dump_waits_history_long; connection con1; UNLOCK TABLES; connection con2; connection default; "---- Marker 8 ----" execute dump_metadata_locks; OBJECT_TYPE BACKUP OBJECT_SCHEMA NULL OBJECT_NAME NULL LOCK_TYPE BACKUP_DDL LOCK_DURATION STATEMENT LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE BACKUP OBJECT_SCHEMA NULL OBJECT_NAME NULL LOCK_TYPE BACKUP_DML LOCK_DURATION STATEMENT LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE SCHEMA OBJECT_SCHEMA test OBJECT_NAME NULL LOCK_TYPE INTENTION_EXCLUSIVE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_NO_READ_WRITE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE EXTERNAL_LOCK WRITE EXTERNAL OWNER_THREAD_ID USER2 execute dump_waits_current; execute dump_waits_history_long; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock connection con1; LOCK TABLES t1 read;; connection default; "---- Marker 9 ----" execute dump_metadata_locks; OBJECT_TYPE BACKUP OBJECT_SCHEMA NULL OBJECT_NAME NULL LOCK_TYPE BACKUP_DDL LOCK_DURATION STATEMENT LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE BACKUP OBJECT_SCHEMA NULL OBJECT_NAME NULL LOCK_TYPE BACKUP_DML LOCK_DURATION STATEMENT LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE SCHEMA OBJECT_SCHEMA test OBJECT_NAME NULL LOCK_TYPE INTENTION_EXCLUSIVE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_NO_READ_WRITE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS PENDING OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE EXTERNAL_LOCK WRITE EXTERNAL OWNER_THREAD_ID USER2 execute dump_waits_current; THREAD_ID USER1 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock execute dump_waits_history_long; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock connection con2; UNLOCK TABLES; connection con1; connection default; "---- Marker 10 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK READ NO INSERT EXTERNAL_LOCK READ EXTERNAL OWNER_THREAD_ID USER1 execute dump_waits_current; execute dump_waits_history_long; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock THREAD_ID USER1 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock connection con1; UNLOCK TABLES; connection default; "---- Marker 11 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK READ NO INSERT EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; execute dump_waits_history_long; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock THREAD_ID USER1 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INDEX_NAME NULL OPERATION metadata lock TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_waits_history; TRUNCATE TABLE performance_schema.events_waits_current; connection con1; BEGIN; UPDATE LOW_PRIORITY t1 SET a=8; connection default; "---- Marker 12 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 LOCK_TYPE SHARED_WRITE LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE LOW PRIORITY EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; execute dump_waits_history_long; connection con1; COMMIT; connection default; TRUNCATE TABLE performance_schema.events_waits_history_long; TRUNCATE TABLE performance_schema.events_waits_history; TRUNCATE TABLE performance_schema.events_waits_current; connection con1; SELECT GET_LOCK('test', 0); GET_LOCK('test', 0) 1 connection default; "---- Marker 13 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test LOCK_TYPE SHARED_NO_WRITE LOCK_DURATION EXPLICIT LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE LOW PRIORITY EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; execute dump_waits_history_long; connection con2; SELECT GET_LOCK('test', 120);; connection default; "---- Marker 14 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test LOCK_TYPE SHARED_NO_WRITE LOCK_DURATION EXPLICIT LOCK_STATUS GRANTED OWNER_THREAD_ID USER1 OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test LOCK_TYPE SHARED_NO_WRITE LOCK_DURATION EXPLICIT LOCK_STATUS PENDING OWNER_THREAD_ID USER2 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE LOW PRIORITY EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test INDEX_NAME NULL OPERATION metadata lock execute dump_waits_history_long; connection con1; SELECT RELEASE_LOCK('test'); RELEASE_LOCK('test') 1 connection con2; GET_LOCK('test', 120) 1 connection default; "---- Marker 15 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test LOCK_TYPE SHARED_NO_WRITE LOCK_DURATION EXPLICIT LOCK_STATUS GRANTED OWNER_THREAD_ID USER2 execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE LOW PRIORITY EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test INDEX_NAME NULL OPERATION metadata lock execute dump_waits_history_long; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test INDEX_NAME NULL OPERATION metadata lock connection con2; SELECT RELEASE_LOCK('test'); RELEASE_LOCK('test') 1 connection default; "---- Marker 16 ----" execute dump_metadata_locks; OBJECT_TYPE FUNCTION OBJECT_SCHEMA test OBJECT_NAME thread_id_name LOCK_TYPE SHARED LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER OBJECT_TYPE TABLE OBJECT_SCHEMA performance_schema OBJECT_NAME metadata_locks LOCK_TYPE SHARED_READ LOCK_DURATION TRANSACTION LOCK_STATUS GRANTED OWNER_THREAD_ID OTHER execute dump_table_handles; OBJECT_TYPE TABLE OBJECT_SCHEMA test OBJECT_NAME t1 INTERNAL_LOCK WRITE LOW PRIORITY EXTERNAL_LOCK NULL OWNER_THREAD_ID OTHER execute dump_waits_current; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test INDEX_NAME NULL OPERATION metadata lock execute dump_waits_history_long; THREAD_ID USER2 EVENT_NAME wait/lock/metadata/sql/mdl TIMER_START_SET 1 TIMER_END_SET 1 OBJECT_TYPE USER LEVEL LOCK OBJECT_SCHEMA NULL OBJECT_NAME test INDEX_NAME NULL OPERATION metadata lock disconnect con1; disconnect con2; connection default; drop table t1; UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; revoke all privileges, grant option from user1@localhost; revoke all privileges, grant option from user2@localhost; drop user user1@localhost; drop user user2@localhost; drop function thread_id_name; drop prepare dump_metadata_locks; drop prepare dump_table_handles; drop prepare dump_waits_current; drop prepare dump_waits_history_long; delete from performance_schema.setup_objects where object_schema='mtr'; show global status like "performance_schema%"; Variable_name Value Performance_schema_accounts_lost 0 Performance_schema_cond_classes_lost 0 Performance_schema_cond_instances_lost 0 Performance_schema_digest_lost 0 Performance_schema_file_classes_lost 0 Performance_schema_file_handles_lost 0 Performance_schema_file_instances_lost 0 Performance_schema_hosts_lost 0 Performance_schema_index_stat_lost 0 Performance_schema_locker_lost 0 Performance_schema_memory_classes_lost 0 Performance_schema_metadata_lock_lost 0 Performance_schema_mutex_classes_lost 0 Performance_schema_mutex_instances_lost 0 Performance_schema_nested_statement_lost 0 Performance_schema_prepared_statements_lost 0 Performance_schema_program_lost 0 Performance_schema_rwlock_classes_lost 0 Performance_schema_rwlock_instances_lost 0 Performance_schema_session_connect_attrs_lost 0 Performance_schema_socket_classes_lost 0 Performance_schema_socket_instances_lost 0 Performance_schema_stage_classes_lost 0 Performance_schema_statement_classes_lost 0 Performance_schema_table_handles_lost 0 Performance_schema_table_instances_lost 0 Performance_schema_table_lock_stat_lost 0 Performance_schema_thread_classes_lost 0 Performance_schema_thread_instances_lost 0 Performance_schema_users_lost 0