Deadlock found when trying to get lock，mysql死锁

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

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

##### 初始化表 #####

国籍表：

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

用户表：

CREATE TABLE `person` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `desc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `nationality_id` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `nationality_fk` (`nationality_id`),
      CONSTRAINT `nationality_fk` FOREIGN KEY (`nationality_id`) REFERENCES `nationality` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

##### 功能代码 #####

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

新增国籍人数的sql：

<insert id="updateCount">
            update nationality
            set `count` = `count` + #{increase}
            where id = #{id}
        </insert>

创建用户的sql：

<insert id="create">
            insert into
            person
            (`name`,`desc`,`nationality_id`)
            values(
            #{name},
            #{desc},
            #{nationalityId})
        </insert>

创建用户的service代码：

@Service
    public class PersonService {
        private static final Integer CHINA_ID = 1;
        @Autowired
        private PersonMapper personMapper;
        @Autowired
        private NationalityMapper nationalityMapper;
    
        @Transactional
        public void create(String name, String desc) {
            Person person = new Person();
            person.setName(name);
            person.setDesc(desc);
            person.setNationalityId(CHINA_ID);
            personMapper.create(person);
    
            try {
                //这里加了一个延迟，方便测试
                Thread.sleep(5000L);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
    
            nationalityMapper.updateCount(CHINA_ID, 1);
        }
    }

##### 测试 #####

模拟并发创建用户：

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

控制台报错如下：

Exception in thread "Thread-155" 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: update nationality         set `count` = `count` + ?         where id = ?
    ### 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:267)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    	at com.sun.proxy.$Proxy57.insert(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
    	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
    	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    	at com.sun.proxy.$Proxy59.updateCount(Unknown Source)
    	at com.example.bootmybatis.service.PersonService.create(PersonService.java:37)
    	at com.example.bootmybatis.service.PersonService$$FastClassBySpringCGLIB$$c378fac9.invoke(<generated>)
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
    	at com.example.bootmybatis.service.PersonService$$EnhancerBySpringCGLIB$$85b28c3f.create(<generated>)
    	at com.example.bootmybatis.BootmybatisApplication.lambda$main$0(BootmybatisApplication.java:16)
    	at java.lang.Thread.run(Thread.java:745)
    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:953)
    	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051)
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049)
    	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
    	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 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    	at com.sun.proxy.$Proxy69.update(Unknown Source)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	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:433)
    	... 19 more

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

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

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2020-06-11 15:55:25 0x3d8c
    *** (1) TRANSACTION:
    TRANSACTION 164396, ACTIVE 5 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 20, OS thread handle 17124, query id 341 localhost 127.0.0.1 root updating
    update nationality
            set `count` = `count` + 1
            where id = 1
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    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
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
     0: len 4; hex 80000001; asc     ;;
     1: len 6; hex 00000002822a; asc      *;;
     2: len 7; hex 3f000001f521c4; asc ?    ! ;;
     3: len 5; hex 4348494e41; asc CHINA;;
     4: len 6; hex e4b8ade59bbd; asc       ;;
     5: len 4; hex 80000002; asc     ;;
    
    *** (2) TRANSACTION:
    TRANSACTION 164397, ACTIVE 5 sec starting index read, thread declared inside InnoDB 5000
    mysql tables in use 1, locked 1
    5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 21, OS thread handle 15756, query id 340 localhost 127.0.0.1 root updating
    update nationality
            set `count` = `count` + 1
            where id = 1
    *** (2) HOLDS THE LOCK(S):
    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
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
     0: len 4; hex 80000001; asc     ;;
     1: len 6; hex 00000002822a; asc      *;;
     2: len 7; hex 3f000001f521c4; asc ?    ! ;;
     3: len 5; hex 4348494e41; asc CHINA;;
     4: len 6; hex e4b8ade59bbd; asc       ;;
     5: len 4; hex 80000002; asc     ;;
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    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
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
     0: len 4; hex 80000001; asc     ;;
     1: len 6; hex 00000002822a; asc      *;;
     2: len 7; hex 3f000001f521c4; asc ?    ! ;;
     3: len 5; hex 4348494e41; asc CHINA;;
     4: len 6; hex e4b8ade59bbd; asc       ;;
     5: len 4; hex 80000002; asc     ;;
    
    *** WE ROLL BACK TRANSACTION (2)

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