Deadlock found when trying to get lock，mysql死锁

谁借莪１个温暖的怀抱￠ 2023-02-17 11:10 100阅读 0赞

最近遇到一个死锁问题，我整理了一下，写了一个例子。

初始化表

国籍表：

  1. CREATE TABLE `nationality` (
  2. `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  3. `type` VARCHAR ( 64 ) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  4. `desc` VARCHAR ( 255 ) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  5. `count` INT ( 11 ) DEFAULT NULL,
  6. PRIMARY KEY ( `id` ) 
  7. ) ENGINE = INNODB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

用户表：

  1. CREATE TABLE `person` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  4.   `desc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  5.   `nationality_id` int(11) NOT NULL,
  6.   PRIMARY KEY (`id`),
  7.   KEY `nationality_fk` (`nationality_id`),
  8.   CONSTRAINT `nationality_fk` FOREIGN KEY (`nationality_id`) REFERENCES `nationality` (`id`)
  9. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
功能代码

下面是一个很简单的功能。
功能如下：新增一个用户，并且往把该用户所在国籍的人数加1。

新增国籍人数的sql：

  1. <insert id="updateCount">
  2.         update nationality
  3.         set `count` = `count` + #{increase}
  4.         where id = #{id}
  5.     </insert>

创建用户的sql：

  1. <insert id="create">
  2.         insert into
  3.         person
  4.         (`name`,`desc`,`nationality_id`)
  5.         values(
  6.         #{name},
  7.         #{desc},
  8.         #{nationalityId})
  9.     </insert>

创建用户的service代码：

  1. @Service
  2. public class PersonService {
  3.     private static final Integer CHINA_ID = 1;
  4.     @Autowired
  5.     private PersonMapper personMapper;
  6.     @Autowired
  7.     private NationalityMapper nationalityMapper;
  9.     @Transactional
  10.     public void create(String name, String desc) {
  11.         Person person = new Person();
  12.         person.setName(name);
  13.         person.setDesc(desc);
  14.         person.setNationalityId(CHINA_ID);
  15.         personMapper.create(person);
  17.         try {
  18.             //这里加了一个延迟，方便测试
  19.             Thread.sleep(5000L);
  20.         } catch (Exception e) {
  21.             throw new RuntimeException(e);
  22.         }
  24.         nationalityMapper.updateCount(CHINA_ID, 1);
  25.     }
  26. }
测试

模拟并发创建用户：

  1. @SpringBootApplication
  2. @MapperScan("com.example.bootmybatis.dao")
  3. public class BootmybatisApplication {
  5.     public static void main(String[] args) {
  6.         ApplicationContext applicationContext = SpringApplication.run(BootmybatisApplication.class, args);
  7.         PersonService personService = applicationContext.getBean(PersonService.class);
  8.         new Thread(() -> personService.create("测试1", "无")).start();
  9.         new Thread(() -> personService.create("测试2", "无")).start();
  10.     }
  12. }

控制台报错如下：

  1. Exception in thread "Thread-155" org.springframework.dao.DeadlockLoserDataAccessException: 
  2. ### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  3. ### The error may involve defaultParameterMap
  4. ### The error occurred while setting parameters
  5. ### SQL: update nationality         set `count` = `count` + ?         where id = ?
  6. ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  7. ; 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
  8.     at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
  9.     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
  10.     at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
  11.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
  12.     at com.sun.proxy.$Proxy57.insert(Unknown Source)
  13.     at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
  14.     at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
  15.     at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
  16.     at com.sun.proxy.$Proxy59.updateCount(Unknown Source)
  17.     at com.example.bootmybatis.service.PersonService.create(PersonService.java:37)
  18.     at com.example.bootmybatis.service.PersonService$$FastClassBySpringCGLIB$$c378fac9.invoke(<generated>)
  19.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  20.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  21.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  22.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  23.     at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)
  24.     at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
  25.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  26.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  27.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  28.     at com.example.bootmybatis.service.PersonService$$EnhancerBySpringCGLIB$$85b28c3f.create(<generated>)
  29.     at com.example.bootmybatis.BootmybatisApplication.lambda$main$0(BootmybatisApplication.java:16)
  30.     at java.lang.Thread.run(Thread.java:745)
  31. Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  32.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
  33.     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
  34.     at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  35.     at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
  36.     at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
  37.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051)
  38.     at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
  39.     at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
  40.     at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
  41.     at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
  42.     at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
  43.     at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
  44.     at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
  45.     at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
  46.     at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
  47.     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  48.     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  49.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  50.     at java.lang.reflect.Method.invoke(Method.java:498)
  51.     at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
  52.     at com.sun.proxy.$Proxy69.update(Unknown Source)
  53.     at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
  54.     at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
  55.     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  56.     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  57.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  58.     at java.lang.reflect.Method.invoke(Method.java:498)
  59.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
  60.     ... 19 more

