江苏龙网

  1. 首页 > 人文 > >

mysql线上频出MySQL死锁问题！分享一下教科书般的排查和分析过程


1. 日志
1.1 业务日志
一直平稳运行大半年的代码 ， 突然在最近几天频出现死锁异常 ， 业务机器大概每一两天在业务高峰期发生一次如下的业务日志：
INFO 57553 --- [ConsumerThread2] org.example.controller.TestController : 全局链路跟踪id:2的日志：[TransactionReqVO(userId=4, money=4), TransactionReqVO(userId=2, money=2), TransactionReqVO(userId=5, money=5)] INFO 57553 --- [ConsumerThread1] org.example.controller.TestController : 全局链路跟踪id:1的日志：[TransactionReqVO(userId=5, money=5), TransactionReqVO(userId=1, money=1), TransactionReqVO(userId=4, money=4)]ERROR 57553 --- [ConsumerThread2] org.example.controller.TestController : 全局链路跟踪id:2的异常：### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock try restarting transaction### The error may exist in org/example/mapper/TestTableMapper.java (best guess)### The error may involve org.example.mapper.TestTableMapper.update-Inline### The error occurred while setting parameters### SQL: UPDATE test_table SET money = money + ? WHERE user_id = ?### Cause: com.mysql.jdbc.exceptions.jdbc4.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.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock try restarting transactionorg.springframework.dao.DeadlockLoserDataAccessException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock try restarting transaction### The error may exist in org/example/mapper/TestTableMapper.java (best guess)### The error may involve org.example.mapper.TestTableMapper.update-Inline### The error occurred while setting parameters### SQL: UPDATE test_table SET money = money + ? WHERE user_id = ?### Cause: com.mysql.jdbc.exceptions.jdbc4.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.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock try restarting transaction at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:266) ~[spring-jdbc-5.0.13.RELEASE.jar:5.0.13.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.0.13.RELEASE.jar:5.0.13.RELEASE] at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-2.0.1.jar:2.0.1] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-2.0.1.jar:2.0.1] at com.sun.proxy.$Proxy59.update(Unknown Source) ~[na:na] at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294) ~[mybatis-spring-2.0.1.jar:2.0.1] at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67) ~[mybatis-3.5.1.jar:3.5.1] at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58) ~[mybatis-3.5.1.jar:3.5.1] at com.sun.proxy.$Proxy62.update(Unknown Source) ~[na:na] at org.example.service.impl.TestServiceImpl.update(TestServiceImpl.java:16) ~[classes/:na] at org.example.manager.impl.BizManagerImpl.transactionMoney(BizManagerImpl.java:25) ~[classes/:na] at org.example.manager.impl.BizManagerImpl$$FastClassBySpringCGLIB$$824241b9.invoke(


推荐阅读


上一篇：北青网|璀璨韶华 御见金笙记

下一篇：北青网|周保林：把知识藏进故事 为小朋友讲述祖国前沿科技