java.sql.SQLException: Lock wait timeout exceeded

来源：互联网发布：网络推广经理职责编辑：程序博客网时间：2024/06/06 01:02

先说我我的解决方法：找到锁住的线程然后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 sec2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 15MySQL thread id 1196, OS thread handle 0x7f1f0759d700, query id 140864 112.124.64.64 rootTrx read view will not see trx with id >= 17E34C, sees < 17DFA9---TRANSACTION 17E11F, ACTIVE 6222 sec10 lock struct(s), heap size 3112, 9 row lock(s), undo log entries 11MySQL thread id 1199, OS thread handle 0x7f1f07458700, query id 140844 112.124.64.64 rootTrx read view will not see trx with id >= 17E120, sees < 17DFA9---TRANSACTION 17DFAA, ACTIVE 11653 sec2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 15MySQL thread id 1178, OS thread handle 0x7f1f0af75700, query id 133760 112.124.64.64 rootTrx read view will not see trx with id >= 17DFAB, sees < 17DFA7---TRANSACTION 17DFA9, ACTIVE 11655 sec3 lock struct(s), heap size 376, 1 row lock(s), undo log entries 15MySQL thread id 1177, OS thread handle 0x7f1f1b682700, query id 133743 112.124.64.64 rootTrx 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

+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+| 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 |+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+| 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表里无记录。

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引擎。

+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+| INNODB_TRX | MEMORY |      10 | Fixed      | NULL |           4534 |           0 |        16766732 |            0 |         0 |           NULL | 2016-05-23 22:06:37 | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=3700  |         |+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

0 0