1205 Lock wait timeout exceeded try restarting transaction

蔚落 2022-05-29 08:06 307阅读 0赞

早上执行语句：

update report\_user\_info set cell = replace(cell,'"','') where id<10000;

就报了标题上面的错误。

然后我去查找原因 是因为我早上的一个动作导致 report\_user\_info表锁住了。

方法1：

mysql -uroot -pmypassword -e"show processlist"|grep -i "locked"

| 103466 | root             | localhost           | report\_user        | Killed  | 9646 | query end                   | update report\_user\_info set cell = replace(cell,'"','')                                              |         0 |      15885184 |  15885185 |

方法2：

show engine innodb status\\G

mysql tables in use 1, locked 1  
ROLLING BACK 111427 lock struct(s), heap size 17168824, 16000230 row lock(s), undo log entries 13824412  
MySQL thread id 103466, query id 3367705981 localhost root query end  
update report\_user\_info set cell = replace(cell,'"','')  
TABLE LOCK table \`report\_use\`.\`report\_user\_info\` trx id 8DCD01E4 lock mode IX  
RECORD LOCKS space id 13812 page no 20 n bits 368 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode X  
RECORD LOCKS space id 13812 page no 19027 n bits 360 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode X  
RECORD LOCKS space id 13812 page no 19028 n bits 320 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode X  
RECORD LOCKS space id 13812 page no 19026 n bits 352 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode X  
RECORD LOCKS space id 13812 page no 19030 n bits 248 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode X  
RECORD LOCKS space id 13812 page no 19029 n bits 296 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode X  
RECORD LOCKS space id 13812 page no 19031 n bits 240 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode X  
RECORD LOCKS space id 13812 page no 19031 n bits 368 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode Xlocks gap before rec  
RECORD LOCKS space id 13812 page no 19031 n bits 368 index \`PRIMARY\` of table \`report\_user\`.\`report\_user\_info\` trx id 8DCD01E4 lock\_mode X  
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS  
\----------------------------  
END OF INNODB MONITOR OUTPUT

show processlist;

| 103466 | root             | localhost           | report\_user        | Locked  | 9646 | query end                   | update report\_user\_info set cell = replace(cell,'"','')                                              |         0 |      15885184 |  15885185 |

kill掉这个进程：  
  
mysql>  kill 103466 ;  
Query OK, 0 rows affected (0.00 sec)

当然，可能造成死锁的事务比较的大，他在processlist里面驻留的时间比较的长。