一次隐蔽的mysql死锁排查（附带redis分布式锁解决方法源码）

最新推荐文章于 2024-05-29 17:02:37 发布

JellyfishMIX

最新推荐文章于 2024-05-29 17:02:37 发布

阅读量531

点赞数

分类专栏： Database 文章标签： mysql redis java

本文链接：https://blog.csdn.net/weixin_43735348/article/details/105781554

版权

Database 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

概念

首先整合一下搜集到的概念，排查、解决问题需要了解。博主遇到的死锁问题并不在概念所列的案例中，后面将会展示遇到的情况和解决办法。

1. mysql锁的等级

出自：https://www.cnblogs.com/zejin2008/p/5262751.html

mysql有三种锁的级别：页级、表级、行级。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

2. mysql事务隔离级别要实际解决的问题

出自：https://zhuanlan.zhihu.com/p/117476959

脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。
可重复读：可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。
不可重复读：对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。
幻读：幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

3. mysql的事务隔离级别

出自：http://www.520code.net/index.php/archives/38/

读未提交（Read Uncommited）：如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。
读提交（Read Committed）：读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。可以通过“瞬间共享读锁”和“排他写锁”实现。
可重复读（Repeatable Read）读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。可以通过“共享读锁”和“排他写锁”实现。
串行化（SERIALIZABLE）：Serializable 读加共享锁，写加排他锁，读写互斥。

mysql的默认事务隔离级别是：可重复读（Repeatable Read）

4. mysql锁的种类

出自：http://www.520code.net/index.php/archives/38/

共享锁（S锁）：
用于只读操作(SELECT)，锁定共享的资源。共享锁不会阻止其他用户读，但是阻止其他的用户写和修改。
更新锁（U锁）：
用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
独占锁（X锁，也叫排他锁）：
一次只能有一个独占锁用在一个资源上，并且阻止其他所有的锁包括共享锁。写是独占锁，可以有效的防止“脏读”。

5. 什么情况下会造成死锁

出自：https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html

以 students 表为例：

在这里插入图片描述

其中，id 为主键，no（学号）为二级唯一索引，name（姓名）和 age（年龄）为二级非唯一索引，score（学分）无索引。数据库隔离级别为 RR。

死锁案例一

02 死锁案例1

死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的，这个死锁案例其实是最经典的死锁场景。

首先，事务 A 获取 id = 20 的锁（lock_mode X locks rec but not gap），事务 B 获取 id = 30 的锁；然后，事务 A 试图获取 id = 30 的锁，而该锁已经被事务 B 持有，所以事务 A 等待事务 B 释放该锁，然后事务 B 又试图获取 id = 20 的锁，这个锁被事务 A 占有，于是两个事务之间相互等待，导致死锁。

死锁案例二

03 死锁案例-2

首先事务 A 和事务 B 执行了两条 UPDATE 语句，但是由于 id = 25 和 id = 26 记录都不存在，事务 A 和事务 B 并没有更新任何记录，但是由于数据库隔离级别为 RR，所以会在 (20, 30) 之间加上间隙锁（lock_mode X locks gap before rec），间隙锁和间隙锁并不冲突。之后事务 A 和事务 B 分别执行 INSERT 语句要插入记录 id = 25 和 id = 26，需要在 (20, 30) 之间加插入意向锁（lock_mode X locks gap before rec insert intention），插入意向锁和间隙锁冲突，所以两个事务互相等待，最后形成死锁。

要解决这个死锁很简单，显然，前面两条 UPDATE 语句是无效的，将其删除即可。另外也可以将数据库隔离级别改成 RC，这样在 UPDATE 的时候就不会有间隙锁了。这个案例正是文章开头提到的死锁日志中的死锁场景，别看这个 UPDATE 语句是无效的，看起来很傻，但是确实是真实的场景，因为在真实的项目中代码会非常复杂，比如采用了 ORM 框架，应用层和数据层代码分离，一般开发人员写代码时都不知道会生成什么样的 SQL 语句，我也是从 DBA 那里拿到了 binlog，然后从里面找到了事务执行的所有 SQL 语句，发现其中竟然有一行无效的 UPDATE 语句，最后追本溯源，找到对应的应用代码，将其删除，从而修复了这个死锁。

死锁案例三

04 死锁案例-3

