KittyDaddy's blog KittyDaddy's blog
首页
  • 学习笔记

    • 《Java基础》
    • 《常用设计模式》
    • 《MYSQL》
    • 《GO语言》
    • 《Spring源码解读》
  • 微服务解决方案

    • 锁的演化
    • 简单限流方案
    • 海量数据切分
  • 中间件

    • Nginx
    • MQ
    • Redis
    • Keepalived
  • 面试记
  • 杂文
  • 开源
  • 友情链接
关于
收藏
  • 分类
  • 标签
  • 归档
GitHub (opens new window)

老猫

万物皆系统
首页
  • 学习笔记

    • 《Java基础》
    • 《常用设计模式》
    • 《MYSQL》
    • 《GO语言》
    • 《Spring源码解读》
  • 微服务解决方案

    • 锁的演化
    • 简单限流方案
    • 海量数据切分
  • 中间件

    • Nginx
    • MQ
    • Redis
    • Keepalived
  • 面试记
  • 杂文
  • 开源
  • 友情链接
关于
收藏
  • 分类
  • 标签
  • 归档
GitHub (opens new window)
  • MySQL-死锁探讨
    • 遵循这些MySQL设计规范,再也没被组长喷过
    • 半夜被慢查询告警吵醒,limit深度分页的坑
    • 《MYSQL》笔记
    老猫
    2020-03-26
    目录
    MySQL锁定义
    Mysql锁类型
    InnoDB锁
    行级锁
    表级锁
    死锁成因
    如何避免死锁?
    排查死锁

    MySQL-死锁探讨

    项目中碰到死锁问题,具体场景是定时任务在不停地计算匹配任务,另外的话也有人工操作数据,当同时进行update的时候,发生了数据库的死锁情况,在此想要深入研究,并且总结一下解决该类问题的方案。

    # MySQL锁定义

    简单来说,就是数据库为了保证数据的一致性,使各种共享资源在被访问时候变得有序而设计出来的一种规则。

    # Mysql锁类型

    mysql有多种存储引擎,每种存储引擎的锁定机制都有所不同。但是MySQL各种存储引擎主要还是使用了这三种类型的锁表机制:表级锁定、行级锁定、页级锁定。

    名称 特性 是否出现死锁 缺点
    表级锁定 开销小,加锁快 不会出现死锁 锁定粒度大,发生锁冲突的概率最高,并发度最低
    行级锁定 开销大,加锁慢 最容易出现死锁 锁定粒度最小,发生锁冲突的概率最低,并发度也最高
    页级锁定 时间界于表锁和行锁之间 会出现死锁 锁定粒度界于表锁和行锁之间,并发度一般

    MySQL数据库中,使用表级锁定的主要是MyISAM,Memory,CSV等一些非事务性存储引擎,而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎,页级锁定主要是BerkeleyDB存储引擎的锁定方式。


    目前由于很多公司用的都是Innodb存储引擎,所以,我们就针对这一搜索引擎进行研究。当然目前我们公司也是用的MySQL的Innodb引擎。InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是默认采用了行级锁。通过上述表的分析,就很容易就可以看出为什么我在项目中会遇到死锁问题了,我们用了行级锁,开销大加锁慢,最容易出现死锁。

    # InnoDB锁

    # 行级锁

    之前我们提到InnoDB引擎是默认采用了行级锁。InnoDB实现了两种标准的行级锁,一种是共享锁(shared locks,S锁),另外一种是排他锁(exclusive locks,X锁)。

    S锁:允许当前持有该锁的事务读取行。如果事务T1持有了行r上的S锁,则其他事务可以同时持有行r的S锁,但是不能对行r加X锁。

    X锁:允许当前持有该锁的事务更新或删除行。如果事务T1持有了行r上的X锁,则其他任何事务不能持有行r的X锁,必须等待T1在行r上的X锁释放。

    如果事务T1在行r上保持S锁,则另一个事务T2对行r的锁的请求按如下方式处理:

    • T2可以同时持有S锁
    • T2如果想在行r上获取X锁,必须等待其他事务对该行添加的S锁或X锁的释放。
    # 表级锁

    之前强调默认是行级锁,但是在某些情况下,也会发生表锁

    行级锁变为表级锁情况如下:

    1、如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁把整张表锁住。

    2、表字段进行变更。

    3、进行整表查询。(没使用索引)


    # 死锁成因

    有了上述的基础知识,再来看一下我目前遇到的这样的一个场景。当然此处不适合将具体公司业务呈现,所以就举一个类似的例子。

    现在有这样一张流水(trade)表。

    字段 类型 描述
    id bigint 主键id
    trade_no varchar(256) 交易编号
    status tinyint(4) 状态

    上述表中,id有主键,trade_no为非唯一索引,status没有索引。 Innodb对于主键使用了聚簇索引,这是一种数据存储方式,表数据是和主键一起存储,主键索引的叶结点存储行数据。对于普通索引,其叶子节点存储的是主键值。如图1

    图1

    接下来分析索引和锁之间的关系

    情况一: 由于id是主键,因此更新时候直接锁定整行记录。如图二

    图2

    情况二: 由于trade_no是普通索引,因此首先锁住二级索引(两行),接着会锁住相应主键所对应的记录。如图三

    图3

    **情况三:**由于status没有索引,因此执行的时候会锁住整个表.如图四

    图4

    有了上述几种索引以及锁的关系,我们再来看一下死锁成因:

    第一种情况(也是我项目遇到的情况):由于相同表记录行锁冲突造成了死锁。

    项目中是这样的,我的定时任务每20分钟执行一次匹配任务,每次匹配任务的时候需要 7-8分钟,后面涉及匹配完毕之后对id会进行批量更新操作,例如批量更新了id[1,2,3,4,5]。而另外一个事物,就是我们操作人员的更新动作,操作人员更新的id[4,5,7,8]。两个事物恰巧都没有被提交,因此发生死锁,具体如图五

    图5

    第二种情况(没有遇到,但是看到有人踩了这个坑):不同表相同记录行锁冲突。这种情况其实也是比较容易理解的,事务A和事务B操作两张表,但出现循环等待锁情况。 图6

    第三种情况:不同索引冲突。在执行对普通索引进行操作的时候,相对应的聚簇索引也会被锁定,上述情况二,已经作出解释,如果先锁定普通索引,后锁定聚簇索引的id[1,3,4,5],而另外一个事物正好操作的是聚簇索引id[2,3,4]的情况,此时就有可能会发生死锁。 图7

    第四种情况:gap锁冲突。从而引发死锁问题。这块锁由于比较隐晦,另外的我们目前大多数数据库都是RC(已提交读)级别,所以在此对由RR(可重复读)级别造成的gap锁不做深究。 有兴趣的朋友可自行研究。

    # 如何避免死锁?

    (1)将大的事务拆成小的事务,因为大事务更加倾向于死锁。

    (2)为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁,这样死锁的概率会大大增大。

    (3)同一个事务中,尽量一次锁定所有所需要的资源。

    (4)以固定的顺序进行访问表以及行信息,比如可以先对id进行排序处理,然后再更新等。

    (5)降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。

    # 排查死锁

    (1)通过业务代码报错定位。

    (2)查询数据库的隔离级别,从而排除RR隔离级别中的gap锁。具体查询隔离级别的SQL:select @@global.tx_isolation

    #mysql#mysql死锁
    上次更新: 2022/11/30, 00:06:25
    遵循这些MySQL设计规范,再也没被组长喷过

    遵循这些MySQL设计规范,再也没被组长喷过→

    最近更新
    01
    让大龄程序员欲罢不能的事儿
    09-23
    02
    运营明明设置了活动开始时间,为什么到点没生效?聊聊动态定时任务
    07-30
    03
    不是,大哥,咱这小门小户的,别搞我CDN流量啊
    07-25
    更多文章>
    Theme by Vdoing | Copyright © 2020-2025 Kitty Daddy | License
    • 跟随系统
    • 浅色模式
    • 深色模式
    • 阅读模式