溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

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

發(fā)布時(shí)間：2020-08-16 20:07:52 來源：ITPUB博客 閱讀：216 作者：urgel_babay 欄目：MySQL數(shù)據(jù)庫
2010.10.30
     要下班了，開發(fā)人員過來跟我說，為什么他截?cái)嘁粋€(gè)表，才163條記錄。但是需要等待很長時(shí)間。
當(dāng)時(shí)?想 應(yīng)該是有等待事件，那就等著唄。于是我自己再Navicat 上操作他反應(yīng)的那張表，結(jié)果出現(xiàn)下面的提示：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
這明顯是出現(xiàn)了鎖，而且這種情況下是表鎖。我就問開發(fā)，是不是有人在操作這張表，他說就他一個(gè)人操作，他也早就沒操作了。
尼瑪，這點(diǎn)印證了當(dāng)初師傅的話，錯(cuò)誤操作的人，永遠(yuǎn)都是會(huì)說：沒有啊，我什么都沒有做啊。

先查看一下進(jìn)程里面有沒有鎖：

點(diǎn)擊(此處)折疊或打開

  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>
很明顯出現(xiàn)了鎖，也是他操作的那個(gè)庫。

   當(dāng)然如果沒有看到正在執(zhí)行的慢SQL記錄線程，再去查看innodb的事務(wù)表INNODB_TRX，看下里面是否有正在鎖定的事務(wù)線程，看看ID是否在show full processlist里面的sleep線程中，如果是，就證明這個(gè)線程事務(wù)一直沒有commit或者rollback而是卡住了，我們需要手動(dòng)kill掉。

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

現(xiàn)在殺死進(jìn)程：
mysql> kill 86200;

Query OK, 0 rows affected (0.00 sec)


接下來階段表的時(shí)候就很快了。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手