ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，hy000restarting

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，hy000restarting

前言：朋友諮詢我說執行簡單的update語句失效，癥狀如下：
mysql> update order_info  set province_id=15  ,city_id= 1667  where order_from=10 and order_out_sn='1407261241xxxx';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 

QQ遠程過去，開始check
1，查看資料庫的隔離等級：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> 

 
2，去查看先當前庫的線程情況：

mysql> show full processlist;

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

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

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

|        1 | event_scheduler | localhost         | NULL            | Daemon      | 9635385 | Waiting on empty queue  | NULL                  |

|  9930577 | business_web    | 192.168.1.21:45503 | business_db     | Sleep       |     153 |                         | NULL                  |

|  9945825 | business_web    | 192.168.1.25:49518 | business_db     | Sleep       |      43 |                         | NULL                  |

|  9946322 | business_web    | 192.168.1.23:44721 | business_db     | Sleep       |     153 |                         | NULL                  |

|  9960167 | business_web    | 192.168.3.28:2409  | business_db     | Sleep       |      93 |                         | NULL                  |

|  9964484 | business_web    | 192.168.1.21:24280 | business_db     | Sleep       |       7 |                         | NULL                  |

|  9972499 | business_web    | 192.168.3.28:35752 | business_db     | Sleep       |      13 |                         | NULL                  |

| 10000117 | business_web    | 192.168.3.28:9149  | business_db     | Sleep       |       6 |                         | NULL                  |

| 10002523 | business_web    | 192.168.3.29:42872 | business_db     | Sleep       |       6 |                         | NULL                  |

| 10007545 | business_web    | 192.168.1.21:51379 | business_db     | Sleep       |     155 |                         | NULL                  |
......

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

 

沒有看到正在執行的慢SQL記錄線程，再去查看innodb的事務表INNODB_TRX，看下裡面是否有正在鎖定的事務線程，看看ID是否在show full processlist裡面的sleep線程中，如果是，就證明這個sleep的線程事務一直沒有commit或者rollback而是卡住了，我們需要手動kill掉。

 

mysql> SELECT * FROM information_schema.INNODB_TRX\G;

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

                    trx_id: 20866

                 trx_state: LOCK WAIT

               trx_started: 2014-07-31 10:42:35

     trx_requested_lock_id: 20866:617:3:3

          trx_wait_started: 2014-07-30 10:42:35

                trx_weight: 2

       trx_mysql_thread_id: 9930577

                 trx_query: delete from dltask where id=1

       trx_operation_state: starting index read

         trx_tables_in_use: 1

         trx_tables_locked: 1

          trx_lock_structs: 2

     trx_lock_memory_bytes: 376

           trx_rows_locked: 1

         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: 10000

          trx_is_read_only: 0

trx_autocommit_non_locking: 0

 

3，看到有這條9930577的sql，kill掉，執行kill 9930577;

mysql> kill 9930577;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

 

然後再去查詢INNODB_TRX表，就沒有阻塞的事務sleep線程存在了，如下所示：

mysql> SELECT * FROM INNODB_TRX\G;

Empty set (0.00 sec)

 

ERROR:

No query specified

 

mysql>

再去執行update語句，就能正常執行了，如下所示：

mysql> update order_info  set province_id=15  ,city_id= 1667  where order_from=10 and order_out_sn='1407261241xxxx';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql>

4，總結分析
表資料量也不大，按照普通的情況來說，簡單的update應該不會造成阻塞的，mysql都是autocommit，不會出現update卡住的情況，去查看下autocommit的值。
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)


mysql>

看到亮閃閃的0，這個設定導致原來的update語句如果沒有commit的話，你再重新執行update語句，就會等待鎖定，當等待時間過長的時候，就會報ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的錯誤。
所以趕緊commit剛才執行的update語句，之後 set global autocommit=1;

來自：http://blog.itpub.net/26230597/viewspace-1242948/

mysql 更新操作資料丟失

執行 update 語句以前

先執行
START TRANSACTION;
開始交易處理

會話1：
mysql> select * from test_main;
+----+-------+
| id | value |
+----+-------+
| 1 | ONE |
| 2 | TWO |
| 3 | THREE |
| 4 | Four |
+----+-------+
4 rows in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test_main SET value = 'FOUR-4' WHERE id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

會話2：
mysql> select * from test_main;
+----+-------+
| id | value |
+----+-------+
| 1 | ONE |
| 2 | TWO |
| 3 | THREE |
| 4 | Four |
+----+-------+
4 rows in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test_main SET value ='THREE-3' WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

會話1：
mysql> select * from test_main;
+----+--------+
| id | value |
+----+--------+
| 1 | ONE |
| 2 | TWO |
| 3 | THREE |
| 4 | FOUR-4 |
+----+--------+
4 rows in set (0.00 sec)

mysql> UPDATE test_main SET value ='THREE-5' WHERE id = 3;
由於 id=3 的行，已經被會話2修改，並鎖定，因此會話1當前進入等待狀態。

一定時間後逾時錯誤發生
mysql> UPDATE test_main SET value ='THREE-5' WHERE id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

會話2：
mysql> commit;
Query OK, 0 rows affected (0.05 sec)

會話1：
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test_main;
+----+---------+
| i......餘下全文>>
 
mysql大資料插入刪除同時進行，刪除出錯

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
資料庫的格式。是行鎖還是表鎖，在個資料量太大時。java session逾時。