Mysql数据库更新操作导致死锁问题
来源:互联网 发布:python搭建web服务器 编辑:程序博客网 时间:2024/06/04 17:50
最近维护项目发现的一个有意思的问题,写篇文章记录一下。
项目的问题是数据库发生了死锁,在盘查的所有的业务代码后我认为是“单条”批量update语句需要锁表而引发的问题
项目是基于spring的webservice,采用mysql数据库innodb引擎,问题涉及的主要业务如下:
业务1:系统会定期盘点数据(以下称为自动盘点),盘点中一个必要的数据不是存放在本地,需要通过http请求远程服务器,所以会导致服务层的一个方法运行缓慢(大约需要5小时)
业务2:管理员可以对某些数据进行手动盘点,手动盘点也需要通过http请求远程服务器,但是由于手动盘点数据一般较少,所以手动盘点只需要执行数分钟。
我查看了一下spring的配置,发现原先开发的时候为了省事,直接采用spring自动管理事务方式,代理了service层的所有方法。我们知道update事务需要获取mysql的写锁,由于数据库引擎采用的是innodb,在有索引的情况下会使用行锁,这样只要更新完立即释放锁,理论上就可以避免死锁了。
在咨询了老员工,发现某条数据更新失败不会影响整体的数据完整性,于是我将自动盘点更改为手动事务,获取到数据之后立即写入数据库,但是还是会与手动盘点冲突,问题还是存在。我查看了一下mybatis的更新语句,发现手动盘点有一条这样的语句:
update `A` set 省略 where id in(select id from `B` where 省略)
id在A表中为主键,检查发现update查询会锁住A表。
这样问题就明了了,手动盘点需要锁住整个表,但是自动判断锁住了某条记录,这样手动盘点就会因为获取锁超时而失败。
于是我想当然的把sql改成下面这样
update `A` set 省略 where id in(1,2,3,4,5....)
发现在in查询中条件不多的情况下(我机器上市小于3)会锁住需要操作的行,条件多则锁表
我又改成了这样
update `A` set 省略 where id = 1 or id=2 or id=3 ......
执行查询计划和in查询一样,在条件不多锁行,条件多了依旧锁表
将批量的update语句改为多条update后,问题暂时没有复现。
最后我做了一个如下实验,有兴趣的朋友可以自己做一下,环境为mysql5.6,innodb引擎
创建表语句:
CREATE TABLE `update_table` ( `id` int(11) NOT NULL, `num` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
执行如下查询计划,结果如下:
EXPLAIN UPDATE `update_table` SET `num`='1' WHERE id =1 or id=2 or id=3;
这里重点关注下type和rows,可以发现是锁行的
接着把条件增多,执行查询计划:
EXPLAIN UPDATE `update_table` SET `num`='1' WHERE id =1 or id=2 or id=3 or id=4 or id=5;
发现type变成了index,行数等于整个表的行数,表被锁
使用in和or一样,条件少锁行,多锁表
最后我建议若业务对高并发比较敏感,使用单条多次update语句,以免将这个表锁住,就像下面这样,当然前提是where能用到索引,不然依旧会锁表
EXPLAIN UPDATE `update_table` SET `num`='1' WHERE id =1EXPLAIN UPDATE `update_table` SET `num`='1' WHERE id =2EXPLAIN UPDATE `update_table` SET `num`='1' WHERE id =3....
- Mysql数据库更新操作导致死锁问题
- mysql的插表以及更新操作导致的死锁问题
- mysql更新死锁问题
- MySQL更新死锁问题
- MySQL更新死锁问题
- 数据库死锁导致分布事务中大批量更新数据库不成功
- [数据库]mysql死锁问题分析
- mysql数据库更新操作
- 关于使用Spring导致c3p0数据库死锁问题
- 误用Connection.setAutoCommit导致的数据库死锁问题
- 转载: 关于使用Spring导致c3p0数据库死锁问题 zz
- 关于使用Spring导致c3p0数据库死锁问题
- H2 数据库,进程死锁导致访问异常的问题
- MySql 数据库操作(持续更新...)
- mysql数据库批量更新操作
- mysql更新死锁
- DllMain中不当操作导致死锁问题的分析--死锁介绍
- DllMain中不当操作导致死锁问题的分析--DisableThreadLibraryCalls对DllMain中死锁的影响
- 站在巨人的肩膀上—英语
- ExecutorService的shutdown
- fir.im Log Guru 正式开源,快速找到 iOS 应用无法安装的原因
- Problem A: 第一个类
- Linux根文件之lib库制作详解
- Mysql数据库更新操作导致死锁问题
- C语言数据结构——子串在主串中的定位函数
- Eigen3.3.3安装配置+VS2015+Cmake
- Problem B: 建造一间教室
- MATLAB测试机器零阈值的大小
- 【转载】锁存器、寄存器、触发器区别
- 第2章 编程问题 2.2节 1
- 3月23日成为C++游戏服务器工程师的学习收获
- 字符流的读写相关