MySQL 查看事务和锁情况的常用语句分享

一些查看数据库中事务和锁情况的常用语句


查看事务等待状况：

SELECT

   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

FROM

   information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
查看更具体的事务等待状况：

SELECT

   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

FROM

   information_schema.INNODB_LOCK_WAITS a

LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id

LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id

LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id

LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id

ORDER BY

   a.requesting_trx_id;
查看未关闭的事务：

–MySQL 5.6

SELECT

   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

FROM

   information_schema.INNODB_TRX a

LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id

AND b.COMMAND = ‘Sleep’

LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID

LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;–MySQL 5.5

SELECT

   a.trx_id,

   a.trx_state,

   a.trx_started,

   a.trx_query,

   b.ID,

   b. USER,

   b. HOST,

   b.DB,

   b.COMMAND,

   b.TIME,

   b.STATE,

   b.INFO

FROM

   information_schema.INNODB_TRX a

LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id

WHERE

   b.COMMAND = ‘Sleep’;
查看某段时间以来未关闭事务：

SELECT

    trx_id,

    trx_started,

    trx_mysql_thread_id

FROM

    INFORMATION_SCHEMA.INNODB_TRX

WHERE

    trx_started < date_sub(now(), INTERVAL 1 MINUTE)

AND trx_operation_state IS NULL

AND trx_query IS NULL;
补充：MySQL锁定状态查看命令


1 show processlist;

SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。如果有线程在update或者insert 某个表，此时进程的status为updating 或者 sending data。

如果您得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。MySQL保留一个额外的连接，让拥有SUPER权限的账户使用，以确保管理员能够随时连接和检查系统（假设您没有把此权限给予所有的用户）。

大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。


2 show full processlist;

show processlist;只列出前100条，如果想全列出请使用show full processlist;


3 show open tables;

这条命令能够查看当前有那些表是打开的。In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁，这一般发生在Drop或Rename命令操作这张表时。所以这条命令不能帮助解答我们常见的问题：当前某张表是否有死锁，谁拥有表上的这个锁等。

show open tables from database;

4 show status like ‘%lock%’

查看服务器状态。

5 show engine innodb status\G;

MySQL 5.1之前的命令是：show innodbstatus\G;，MySQL 5.5使用上面命令即可查看innodb引擎的运行时信息。

6 show variables like ‘%timeout%’;

查看服务器配置参数。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持共生网络。如有错误或未考虑完全的地方，望不吝赐教。