一、死锁相关的表
- information_schema.INNODB_TRX
- `performance_schema`.data_lock_waits
- `performance_schema`.threads
- `performance_schema`.data_locks
相关说明：
- -- 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
四、结果展示
req_trx_query 是被阻塞的语句，block_trx_query是导致阻塞的语句