java.sql.SQLException: Lock wait timeout exceeded
先說我個人解決方法：找到鎖住的線程而後kill掉。java
mysql> kill thr_id;
下面簡單分析一下到底應該kill哪一個線程。mysql
show processlist查看全部運行的線程，發現全部線程的狀態都是sleep，不清楚這麼多sleep的線程，INNODB_TRX表裏也沒有對應RUNNING的記錄，那些線程假死了?sql
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發現有如下幾行：app
---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】。lua
先來嘗試還原當時的情景，插入一條數據，由於未知緣由事務一直未提交，得到的鎖固然未釋放，而後再去操做同一條數據兩次，查詢INNODB_TRX表，發現出現三條數據，前兩條是後來更新操做的信息，trx_state字段爲LOCK WAIT，第三個是沒有提交事務的那條操做：spa
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】rest
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 | | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+