ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的问题解决(备忘)
来源:互联网 发布:2015伊戈达拉数据 编辑:程序博客网 时间:2024/05/16 18:08
今天让运维将测试库的数据到了导出一份到自己电脑本地来做测试,数据量较大(几百万条吧),系统运行起来之后查看订单列表数据很慢(估计是自己电脑配置太low了,刷新列表的时候一直在加载),然后就想删除部分数据,在删除的时候出现报错:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。(把这个问题记录下来,防止以后再次遇到了方便处理)。
DELETE FROM t_loan_apply WHERE DATE(addtime) < '2017-06-01';
网上一通查询,解决方式如下:
1、先查看数据库的事务隔离级别:
select @@tx_isoloation;
REPEATABLE-READ // MySQL默认的事务隔离级别就是REPEATABLE-READ
2、然后查看当前数据库的线程情况:
SHOW FULL PROCESSLIST;
没有看到正在执行的很慢SQL记录线程,再去查看innodb的事务表INNODB_TRX,看下里面是否有正在锁定的事务线程,看看ID是否在show full processlist里面的sleep线程中,如果是,就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了,我们需要手动kill掉。
SELECT * FROM information_schema.INNODB_TRX;
3、发现有id为616694的sql,需要手动kill掉
KILL 616694;
kill之后,再去执行上面的delete语句,就可以执行成功了。
注意:MySQL是自动提交事务的(即:autocommit=1),可以使用 show variables like 'autocommit' 或者 select @@autocommit 查看当前数据库是否为自动提交事务;若autocommit的值不是1还可以使用set global autocommit = 1 将自动提交设置为开启。
- 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
- 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
- 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
- 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
- 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
- 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的解决办法
- opencv-python填充算法(水漫填充)
- ES8(ECMAScript 2017) 语言规范发布,附ES6,ES7,ES8规范文档下载
- 可拖动的div(拖动音量调效果)
- Caused by: android.util.AndroidRuntimeException: Calling startActivity() from outside of an Activity
- logistic regression
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的问题解决(备忘)
- 安卓键盘弹出 布局上移,清单文件设置键盘属性失效
- OpenLayers部分中文API
- Ajax 学习笔记
- uvalive6859 凸包
- vue入门
- Spring.profiles多环境配置
- NOIP 2015 斗地主
- React学习的各种资源