别看这个案例里每个事务都只有一条 SQL 语句，但是却实实在在可能会导致死锁问题，其实说起来，这个死锁和案例一并没有什么区别，只不过理解起来要更深入一点。要知道在范围查询时，加锁是一条记录一条记录挨个加锁的，所以虽然只有一条 SQL 语句，如果两条 SQL 语句的加锁顺序不一样，也会导致死锁。

在案例一中，事务 A 的加锁顺序为： id = 20 -> 30，事务 B 的加锁顺序为：id = 30 -> 20，正好相反，所以会导致死锁。这里的情景也是一样，事务 A 的范围条件为 id < 30，加锁顺序为：id = 15 -> 18 -> 20，事务 B 走的是二级索引 age，加锁顺序为：(age, id) = (24, 18) -> (24, 20) -> (25, 15) -> (25, 49)，其中，对 id 的加锁顺序为 id = 18 -> 20 -> 15 -> 49。可以看到事务 A 先锁 15，再锁 18，而事务 B 先锁 18，再锁 15，从而形成死锁。

如何避免死锁

死锁的案例还没有说完，文章末尾将会提到避免死锁的方法。

问题出现

博主遇到的死锁问题并不在概念所列的案例中。有一个向小组中上传文件的接口，此处测试的是更改小组中文件的数量计数。在测试并发时，报了一个500错误：

### 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 wx_interchange.team_info          SET file_count = ?          where tid = ?
### 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",

开头报错了Deadlock found when trying to get lock; try restarting transaction，毫无疑问是mysql遇到死锁了。

登入mysql，并使用：

show engine innodb status;

查看Innodb Status，在Innodb Status中会记录上一次死锁的信息：


------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-04-25 17:16:24 0x7fb11e794700
*** (1) TRANSACTION:
TRANSACTION 4858, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 18151, OS thread handle 140398695458560, query id 123200 ip.ip.ip.ip root updating
update wx_interchange.team_info
         SET file_count = 5 
        where tid = '123'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 5 n bits 96 index uk_team_info_tid of table `wx_interchange`.`team_info` trx id 4858 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 11; hex 3137313764343932643439; asc 123;;
 1: len 4; hex 80000015; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 4861, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 18152, OS thread handle 140398697203456, query id 123206 ip.ip.ip.ip root updating
update wx_interchange.team_info
         SET file_count = 5 
        where tid = '123'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 58 page no 5 n bits 96 index uk_team_info_tid of table `wx_interchange`.`team_info` trx id 4861 lock mode S locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 11; hex 3137313764343932643439; asc 123;;
 1: len 4; hex 80000015; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 5 n bits 96 index uk_team_info_tid of table `wx_interchange`.`team_info` trx id 4861 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 11; hex 3137313764343932643439; asc 123;;
 1: len 4; hex 80000015; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

单独提取出死锁发生处的sql：

*** (1) TRANSACTION:
update wx_interchange.team_info
         SET file_count = 5 
        where tid = '123'
        
*** (2) TRANSACTION:
update wx_interchange.team_info
         SET file_count = 5 
        where tid = '123'

事务1执行update语句的时候需要获取uk_team_info_tid这个索引再where条件上的X锁(行锁)，事务2执行同样的update语句，也在uk_team_info_tid上面想要获取X锁(行锁)，然后就出现了死锁，回滚了事务2。

死锁产生的必要条件：

互斥。
请求与保持条件。
不剥夺条件。
循环等待。

从日志上来看事务1和事务2都是取争夺同一行的行锁，和前面概念部分提到的三种案例互相循环争夺锁有点不同，怎么看都无法满足循环等待条件。

因为发生死锁的这段逻辑添加了事务管理，因此去看此段业务代码：

/**
 * 向项目组上传文件
 *
 * @param fileInfo 实例对象
 * @return 实例对象
 */
@Override
@Transactional(rollbackFor = TeamException.class)
public FileInfoDTO uploadFileToTeam(FileInfo fileInfo, TeamFile teamFile) {
    String fileId = UniqueKeyUtil.getUniqueKey();
    fileInfo.setFileId(fileId);
    teamFile.setFileId(fileId);
    this.fileInfoDao.insert(fileInfo);
    this.teamFileDao.insert(teamFile);

    String tid = teamFile.getTid();
    // 修改项目组文件计数
    this.updateTeamInfoCountProperty(tid, 1);

    return fileInfoDao.queryByFileId(fileInfo.getFileId());
}


