知行合一，止于至善
千里之行，始于足下；合抱之木，生于毫末；九层之台，起于累土查询事务间锁等待情况(trx_lock_wait)
11月 29, 2021 |
Nix.Huang
不适合mysql8， 请使用从mysql7开始就支持的 select * from sys.innodb_lock_waits
相关表解释
from information_schema.INNODB_LOCK_WAITS 事务对锁等待
information_schema.innodb_trx 事务列表(trx_id, trx_mysql_thread_id=processlist_id)
information_schema.processlist 进程列表processlist_id
performance_schema.threads processlist_id 和thread_id映射
performance_schema.events_statements_history 每个thread_id执行的最后10条sql
具体sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
select w.requesting_trx_id, r.trx_mysql_thread_id as r_thread_id, case when r.trx_query is not null then r.trx_query else ( -- 从events_statements_history查询被阻塞线程执行的最后一条sql select sql_text from performance_schema.events_statements_history where (thread_id, event_id) in ( select thread_id, max(event_id) from performance_schema.events_statements_history where thread_id in ( select thread_id from performance_schema.threads where processlist_id = r.trx_mysql_thread_id ) group by thread_id ) ) end as r_query, w.blocking_trx_id, b.trx_mysql_thread_id as b_thread_id, case when b.trx_query is not null then b.trx_query else ( -- 从events_statements_history查询阻塞线程执行的最后一条sql select sql_text from performance_schema.events_statements_history where (thread_id, event_id) in ( select thread_id, max(event_id) from performance_schema.events_statements_history where thread_id in ( select thread_id from performance_schema.threads where processlist_id = b.trx_mysql_thread_id ) group by thread_id ) ) end as b_query from information_schema.INNODB_LOCK_WAITS w, information_schema.innodb_trx r, information_schema.innodb_trx b where w.requesting_trx_id = r.trx_id and w.blocking_trx_id = b.trx_id; |
Posted in: database
Comments are closed.