mysql trouble shooting---- 從庫停止同步lock_wait_timeout_exceeded_try_restarting_transaction

標籤：

問題描述：

資料庫從庫停止同步。

 

問題分析：

show slave status\G;（也可使用show full processlist）

顯示 某個update語句出錯，Lock wait timeout exceeded; try restarting transaction; 

原因是這條語句提交的時候逾時堵塞。原因在於另外的一個操作開啟了事務，鎖定了相應的資料，導致這條操作相同資料的sql出錯。

 

樣本，在sql中

（1）開啟事務，鎖定資料

終端A：

MySQL> begin; (開啟事務，開啟事務會鎖定相關資料)
Query OK, 0 rows affected (0.00 sec)

mysql> update pet set sex="m" where name="Fluffy";（修改）
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Fang   | Benny  | dog     | m    | 2013-02-05 | NULL       |
| Fluffy | Harold | cat     | m    | 2012-09-30 | NULL       |
+--------+--------+---------+------+------------+------------+
5 rows in set (0.00 sec)

終端B：

mysql> select * from pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Fang   | Benny  | dog     | m    | 2013-02-05 | NULL       |
| Fluffy | Harold | cat     | NULL | 2012-09-30 | NULL       |
+--------+--------+---------+------+------------+------------+
5 rows in set (0.01 sec)

因為沒有提交，所以實際上沒有改變。但是會鎖定Fluffy這一條資料。導致如下的錯誤。

終端B：

mysql> update pet set sex="m" where name="Fluffy";

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

 

（2）提交事務，解鎖資料

終端A：

mysql> commit;
Query OK, 0 rows affected (0.10 sec)

終端B：

mysql> select * from pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Fang   | Benny  | dog     | m    | 2013-02-05 | NULL       |
| Fluffy | Harold | cat     | m    | 2012-09-30 | NULL       |
+--------+--------+---------+------+------------+------------+
5 rows in set (0.00 sec)
mysql> update pet set sex="f" where name="Fluffy";
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

（3）如果where中的條件是索引欄位，那麼只會鎖定索引對應的條目；如果不是索引欄位，那麼會鎖定整張表。

終端A：

mysql> update pet set sex="f" where death="1995-07-29";
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Bowser | Diane  | dog     | f    | 1979-08-31 | 1995-07-29 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Fang   | Benny  | dog     | m    | 2013-02-05 | NULL       |
| Fluffy | Harold | cat     | f    | 2012-09-30 | NULL       |
+--------+--------+---------+------+------------+------------+
5 rows in set (0.00 sec)

終端B：

mysql> select * from pet;
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Fang   | Benny  | dog     | m    | 2013-02-05 | NULL       |
| Fluffy | Harold | cat     | f    | 2012-09-30 | NULL       |
+--------+--------+---------+------+------------+------------+
5 rows in set (0.00 sec)
mysql> update pet set death="2013-07-00" where birth="2013-02-05";

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

 

解決辦法

（1） stop slave;      start slave

(2)網上分析說

 

Mysql ‘Lock wait timeout exceeded; try restarting transaction‘ 解決方案

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 
Temporary error: 266: Time-out in NDB, probably caused by deadlock 解決方案：在管理節點的[ndbd default] 
區加：
TransactionDeadLockDetectionTimeOut=10000（設定為10秒）預設是1200（1.2秒）按照順序重新啟動各個節點就不會出現問題了。

不過，本身貌似資料庫的TransactionDeadLockDetectionTimeOut已經設定很大，而且即使設定很大，如果那邊一直鎖表，也是解決不了問題的。

mysql trouble shooting---- 從庫停止同步lock_wait_timeout_exceeded_try_restarting_transaction