大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说mysql死锁排查_java可以检测死锁吗,希望您对编程的造诣更进一步.
死锁示意图
死锁相关的表
information_schema.INNODB_TRX
`performance_schema`.data_lock_waits
`performance_schema`.threads
`performance_schema`.data_locks -- 可不使用代码100分
相关说明
代码100分-- request 被阻塞的
-- block 引起阻塞的
等效字段
`performance_schema`.threads.PROCESSLIST_ID = `information_schema`.innodb_trx.trx_mysql_thread_id = information_schema.`PROCESSLIST`.id
`performance_schema`.threads.thread_id = `performance_schema`.data_lock_waits.REQUESTING_THREAD_ID
检测脚本
SELECT a.*,c.trx_state as block_trx_state,c.trx_started as block_trx_started,c.trx_query as block_trx_query,c.trx_mysql_thread_id as block_trx_mysql_thread_id,d.thread_id as block_thread_id,d.PROCESSLIST_USER as block_user,d.PROCESSLIST_HOST as block_host
from
(SELECT b.trx_id as req_trx_id,b.trx_state as req_trx_state,b.trx_started as req_trx_started,b.trx_query as req_trx_query,b.trx_mysql_thread_id as req_trx_mysql_thread_id,c.thread_id as req_thread_id,c.PROCESSLIST_USER as req_user,c.PROCESSLIST_HOST as req_host
from `performance_schema`.data_lock_waits a
INNER JOIN information_schema.INNODB_TRX b
on a.REQUESTING_ENGINE_TRANSACTION_ID=b.trx_id and a.REQUESTING_ENGINE_LOCK_ID = b.trx_requested_lock_id
INNER JOIN `performance_schema`.threads c
on a.REQUESTING_THREAD_ID = c.THREAD_ID
) a INNER JOIN `performance_schema`.data_lock_waits b
on a.req_trx_id = b.REQUESTING_ENGINE_TRANSACTION_ID and a.req_thread_id = b.REQUESTING_THREAD_ID
INNER JOIN information_schema.INNODB_TRX c
on b.BLOCKING_ENGINE_TRANSACTION_ID = c.TRX_ID
INNER JOIN `performance_schema`.threads d
on b.BLOCKING_THREAD_ID = d.THREAD_ID
检测结果示意
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/8488.html
