1205 Lock wait timeout exceeded try restarting transaction
来源:互联网 发布:199it大数据工具导航 编辑:程序博客网 时间:2024/06/05 18:04
早上执行语句:
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)
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里面驻留的时间比较的长。
转载:http://blog.csdn.net/lxpbs8851/article/details/7962435
0 0
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
- 1205 Lock wait timeout exceeded try restarting transaction
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- 1205 Lock wait timeout exceeded try restarting transaction .
- 1205 Lock wait timeout exceeded try restarting transaction
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- fedora14-yum源的配置使用详解-zz150122
- MySQL 事务没有提交导致 锁等待 Lock wait timeout exceeded
- MDS图示聚类结果
- mac下android studio打不开sdk manage
- MySQL 死锁导致无法查询解决
- 1205 Lock wait timeout exceeded try restarting transaction
- 启动innodb_monitor的方法
- 怎么看mysql有没阻塞
- Include EPS files in LaTeX
- chm
- iOS 两个视频首尾合并- AVAssetExportSession
- MySQL死锁导致无法查询
- Linux下 编译C++
- MySQL查询超时问题的解决