本文共 10309 字，大约阅读时间需要 34 分钟。

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

初始化表

国籍表：

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;

功能代码

下面是一个很简单的功能。

新增国籍人数的sql：

   
            update nationality        set `count` = `count` + #{increase}        where id = #{id}    
   

创建用户的sql：

   
            insert into        person        (`name`,`desc`,`nationality_id`)        values(        #{name},        #{desc},        #{nationalityId})    
   

创建用户的service代码：

@Servicepublic 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(
   
    )	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(
    
     )	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 readmysql tables in use 1, locked 1LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 20, OS thread handle 17124, query id 341 localhost 127.0.0.1 root updatingupdate 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 waitingRecord 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 5000mysql tables in use 1, locked 15 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 21, OS thread handle 15756, query id 340 localhost 127.0.0.1 root updatingupdate 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 gapRecord 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 waitingRecord 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锁都不释放，所以

转载地址：http://lsjqi.baihongyu.com/