解决org.springframework.dao.DeadlockLoserDataAccessException
添加链接池后批量添加更新出现了死锁
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,但是这样耗时比较多
相关文章
- spring 2.5.6 错误:Context namespace element 'component-scan' and its parser class [org.springframework.context.annotation.ComponentScanBeanDefinitionParser] are only available on JDK 1.5 and higher
- org.springframework.web.multipart.MultipartException: Failed to parse multipart servlet request; nested exception is java.io.IOException: The temporary upload location [/tmp/tomcat.1428942566812653608
- java.lang.NoClassDefFoundError: org/springframework/web/context/WebApplicationContext
- mybatis+spring报错PropertyAccessException 1: org.springframework.beans.MethodInvocationException
- [解决]程序包org.springframework.boot不存在
- 解决org.openqa.selenium.WebDriverException: Unable to connect to host 127.0.0.1 on port 7055 after 45000 ms org.springframework.beans.BeanInstantiation
- how to resolve error message java.lang.ClassNotFoundException: org.springframework
- 【已解】Nacos Unable to start web server; nested exception is org.springframework.boot.web.server.WebSer
- java.lang.ClassNotFoundException: org.springframework.web.servlet.DispatcherServlet
- Caused by: org.springframework.context.annotation.ConflictingBeanDefinitionException
- SpringFramework注解的发展与演变,从Spring 1.x到Spring 5.x