多线程更新/插入同一表出现数据库死锁(Dead Lock)问题解决

Java技术 潘老师 2个月前 (03-15) 343 ℃ (0) 扫码查看

今天在使用多线程在同一个事务中往MySQL数据库的同一张表中批量插入(insert)和更新(update)数据,然后就出现了死锁报错,即提示出现Dead Lock,类似提示如下:

org.springframework.dao.DeadlockLoserDataAccessException: com.xxxMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction

问题分析

产生数据库表死锁的原因比较多,建议看下MySQL数据库死锁的产生原因分析以及解决办法,我这里可能存在原因可能是这里提到的行级锁升级为表级锁的原因,但也不确定,也可能是在一个repeatable read级别(mysql默认隔离级别)的事务中采用先update再insert的方式,然后这段代码并发执行时造成的死锁。这里我通过第二种原因的解决了这个问题,毕竟我这里就是先更新后插入,然后多线程并发执行时,导致了死锁出现。

示例演示

示例如下: 如表student中只有一条记录 ,该记录的code 字段值为1,且该表在code上建立索引。
transaction A:

update student set yn=1 where code = '2'; insert into student (code, …) values ('2', ...);

transaction B:

update student set yn=1 where code = '3'; insert into student (code, …) values ('3', ...);

此时A、B事务会出现死锁现象。

原因:

在repeatable级别,update/select … for update/delete不存在的记录时会持有一个X(互斥锁)gap锁(间隙锁),当执行顺序如下时:

T-A update student set yn=1 where code = '2'; ——T-A获得一个X的next-key锁

T-B update student set yn=1 where code = '3'; ——T-B获得一个X的next-key锁

T-B insert into student (code, …) values ('3', ...); ——T-B需获取一个insert intention gap lock,需等待T-A的next-key锁释放

T-A insert into student (code, …) values ('2', ...); ——T-A同理,等待T-B的next-key锁释放死锁产生

解决方案:

第一种方案:将事务隔离级别降低到read committed即可,此时无gap锁,T-A、T-B无冲突。

第二种方案:事务隔离级别仍然为repeatable read,但将参数innodb_locks_unsafe_for_binlog置为true。

这里我就是采用了第一种方案解决的,直接Spring事务注解如下:

@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)

另外补充一些知识:

InnoDB行锁实际锁的是索引记录,为了防止死锁的产生以及维护所需要的隔离级别,在执行sql语句的全过程中,innodb必须对所需要修改的行每条索引记录上锁。如此一来,如果你执行的 UPDATE 没有很好的索引,那么会导致锁定许多行:

update employees set store_id = 0 where store_id = 1;
---TRANSACTION 1EAB04, ACTIVE 7 sec
633 lock struct(s), <strong>heap size 96696</strong>, 218786 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 47 localhost root
show engine innodb status

上面的 employees 表 store_id 列没有索引。注意 UPDATE 已经执行完成(没有提交),但依然有 218786 个行锁没有释放,还有一个undo记录。这意味着只有一行被更改,但却持有了额外的锁。堆大小(heap size)代表了分配给锁使用的内存数量。

在 REPEATABLE-READ 级别,事务持有的 每个锁 在整个事务期间一直被持有。

在 READ-COMMITED 级别,事务里面特定语句结束之后,不匹配该sql语句扫描条件的锁,会被释放。

下面是上述相同的 UPDATE 在 READ-COMMITED 级别下的结果:

---TRANSACTION 1EAB06, ACTIVE 11 sec
631 lock struct(s), <strong>heap size 96696</strong>, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 62 localhost root
show engine innodb status

可以看到 heap size 没有变化,但是现在我们只持有一个行锁。无论什么隔离级别下,InnoDB 会为扫描过的每条索引记录创建锁,不同的是在 RC 模式,一旦语句执行完毕(事务未必完成),不符合扫描条件的记录上的锁会被随即释放。释放这些锁后,堆内存并不会马上释放,所以heap size看到与 RR 模式是一样的,但是持有的锁数量明显小了很多。

这也就意味着在 RC 级别下的事务A,只要A的UPDATE 语句 完成了,其它事务可以修改A中也扫描过的行,但在 RR 级别下不允许。

总结

以上就是多线程更新/插入同一表出现数据库死锁(Dead Lock)问题解决的全部内容,希望对你有帮助。


版权声明:本站所有文章,如无特殊说明,均为本站原创。转载请务必注明文章来源,谢谢支持。
本文链接:https://www.panziye.com/java/4659.html
喜欢 (0)
请潘老师喝杯Coffee吧!】
分享 (0)
用户头像
发表我的评论
取消评论
表情 贴图 签到 代码

Hi,您需要填写昵称和邮箱!

  • 昵称【必填】
  • 邮箱【必填】
  • 网址【可选】