再执行mysql的DML时,出现ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,甚至是特别简单的DML时也会出现,简单的来看执行超时,这么简单的sql怎么会执行超时呢?会不会是其他操作把表锁着了,或者是阻塞了呢?最大可能是表被之前哪儿个操作锁着了。
一步一步的查询:
mysql> show full processlist;
mysql> show full processlist;
+-------+----------+----------------------+---------+---------+-------+--------------+--------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+----------------------+---------+---------+-------+--------------+--------------------------------------------------------------------------------------------------------------------------+
| 6140 | tuan_sql | 192.168.100.39:42806 | tao800 | Sleep | 3474 | | NULL |
| 6590 | tuan_sql | 192.168.100.39:45777 | tao800 | Sleep | 252 | | NULL |
| 7497 | tuan_sql | 192.168.100.39:51955 | tao800 | Sleep | 48 | | NULL |
| 8150 | tuan_sql | 192.168.100.39:56589 | tao800 | Query | 59666 | Sending data| INSERT INTO tao_search_stat_backups SELECT * FROM tao_search_statistics WHERE id < '171990582' AND st_date > '170286154' |
| 14557 | tuan_sql | 192.168.100.39:51961 | tao800 | Sleep | 2 | | NULL |
| 14817 | tuan_sql | 192.168.100.39:54749 | tao800 | Sleep | 4 | | NULL |
| 14896 | tuan_sql | 192.168.100.39:55558 | tao800 | Sleep | 4 | | NULL |
| 14948 | tuan_sql | 192.168.100.39:56130 | tao800 | Sleep | 262 | | NULL |
| 14960 | tuan_sql | 192.168.100.39:56231 | tao800 | Sleep | 6 | | NULL |
| 15005 | tuan_sql | 192.168.100.39:56711 | tao800 | Sleep | 112 | | NULL |
| 15144 | tuan_sql | 192.168.100.39:58009 | tao800 | Sleep | 241 | | NULL |
| 15169 | tuan_sql | 192.168.100.39:58068 | tao800 | Sleep | 241 | | NULL |
| 15291 | tuan_sql | 192.168.100.39:59325 | tao800 | Sleep | 50 | | NULL |
| 15341 | tuan_sql | 192.168.100.39:59589 | tao800 | Sleep | 1894 | | NULL |
| 15381 | tuan_sql | 192.168.100.39:59878 | tao800 | Sleep | 4 | | NULL |
| 15445 | tuan_sql | 192.168.100.39:60312 | tao800 | Sleep | 584 | | NULL |
| 15479 | tuan_sql | 192.168.100.39:60616 | tao800 | Sleep | 908 | | NULL |
| 15542 | tuan_sql | 192.168.100.39:33002 | zhe_oem | Sleep | 303 | | NULL |
| 15543 | tuan_sql | 192.168.100.39:33004 | tao800 | Sleep | 303 | | NULL |
| 15554 | tuan_sql | 192.168.100.39:33076 | tao800 | Sleep | 262 | | NULL |
| 15559 | tuan_sql | 192.168.100.39:33116 | tao800 | Sleep | 131 | | NULL |
| 15577 | tuan_sql | 192.168.100.39:33302 | tao800 | Sleep | 74 | | NULL |
| 15587 | tuan_sql | 192.168.100.39:33364 | tao800 | Query | 0 | NULL | show full processlist |
+-------+----------+----------------------+---------+---------+-------+--------------+--------------------------------------------------------------------------------------------------------------------------+
很明显之前的一个DML正在执行中,并且和将要执行的表是同一个表,都是insert或者操作同一行数据,我出现这个错误的原因两个DML都是insert语句,肯定会锁表了。
那么接下来,我们就需要kill掉这个语句了:
mysql> kill 8150;
Query OK, 0 rows affected (0.00 sec)
再执行插入或者更新,就成功了。