/**
 * 更新项目组的计数属性
 *
 * @param tid            项目组tid
 * @param countChangeNum 计数更改的数量，有正负
 */
@Override
public void updateTeamInfoCountProperty(String tid, Integer countChangeNum) {
    TeamInfo teamInfoFromQuery = teamInfoDao.queryByTid(tid);
    TeamInfo teamInfoForUpdate = new TeamInfo();
    teamInfoForUpdate.setTid(tid);
    teamInfoForUpdate.setFileCount(teamInfoFromQuery.getFileCount() + countChangeNum);
    teamInfoDao.updateByTid(teamInfoForUpdate);
}

相关表结构（做了简化处理）：

create table `team_info` (
    `id` int not null auto_increment comment '代理主键',
    `tid` varchar(32) not null comment '项目组tid，随机生成，唯一键',
    `file_count` int not null default 0 comment '项目组文件数量',
    primary key (`id`),
    unique key `uk_team_info_tid` (`tid`)
) comment '项目组表';

create table `team_file` (
    `id` int not null auto_increment comment '代理主键',
    `tid` varchar(32) not null comment '项目组tid，外键',
    `file_id` varchar(32) not null comment '文件fileId，外键',
    `creation_time` timestamp not null default current_timestamp comment '创建时间，自动写入',
    `modified_time` timestamp not null default current_timestamp on update current_timestamp comment '修改时间，自动写入',
    primary key (`id`),
    foreign key `fk_team_file_tid`  (`tid`) references wx_interchange.team_info(`tid`),
    foreign key `fk_team_file_file_id` (`file_id`) references wx_interchange.file_info(`file_id`)
) comment '项目组文件表';

事务逻辑为：

有新文件上传至tid小组时，在文件信息表insert一行
在小组-文件关联表中insert一行，由于存在外键fk_team_file_tid (tid)，因此需要在team_info表中查询tid是否存在
查询tid小组的文件数量
tid小组的文件数量 + 1
更新tid小组的文件数量
查询fileId对应的文件信息

问题分析

参考：https://juejin.im/post/5c774114f265da2d993d9908#comment

假设目前有tid = 123小组，文件数量 file_count = 5

对于上述事务逻辑，当出现两个并发线程时，2 - 5 步有可能发生如下情况：

