java.sql.SQLException: Lock wait timeout exceeded

r囧r小猫 2022-07-11 01:46 130阅读 0赞

先说我我的解决方法：找到锁住的线程然后kill掉。

mysql> kill thr_id;

下面简单分析一下到底应该kill哪个线程。

show processlist查看所有运行的线程，发现所有线程的Command都是sleep，sleep command官方解释是"The thread is waiting for the client to send a new statement to it"，等待客户端发送新语句，"wait\_timeout"时间间隔后MySQL会自动关闭这些连接，也可以手动kill掉。

mysql> show full processlist;
    +------+------+---------------------+----------+---------+-------+-------+-----------------------+
    | Id   User | Host          | db  | Command | Time  | State | Info |
    +------+------+---------------------+----------+---------+-------+-------+-----------------------+
    | 1172 | root | 112.124.64.64:43083 | eye | Sleep  | 12384 |     | NULL  |
    | 1179 | root | 112.124.64.64:43086 | eye | Sleep  |  596 |     | NULL  |
    | 1181 | root | 112.124.64.64:43087 | eye  | Sleep  | 12467 |     | NULL  |
    | 1182 | root | 112.124.64.64:43088 | eye | Sleep  |  149 |     | NULL  |
    | 1183 | root | 112.124.64.64:43089 | eye | Sleep  |  612 |     | NULL   |
    | 1184 | root | 112.124.64.64:43090 | eye | Sleep  |   39 |     | NULL  |
    | 1197 | root | 112.124.64.64:43092 | eye | Sleep  |  148 |     | NULL  |
    | 1198 | root | 112.124.64.64:43093 | eye | Sleep  |  5415 |     | NULL  |
    | 1200 | root | 112.124.64.64:43095 | eye | Sleep  |  581 |     | NULL  |
    | 1289 | root | 58.249.112.63:5657  | eye | Sleep  |  1496 |     | NULL  |
    | 1325 | root | localhost      | NULL | Query  |   0 | NULL  | show full processlist |
    +------+------+---------------------+----------+---------+-------+-------+-----------------------+

查看information\_schema库中的INNODB\_TRX表发现有几条记录，时间和用户反馈的时间是对上号的，id号在上结果中也有显示 ， show engine innodb status发现有以下几行：

---TRANSACTION 17E34B, ACTIVE 3296 sec
    2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 15
    MySQL thread id 1196, OS thread handle 0x7f1f0759d700, query id 140864 112.124.64.64 root
    Trx read view will not see trx with id >= 17E34C, sees < 17DFA9
    ---TRANSACTION 17E11F, ACTIVE 6222 sec
    10 lock struct(s), heap size 3112, 9 row lock(s), undo log entries 11
    MySQL thread id 1199, OS thread handle 0x7f1f07458700, query id 140844 112.124.64.64 root
    Trx read view will not see trx with id >= 17E120, sees < 17DFA9
    ---TRANSACTION 17DFAA, ACTIVE 11653 sec
    2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 15
    MySQL thread id 1178, OS thread handle 0x7f1f0af75700, query id 133760 112.124.64.64 root
    Trx read view will not see trx with id >= 17DFAB, sees < 17DFA7
    ---TRANSACTION 17DFA9, ACTIVE 11655 sec
    3 lock struct(s), heap size 376, 1 row lock(s), undo log entries 15
    MySQL thread id 1177, OS thread handle 0x7f1f1b682700, query id 133743 112.124.64.64 root
    Trx read view will not see trx with id >= 17DFAA, sees < 17DFA7

（TRANSACTION 17DFAA，2 lock struct(s), 0 row lock(s)，0 row locks，那 2 lock struct(s)到底锁在了啥上面）【当事务超时之后show engine innodb status就不会显示此事务运行时的执行SQL，以上信息只能说明这个事务锁等待过，2016-08-20】， （不是特别理解"MySQL thread id 1196, OS thread handle 0x7f1f0759d700, query id 140864 112.124.64.64 root Trx read view will not see trx with id >= 17E34C, sees < 17DFA9"，有什么提示信息，难道trx\_id还必须连续？"）【InnoDB使用MVCC来减少锁的使用和开销，允许非阻塞读，写操作时只锁定必要的记录，MVCC工作在READ-COMMITTED，REAPEATABLE-READ隔离级别，通过TRX\_ID并发控制，2016-08-20】 （undo log entries 15"的意思是15条没有insert进去，还是回滚操作没有回滚回来？）【undo log记录的是没有更改之前的数据，回滚用，这里应该是回滚了的条数，2016-08-20】。

先来尝试还原当时的情景，插入一条数据，因为未知原因事务一直未提交，获得的锁当然未释放，然后再去操作同一条数据两次，查询INNODB\_TRX表，发现出现三条数据，前两条是后来更新操作的信息，trx\_state字段为LOCK WAIT，第三个是没有提交事务的那条操作：

mysql> select * from INNODB_TRX;
    +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
    | trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                                                                                                                   | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
    +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
    | 1852B9 | LOCK WAIT | 2016-05-23 21:09:24 | 1852B9:0:3611:51      | 2016-05-23 21:09:25 |          2 |                7920 | UPDATE inspectionhistory 			SET   	          evaluation = null,  	          photoName = null 			WHERE inspectionID = '000154DD83C3313FA051' | starting index read |                 1 |                 1 |                2 |                   376 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                      9997 |
    | 1852A8 | LOCK WAIT | 2016-05-23 21:09:13 | 1852A8:0:3611:51      | 2016-05-23 21:09:13 |          2 |                7861 | UPDATE inspectionhistory 			SET   	          evaluation = null,  	          photoName = null 			WHERE inspectionID = '000154DD83C3313FA051' | starting index read |                 1 |                 1 |                2 |                   376 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                      9997 |
    | 18529F | RUNNING   | 2016-05-23 21:09:06 | NULL                  | NULL                |         31 |                7863 | NULL                                                                                                                                        | NULL                |                 0 |                 0 |               14 |                  3112 |              17 |                17 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                      9982 |
    +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+

上表里前两个事务等待超时返回错误信息，然后INNODB\_TRX表里记录自动删除，剩下未提交事务的那条记录，如下：

--- The error occurred in mit/datamodel/ibatis/inspectionhistory.xml.  
    --- The error occurred while applying a parameter map.  
    --- Check the updateInspectionHistory-InlineParameterMap.  
    --- Check the statement (update failed).  
    --- Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

当提交事务时，INNODB\_TRX表里无记录。

mysql> select * from INNODB_TRX;
    Empty set (0.00 sec)

如果真是这样，那么问题来了， （为什么上面截图里面5个事务一直RUNNING状态，什么导致了它们不去提交事务。）【此处应有截图，但是之前可以显示，后来显示图片损坏标识，就删了。什么导致了5个事务一直RUNNING现在还不清原因，也还原不了了，但是前段时间遇到一种这样的情况，在一次事务没有提交时，我更改了这张表的索引字段，导致卡死，之后强制关闭命令行，再重新打开去show processlist，发现更改索引的那个事务一直RUNNING,2016-08-20】 （以至于后来查询其它数据也等待超时，难道是它们之间及到了X，S，GAP锁？或者因为 Trx read view will not see？）【确实是因为没有提交的事务锁定了某些行数据，如果字段没有建索引，会锁住整张表数据，2016-08-20】

ps：无聊时随手看了下INNIDB\_TRX表信息，它的Engine竟然是Memory，其实也该想到了，只是经验太少，从没有实践机会去用Memory引擎。