记一次mysql(innodb)死锁分析

miss_cxy

已于 2022-03-23 11:28:12 修改

阅读量269

点赞数 1

分类专栏：技术分析文章标签： mysql

于 2021-07-05 20:30:21 首次发布

本文链接：https://blog.csdn.net/weixin_39265427/article/details/118497902

版权

本文详细记录了一次MySQL InnoDB引擎下遇到的死锁情况，从死锁日志、业务场景到可能的原因分析，再到解决方案的实现。死锁主要是由于表中缺乏索引，导致全表扫描时产生锁等待闭环。通过增加索引，可以有效避免此类死锁的发生。

摘要由CSDN通过智能技术生成

记一次mysql(innodb)死锁分析

我是带着比较复杂的心情来写这个文章的，首先，因为mysql innodb引擎模式下的内容实在较多，我没有把握能说明清除；其次，分析死锁的原理我也只是根据当前这种原因导致的死锁场景做出分析，并不代表我能分析清楚所有的死锁场景。总之，这边文章可供参考的范围十分有限，请各位童鞋具体情况具体分析，那就开始吧~

一、死锁日志出现

第一次在服务器看到我写的模块出现了死锁情形，纳尼？？？不敢相信呀，要知道，对于一个资深程序员来说死锁，以为这你这能力水平是很差的呢，当时被羞愧之心充满内心，同时也痛恨着那位发现死锁bug的臭同事的。不爽归不爽，含着泪也是要把问题原因找出来的呀，呜呜~~（此处问废话，请略过）

上日志(sql非真实sql)

aused by: 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 involve defaultParameterMap
### The error occurred while setting parameters
### SQL: DELETE FROM test    WHERE  a = ?       AND b = ?    AND c = ?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; 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:263)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
        at com.sun.proxy.$Proxy20.delete(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:311)
        ... 59 more
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:975)
        at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:392)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:494)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:213)
        at sun.reflect.GeneratedMethodAccessor6530.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
        ... 64 more