MySQL死锁案例分析及避免办法

1. 什么是死锁

• 死锁就是两个以上线程互相竞争资源导致相互等待的现象
• 发生死锁有四个条件：互斥、请求与保持条件、不可抢占、循环等待

2. 举个栗子：

• 环境：MYSQL 8.0+，默认隔离级别RR
• 表存在主键索引和仅name字段的普通索引

2.1. 栗子一：

• 事务A将id=1的余额字段减100金额，然后对id=2的余额字段加100金额
• 在两个操作中间时刻，事务B对id=2的余额减300金额，又对id=1的余额加300

• 事务A在执行第二条update语句时，需要等待事务B释放id=2的行锁
• 事务B在执行第二条update语句时，需要等待事务A释放id=1的行锁
• 所以就发生了死锁

2.1.1 代码栗子：

    @PutMapping("/dead/lock")
    public BaseResponse deadLock() {
        CompletableFuture.runAsync(() -> userAccountService.deadLock());
        try {
            TimeUnit.SECONDS.sleep(2);
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
        userAccountService.mockOtherTransactional();
        return BaseResponse.SUCCESS();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deadLock() {
        try {
            UserAccount account = this.getById(1L);
            BigDecimal decimal = account.getAmount().subtract(new BigDecimal("100"));
            this.updateAmountById(account.getId(), decimal);
            TimeUnit.SECONDS.sleep(5);

            UserAccount account2 = this.getById(2L);
            BigDecimal decimal2 = account2.getAmount().add(new BigDecimal("100"));
            this.updateAmountById(account2.getId(), decimal2);
        } catch (Exception e) {
            log.info("++++++++++++++异常");
            throw new RuntimeException(e);
        }

    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
    public void mockOtherTransactional() {
        try {
            UserAccount account = this.getById(2L);
            BigDecimal decimal = account.getAmount().subtract(new BigDecimal("300"));
            this.updateAmountById(account.getId(), decimal);
            TimeUnit.SECONDS.sleep(10);

            UserAccount account2 = this.getById(1L);
            BigDecimal decimal2 = account2.getAmount().add(new BigDecimal("300"));
            this.updateAmountById(account2.getId(), decimal2);
        } catch (Exception e) {
            log.info("-------------异常");
            throw new RuntimeException(e);
        }
    }

    private void updateAmountById(Long id, BigDecimal decimal) {
        UserAccount update = new UserAccount();
        update.setId(id);
        update.setAmount(decimal);
        this.updateById(update);
    }

• 模拟场景如上文所述，使用多线程和事务隔离级别 REQUIRES_NEW
• 接口请求之后就会报错： Deadlock found when trying to get lock; try restarting transaction

2.1.2 存储引擎状态分析

执行：SHOW ENGINE INNODB STATUS;


2.1.2.1 第一部分：

• 事务A（(1) TRANSACTION:）在执行第二条语句：update t_user_account set amount = 1100 where id = 2;时
• 需要等待（WAITING FOR THIS LOCK TO BE GRANTED:）排它锁（X锁）的释放
• 这个锁是由这个表的主键索引PRIMARY of table cloud.t_user_account产生的
• 这条被锁住的记录在heap no 3 PHYSICAL RECORD

2.1.2.2 第二部分：

• 事务B（(2) TRANSACTION:）在执行第二条语句：update t_user_account set amount = 1300 where id = 1;时
• 事务B持有（ HOLDS THE LOCK）排它锁，这条锁住的记录在heap no 3 PHYSICAL RECORD,正是事务A等待释放的行锁
• 需要等待（WAITING FOR THIS LOCK TO BE GRANTED:）排它锁（X锁）的释放
• 这条被锁住的记录在heap no 2 PHYSICAL RECORD

2.1.2.3 第三部分：

• 事务回滚：回滚事务B

• 值得一提的是，这里只回滚了事务B，而事务A是提交了的，数据库的记录以被事务A修改
• 网上说可以设置innodb_rollback_on_timeout来达到死锁事务都回滚，各位自行验证
• MySQL 死锁后事务无法回滚是真的吗？
  

2.1.3 解决方式

2.1.3.1 注意资源的获取顺序

• 像本栗子，事务B和事务A获取资源的顺序相反，便容易造成死锁
• 所以，调整一些顺序，将事务B中，先对id=1的加300，再对id=2的减300，即可解决

• 当然也可以对要获取资源显式的加锁，如for update
• 但是这样当发生资源冲突式，便会阻塞

2.1.3.2 大事务拆小

• 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小
• 栗子中用了好几个等待，复杂的大事务占用锁时间久，就容易发生冲突
• 大事务占有的锁时间久，也很有可能会导致事务超时

2.2. 栗子二：

• 当当前读name字段等值匹配不存在时，会加上间隙锁（name-1，name-5），不懂的可以看一下MySQL的锁机制

• 模拟的场景就是先查询，数据不存在然后插入数据
• 事务A查询、插入“name-2”的数据；事务B查询、插入“name-3”的数据

• 事务A执行第一条语句时，‘name-2’的数据不存在，则加上了间隙锁（name-1，name-5）
• 然后事务B执行第一条语句时，‘name-3’的数据不存在，也加上了间隙锁（name-1，name-5）
• 间隙锁与间隙锁之间是兼容的，因为间隙锁目的是为了防止其他事务插入数据；
• 所以当事务A要插入name-2时，事务A要获取name-2的插入意向锁，但此时name-2被事务B的间隙锁占有
• 当事务B要插入name-3时，事务B要获取name-3的插入意向锁，但此时name-3被事务B的间隙锁占有，死锁便发生了

2.2.1 代码栗子：

  	@Override
    @Transactional(rollbackFor = Exception.class)
    public void deadLock(Integer no) {
        try {
            if (no == 1) {
                this.deadLockOne();
            } else if (no == 2) {
                this.deadLockTwo("name-2");
            }
        } catch (Exception e) {
            log.info("++++++++++++++异常");
            throw new RuntimeException(e);
        }
    }
    
    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
    public void mockOtherTransactional(Integer no) {
        try {
            if (no == 1) {
                this.mockOtherTransactionalOne();
            } else if (no == 2) {
                this.deadLockTwo("name-3");
            }
        } catch (Exception e) {
            log.info("-------------异常");
            throw new RuntimeException(e);
        }
    }
    
    private void deadLockTwo(String name) throws InterruptedException {
        UserAccount userAccount = lambdaQuery().eq(UserAccount::getName, name).last("for update").one();
        TimeUnit.SECONDS.sleep(5);
        if (userAccount != null) {
            BigDecimal decimal2 = userAccount.getAmount().add(new BigDecimal("1000"));
            lambdaUpdate().eq(UserAccount::getName, name).set(UserAccount::getAmount, decimal2).update();
        } else {
            userAccount = new UserAccount();
            userAccount.setName(name);
            userAccount.setMobile(String.valueOf((12345678900L)));
            userAccount.setAccount(UUID.randomUUID().toString(true));
            userAccount.setAmount(new BigDecimal("1000"));
            this.save(userAccount);
        }

    }

2.2.2 存储引擎状态分析

执行：SHOW ENGINE INNODB STATUS;


2.2.2.1 第一部分：

• **事务A（(1) TRANSACTION:）在执行insert语句时：
  • 持有 HOLDS THE LOCK锁**
    • 这个锁是由这个表的普通索引idx_name产生的间隙锁lock_mode X locks gap
  • 需要等待（WAITING FOR THIS LOCK TO BE GRANTED:）锁的释放
    • 这个锁是由这个表的普通索引idx_name产生的间隙锁lock_mode X locks gap
    • 在获得插入意向锁之前lock_mode X locks gap before rec insert intention waiting需要等待间隙锁的释放

2.2.2.2 第二部分：

• 事务B（(2) TRANSACTION:）在执行insert语句时：
  • 持有 HOLDS THE LOCK锁
    • 这个锁是由这个表的普通索引idx_name产生的间隙锁lock_mode X locks gap
  • 需要等待（WAITING FOR THIS LOCK TO BE GRANTED:）锁的释放
    • 这个锁是由这个表的普通索引idx_name产生的间隙锁lock_mode X locks gap
    • 在获得插入意向锁之前lock_mode X locks gap before rec insert intention waiting需要等待间隙锁的释放

2.2.2.3 第三部分：

• 事务回滚：回滚事务B

2.2.3 解决方式

2.2.3.1 避免显式加锁

• 显式加锁时，因避免对非唯一索引加锁，不管是否等值匹配，都会存在间隙锁，间隙锁便容易照成死锁
• 本栗子去掉显式加锁 for update 便不会死锁

2.2.3.2 隔离级别调整

• 本栗子隔离级别为RR，如果业务允许，可以降低隔离级别为RC，这样不会存在间隙锁影响

3. 小结：

• 数据库死锁一般发生在并发操作数据库资源相互抢占的时候，大多是因为行级锁造成的；
• 行级锁大多是因为索引不合理获没有索引，所以为表设置合理的索引，也可以避免死锁
• 其次，如果业务允许，可以降低隔离级别，比如 MySQL 由 RR 调整为 RC，可以避免由很多间隙锁造成的死锁
• 还有可以将大事务拆小，大事务占用锁时间更长，更容易发生死锁
• 还有就是注意资源的获取顺序，避免显式加锁等
• 一些分析语句（MySQL 8+可用的）：
  • 查看事务隔离级别：SHOW VARIABLES LIKE ‘TRANSACTION_ISOLATION’
  • 查看事务超时时间：SHOW VARIABLES LIKE ‘INNODB_LOCK_WAIT_TIMEOUT’
  • 查看存储引擎状态：SHOW ENGINE INNODB STATUS;
  • 查看锁数据的分析：SELECT * FROM PERFORMANCE_SCHEMA.DATA_LOCKS;
  • 查看存储引擎事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
  • 杀死死锁线程：KILL 46601363（线程id由存储引擎事务语句可查到trx_mysql_thread_id）