导航：首页 > 数据库 >

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

发表于：2026-11-01 作者：千家信息网编辑

千家信息网最后更新 2026年11月01日，2010.10.30要下班了，开发人员过来跟我说，为什么他截断一个表，才163条记录。但是需要等待很长时间。当时?想应该是有等待事件，那就等着呗。于是我自己再Navicat 上操作他反应的那张表，结

千家信息网最后更新 2026年11月01日ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction2010.10.30
要下班了，开发人员过来跟我说，为什么他截断一个表，才163条记录。但是需要等待很长时间。
当时?想应该是有等待事件，那就等着呗。于是我自己再Navicat 上操作他反应的那张表，结果出现下面的提示：

这明显是出现了锁，而且这种情况下是表锁。我就问开发，是不是有人在操作这张表，他说就他一个人操作，他也早就没操作了。
尼玛，这点印证了当初师傅的话，错误操作的人，永远都是会说：没有啊，我什么都没有做啊。

先查看一下进程里面有没有锁：

点击(此处)折叠或打开

mysql> SHOW PROCESSLIST;
+-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
| 85558 | root | 172.17.210.205:54661 | NULL | Sleep | 80883 | | NULL |
| 85559 | root | 172.17.210.205:54662 | mdm_test | Sleep | 29632 | | NULL |
| 86200 | root | 172.17.210.205:55170 | mdm_test | Query | 28942 | Sending data | INSERT INTO dim_pro_product_extension
(product_no, brand_detail_no, attribute_no, attrib |
| 86210 | root | 172.17.205.110:50237 | NULL | Sleep | 13007 | | NULL |
| 86393 | root | 172.17.206.166:50555 | mdm_dev | Sleep | 2664 | | NULL |
| 86394 | root | 172.17.206.166:50556 | mdm_dev | Sleep | 999 | | NULL |
| 86403 | root | 172.17.206.166:50563 | mdm_dev | Sleep | 145 | | NULL |
| 86404 | root | 172.17.206.166:50564 | mdm_dev | Sleep | 145 | | NULL |
| 86409 | root | 172.17.206.166:50565 | mdm_dev | Sleep | 999 | | NULL |
| 86410 | root | 172.17.206.166:50566 | mdm_dev | Sleep | 2664 | | NULL |
| 86411 | root | 172.17.206.166:50569 | mdm_dev | Sleep | 145 | | NULL |
| 86412 | root | 172.17.206.166:50570 | mdm_dev | Sleep | 145 | | NULL |
| 86425 | root | 172.17.206.166:50593 | mdm_dev | Sleep | 114 | | NULL |
| 86427 | root | 172.17.206.166:50595 | mdm_dev | Sleep | 114 | | NULL |
| 86428 | root | 172.17.206.166:50596 | mdm_dev | Sleep | 114 | | NULL |
| 86440 | root | 172.17.206.45:51526 | NULL | Sleep | 15874 | | NULL |
| 86441 | root | 172.17.206.45:51527 | mdm | Sleep | 15684 | | NULL |
| 86461 | root | 172.17.206.166:50620 | mdm_dev | Sleep | 145 | | NULL |
| 86464 | root | 172.17.206.166:50623 | mdm_dev | Sleep | 145 | | NULL |
| 86467 | root | 172.17.206.118:55598 | NULL | Sleep | 14845 | | NULL |
| 86488 | root | 172.17.211.114:62576 | NULL | Sleep | 13999 | | NULL |
| 86489 | root | 172.17.211.114:62593 | miu_mobile_server_ol | Sleep | 13992 | | NULL |
| 86490 | root | 172.17.211.114:62712 | miu_mobile_server_ol | Sleep | 13986 | | NULL |
| 86491 | root | 172.17.211.114:62772 | miu_mobile_server_ol | Sleep | 13974 | | NULL |
| 86531 | root | 172.17.206.166:51119 | mdm_dev | Sleep | 999 | | NULL |
| 86532 | root | 172.17.206.166:51120 | mdm_dev | Sleep | 999 | | NULL |
| 86557 | root | 172.17.206.166:51173 | mdm_dev | Sleep | 114 | | NULL |
| 86642 | root | 172.17.210.104:31027 | mdm | Sleep | 256 | | NULL |
| 86643 | root | 172.17.210.104:31028 | mdm | Sleep | 2234 | | NULL |
| 86644 | root | 172.17.210.104:31029 | mdm | Sleep | 256 | | NULL |
| 86645 | root | 172.17.210.104:31030 | mdm | Sleep | 256 | | NULL |
| 86652 | root | 172.17.206.114:55635 | mdm | Sleep | 853 | | NULL |
| 86653 | root | 172.17.206.114:55636 | mdm | Sleep | 853 | | NULL |
| 86654 | root | 172.17.206.114:55637 | mdm | Sleep | 853 | | NULL |
| 86655 | root | 172.17.206.114:55638 | mdm | Sleep | 853 | | NULL |
| 86656 | root | 172.17.206.114:55639 | mdm | Sleep | 911 | | NULL |
| 86657 | root | 172.17.206.114:55640 | mdm | Sleep | 851 | | NULL |
| 86658 | root | 172.17.206.114:55641 | mdm | Sleep | 853 | | NULL |
| 86659 | root | 172.17.206.114:55642 | mdm | Sleep | 853 | | NULL |
| 86660 | root | 172.17.206.114:55643 | mdm | Sleep | 825 | | NULL |
| 86661 | root | 172.17.206.114:55644 | mdm | Sleep | 912 | | NULL |
| 86662 | root | 172.17.206.114:55645 | mdm | Sleep | 827 | | NULL |
| 86663 | root | 172.17.206.114:55646 | mdm | Sleep | 825 | | NULL |
| 86664 | root | 172.17.206.114:56394 | mdm | Sleep | 912 | | NULL |
| 86665 | root | 172.17.206.114:56395 | mdm | Sleep | 853 | | NULL |
| 86672 | root | 172.17.206.114:56454 | mdm | Sleep | 824 | | NULL |
| 86673 | root | 172.17.206.114:56455 | mdm | Sleep | 825 | | NULL |
| 86674 | root | 172.17.206.114:56487 | mdm | Sleep | 851 | | NULL |
| 86675 | root | 172.17.206.114:56488 | mdm | Sleep | 853 | | NULL |
| 86690 | root | 172.17.206.166:51536 | mdm_dev | Sleep | 2529 | | NULL |
| 86691 | root | 172.17.206.166:51537 | NULL | Sleep | 4628 | | NULL |
| 86693 | root | 172.17.210.104:34433 | mdm | Sleep | 2204 | | NULL |
| 86694 | root | 172.17.210.104:34434 | mdm | Sleep | 2234 | | NULL |
| 86695 | root | 172.17.210.104:34441 | mdm | Sleep | 2204 | | NULL |
| 86696 | root | 172.17.210.104:34442 | mdm | Sleep | 256 | | NULL |
| 86697 | root | 172.17.210.104:35207 | mdm | Sleep | 2234 | | NULL |
| 86698 | root | 172.17.210.104:35208 | mdm | Sleep | 2204 | | NULL |
| 86699 | root | 172.17.210.104:35209 | mdm | Sleep | 2234 | | NULL |
| 86700 | root | 172.17.210.104:35210 | mdm | Sleep | 2204 | | NULL |
| 86701 | root | 172.17.210.104:35475 | mdm | Sleep | 2267 | | NULL |
| 86702 | root | 172.17.210.104:35476 | mdm | Sleep | 2267 | | NULL |
| 86703 | root | 172.17.210.104:35478 | mdm | Sleep | 2204 | | NULL |
| 86704 | root | 172.17.210.104:35479 | mdm | Sleep | 256 | | NULL |
| 86712 | root | 172.17.210.104:37471 | mdm | Sleep | 2040 | | NULL |
| 86713 | root | 172.17.210.104:37472 | mdm | Sleep | 2040 | | NULL |
| 86714 | root | 172.17.210.104:37473 | mdm | Sleep | 2040 | | NULL |
| 86715 | root | 172.17.210.104:37474 | mdm | Sleep | 2040 | | NULL |
| 86716 | root | 172.17.210.104:37475 | mdm | Sleep | 2040 | | NULL |
| 86717 | root | 172.17.210.104:37476 | mdm | Sleep | 2040 | | NULL |
| 86718 | root | 172.17.210.104:37477 | mdm | Sleep | 506 | | NULL |
| 86719 | root | 172.17.210.104:37478 | mdm | Sleep | 2040 | | NULL |
| 86720 | root | 172.17.210.104:37479 | mdm | Sleep | 506 | | NULL |
| 86721 | root | 172.17.210.104:37480 | mdm | Sleep | 2040 | | NULL |
| 86722 | root | 172.17.210.104:37481 | mdm | Sleep | 2040 | | NULL |
| 86723 | root | 172.17.210.104:37482 | mdm | Sleep | 2040 | | NULL |
| 86724 | root | 172.17.210.104:38325 | mdm_pro | Sleep | 1368 | | NULL |
| 86725 | root | 172.17.210.104:38326 | mdm_pro | Sleep | 1368 | | NULL |
| 86727 | root | 172.17.210.104:38428 | mdm_pro | Sleep | 1285 | | NULL |
| 86728 | root | 172.17.210.104:38429 | mdm_pro | Sleep | 1285 | | NULL |
| 86730 | root | 172.17.206.113:51041 | NULL | Sleep | 1269 | | NULL |
| 86732 | root | 172.17.210.104:38468 | mdm_pro | Sleep | 1253 | | NULL |
| 86733 | root | 172.17.210.104:38469 | mdm_pro | Sleep | 1253 | | NULL |
| 86734 | root | 172.17.210.104:38470 | mdm_pro | Sleep | 1253 | | NULL |
| 86735 | root | 172.17.210.104:38471 | mdm_pro | Sleep | 1253 | | NULL |
| 86736 | root | 172.17.210.104:38605 | mdm_pro | Sleep | 1147 | | NULL |
| 86737 | root | 172.17.210.104:38606 | mdm_pro | Sleep | 1147 | | NULL |
| 86738 | root | 172.17.210.104:38607 | mdm_pro | Sleep | 1147 | | NULL |
| 86739 | root | 172.17.210.104:38608 | mdm_pro | Sleep | 1147 | | NULL |
| 86740 | root | 172.17.206.113:51064 | mdm | Sleep | 385 | | NULL |
| 86741 | root | 172.17.210.205:55709 | NULL | Sleep | 1092 | | NULL |
| 86742 | root | 172.17.210.205:55711 | mdm_test | Sleep | 1088 | | NULL |
| 86743 | root | 172.17.210.205:55712 | mdm_test | Sleep | 786 | | NULL |
| 86746 | root | 172.17.211.99:53039 | NULL | Sleep | 959 | | NULL |
| 86747 | root | 172.17.211.99:53040 | mdm | Sleep | 958 | | NULL |
| 86748 | root | 172.17.211.99:53080 | mdm_test | Sleep | 320 | | NULL |
| 86750 | root | 172.17.211.99:53130 | mdm_test | Sleep | 800 | | NULL |
| 86751 | root | 172.17.210.205:55718 | mdm_test | Sleep | 782 | | NULL |
| 86752 | root | localhost | mdm_test | Query | 0 | init | SHOW PROCESSLIST |
| 86755 | root | 172.17.206.113:51149 | NULL | Sleep | 122 | | NULL |
| 86756 | root | 172.17.206.113:51150 | mdm | Sleep | 133 | | NULL |
| 86759 | root | 172.17.206.113:51181 | mdm_test | Sleep | 124 | | NULL |
| 86760 | root | 172.17.206.113:51183 | mdm | Sleep | 115 | | NULL |
+-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
102 rows in set (0.00 sec)
mysql>

很明显出现了锁，也是他操作的那个库。

当然如果没有看到正在执行的慢SQL记录线程，再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。