mysql查看挂起事务_MySQL运维中长事务和锁等待排查

作为一个DBA, 常常实际工作中遇到各种稀奇八怪的问题。这里重点说一说长事务和锁等待。

长事务

简单来说就是长时间未递交的事务。它又可分为读长事务和写常事务。

读长事务

比如研发同事连接查询机(从库)查询，没有启用autocommit, 执行了一个查询SQL，没有commit(一般执行查询都不会再执行commit)，连接就这样长时间挂起。这就造成了一个读长事务。这个事务持有一个share_read DML锁，它会影响对该表的DDL操作。如果这时DBA在主库对这个表执行了DDL操作，这个DDL操作复制到从库的时候，会因等待MDL锁而无法执行。这会造成从库复制大量延迟！

还有一种读长事务的情况就是研发人员执行了一个复杂的统计类SQL，这个SQL执行完本身就耗时很长，这也会造成长时间占用DML锁，即使启用了autocommit也没用。而且还有可能大量数据文件排序造成磁盘空间耗尽。

更有甚者，程序的连接执行了查询，没有commit,而程序用的是连接池，连接又不关闭。这个问题就很严重了。

写长事务

写长事务比较好理解，就是批量更新、插入，造成事务长时间执行。还有就是事务本身逻辑比较复杂，存在锁竞争、锁等待。锁等待都会有超时，超时后应用端应该回滚或者重试。

面对复杂的应用场景，DBA要以不变应万变，这个法宝就是监控。监控的目的是即时发现，发现了就能即时处理。对于读长事务，一旦超过一定阈值(比如10m)可立马kill,对于写操作则不能这么任性，需要结合业务来分析。

INFORMATION_SCHEMA.INNODB_TRX表中包含了当前innodb内部正在运行的事务信息，包括只读事务。这个表中给出了事务的开始时间，我们可以稍加运算即可得到事务的运行时间。

mysql> select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G

*************************** 1. row ***************************

trx_id: 421860487065200

trx_state: RUNNING

trx_started: 2018-06-28 08:42:50

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 0

trx_mysql_thread_id: 207

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 0

trx_lock_memory_bytes: 1136

trx_rows_locked: 0

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: READ COMMITTED

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 0

trx_is_read