ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

MySQL 作者：urgel_babay 时间：2016-02-29 17:11:59 0 删除 编辑
2010.10.30
     要下班了，开发人员过来跟我说，为什么他截断一个表，才163条记录。但是需要等待很长时间。
当时?想 应该是有等待事件，那就等着呗。于是我自己再Navicat 上操作他反应的那张表，结果出现下面的提示：

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

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

点击(此处)折叠或打开

  1. mysql> SHOW PROCESSLIST;
  2. +-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
  3. | Id | User | Host | db | Command | Time | State | Info |
  4. +-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
  5. | 85558 | root | 172.17.210.205:54661 | NULL | Sleep | 80883 | | NULL |
  6. | 85559 | root | 172.17.210.205:54662 | mdm_test | Sleep | 29632 | | NULL |
  7. | 86200 | root | 172.17.210.205:55170 | mdm_test | Query | 28942 | Sending data | INSERT INTO dim_pro_product_extension
  8.             (product_no, brand_detail_no, attribute_no, attrib |
  9. | 86210 | root | 172.17.205.110:50237 | NULL | Sleep | 13007 | | NULL |
  10. | 86393 | root | 172.17.206.166:50555 | mdm_dev | Sleep | 2664 | | NULL |
  11. | 86394 | root | 172.17.206.166:50556 | mdm_dev | Sleep | 999 | | NULL |
  12. | 86403 | root | 172.17.206.166:50563 | mdm_dev | Sleep | 145 | | NULL |
  13. | 86404 | root | 172.17.206.166:50564 | mdm_dev | Sleep | 145 | | NULL |
  14. | 86409 | root | 172.17.206.166:50565 | mdm_dev | Sleep | 999 | | NULL |
  15. | 86410 | root | 172.17.206.166:50566 | mdm_dev | Sleep | 2664 | | NULL |
  16. | 86411 | root | 172.17.206.166:50569 | mdm_dev | Sleep | 145 | | NULL |
  17. | 86412 | root | 172.17.206.166:50570 | mdm_dev | Sleep | 145 | | NULL |
  18. | 86425 | root | 172.17.206.166:50593 | mdm_dev | Sleep | 114 | | NULL |
  19. | 86427 | root | 172.17.206.166:50595 | mdm_dev | Sleep | 114 | | NULL |
  20. | 86428 | root | 172.17.206.166:50596 | mdm_dev | Sleep | 114 | | NULL |
  21. | 86440 | root | 172.17.206.45:51526 | NULL | Sleep | 15874 | | NULL |
  22. | 86441 | root | 172.17.206.45:51527 | mdm | Sleep | 15684 | | NULL |
  23. | 86461 | root | 172.17.206.166:50620 | mdm_dev | Sleep | 145 | | NULL |
  24. | 86464 | root | 172.17.206.166:50623 | mdm_dev | Sleep | 145 | | NULL |
  25. | 86467 | root | 172.17.206.118:55598 | NULL | Sleep | 14845 | | NULL |
  26. | 86488 | root | 172.17.211.114:62576 | NULL | Sleep | 13999 | | NULL |
  27. | 86489 | root | 172.17.211.114:62593 | miu_mobile_server_ol | Sleep | 13992 | | NULL |
  28. | 86490 | root | 172.17.211.114:62712 | miu_mobile_server_ol | Sleep | 13986 | | NULL |
  29. | 86491 | root | 172.17.211.114:62772 | miu_mobile_server_ol | Sleep | 13974 | | NULL |
  30. | 86531 | root | 172.17.206.166:51119 | mdm_dev | Sleep | 999 | | NULL |
  31. | 86532 | root | 172.17.206.166:51120 | mdm_dev | Sleep | 999 | | NULL |
  32. | 86557 | root | 172.17.206.166:51173 | mdm_dev | Sleep | 114 | | NULL |
  33. | 86642 | root | 172.17.210.104:31027 | mdm | Sleep | 256 | | NULL |
  34. | 86643 | root | 172.17.210.104:31028 | mdm | Sleep | 2234 | | NULL |
  35. | 86644 | root | 172.17.210.104:31029 | mdm | Sleep | 256 | | NULL |
  36. | 86645 | root | 172.17.210.104:31030 | mdm | Sleep | 256 | | NULL |
  37. | 86652 | root | 172.17.206.114:55635 | mdm | Sleep | 853 | | NULL |
  38. | 86653 | root | 172.17.206.114:55636 | mdm | Sleep | 853 | | NULL |
  39. | 86654 | root | 172.17.206.114:55637 | mdm | Sleep | 853 | | NULL |
  40. | 86655 | root | 172.17.206.114:55638 | mdm | Sleep | 853 | | NULL |
  41. | 86656 | root | 172.17.206.114:55639 | mdm | Sleep | 911 | | NULL |
  42. | 86657 | root | 172.17.206.114:55640 | mdm | Sleep | 851 | | NULL |
  43. | 86658 | root | 172.17.206.114:55641 | mdm | Sleep | 853 | | NULL |
  44. | 86659 | root | 172.17.206.114:55642 | mdm | Sleep | 853 | | NULL |
  45. | 86660 | root | 172.17.206.114:55643 | mdm | Sleep | 825 | | NULL |
  46. | 86661 | root | 172.17.206.114:55644 | mdm | Sleep | 912 | | NULL |
  47. | 86662 | root | 172.17.206.114:55645 | mdm | Sleep | 827 | | NULL |
  48. | 86663 | root | 172.17.206.114:55646 | mdm | Sleep | 825 | | NULL |
  49. | 86664 | root | 172.17.206.114:56394 | mdm | Sleep | 912 | | NULL |
  50. | 86665 | root | 172.17.206.114:56395 | mdm | Sleep | 853 | | NULL |
  51. | 86672 | root | 172.17.206.114:56454 | mdm | Sleep | 824 | | NULL |
  52. | 86673 | root | 172.17.206.114:56455 | mdm | Sleep | 825 | | NULL |
  53. | 86674 | root | 172.17.206.114:56487 | mdm | Sleep | 851 | | NULL |
  54. | 86675 | root | 172.17.206.114:56488 | mdm | Sleep | 853 | | NULL |
  55. | 86690 | root | 172.17.206.166:51536 | mdm_dev | Sleep | 2529 | | NULL |
  56. | 86691 | root | 172.17.206.166:51537 | NULL | Sleep | 4628 | | NULL |
  57. | 86693 | root | 172.17.210.104:34433 | mdm | Sleep | 2204 | | NULL |
  58. | 86694 | root | 172.17.210.104:34434 | mdm | Sleep | 2234 | | NULL |
  59. | 86695 | root | 172.17.210.104:34441 | mdm | Sleep | 2204 | | NULL |
  60. | 86696 | root | 172.17.210.104:34442 | mdm | Sleep | 256 | | NULL |
  61. | 86697 | root | 172.17.210.104:35207 | mdm | Sleep | 2234 | | NULL |
  62. | 86698 | root | 172.17.210.104:35208 | mdm | Sleep | 2204 | | NULL |
  63. | 86699 | root | 172.17.210.104:35209 | mdm | Sleep | 2234 | | NULL |
  64. | 86700 | root | 172.17.210.104:35210 | mdm | Sleep | 2204 | | NULL |
  65. | 86701 | root | 172.17.210.104:35475 | mdm | Sleep | 2267 | | NULL |
  66. | 86702 | root | 172.17.210.104:35476 | mdm | Sleep | 2267 | | NULL |
  67. | 86703 | root | 172.17.210.104:35478 | mdm | Sleep | 2204 | | NULL |
  68. | 86704 | root | 172.17.210.104:35479 | mdm | Sleep | 256 | | NULL |
  69. | 86712 | root | 172.17.210.104:37471 | mdm | Sleep | 2040 | | NULL |
  70. | 86713 | root | 172.17.210.104:37472 | mdm | Sleep | 2040 | | NULL |
  71. | 86714 | root | 172.17.210.104:37473 | mdm | Sleep | 2040 | | NULL |
  72. | 86715 | root | 172.17.210.104:37474 | mdm | Sleep | 2040 | | NULL |
  73. | 86716 | root | 172.17.210.104:37475 | mdm | Sleep | 2040 | | NULL |
  74. | 86717 | root | 172.17.210.104:37476 | mdm | Sleep | 2040 | | NULL |
  75. | 86718 | root | 172.17.210.104:37477 | mdm | Sleep | 506 | | NULL |
  76. | 86719 | root | 172.17.210.104:37478 | mdm | Sleep | 2040 | | NULL |
  77. | 86720 | root | 172.17.210.104:37479 | mdm | Sleep | 506 | | NULL |
  78. | 86721 | root | 172.17.210.104:37480 | mdm | Sleep | 2040 | | NULL |
  79. | 86722 | root | 172.17.210.104:37481 | mdm | Sleep | 2040 | | NULL |
  80. | 86723 | root | 172.17.210.104:37482 | mdm | Sleep | 2040 | | NULL |
  81. | 86724 | root | 172.17.210.104:38325 | mdm_pro | Sleep | 1368 | | NULL |
  82. | 86725 | root | 172.17.210.104:38326 | mdm_pro | Sleep | 1368 | | NULL |
  83. | 86727 | root | 172.17.210.104:38428 | mdm_pro | Sleep | 1285 | | NULL |
  84. | 86728 | root | 172.17.210.104:38429 | mdm_pro | Sleep | 1285 | | NULL |
  85. | 86730 | root | 172.17.206.113:51041 | NULL | Sleep | 1269 | | NULL |
  86. | 86732 | root | 172.17.210.104:38468 | mdm_pro | Sleep | 1253 | | NULL |
  87. | 86733 | root | 172.17.210.104:38469 | mdm_pro | Sleep | 1253 | | NULL |
  88. | 86734 | root | 172.17.210.104:38470 | mdm_pro | Sleep | 1253 | | NULL |
  89. | 86735 | root | 172.17.210.104:38471 | mdm_pro | Sleep | 1253 | | NULL |
  90. | 86736 | root | 172.17.210.104:38605 | mdm_pro | Sleep | 1147 | | NULL |
  91. | 86737 | root | 172.17.210.104:38606 | mdm_pro | Sleep | 1147 | | NULL |
  92. | 86738 | root | 172.17.210.104:38607 | mdm_pro | Sleep | 1147 | | NULL |
  93. | 86739 | root | 172.17.210.104:38608 | mdm_pro | Sleep | 1147 | | NULL |
  94. | 86740 | root | 172.17.206.113:51064 | mdm | Sleep | 385 | | NULL |
  95. | 86741 | root | 172.17.210.205:55709 | NULL | Sleep | 1092 | | NULL |
  96. | 86742 | root | 172.17.210.205:55711 | mdm_test | Sleep | 1088 | | NULL |
  97. | 86743 | root | 172.17.210.205:55712 | mdm_test | Sleep | 786 | | NULL |
  98. | 86746 | root | 172.17.211.99:53039 | NULL | Sleep | 959 | | NULL |
  99. | 86747 | root | 172.17.211.99:53040 | mdm | Sleep | 958 | | NULL |
  100. | 86748 | root | 172.17.211.99:53080 | mdm_test | Sleep | 320 | | NULL |
  101. | 86750 | root | 172.17.211.99:53130 | mdm_test | Sleep | 800 | | NULL |
  102. | 86751 | root | 172.17.210.205:55718 | mdm_test | Sleep | 782 | | NULL |
  103. | 86752 | root | localhost | mdm_test | Query | 0 | init | SHOW PROCESSLIST |
  104. | 86755 | root | 172.17.206.113:51149 | NULL | Sleep | 122 | | NULL |
  105. | 86756 | root | 172.17.206.113:51150 | mdm | Sleep | 133 | | NULL |
  106. | 86759 | root | 172.17.206.113:51181 | mdm_test | Sleep | 124 | | NULL |
  107. | 86760 | root | 172.17.206.113:51183 | mdm | Sleep | 115 | | NULL |
  108. +-------+------+----------------------+----------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
  109. 102 rows in set (0.00 sec)

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

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

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
---略
trx_mysql_thread_id: 86200

现在杀死进程：
mysql> kill 86200;

Query OK, 0 rows affected (0.00 sec)


接下来阶段表的时候就很快了。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30936525/viewspace-2016604/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： MySQL ：Ignoring query to other database
下一篇： xtrabackup: Error: cannot open /../../base/xtrabackup_checkpoints
请登录后发表评论 登录
全部评论
urgel_babay

注册时间：2016-02-29

  • 博文量
    203
  • 访问量
    339116

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593