本文提供了一种SQL查询方法来检测MySQL中的事务阻塞情况，包括阻塞双方的详细信息如主机名、用户名、事务ID等，并介绍了如何针对元数据锁（MDL）进行检查。
SELECT
p2.`HOST` Blockedhost,
p2.`USER` BlockedUser,
r.trx_id BlockedTrxId,
r.trx_mysql_thread_id BlockedThreadId,
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) WaitTime,
r.trx_query BlockedQuery,
l.lock_table BlockedTable,
m.`lock_mode` BlockedLockMode,
m.`lock_type` BlockedLockType,
m.`lock_index` BlockedLockIndex,
m.`lock_space` BlockedLockSpace,
m.lock_page BlockedLockPage,
m.lock_rec BlockedLockRec,
m.lock_data BlockedLockData,
p.`HOST` blocking_host,
p.`USER` blocking_user,
b.trx_id BlockingTrxid,
b.trx_mysql_thread_id BlockingThreadId,
b.trx_query BlockingQuery,
l.`lock_mode` BlockingLockMode,
l.`lock_type` BlockingLockType,
l.`lock_index` BlockingLockIndex,
l.`lock_space` BlockingLockSpace,
l.lock_page BlockingLockPage,
l.lock_rec BlockingLockRec,
l.lock_data BlockingLockData,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx
FROM
information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY
WaitTime DESC \G;#对应的中文：
SELECT
p2.`HOST` 被阻塞方host,
p2.`USER` 被阻塞方用户,
r.trx_id 被阻塞方事务id,
r.trx_mysql_thread_id 被阻塞方线程号,
TIMESTAMPDIFF(
SECOND,
r.trx_wait_started,
CURRENT_TIMESTAMP
) 等待时间,
r.trx_query 被阻塞的查询,
l.lock_table 阻塞方锁住的表,
m.`lock_mode` 被阻塞方的锁模式,
m.`lock_type` "被阻塞方的锁类型(表锁还是行锁)",
m.`lock_index` 被阻塞方锁住的索引,
m.`lock_space` 被阻塞方锁对象的space_id,
m.lock_page 被阻塞方事务锁定页的数量,
m.lock_rec 被阻塞方事务锁定行的数量,
m.lock_data 被阻塞方事务锁定记录的主键值,
p.`HOST` 阻塞方主机,
p.`USER` 阻塞方用户,
b.trx_id 阻塞方事务id,
b.trx_mysql_thread_id 阻塞方线程号,
b.trx_query 阻塞方查询,
l.`lock_mode` 阻塞方的锁模式,
l.`lock_type` "阻塞方的锁类型(表锁还是行锁)",
l.`lock_index` 阻塞方锁住的索引,
l.`lock_space` 阻塞方锁对象的space_id,
l.lock_page 阻塞方事务锁定页的数量,
l.lock_rec 阻塞方事务锁定行的数量,
l.lock_data 阻塞方事务锁定记录的主键值,
IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' 秒'), 0) 阻塞方事务空闲的时间
FROM
information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY
等待时间 DESC \G;示例：
我在一个会话窗口中关闭自动提交选项,然后更新
set autocommit=0;
UPDATE eip.`t_offer_instance` SET description='dandan';#由于该表数据量比较大，所以更新时间比较长。
此时，打开一个新的会话窗口，执行一个update语句：
UPDATE eip.`t_offer_instance` SET description='jiao' WHERE id = 431;
发现，执行第二个update卡住了。因为被阻塞了。
执行检测阻塞sql，查询结果：
*************************** 1. row ***************************
被阻塞方host: 10.192.203.9:61768
被阻塞方用户: root
被阻塞方事务id: 2837
被阻塞方线程号: 191
等待时间: 12
被阻塞的查询: update eip.`t_offer_instance` set description='jiao' where id = 431
阻塞方锁住的表: `eip`.`t_offer_instance`
被阻塞方的锁模式: X
被阻塞方的锁类型(表锁还是行锁): RECORD
被阻塞方锁住的索引: `PRIMARY`
被阻塞方锁对象的space_id: 62
被阻塞方事务锁定页的数量: 7
被阻塞方事务锁定行的数量: 2
被阻塞方事务锁定记录的主键值: 431
阻塞方主机: 10.192.203.9:60872
阻塞方用户: root
阻塞方事务id: 2834
阻塞方线程号: 71
阻塞方查询: update eip.`t_offer_instance` set description='dandan'
阻塞方的锁模式: X
阻塞方的锁类型(表锁还是行锁): RECORD
阻塞方锁住的索引: `PRIMARY`
阻塞方锁对象的space_id: 62
阻塞方事务锁定页的数量: 7
阻塞方事务锁定行的数量: 2
阻塞方事务锁定记录的主键值: 431
阻塞方事务空闲的时间: 0
需要注意的是：
会话1对表进行DML（包括query）事务操作完成后，但是没有commit/rollback，这个时候show processlist是看到的只是会话处于sleep状态，执行的SQL(info)显示为空。同理，在innodb_trx查询该事务的trx_query也为空。因此查看不到阻塞方正在执行哪些查询。
示例：
我在一个会话窗口中关闭自动提交选项,然后更新
set autocommit=0;
UPDATE eip.`t_offer_instance` SET description='dandan' WHERE id = 431;
此时，打开一个新的会话窗口，执行同样的update语句：
UPDATE eip.`t_offer_instance` SET description='jiao' WHERE id = 431;
发现，执行第二个update卡住了。因为被阻塞了。
执行检测阻塞sql，查询结果，这时阻塞方查询结果为NULL：
*************************** 1. row ***************************
被阻塞方host: 10.192.203.9:54401
被阻塞方用户: root
被阻塞方事务id: 2844
被阻塞方线程号: 312
等待时间: 6
被阻塞的查询: update eip.`t_offer_instance` set description='jiao' where id = 431
阻塞方锁住的表: `eip`.`t_offer_instance`
被阻塞方的锁模式: X
被阻塞方的锁类型(表锁还是行锁): RECORD
被阻塞方锁住的索引: `PRIMARY`
被阻塞方锁对象的space_id: 62
被阻塞方事务锁定页的数量: 7
被阻塞方事务锁定行的数量: 116
被阻塞方事务锁定记录的主键值: 431
阻塞方主机: 10.192.203.9:54392
阻塞方用户: root
阻塞方事务id: 2841
阻塞方线程号: 310
阻塞方查询: NULL
阻塞方的锁模式: X
阻塞方的锁类型(表锁还是行锁): RECORD
阻塞方锁住的索引: `PRIMARY`
阻塞方锁对象的space_id: 62
阻塞方事务锁定页的数量: 7
阻塞方事务锁定行的数量: 116
阻塞方事务锁定记录的主键值: 431
阻塞方事务空闲的时间: 19 秒
#如果是metadata lock，用该sql查不出来，这时可以这样查询，查看谁导致谁产生了元数据锁
select *
from
(
SELECT locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_processlist_user,
waiting_processlist_host,
waiting_thread_id,
waiting_time,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_processlist_user,
blocking_processlist_host,
granted_thread_id as blocking_thread_id,
blocking_time,
sql_text AS blocking_query,
blocking_command,
blocking_state,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_ID AS granted_thread_id,
a.OWNER_THREAD_ID as waiting_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_user AS waiting_processlist_user,
c.PROCESSLIST_host AS waiting_processlist_host,
c.PROCESSLIST_TIME AS waiting_time,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_user AS blocking_processlist_user,
d.PROCESSLIST_host AS blocking_processlist_host,
d.PROCESSLIST_TIME AS blocking_time,
d.PROCESSLIST_INFO AS blocking_query,
d.PROCESSLIST_COMMAND as blocking_command,
d.PROCESSLIST_STATE as blocking_state,
CONCAT('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
#AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT thread_id, sql_text
FROM
performance_schema.events_statements_history
) t2
WHERE t1.granted_thread_id = t2.thread_id
) t3
where t3.blocking_query like concat('%',locked_table,'%');
前面的waiting是等待方，后面的blocking是阻塞方。
如果blocking_query为空，则可能这个sql已经在历史会话里不存在了，需要酌情调整performance_schema_events_statements_history_size变量的大小
/*
注意：
metadata_locks是5.7中被引入，记录了metadata lock的相关信息，包括持有对象、类型、状态等信息。但5.7默认设置是关闭的（8.0默认打开），需要通过下面命令打开设置：
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait/lock/metadata/sql/mdl';
如果要永久生效，需要在配置文件中加入如下内容：
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
*/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
