一条语句查清Mysql MDL锁阻塞情况

渔夫数据库笔记 2021-04-18

948

版本背景

以下说明的方式适用于Mysql 5.7 及以上版本。对于5.7之前版本，没有具体的方法查看全局读锁，表锁，MDL锁信息。可以通过 information_schema.processlist,performance_schema.events_statements_history, performance_schema.events_statements_current 等表大概推测阻塞情况。

使用前开启如下performance schema监控项

##在5.7版本之前没有专门记录全局读锁信息的表，在5.7中新增performance_schema.metadata_locks视图可以查询全局读锁

update performance_schema.setup_instruments set ENABLED='YES',TIMED='YES' where name like 'statement%';
update performance_schema.setup_instruments set ENABLED='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';
update performance_schema.setup_consumers set ENABLED='YES' where name='events_statements_current' or name='events_statements_history';
update performance_schema.setup_consumers set ENABLED='YES' where name='global_instrumentation';

查询阻塞信息

-- 可以直观的列出阻塞者和被阻塞者及是否存在事务，但是无法列出阻塞者及被阻塞者目前持有的所有 mdl锁信息)
   SELECT
  `g`.`OBJECT_SCHEMA` AS `object_schema`,
  `g`.`OBJECT_NAME` AS `object_name`,
  `pt`.`THREAD_ID` AS `waiting_thread_id`,
  `pt`.`PROCESSLIST_ID` AS `waiting_pid`,
  `sys`.`ps_thread_account` (`p`.`OWNER_THREAD_ID`) AS `waiting_account`,
  `pi`.`trx_started` AS `waiting_trx_started`,
  `pi`.`trx_state` AS `waiting_trx_state`,
  `p`.`LOCK_TYPE` AS `waiting_lock_type`,
  `p`.`LOCK_DURATION` AS `waiting_lock_duration`,
  `pt`.`PROCESSLIST_STATE` AS `waiting_state`,
  `pt`.`PROCESSLIST_TIME` AS `waiting_query_secs`,
  `ps`.`ROWS_AFFECTED` AS `waiting_query_rows_affected`,
  `ps`.`ROWS_EXAMINED` AS `waiting_query_rows_examined`,
  `sys`.`format_statement` (`pt`.`PROCESSLIST_INFO`) AS `waiting_query`,
  (select group_concat(tem_b.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_b where tem_b.thread_id=ps.THREAD_ID) waiting_trx_statements,
  `gt`.`THREAD_ID` AS `blocking_thread_id`,
  `gt`.`PROCESSLIST_ID` AS `blocking_pid`,
  `sys`.`ps_thread_account` (`g`.`OWNER_THREAD_ID`) AS `blocking_account`,
  `gi`.`trx_started` AS `blocking_trx_started`,
  `gi`.`trx_state` AS `blocking_trx_state`,
  `g`.`LOCK_TYPE` AS `blocking_lock_type`,
  `gt`.`PROCESSLIST_STATE` AS `blocking_state`,
  `g`.`LOCK_DURATION` AS `blocking_lock_duration`,
  (select group_concat(tem_b.sql_text order by EVENT_ID SEPARATOR ';\n') from (select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_history his union select thread_id,sql_text,EVENT_ID from performance_schema.events_statements_current cur) tem_b where tem_b.thread_id=gs.THREAD_ID) blocking_trx_statements,
  concat('KILL QUERY ',`gt`.`PROCESSLIST_ID`) AS `sql_kill_blocking_query`,
  concat('KILL ',`gt`.`PROCESSLIST_ID`) AS `sql_kill_blocking_connection`
FROM
  `performance_schema`.`metadata_locks` `g`
JOIN `performance_schema`.`metadata_locks` `p` ON `g`.`OBJECT_TYPE` = `p`.`OBJECT_TYPE`
AND `g`.`OBJECT_SCHEMA` = `p`.`OBJECT_SCHEMA`
AND `g`.`OBJECT_NAME` = `p`.`OBJECT_NAME`
AND `g`.`OWNER_THREAD_ID` != `p`.`OWNER_THREAD_ID`
AND `g`.`LOCK_STATUS` = 'GRANTED'
AND `p`.`LOCK_STATUS` = 'PENDING'
JOIN `performance_schema`.`threads` `gt` ON `g`.`OWNER_THREAD_ID` = `gt`.`THREAD_ID`
JOIN `performance_schema`.`threads` `pt` ON `p`.`OWNER_THREAD_ID` = `pt`.`THREAD_ID`
LEFT JOIN `performance_schema`.`events_statements_current` `gs` ON `g`.`OWNER_THREAD_ID` = `gs`.`THREAD_ID`
LEFT JOIN `performance_schema`.`events_statements_current` `ps` ON `p`.`OWNER_THREAD_ID` = `ps`.`THREAD_ID`
LEFT JOIN `information_schema`.`innodb_trx` `gi` ON `gi`.`trx_mysql_thread_id` = `gt`.`PROCESSLIST_ID`
LEFT JOIN `information_schema`.`innodb_trx` `pi` ON `pi`.`trx_mysql_thread_id` = `pt`.`PROCESSLIST_ID`
WHERE
  `g`.`OBJECT_TYPE` = 'TABLE'\G;
NOTE1:sys.schema_table_lock_waits 视图可以用来查mdl，但是有一些不足 
1)没有`g`.`OWNER_THREAD_ID` != `p`.`OWNER_THREAD_ID`会查出自己阻塞自己的情况；
2)没有关联`information_schema`.`innodb_trx`事务表，不知道事务情况；
3)没有输出线程最近执行的10条语句

阻塞示例

级联阻塞查询示例

