查看 Waiting for table metadata lock 相关信息

mysql 有两个视图记录 metadata_lock  相关 的信息：

performance_schema.metadata_locks

sys.schema_table_lock_waits

但是默认是情况下，即使出现了 Waiting for table metadata lock ，两个视图里面也没有任何的记录 。

如果想在两个视图中看到相关信息，需要启用相关的设置：
 

primary:performance_schema 11:38:34>update setup_consumers set enabled='YES' where name ='global_instrumentation';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

primary:performance_schema 11:40:28>update setup_instruments set enabled='YES' where name='wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

下面我们用一个案例来进行分析：

session 1	session 2
begin; select * from hlb limit 1;
	alter table hlb add cxx int;

 

该视图记录的是被阻塞会话的信息，因此我们需要 kill 9 来rollback 会话 9 。

primary:performance_schema 11:57:32>SELECT * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: bak
                 object_name: hlb
           waiting_thread_id: 31         --等待的线程ID
                 waiting_pid: 6          --等待的连接PID
             waiting_account: root@localhost    
           waiting_lock_type: EXCLUSIVE    --等待的锁类型
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table bak.hlb add cxxx int
          waiting_query_secs: 4           --锁等待时间
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 31
                blocking_pid: 6
            blocking_account: root@localhost  
          blocking_lock_type: SHARED_UPGRADABLE  --等待的锁类型
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 6
sql_kill_blocking_connection: KILL 6
*************************** 2. row ***************************
               object_schema: bak
                 object_name: hlb
           waiting_thread_id: 31
                 waiting_pid: 6
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table bak.hlb add cxxx int
          waiting_query_secs: 4
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 34
                blocking_pid: 9
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
2 rows in set (0.01 sec)

另外MDL 是在mysql server 层的锁，而innodb 层也有表级别上的锁（IS/IX）,但两者并不是一回事

MDL 锁等待超时阈值 由选项 lock_wait_timeout 控制，默认值是一年，我们可以将它调低