事务1	事务2
INSERT into team_file(tid, file_id) values (#{tid}, #{fileId})	INSERT into team_file(tid, file_id) values (#{tid}, #{fileId})
需要检测唯一索引是否存在获取S锁，阻塞	需要检测唯一索引是否存在获取S锁，阻塞
唯一索引存在，执行 SELECT id, tid, file_count FROM team_info WHERE tid = 123;	唯一索引存在，执行 SELECT id, tid, file_count FROM team_info WHERE tid = 123;
执行UPDATE语句（此时S锁未释放） UPDATE team_info SET file_count = 5 WHERE id = 123;	执行UPDATE语句（此时S锁未释放） UPDATE team_info SET file_count = 5 WHERE id = 123;
获取该行的X锁，被事务2的S锁阻塞	获取该行的X锁，被事务1的S锁阻塞
update成功,commit;	发现死锁，回滚该事务

小提示:S锁是共享锁，X锁是互斥锁。一般来说X锁和S，X锁都互斥，S锁和S锁不互斥。

mysql的默认事务隔离级别是：可重复读（Repeatable Read）

可重复读（Repeatable Read）读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。可以通过“共享读锁”和“排他写锁”实现。

从上面的流程中看见发生这个死锁的关键需要获取S锁，为什么插入的时候需要获取S锁呢？因为我们需要检测外键索引是否存在，在RR隔离级别下读取需要加上S锁。发现唯一键存在，然后执行update。update被两个事务的S锁互相阻塞，从而形成上面的循环等待条件。

05 update循环等待

解决问题

解决方案

核心问题是需要把S锁给干掉，有三个可供参考的解决方案:

将RR隔离级别，降低成RC隔离级别。这里RC隔离级别会用快照读，从而不会加S锁。
再插入的时候使用select * for update,加X锁，从而不会加S锁。
可以提前加上分布式锁，可以利用Redis,或者ZK等等，分布式锁可以参考我的这篇文章。聊聊分布式锁

第一种方法不太现实，毕竟隔离级别不能轻易的修改。第二种方法需要吃数据库的计算性能，数据库的计算资源比较宝贵，如果能把计算负载分摊到应用服务器上更好一些。

这里采用第三种方案，用redis做分布式锁。

Redis分布式锁

@Component
@Slf4j
public class RedisLockService {
    @Autowired
    private StringRedisTemplate redisTemplate;

    /**
     * 加锁
     * @param key key值
     * @param value 当前时间 + 超时时间
     * @return true拿到锁，false未拿到锁
     */
    public boolean lock(String key, String value) {
        // 此处redisTemplate.opsForValue().setIfAbsent(key, value)使用了redis官方文档的SETNX方法
        // 将key设置值为value，如果key不存在，这种情况下等同SET命令。 当key存在时，什么也不做。SETNX是"SET if Not eXists"的简写。
        // 此处.setIfAbsent(key, value)，如果key不存在，返回true，即设置成功。 当key存在时，返回false，即设置失败。
        if (redisTemplate.opsForValue().setIfAbsent(key, value)) {
            return true;
        }

        // 如果解锁与开锁之间的业务代码出现线程阻塞，则后续线程拿到的都是已上锁，业务代码永远无法执行。为防止死锁，需要设置锁有效期机制

        String currentValue = redisTemplate.opsForValue().get(key);
        // 如果锁过期
        if (!StringUtils.isEmpty(currentValue) && Long.parseLong(currentValue) < System.currentTimeMillis()) {
            // 如果有两个线程获取到了currentValue = A，这两个线程的value都是B，其中一个线程拿到锁
            String oldValue = redisTemplate.opsForValue().getAndSet(key, value);
            if (!StringUtils.isEmpty(oldValue) && oldValue.equals(currentValue)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 解锁，即删除key
     *
     * @param key key值
     * @param value 当前时间 + 超时时间。此处用来做校验，key和value对应再删除。
     */
    public void unlock(String key, String value) {
        try {
            String currentValue = redisTemplate.opsForValue().get(key);
            if (!StringUtils.isEmpty(currentValue) && currentValue.equals(value)) {
                redisTemplate.opsForValue().getOperations().delete(key);
            }
        } catch (Exception e) {
            log.error("[redis分布式锁]解锁异常，errMsg = {}", e.getMessage());
        }
    }
}

使用方法

@Autowired
private RedisLockService redisLockService;
    
/**
 * 向项目组上传文件
 *
 * @param fileInfo 实例对象
 * @return FileInfoDTO
 */
@Override
@Transactional(rollbackFor = TeamException.class)
public FileInfoDTO uploadFileToTeam(FileInfo fileInfo, TeamFile teamFile) {
    String fileId = UniqueKeyUtil.getUniqueKey();
    fileInfo.setFileId(fileId);
    teamFile.setFileId(fileId);
    this.fileInfoDao.insert(fileInfo);

    String tid = teamFile.getTid();
    // 加分布式锁，避免出现S锁和X锁循环等待死锁
    // 分布式锁过期时间
    int timeout = 10 * 1000;
    long time = System.currentTimeMillis() + timeout;
    // 加锁
    while (!redisLockService.lock(tid, String.valueOf(time))) {
        try {
            Thread.sleep(100);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    this.teamFileDao.insert(teamFile);
    // 修改项目组文件计数
    this.updateTeamInfoCountProperty(tid, TeamEnum.UPDATE_FILE_COUNT, 1);

    // 解锁
    redisLockService.unlock(tid, String.valueOf(time));

    return fileInfoDao.queryByFileId(fileInfo.getFileId());
}

在会发生死锁的代码块前后加一下锁。这里是insert和update可能发生死锁的情况，于是在insert和update代码块前后加锁。

JellyfishMIX

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
一次隐蔽的mysql死锁排查（附带redis分布式锁解决方法源码）

概念首先整合一下搜集到的概念，排查、解决问题需要了解。博主遇到的死锁问题并不在概念所列的案例中，后面将会展示遇到的情况和解决办法。1. mysql锁的等级出自：https://www.cnblogs.com/zejin2008/p/5262751.htmlmysql有三种锁的级别：页级、表级、行级。表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。...
复制链接

扫一扫