查看数据库只有demo2创建成功了。

查看mysql死锁日志如下，（可以通过SHOW ENGINE INNODB STATUS 查看最近死锁日志）

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2020-06-11 15:55:25 0x3d8c
  5. *** (1) TRANSACTION:
  6. TRANSACTION 164396, ACTIVE 5 sec starting index read
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  9. MySQL thread id 20, OS thread handle 17124, query id 341 localhost 127.0.0.1 root updating
  10. update nationality
  11.         set `count` = `count` + 1
  12.         where id = 1
  13. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  14. RECORD LOCKS space id 436 page no 3 n bits 72 index PRIMARY of table `boot`.`nationality` trx id 164396 lock_mode X locks rec but not gap waiting
  15. Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
  16.  0: len 4; hex 80000001; asc     ;;
  17.  1: len 6; hex 00000002822a; asc      *;;
  18.  2: len 7; hex 3f000001f521c4; asc ?    ! ;;
  19.  3: len 5; hex 4348494e41; asc CHINA;;
  20.  4: len 6; hex e4b8ade59bbd; asc       ;;
  21.  5: len 4; hex 80000002; asc     ;;
  23. *** (2) TRANSACTION:
  24. TRANSACTION 164397, ACTIVE 5 sec starting index read, thread declared inside InnoDB 5000
  25. mysql tables in use 1, locked 1
  26. 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  27. MySQL thread id 21, OS thread handle 15756, query id 340 localhost 127.0.0.1 root updating
  28. update nationality
  29.         set `count` = `count` + 1
  30.         where id = 1
  31. *** (2) HOLDS THE LOCK(S):
  32. RECORD LOCKS space id 436 page no 3 n bits 72 index PRIMARY of table `boot`.`nationality` trx id 164397 lock mode S locks rec but not gap
  33. Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
  34.  0: len 4; hex 80000001; asc     ;;
  35.  1: len 6; hex 00000002822a; asc      *;;
  36.  2: len 7; hex 3f000001f521c4; asc ?    ! ;;
  37.  3: len 5; hex 4348494e41; asc CHINA;;
  38.  4: len 6; hex e4b8ade59bbd; asc       ;;
  39.  5: len 4; hex 80000002; asc     ;;
  41. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  42. RECORD LOCKS space id 436 page no 3 n bits 72 index PRIMARY of table `boot`.`nationality` trx id 164397 lock_mode X locks rec but not gap waiting
  43. Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
  44.  0: len 4; hex 80000001; asc     ;;
  45.  1: len 6; hex 00000002822a; asc      *;;
  46.  2: len 7; hex 3f000001f521c4; asc ?    ! ;;
  47.  3: len 5; hex 4348494e41; asc CHINA;;
  48.  4: len 6; hex e4b8ade59bbd; asc       ;;
  49.  5: len 4; hex 80000002; asc     ;;
  51. *** WE ROLL BACK TRANSACTION (2)

根据日志可以看到事务1在等待获取X锁，事务2持有S锁，在等待X锁。其实事务1和事务2都持有S锁，都在等待X锁，因为两个事务S锁都不释放，所以
都无法获取X锁，所以就死锁了。删除外键后就没有问题了。

发表评论

表情：
评论列表 （有 0 条评论，100人围观）

还没有评论，来说两句吧...

相关阅读