ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 02/22 Update SLTechnology News&Howtos

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

2025-02-22 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

2010.10.30

When it was time to get off work, the developer came to me and told me why he truncated a table with only 163 records. But it will take a long time to wait.

at that time? I think there should be a waiting event, so just wait. So I manipulated the table of his reaction on Navicat myself, and the following prompt appeared:

This is obviously a lock, and in this case a table lock. I asked the developer if someone was operating the table, and he said that he was the only one to operate it, and he had not operated it for a long time.

Nima, this confirms what the original master said, the wrong operator will always say, "No, I didn't do anything."

First check to see if there are any locks in the process:

Click (here) to collapse or open

Mysql > SHOW PROCESSLIST

+- -+

+- -+

(product_no, brand_detail_no, attribute_no, attrib |

| | 86441 | root | 172.17.206.45 Sleep 51527 | mdm | Sleep | 15684 | | NULL |

| | 86642 | root | 172.17.210.104 Sleep 31027 | mdm | Sleep | 256th | | NULL |

| | 86643 | root | 172.17.210.104 Sleep 31028 | mdm | Sleep | 2234 | | NULL |

| | 86652 | root | 172.17.206.114 Sleep 55635 | mdm | Sleep | 853 | | NULL |

| | 86653 | root | 172.17.206.114 Sleep 55636 | mdm | Sleep | 853 | | NULL |

| | 86654 | root | 172.17.206.114 Sleep 55637 | mdm | Sleep | 853 | | NULL |

| | 86655 | root | 172.17.206.114 Sleep 55638 | mdm | Sleep | 853 | | NULL |

| | 86656 | root | 172.17.206.114Sleep 55639 | mdm | Sleep | 911 | | NULL |

| | 86657 | root | 172.17.206.114 NULL 55640 | mdm | Sleep | 851 | | NULL |

| | 86658 | root | 172.17.206.114Sleep 55641 | mdm | Sleep | 853 | | NULL |

| | 86659 | root | 172.17.206.114Sleep 55642 | mdm | Sleep | 853 | | NULL |

| | 86660 | root | 172.17.206.114Sleep 55643 | mdm | Sleep | 825 | | NULL |

| | 86661 | root | 172.17.206.114 Sleep 55644 | mdm | Sleep | 912 | | NULL |

| | 86662 | root | 172.17.206.114 Sleep 55645 | mdm | Sleep | 827 | | NULL |

| | 86663 | root | 172.17.206.114 Sleep 55646 | mdm | Sleep | 825 | | NULL |

| | 86664 | root | 172.17.206.114NULL 56394 | mdm | Sleep | 912 | | NULL |

| | 86665 | root | 172.17.206.114NULL 56395 | mdm | Sleep | 853 | | NULL |

| | 86673 | root | 172.17.206.114Sleep 56455 | mdm | Sleep | 825 | | NULL |

| | 86674 | root | 172.17.206.114NULL 56487 | mdm | Sleep | 851 | | NULL |

| | 86675 | root | 172.17.206.114Sleep 56488 | mdm | Sleep | 853 | | NULL |

| | 86693 | root | 172.17.210.104 Sleep 34433 | mdm | Sleep | 2204 | | NULL |

| | 86694 | root | 172.17.210.104 Sleep 34434 | mdm | Sleep | 2234 | | NULL |

| | 86695 | root | 172.17.210.104 Sleep 34441 | mdm | Sleep | 2204 | | NULL |

| | 86697 | root | 172.17.210.104 Sleep 35207 | mdm | Sleep | 2234 | | NULL |

| | 86698 | root | 172.17.210.104 Sleep 35208 | mdm | Sleep | 2204 | | NULL |

| | 86699 | root | 172.17.210.104 Sleep 35209 | mdm | Sleep | 2234 | | NULL |

| | 86700 | root | 172.17.210.104 Sleep 35210 | mdm | Sleep | 2204 | | NULL |

| | 86701 | root | 172.17.210.104 Sleep 35475 | mdm | Sleep | 2267 | | NULL |

| | 86702 | root | 172.17.210.104 Sleep 35476 | mdm | Sleep | 2267 | | NULL |

| | 86703 | root | 172.17.210.104 Sleep 35478 | mdm | Sleep | 2204 | | NULL |

| | 86712 | root | 172.17.210.104 Sleep 37471 | mdm | Sleep | 2040 | | NULL |

| | 86713 | root | 172.17.210.104 Sleep 37472 | mdm | Sleep | 2040 | | NULL |

| | 86714 | root | 172.17.210.104 Sleep 37473 | mdm | Sleep | 2040 | | NULL |

| | 86715 | root | 172.17.210.104 Sleep 37474 | mdm | Sleep | 2040 | | NULL |

| | 86716 | root | 172.17.210.104 Sleep 37475 | mdm | Sleep | 2040 | | NULL |

| | 86717 | root | 172.17.210.104 Sleep 37476 | mdm | Sleep | 2040 | | NULL |

| | 86719 | root | 172.17.210.104 Sleep 37478 | mdm | Sleep | 2040 | | NULL |

| | 86721 | root | 172.17.210.104 Sleep 37480 | mdm | Sleep | 2040 | | NULL |

| | 86722 | root | 172.17.210.104 Sleep 37481 | mdm | Sleep | 2040 | | NULL |

| | 86723 | root | 172.17.210.104 Sleep 37482 | mdm | Sleep | 2040 | | NULL |

| | 86740 | root | 172.17.206.113 Sleep 51064 | mdm | Sleep | 385 | | NULL |

| | 86747 | root | 172.17.211.99 Sleep 53040 | mdm | Sleep | 958 | | NULL |

+- -+

102 rows in set (0.00 sec)

Mysql > obviously has the lock, which is also the library he operates on.

Of course, if you do not see the slow SQL recording thread that is executing, then check the transaction table INNODB_TRX of innodb to see if there is a transaction thread that is locking, and see if ID is in the sleep thread in show full processlist. If so, it proves that the thread transaction has been stuck without commit or rollback, and we need to kill it manually.

Mysql > SELECT * FROM information_schema.INNODB_TRX\ G

-slightly

Trx_mysql_thread_id: 86200

Now kill the process:

Mysql > kill 86200

Query OK, 0 rows affected (0.00 sec)

The next stage schedule will be very fast.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.