-- session 1 执行 count(distinct id)，session 2 添加字段 name_5，session 3 添加字段 name_6，session 4 count(*)查询
*************************** 1. row ***************************
               object_schema: test_shao
                 object_name: t
           waiting_thread_id: 1066526
                 waiting_pid: 1066493
             waiting_account: root@127.0.0.1
           waiting_lock_type: SHARED_UPGRADABLE
       waiting_lock_duration: TRANSACTION
          waiting_query_secs: 3
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
               waiting_query: alter table t add column name_6 varchar(20)
      waiting_trx_statements: select USER();
SELECT DATABASE();
show tables;
desc t;
alter table t add column name_5 varchar(20);
select connection_id();
alter table t add column name_5 varchar(20);
alter table t add column name_5 varchar(20);
alter table t add column name_5 varchar(20);
alter table t add column name_6 varchar(20)
          blocking_thread_id: 1066522
                blocking_pid: 1066489
            blocking_account: root@127.0.0.1
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     blocking_trx_statements: selelct connection_id();
select connection_id();
select count(*) from t;
select connection_id();
select sys.ps_thread_id(connection_id());
select sys.ps_thread_id(connection_id());
select count(*) from t;
select count(*) from t;
select count(distinct id) from t
     sql_kill_blocking_query: KILL QUERY 1066489
sql_kill_blocking_connection: KILL 1066489
*************************** 2. row ***************************
               object_schema: test_shao
                 object_name: t
           waiting_thread_id: 1066526
                 waiting_pid: 1066493
             waiting_account: root@127.0.0.1
           waiting_lock_type: SHARED_UPGRADABLE
       waiting_lock_duration: TRANSACTION
          waiting_query_secs: 3
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
               waiting_query: alter table t add column name_6 varchar(20)
      waiting_trx_statements: select USER();
SELECT DATABASE();
show tables;
desc t;
alter table t add column name_5 varchar(20);
select connection_id();
alter table t add column name_5 varchar(20);
alter table t add column name_5 varchar(20);
alter table t add column name_5 varchar(20);
alter table t add column name_6 varchar(20)
          blocking_thread_id: 1066851
                blocking_pid: 1066818
            blocking_account: root@127.0.0.1
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     blocking_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
alter table t add column name_6 varchar(20);
alter table t add column name_5 varchar(20)
     sql_kill_blocking_query: KILL QUERY 1066818
sql_kill_blocking_connection: KILL 1066818
*************************** 3. row ***************************
               object_schema: test_shao
                 object_name: t
           waiting_thread_id: 1066851
                 waiting_pid: 1066818
             waiting_account: root@127.0.0.1
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
          waiting_query_secs: 3
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
               waiting_query: alter table t add column name_5 varchar(20)
      waiting_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
alter table t add column name_6 varchar(20);
alter table t add column name_5 varchar(20)
          blocking_thread_id: 1066522
                blocking_pid: 1066489
            blocking_account: root@127.0.0.1
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     blocking_trx_statements: selelct connection_id();
select connection_id();
select count(*) from t;
select connection_id();
select sys.ps_thread_id(connection_id());
select sys.ps_thread_id(connection_id());
select count(*) from t;
select count(*) from t;
select count(distinct id) from t
     sql_kill_blocking_query: KILL QUERY 1066489
sql_kill_blocking_connection: KILL 1066489
*************************** 4. row ***************************
               object_schema: test_shao
                 object_name: t
           waiting_thread_id: 1066852
                 waiting_pid: 1066819
             waiting_account: root@127.0.0.1
           waiting_lock_type: SHARED_READ
       waiting_lock_duration: TRANSACTION
          waiting_query_secs: 2
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
               waiting_query: select count(*) from t
      waiting_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
select count(*) from t;
desc t;
select count(*) from t
          blocking_thread_id: 1066522
                blocking_pid: 1066489
            blocking_account: root@127.0.0.1
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     blocking_trx_statements: selelct connection_id();
select connection_id();
select count(*) from t;
select connection_id();
select sys.ps_thread_id(connection_id());
select sys.ps_thread_id(connection_id());
select count(*) from t;
select count(*) from t;
select count(distinct id) from t
     sql_kill_blocking_query: KILL QUERY 1066489
sql_kill_blocking_connection: KILL 1066489
*************************** 5. row ***************************
               object_schema: test_shao
                 object_name: t
           waiting_thread_id: 1066852
                 waiting_pid: 1066819
             waiting_account: root@127.0.0.1
           waiting_lock_type: SHARED_READ
       waiting_lock_duration: TRANSACTION
          waiting_query_secs: 2
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
               waiting_query: select count(*) from t
      waiting_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
select count(*) from t;
desc t;
select count(*) from t
          blocking_thread_id: 1066851
                blocking_pid: 1066818
            blocking_account: root@127.0.0.1
          blocking_lock_type: SHARED_UPGRADABLE
      blocking_lock_duration: TRANSACTION
     blocking_trx_statements: select @@version_comment limit 1;
select USER();
SELECT DATABASE();
alter table t add column name_6 varchar(20);
alter table t add column name_5 varchar(20)
     sql_kill_blocking_query: KILL QUERY 1066818
sql_kill_blocking_connection: KILL 1066818
5 rows in set (0.00 sec)
NOTE:在出现级联阻塞的时候，我们要注意找到阻塞的源头(blocking_pid 出现频率最高的会话)

扫描二维码

获取更多精彩

渔夫数据库笔记

mysql

文章转载自渔夫数据库笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

一条语句查清Mysql MDL锁阻塞情况

版本背景

使用前开启如下performance schema监控项

查询阻塞信息

阻塞示例

评论