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权限的账户使用，以确保管理员能够随时连接和检查系统（假设您没有把此权限给予所有的用户）。

Status	含义
Checking table	正在检查数据表（这是自动的）。
Closing tables	正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out	复制从服务器正在连接主服务器。
Copying to tmp table on disk	由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table	正在创建临时表以存放部分查询结果。
deleting from main table	服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables	服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables	正在执行FLUSH TABLES，show innodbstatus\G;，MySQL 5.5使用上面命令即可查看innodb引擎的运行时信息。 6 show variables like ‘%timeout%'; 查看服务器配置参数。以上为个人经验，希望能给大家一个参考，也希望大家多多支持社区。如有错误或未考虑完全的地方，望不吝赐教。