MySQL trouble shooting----stop syncing from the library lock_wait_timeout_exceeded_try_restarting_transaction

Problem Description:

The database stops synchronizing from the library.

Problem Analysis:

show slave status\g; (You can also use show full processlist)

Error displaying an UPDATE statement, Lock wait timeout exceeded; Try restarting transaction;

This is due to a time-out blockage when this statement is committed. The reason is that another operation opened the transaction, locking the corresponding data, causing the SQL error to operate on the same data.

example, in SQL

(1) Turn on the transaction, lock the data

Terminal A:

Mysql> begin; ( open transaction, open transaction will lock related data)
Query OK, 0 rows Affected (0.00 sec)

Mysql> Update pet Set sex= "M" where name= "Fluffy"; (modified)
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)

Terminal 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)

Because there is no commit, it doesn't actually change. However, this data is locked fluffy. Causes the following error.

Terminal B:

Mysql> Update pet Set sex= "M" where name= "Fluffy";

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

(2) Commit the transaction, unlock the data

Terminal A:

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

Terminal 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) If the condition in the where is an indexed field, only the entry corresponding to the index is locked, and the entire table is locked if it is not an indexed field.

Terminal 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)

Terminal 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

Solutions

(1) Stop slave; Start slave

(2) Online analysis says

Mysql ' Lock wait timeout exceeded; Try restarting transaction ' solution

ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
Temporary error:266:time-out in NDB, probably caused by deadlock workaround: In the Management node [NDBD default]
Area Plus:
The transactiondeadlockdetectiontimeout=10000 (set to 10 seconds) defaults to 1200 (1.2 seconds) to restart each node in sequence without a problem.

However, it seems that the database transactiondeadlockdetectiontimeout has been set very large, and even if the setting is very large, if there has been locked table, but also can not solve the problem.

MySQL trouble shooting----stop syncing from the library lock_wait_timeout_exceeded_try_restarting_transaction