MySQL事务问题排查语句

查询当前事务隔离级别

select @@tx_isolation;

查询当前执行中事务

select * from information_schema.innodb_trx;

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

trx_id: 113963                                                            

 trx_state: LOCK WAIT                                                              # 锁状态（主要关注）      #事务状态                

trx_started: 2020-06-13 10:48:48    

 trx_requested_lock_id: 113963:460:3:4          

trx_wait_started:  2020-06-13 10:48:48                

trx_weight: 2        t

rx_mysql_thread_id: 140                                                                       # 执行的线程号 （主要关注）                  

trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3   # 执行语句         （主要关注）        

trx_operation_state: starting index read        

 trx_tables_in_use: 1        

 trx_tables_locked: 1        

  trx_lock_structs: 2      

trx_lock_memory_bytes: 1136                    

 trx_rows_locked: 7                          #事务锁住的行数        

 trx_rows_modified: 0    trx_concurrency_tickets: 0                          #事务并发票数          

 trx_isolation_level: REPEATABLE READ             # 事务隔离级别        

 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_only: 0

trx_autocommit_non_locking: 0

*************************** ***************************

查询执行中进程

命令行状态下,即非可视化软件的查询窗口，navicat可以按F6进入命令行模式

如果有root权限，那么可以看到所有线程。否则，只能看到登录的用户自己的线程，通常只会显示100条如果想看跟多的可以使用full修饰（show full processlist）

show proecesslist;

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

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

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

|      9 | root         | xx.42.15.xxx:41200  | ms_communi      | Sleep       |      28 |                                                               | NULL             |

|  74180 | root         | xx.8.62.xxx:45098   | NULL            | Sleep       |      12 |                                                               | NULL             |

|  74187 | root         | xx.8.62.xxx:45136   | NULL            | Binlog Dump | 1053251 | Master has sent all binlog to slave; waiting for more updates | NULL             |

| 113684 | root         | xx.42.15.1xx:54160  | ms_communi      | Sleep       |       4 |                                                               | NULL             |

| 255894 | root         | xxx.8.62.1xx:36216   | NULL            | Sleep       |       4 |                                                               | NULL             |

| 258610 | root         | xxx.8.62.1xx:31268   | ms_memberdb10   | Sleep       |     173 |                                                               | NULL             |

| 258620 | root         | xxx.8.62.1xx:13101   | ms_memberdb11   | Sleep       |     473 |                                                               | NULL             |

| 258713 | root         | xxx.8.62.1xx:60144   | ms_memberdb11   | Sleep       |     473 |                                                               | NULL             |

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

可以看到当前MySQL执行中的线程

 id       #ID标识，要kill一个语句的时候很有用

use      #当前连接用户

host     #显示这个连接从哪个ip的哪个端口上发出

db       #数据库名

command  #连接状态，一般是休眠（sleep），查询（query），连接（connect）

time     #连接持续时间，单位是秒

state    #显示当前sql语句的状态

info     #显示这个sql语句

重点关注 state,command（状态，命令）

其中ID对应 查询执行中事务的 trx_mysql_thread_id 字段

这两个命令可以帮助我们排查异常锁住的事务，之后可以通过kill命令，杀死线程

杀死线程

kill 74180

74180 为 show processlist 中的某一行的ID，或为 trx_mysql_thread_id，用于杀死异常事务的线程

案例

近期有测试报告BUG，偶发性更新某一个会员的数据时，一直报

Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

看到 transaction，Lock，第一反应，事务锁超时问题。询问测试持续多久，回复一直这样，且只有该会员是这样，那么可以判断出只锁了某一行。

1.查看当前执行中事务

进入数据库，查询当前执行中事务

select * from information_schema.innodb_trx;

 

 

 

可以看到确实有这么个线程在跑

执行 kill 3576652

询问测试更新是否恢复正常，回复说正常，可以确认是事务锁问题。

2.排查是否是自己的代码导致Spring事务未释放。

利用jmeter单会员并发更新，未复现。且方法上加了redis锁，不应该有争抢事务锁的情况出现。

3.询问其他人是否在事务中批量更新会员信息

有，尝试调用并且利用DEBUG模式卡住事务不提交，但是由于锁的行数不对。排除。

4.Navicat误操作

由于navicat开始事务的按钮在筛选按钮附近，十分容易误操作后忘记点击提交。

 

询问测试是否出现上述情况时，是否有手动修改数据，用的软件是否为navicat，回复说是。

自己测试了下，点击开始事务后，修改数据，不点击提交。

调用接口更新会员数据，阻塞50秒后（Mysql默认锁超时时间），返回

Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

执行

select * from information_schema.innodb_trx;

情况复现

回复测试时，测试刚好在手动改数据，调接口时又触发了上述错误，直接到测试电脑跟前查看，发现navicat的事务按钮正好为“提交"显示，即上述图片显示情况。

跟测试解释具体情况，并在点击提交后，情况修复，问题解决