一.错误日志
场景:多线程插入数据库,大概100个线程都在同一张表做插入。
### 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 URL [jar:file:/bk-product-server.jar!/BOOT-INF/lib/bk_product_server_dao-5.15.103-SNAPSHOT.jar!/com/bk/product/repository/granting/StrategyRunRespository.xml] ### The error may involve com.bk.product.repository.granting.StrategyRunRespository.addBatchStrategyRunData-Inline ### The error occurred while setting parameters ### SQL: insert into granting_strategy_run_result ( id, granting_strategy_run_id, biz_id, biz_source, model_id, model_type, model_name, model_query_item, model_sort, granting_status, result_info, credit_investigation_id, sn, credit_amount, tags, del_flag, create_by, create_date, update_by, update_date, client_id ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?, now(), ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?, now(), ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?, now(), ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?, now(), ? ) ### 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 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
二.分析
初步分析是由于100个线程在同一时间做100个事务提交,事务排队时间过长,重新发起事务,但是由于其他事务在做插入,查询,更新,锁定表,重新发起事务。导致死锁的发生。
目前还不能确定是事务的问题,还是代码的问题。
原因只是猜测,没有实际测试,等有时间详细测试
问题在于mysql插入是否会锁表,事务是否会锁表,什么情况下行的死锁会发生,表的死锁会发生
三.解决
暂时解决办法:插入表不用事务
四.参考资料
https://blog.csdn.net/weixin_33739627/article/details/85892433?utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.control
http://www.heartthinkdo.com/?p=910#422
https://blog.csdn.net/weixin_33966095/article/details/92259789?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-1.control&spm=1001.2101.3001.4242
Deadlock found when trying to get lock; try restarting transaction