mysql排查事务及进程的语句

查询事务

SELECT * FROM information_schema.INNODB_TRX;

查询正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查询等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查询进程

show PROCESSLIST;

查询是否锁表

show OPEN TABLES where In_use > 0;

查看session的表

select * from sys.session;

查看当前执行的线程

select * from performance_schema.threads;

查看执行完成，但未commit的sql语句

select * from performance_schema.events_statements_current;

查看慢查询sql语句

   select * from mysql.slow_log;

查看慢查询的设置

  show variable like 'slow%'

查看加锁的状态

show engine innodb status;

查看/修改mysql事务隔离级别

select @@global.tx_isolation,@@tx_isolation;
set global transaction isolation level read committed; //全局的
set session transaction isolation level read committed; //当前会话

查看当前阻塞的事务及对应的线程

select * from information_schema.innodb_trx i,
              performance_schema.events_statements_current c,
              information_schema.processlist b,
              performance_schema.threads t
        where t.thread_id = c.thread_id
          and i.trx_mysql_thread_id = b.id
          and t.processList_id = b.id;

一、问题描述

我们经常会碰到这样的情况，某个事务执行完了未提交，后续再来一个DDL和DML操作，导致后面的session要么处于waiting for metadata lock，要么是锁等待超时。这时我们往往只能找到这个未提交的事务的事务id和session id，但是一般都处于sleep状态，不好分析事务内容到底是什么，所以通常都是粗鲁地kill这个session后解决问题，但是应用层的研发人员往往找不到到底是哪个事务引起的，后面再出现问题时还要重复kill。那这个情况下，怎么办呢？

下面我先模拟两种情况

这里我特意在开启session后执行一条update，又执行了一条insert语句。

这时session2一直卡住

我们再开一个窗口session3。

mysql> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------+

| Id | User | Host      | db  | Command | Time | State                          | Info                                              |

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------+

|  4 | root | localhost | test | Sleep  |  841 |                                | NULL                                              |

|  5 | root | localhost | test | Query  |  709 | Waiting for table metadata lock | alter table test_lock add column name2 varchar(50) |

|  6 | root | localhost | NULL | Query  |    0 | starting                        | show processlist                                  |

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------+

可看到ddl操作也被卡住了，之前的事务1也处于sleep状态，无法得知它到底执行了什么。

这时我们查询innodb_trx表可看到事务1也看不到sql信息。

二、解决方案

方案一

我想到的第一种方法是利用performance_schema中的相关信息查询

mysql> show variables like 'performance_schema';

+--------------------+-------+

|Variable_name      | Value |

+--------------------+-------+

|performance_schema  | ON    |

+--------------------+-------+

1 row in set(0.00 sec)

通过查看events_statements_current表可看到每一个session正在执行的sql，哪怕它依旧执行完成了，只是没有提交。这里可看到事务1最后执行的正是insert into test_lock values(4,'andy');

mysql> select * from performance_schema.events_statements_current\G

不过方案1有个缺陷，一个事务可能有一组sql组成，这个方法只能看到这个事务最后执行的是什么SQL，无法看到全部。也就是说，关于information_schema.processlist和events_statements_current如何一一对应起来，可以通过performance_schema.threads表来关联，语句如下：

mysql> select * from performance_schema.events_statements_current where THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=4)\G

如果你是MySQL 5.7版本，可以通过查看sys.session视图和sys.processlist视图得到最后一次执行的SQL语句。

方案二

然后我想到了是不是可以用general_log的方式，一般情况下general_log不大可能打开，所以我们先打开general_log等着问题复现的方式来定位，经测试，即使事务没有提交，一样会写到general_log。

mysql> show variables like '%general%';

+------------------+-------------------------------------------+

| Variable_name    | Value                                     |

+------------------+-------------------------------------------+

| general_log      | OFF                                       |

| general_log_file | /data/mysql/3306/data/qs-ops-db-01.log |

+------------------+-------------------------------------------+

2 rows in set (0.00 sec)

 

mysql> set global general_log=1;

Query OK, 0 rowsaffected (0.00 sec)

开启general日志后，只要知道了未提交事务的进程号就可以完美找到对应的SQL语句了。

$ cat /data/mysql/3306/data/qs-ops-db-01.log | grep 4

mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306  Unix socket: /data/mysql/3306/mysql.sock

Time                 Id Command    Argument

2017-03-29T07:22:00.949233Z 4 Query begin

2017-03-29T07:22:11.090712Z 4 Query update test_lock set id=123 where id=1

2017-03-29T07:22:18.347311Z 4 Query insert into test_lock values(4,'andy')

这样只要后续能否复现的话，就能找到所有的SQL了，就是如果此会话是长连接，那么必然执行的SQL语句较多，这时候就需要慢慢排查了。

方案三

假如后面应用层最终commit了，那么会在binlog里记录，可以根据当时的session id去binlog里面查看完整事务。

 

作者：张伟科
链接：https://www.jianshu.com/p/be4965ed802e
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。