ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
2010.10.30
要下班了，開發人員過來跟我說，為什麼他截斷一個表，才163條記錄。但是需要等待很長時間。
當時?想 應該是有等待事件，那就等著唄。於是我自己再Navicat 上操作他反應的那張表，結果出現下面的提示：
這明顯是出現了鎖，而且這種情況下是表鎖。我就問開發，是不是有人在操作這張表，他說就他一個人操作，他也早就沒操作了。
尼瑪，這點印證了當初師傅的話，錯誤操作的人，永遠都是會說：沒有啊，我什麼都沒有做啊。
先檢視一下程式裡面有沒有鎖：
很明顯出現了鎖，也是他操作的那個庫。
要下班了，開發人員過來跟我說，為什麼他截斷一個表，才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掉。
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部落格 ” ，連結：http://blog.itpub.net/30936525/viewspace-2016604/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Lock wait timeout exceeded; try restarting transactionAIREST
- 'Lock wait timeout exceeded; try restarting transaction'問題AIREST
- Lock wait timeout exceeded; try restarting transaction引數控制AIREST
- mysql, Lock wait timeout exceeded; try restarting 解決MySqlAIREST
- 鎖:innodb_lock_wait_timeout和 innodb_rollback_on_timeout?AI
- MySQL更新卡住報錯lock wait timeoutMySqlAI
- ORA-02049: timeout: distributed transaction waiting for lockAI
- Oracle 11g DDL 的 wait選項（DDL_LOCK_TIMEOUT）OracleAI
- ERROR 1206 (HY000): The total number of locks exceeds the lock table sizeError
- 設定mysql 事務鎖超時時間 innodb_lock_wait_timeoutMySqlAI
- mysql的wait_timeout和interactive_timeoutMySqlAI
- TX鎖（Transaction Lock）分析 (zt)
- 【MySQL】淺析interactive_timeout和wait_timeoutMySqlAI
- mysql死鎖deadlock相關幾個系統變數innodb_lock_wait_timeoutMySql變數AI
- MySQL之wait_timeout和interactive_timeout引數MySqlAI
- Mysql引數解釋---wait_timeout、interactive_timeoutMySqlAI
- mysql-SQL Error: 1205, SQLState: 41000MySqlError
- MySQL中wait_timeout的坑MySqlAI
- 遭遇DFS LOCK HANDLE wait event,AI
- 【Mysql】MySQL中interactive_timeout和wait_timeout的區別MySqlAI
- Oracle 11G ddl_lock_timeoutOracle
- ERROR 1010 (HY000): Error dropping databaseErrorDatabase
- interactive_timeout和wait_timeout引數區別和設定策略AI
- ERROR 1524 (HY000): Plugin is not loadedErrorPlugin
- ERROR 2026 (HY000): SSL connection error: unknown error numberError
- MySQL 5.5 -- innodb_lock_wait 鎖 等待MySqlAI
- Job for firewalld.service failed because a timeout was exceeded. See "systemctl status firewalld.serAI
- MySQL "ERROR 1010(HY000):Error dropping database(canMySqlErrorDatabase
- ERROR 1114 (HY000) The table '' is fullError
- MYSQL ERROR 2003 (HY000) CanMySqlError
- [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11Error
- 【11g新特性】DDL_LOCK_TIMEOUT的作用
- 設定 ddl_lock_timeout來避免truncate 報錯
- MySQL：引數wait_timeout和interactive_timeout以及空閒超時的實現MySqlAI
- Error 600 encountered while recovering transaction (10, 0)ErrorWhile
- rac例項報SVR4 Error: 49: Disc quota exceededVRError
- OGG Get Error "Statistics reply buffer exceeded. Results truncated..."Error
- ERROR 1290 (HY000): The MySQL server is running withErrorMySqlServer