介绍
本篇文章为大家展示了如何在MySQL中查看事务和锁情况,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。
查看事务等待状况:
选择 ,,r.trx_id waiting_trx_id, ,,r.trx_mysql_thread_id waiting_thread, ,,r.trx_query waiting_query, ,,b.trx_id blocking_trx_id, ,,b.trx_mysql_thread_id blocking_thread, ,,b.trx_query blocking_query 从 ,,information_schema.innodb_lock_waits w INNER JOIN  information_schema.innodb_trx b 提醒b.trx_id =w.blocking_trx_id INNER JOIN  information_schema.innodb_trx r 提醒r.trx_id =, w.requesting_trx_id;
查看更具体的事务等待状况:
选择 ,,b.trx_state, ,,e.state, ,,e.time, ,,d.state AS  block_state, ,,d.time AS  block_time, ,,a.requesting_trx_id, ,,a.requested_lock_id, ,,b.trx_query, ,,b.trx_mysql_thread_id, ,,a.blocking_trx_id, ,,a.blocking_lock_id, ,,c.trx_query AS  block_trx_query, ,,c.trx_mysql_thread_id AS  block_trx_mysql_tread_id 从 ,,information_schema.INNODB_LOCK_WAITS LEFT JOIN  information_schema.INNODB_TRX b 提醒a.requesting_trx_id =b.trx_id LEFT JOIN  information_schema.INNODB_TRX c 提醒a.blocking_trx_id =c.trx_id LEFT JOIN  information_schema.PROCESSLIST d 提醒c.trx_mysql_thread_id =d.id LEFT JOIN  information_schema.PROCESSLIST e 提醒b.trx_mysql_thread_id =e.id ORDER ,,a.requesting_trx_id;
查看未关闭的事务:
mysql 5.6
选择 ,,a.trx_id, ,,a.trx_state, ,,a.trx_started, ,,a.trx_query, ,,b.ID, ,,b.USER, ,,b.DB, ,,b.COMMAND, ,,b.TIME, ,,b.STATE, ,,b.INFO, ,,c.PROCESSLIST_USER, ,,c.PROCESSLIST_HOST, ,,c.PROCESSLIST_DB, d.SQL_TEXT。才能 从 ,,information_schema.INNODB_TRX LEFT JOIN  information_schema.PROCESSLIST b 提醒a.trx_mysql_thread_id =b.id 以及b.COMMAND =, & # 39;睡眠# 39; LEFT JOIN  PERFORMANCE_SCHEMA.threads c 提醒b.id =c.PROCESSLIST_ID LEFT JOIN  PERFORMANCE_SCHEMA.events_statements_current d 提醒d.THREAD_ID =, c.THREAD_ID;
mysql 5.5
选择 ,,a.trx_id, ,,a.trx_state, ,,a.trx_started, ,,a.trx_query, ,,b.ID, ,,b只用户, ,,b只主机, ,,b.DB, ,,b.COMMAND, ,,b.TIME, ,,b.STATE, b.INFO。才能 从 ,,information_schema.INNODB_TRX LEFT JOIN  information_schema.PROCESSLIST b 提醒a.trx_mysql_thread_id =b.id 在哪里 ,,b.COMMAND =, & # 39;睡眠# 39;;
查看某段时间以来未关闭事务:
选择 ,,,trx_id, ,,,trx_started, ,,trx_mysql_thread_id 从 ,,INFORMATION_SCHEMA.INNODB_TRX 在哪里 ,,,trx_started & lt;, date_sub(现在(),,INTERVAL 1,分钟) 以及trx_operation_state IS  NULL null如何在MySQL中查看事务和锁情况