MySQL 8 死锁检测脚本
< 返回列表时间: 2020-05-12来源:OSCHINA
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里?>>> MySQL 8 记录死锁关的几张表有所变化,重新写一个脚本,便于在出现问题的时候快速处置问题。
死锁示意图

死锁相关的表 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
检测结果示意
热门排行