MySql检测阻塞,锁等待sql

雅冰石

已于 2023-07-05 10:43:52 修改

阅读量8.3k

点赞数 2

分类专栏： 2 MySql 学习 # MySql 锁文章标签： mysql 数据库

于 2015-06-19 16:34:19 首次发布

本文链接：https://blog.csdn.net/yabingshi_tech/article/details/46562985

版权

2 MySql 学习同时被 2 个专栏收录

331 篇文章 5 订阅

订阅专栏

MySql 锁

6 篇文章 0 订阅

订阅专栏

 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 locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_processlist_user,
waiting_processlist_host,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_processlist_user,
blocking_processlist_host,
blocking_age,
SUBSTRING_INDEX(sql_text,"transaction_begin;" ,-1) AS blocking_query,
sql_kill_blocking_connection
FROM 
( 
SELECT 
b.OWNER_THREAD_ID AS granted_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_age,
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_age,
d.PROCESSLIST_INFO AS blocking_query,
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, GROUP_CONCAT( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE t1.granted_thread_id = t2.thread_id;

前面的waiting是等待方，后面的blocking是阻塞方。

注意：

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'

--本篇文章参考了MySQL 元数据锁(MDL)_Leon_Jinhai_Sun的博客-CSDN博客

雅冰石

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MySql检测阻塞,锁等待sql

SELECT p2.`HOST` 被阻塞方host,p2.`USER` 被阻塞方用户,r.trx_id 被阻塞方事务id, r.trx_mysql_thread_id 被阻塞方线程号, TIMESTAMPDIFF( SECOND, r.trx_w
复制链接

扫一扫

专栏目录