添加链接池后批量添加更新出现了死锁

org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in file [E:\java\project\bim-service\target\classes\mapper\ApiPropertiesMapper.xml]
### The error may involve com.tydt.bim.dao.ApiPropertiesMapper.batchAddProperties-Inline
### The error occurred while setting parameters
### SQL: insert into api_properties (`guid`,`name`,`file_id`,`value`,`unit`) values (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?) ON DUPLICATE KEY UPDATE `value`=values(`value`),`unit`=values(`unit`)
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

INSERT ON DUPLICATE KEY在执行时,innodb引擎会先判断插入的行是否产生重复key错误

如果存在,在对该现有的行加上S(共享锁)锁,返回该行数据给mysql,然后mysql执行完duplicate后的update操作,然后对该记录加上X(排他锁),最后进行update写入

如果有两个事务并发的执行同样的语句,那么就会产生death lock

解决方法:

1、尽量不对存在多个组合唯一键的table上使用该语句

2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

  先用select查看是否存在,再决定是insert还是update,但是这样耗时比较多


评论关闭
IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

mysql 5.7开启sql日志的配置