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

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

My friend asked me that the execution of a simple update statement failed. The symptoms are as follows:
Mysql> update order_info set province_id = 15, city_id = 1667 where order_from = 10 and order_out_sn = '1401_1241xx ';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Mysql>

QQ remote past, Start check
1. view the database isolation level:

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

Mysql>

 
2. Check the thread status of the current database:

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 |
......

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

 

No slow SQL record thread is being executed. Check innodb's transaction table INNODB_TRX to see if there is a locked Transaction Thread in it, check whether the ID is in the sleep thread in show full processlist. If yes, it indicates that the thread transaction of this sleep has never been commit or rollback, but is stuck. We need to manually kill it.

 

Mysql> SELECT * FROM information_schema.INNODB_TRX \ G;

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

Trx_id: 20866

Trx_state: LOCK WAIT

Trx_started: 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. You can see this9930577Run kill and kill 9930577;

Mysql> kill 9930577;

Query OK, 0 rows affected (0.00 sec)

 

Mysql>

 

Then you can query the INNODB_TRX table, so there is no blocked transaction sleep thread, as shown below:

Mysql> SELECT * FROM INNODB_TRX \ G;

Empty set (0.00 sec)

 

ERROR:

No query specified

 

Mysql>

Run the update statement again, as shown below:

Mysql> update order_info set province_id = 15, city_id = 1667 where order_from = 10 and order_out_sn = '1401_1241xx ';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

 

Mysql>

4. Summary and Analysis
The table data volume is not large. In general cases, simple update should not cause blocking. mysql uses autocommit and does not get stuck with update. Check the autocommit value.
Mysql> select @ autocommit;
+ -------------- +
| @ Autocommit |
+ -------------- +
|0|
+ -------------- +
1 row in set (0.00 sec)


Mysql>

This setting causes the original update statement to wait for the lock if no commit exists. When the wait time is too long, the ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction is reported.
So hurry to commit the update statement just executed, and then set global autocommit = 1;

From: http://blog.itpub.net/26230597/viewspace-1242948/

Mysql update operation data loss

Before executing the update statement

Run
Start transaction;
Start Transaction Processing

Session 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

Session 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

Session 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;
Because the row id = 3 has been modified and locked by session 2, Session 1 is currently in the waiting status.

Timeout error occurs after a certain period of time
Mysql> UPDATE test_main SET value = 'three-5 'WHERE id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session 2:
Mysql> commit;
Query OK, 0 rows affected (0.05 sec)

Session 1:
Mysql> commit;
Query OK, 0 rows affected (0.03 sec)

Mysql> select * from test_main;
+ ---- + --------- +
| I ...... remaining full text>
 
Mysql inserts and deletes big data simultaneously. An error occurred while deleting the data.

Java. SQL. SQLException: Lock wait timeout exceeded; try restarting transaction
The format of the database. Whether it is a row lock or a table lock, when the amount of data is too large. Java session Timeout.