MySQL线上死锁案例分析

这篇具有很好参考价值的文章主要介绍了MySQL线上死锁案例分析。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

项目场景

项目开发中有两张表:c_bill(账单表),c_bill_detail(账单明细表),他们的表结构如下(这里只保留必要信息):

CREATE TABLE `c_bill_detail` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `bill_detail_no` varchar(32)  NOT NULL DEFAULT '' COMMENT '对账单编号',
  `receivable_date` datetime(3) DEFAULT NULL COMMENT '应收日期',
  `order_type` varchar(20) NOT NULL DEFAULT '' COMMENT 
  `bill_no` varchar(32)  NOT NULL DEFAULT '' COMMENT '账单编号',
  `invoice_amount` decimal(12,4) NOT NULL COMMENT '开票金额',
  `active` tinyint NOT NULL DEFAULT '1' COMMENT '是否逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_bill_no` (`bill_no`) USING BTREE
) ENGINE=InnoDB COMMENT='客户账单明细';

CREATE TABLE `c_bill` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `bill_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '对账单编号',
  `should_receive_amount` decimal(12,4) NOT NULL COMMENT '应收总额',
  `actual_should_receive_amount` decimal(12,4) NOT NULL COMMENT '实际应收金额',
  `invoice_status` tinyint DEFAULT NULL COMMENT '开票状态(字典:invoice-status)',
  `invoice_amount` decimal(12,4) NOT NULL COMMENT '开票金额',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_bill_no` (`bill_no`) USING BTREE
) ENGINE=InnoDB COMMENT='客户账单';

c_bill表跟c_bill_detail表是1对多的关系,c_bill表中的invoice_amount是由c_bill_detail表中的invoice_amount统计出来的。
统计sql如下:

UPDATE c_bill
      SET invoice_amount = (SELECT ifnull(sum(invoice_amount), 0)
                            FROM c_bill_detail
                            WHERE bill_no = #{billNo}
                              AND active = 1
                              AND order_type in ('sale_order', 'supplement_order', 'subject_sale_order')),
          invoice_date   = #{invoiceDate},
          invoice_status =
              CASE
                  WHEN invoice_amount = should_receive_amount THEN 1
                  WHEN invoice_amount = 0 THEN 0
                  ELSE 2
                  END
      where bill_no = #{billNo}
        and active = 1;

业务层面,账单进行开发票操作后,会更新c_bill_detail表跟c_bill

问题描述

有一天线上出现告警:
MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁
从日志上看发生了死锁,通过定位代码发现跟执行以下sql有关:

UPDATE c_bill
      SET invoice_amount = (SELECT ifnull(sum(invoice_amount), 0)
                            FROM c_bill_detail
                            WHERE bill_no = #{billNo}
                              AND active = 1
                              AND order_type in ('sale_order', 'supplement_order', 'subject_sale_order')),
          invoice_date   = #{invoiceDate},
          invoice_status =
              CASE
                  WHEN invoice_amount = should_receive_amount THEN 1
                  WHEN invoice_amount = 0 THEN 0
                  ELSE 2
                  END
      where bill_no = #{billNo}
        and active = 1;

通过数据库锁分析得到如下信息:
MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁
从上面信息可以得到以下信息:

  • 事务1等待c_bill_detail表的S锁,该锁对应的索引名称是 PRIMARY(也就是主键索引,id)
  • 事务1持有c_bill表的X锁,该锁对应的索引名称是uk_bill_no
  • 事务2等待c_bill表的X锁,该锁对应的索引名称为uk_bill_no
  • 事务2持有c_bill_detail表额S锁,该锁对应的索引名称是PRIMARY(也就是主键索引,id)

通过上面可以看出,事务1跟事务2直接的锁进入了死循环,形成了死锁。

原因分析:

死锁数据分析

上面的途中,给出了死锁有关的两个索引:c_bill_detail表的主键索引,跟c_bill表的主键索引。c_bill表知道是执行了上面提到的统计sql,那么,c_bill_detail表是执行了什么操作呢?

首先通过审计找出当时这两个事务的操作:
MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁
上面是线程3213915(事务A)的有关操作,可以看到对c_bill_detail表有如下更新:

-- SQL
UPDATE
  c_bill_detail
SET
  receivable_date = '2023-11-15 00:00:00',
  invoice_status = 2,
  invoice_amount = 305412,
  updater = '管理员',
  updater_code = 'ADMINISTRATOR',
  update_time = '2023-12-08 17:47:52.382000'
WHERE id = 146947
  AND active = 1

线程3213754(事务B)操作如下
MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁

UPDATE
  c_bill_detail
SET
  receivable_date = '2023-11-15 00:00:00',
  invoice_status = 2,
  invoice_amount = 305412,
  updater = '管理员',
  updater_code = 'ADMINISTRATOR',
  update_time = '2023-12-08 17:47:52.381000'
WHERE id = 147471
  AND active = 1;

从上面可以看出事务A对表c_bill_detailid = 146947数据进行了更新,事务B对表c_billid=147471进行了更新。

通过审计日志还发现,事务A跟事务B也都更新了c_bill表,而且都是更新了bill_no=XSZD202309070005这一行数据。

事务A:
MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁

UPDATE
  c_bill
SET
  invoice_amount =
  (SELECT
    IFNULL(SUM(invoice_amount), 0)
  FROM
    c_bill_detail
  WHERE bill_no = 'XSZD202309070005'
    AND active = 1
    AND order_type IN (
      'sale_order',
      'supplement_order',
      'subject_sale_order'
    )),
  invoice_date = '2023-12-08 00:00:00',
  invoice_status =
  CASE
    WHEN invoice_amount = should_receive_amount
    THEN 1
    WHEN invoice_amount = 0
    THEN 0
    ELSE 2
  END
WHERE bill_no = 'XSZD202309070005'
  AND active = 1;

事务B:

MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁

-- SQL
UPDATE
  c_bill
SET
  invoice_amount =
  (SELECT
    IFNULL(SUM(invoice_amount), 0)
  FROM
    c_bill_detail
  WHERE bill_no = 'XSZD202309070005'
    AND active = 1
    AND order_type IN (
      'sale_order',
      'supplement_order',
      'subject_sale_order'
    )),
  invoice_date = '2023-12-08 00:00:00',
  invoice_status =
  CASE
    WHEN invoice_amount = should_receive_amount
    THEN 1
    WHEN invoice_amount = 0
    THEN 0
    ELSE 2
  END
WHERE bill_no = 'XSZD202309070005'
  AND active = 1;

上图可以看出,事务B最终在更新c_bill时失败回滚了(因为发生了死锁)。

通过查看数据发现,c_bill_detailid = 146947id=147471对应的bill_no都是XSZD202309070005
MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁
到这里,只是发现了数据上的关联,还是不知道为什么会出现死锁,下面在其他环境进行复现。

select语句添加了共享读锁

为了更好复现这个死锁情况,现将线上的sql执行顺序整理如下:

MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁

下面在本地数据库,选取c_bill_detailid=19380id=19381,这两条数据有相同的bill_no=XSZD202211226768

MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁
开启两个事务,分别按照上面表格的sql数据进行执行,同时观察锁情况:

事务A 更新id = 19380:

Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE
    ->   c_bill_detail
    -> SET
    ->   receivable_date = '2023-11-15 00:00:00',
    ->   invoice_status = 2,
    ->   invoice_amount = 305412,
    ->   updater = '管理员',
    ->   updater_code = 'ADMINISTRATOR',
    ->   update_time = '2023-12-08 17:47:52.382000'
    -> WHERE id = 19380
    ->   AND active = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务B更新id = 19381的记录

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE
    ->   c_bill_detail
    -> SET
    ->   receivable_date = '2023-11-15 00:00:00',
    ->   invoice_status = 2,
    ->   invoice_amount = 305412,
    ->   updater = '管理员',
    ->   updater_code = 'ADMINISTRATOR',
    ->   update_time = '2023-12-08 17:47:52.381000'
    -> WHERE id = 19381
    ->   AND active = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这是观察锁情况

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
  // 省略表意向锁
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347952208:230:8:12:139645358792496
ENGINE_TRANSACTION_ID: 65810
            THREAD_ID: 563867
             EVENT_ID: 34
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill_detail
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139645358792496
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 19381
*************************** 3. row ***************************
       // 省略表锁
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347951400:230:8:9:139645358786480
ENGINE_TRANSACTION_ID: 65809
            THREAD_ID: 563866
             EVENT_ID: 34
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill_detail
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139645358786480
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 19380
4 rows in set (0.00 sec)

从上面看出,c_bill_detail表的id=19381id=19380的数据加上了X锁,这是意料之中的。

接下来事务A执行更新c_bill

mysql> UPDATE
    ->   c_bill
    -> SET
    ->   invoice_amount =
    ->   (SELECT
    ->     IFNULL(SUM(invoice_amount), 0)
    ->   FROM
    ->     c_bill_detail
    ->   WHERE bill_no = 'XSZD202211226768'
    ->     AND active = 1
    ->     AND order_type IN (
    ->       'sale_order',
    ->       'supplement_order',
    ->       'subject_sale_order'
    ->     )),
    ->   invoice_date = '2023-12-08 00:00:00',
    ->   invoice_status =
    ->   CASE
    ->     WHEN invoice_amount = should_receive_amount
    ->     THEN 1
    ->     WHEN invoice_amount = 0
    ->     THEN 0
    ->     ELSE 2
    ->   END
    -> WHERE bill_no = 'XSZD202211226768'
    ->   AND active = 1;

此时事务A发生了阻塞
MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁

这时查看锁情况:

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
           // 表锁
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347952208:230:8:16:139645358792496
ENGINE_TRANSACTION_ID: 65820
            THREAD_ID: 563867
             EVENT_ID: 43
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill_detail
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139645358792496
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 19381
*************************** 3. row ***************************
               //c_bill 表意向锁
*************************** 4. row ***************************
              // c_bill_detail表意向锁
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347951400:230:8:15:139645358786480
ENGINE_TRANSACTION_ID: 65819
            THREAD_ID: 563866
             EVENT_ID: 44
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill_detail
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139645358786480
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 19380
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347951400:229:5:6:139645358786824
ENGINE_TRANSACTION_ID: 65819
            THREAD_ID: 563866
             EVENT_ID: 45
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: uk_bill_no
OBJECT_INSTANCE_BEGIN: 139645358786824
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'XSZD202211226768', 5117
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347951400:229:7:6:139645358787168
ENGINE_TRANSACTION_ID: 65819
            THREAD_ID: 563866
             EVENT_ID: 45
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139645358787168
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5117
*************************** 8. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347951400:230:58:117:139645358787512
ENGINE_TRANSACTION_ID: 65819
            THREAD_ID: 563866
             EVENT_ID: 45
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill_detail
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_bill_no
OBJECT_INSTANCE_BEGIN: 139645358787512
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'XSZD202211226768', 19380
*************************** 9. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347951400:230:58:118:139645358787512
ENGINE_TRANSACTION_ID: 65819
            THREAD_ID: 563866
             EVENT_ID: 45
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill_detail
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_bill_no
OBJECT_INSTANCE_BEGIN: 139645358787512
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'XSZD202211226768', 19381
*************************** 10. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139645347951400:230:8:16:139645358787856
ENGINE_TRANSACTION_ID: 65819
            THREAD_ID: 563866
             EVENT_ID: 45
        OBJECT_SCHEMA: fresh
          OBJECT_NAME: c_bill_detail
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139645358787856
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: WAITING
            LOCK_DATA: 19381
10 rows in set (0.00 sec)

从上面的Row10发现,事务A跟表c_bill_detail的id = 19381的记录添加了S锁,并且在锁状态为等待状态。

接着事务B也执行更新c_bill表,发现就会出现死锁的情况。
MySQL线上死锁案例分析,MySQL,mysql,死锁,共享锁,排他锁
到这里总结上面的持锁过程:

  • 事务A先持有t_bill_detailid=19380X
  • 接着事务B持有t_bll_detailid=19381X锁,与上一把没有存在锁竞争,都能正常执行
  • 事务A更新c_bill,这时不仅给表c_bill表的bill_no=XSZD202211226768的记录加上了X锁,同时也给c_bill_detailid=19381的记录添加了S锁,并且处于等待状态。
  • 事务B更新c_bill同样会给c_bill_detailid=19380的记录添加S锁。

这里有一下几点需要注意:

  1. S锁跟X锁不兼容,会出现锁等待的情况。
  2. 普通的select语句是不加锁的,但是在update语句中进行select查询赋值,这时的select就会添加上共享锁。
  3. 共享锁主要是保证每次读取的都是最新的值(读取时不支持修改)。

以上就是生成环境形成死锁的分析过程。关于X锁跟S锁的更多说明,可以参考:Innodb中的锁文章来源地址https://www.toymoban.com/news/detail-758953.html

到了这里,关于MySQL线上死锁案例分析的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 记录一次mysql死锁日志分析

    记录一次mysql死锁-CSDN博客 MySQL死锁日志的查看和分析_mysql死锁日志解读_lkforce的博客-CSDN博客 此文承接以上两篇文章,文章1原创记录,文章2转载分析 一,死锁sql update tt_task          SET navigation_distance = ?,    plan_arrive_time = ?          where id = ? update tt_task set grabbing_status

    2023年04月15日
    浏览(85)
  • mysql死锁分析show engine innodb status

    最近在使用mysql的show engine innodb status命令分析死锁,发现了一个有意思的点。就是红框里圈出来的这个。 红框的数据所代表的是sql语句需要获取的行锁。实际上,这个值应该是-1才对,很纳闷,为什么会展示7fffffff呢?其实这个事和innodb的一条关于有符号数的规定有关系。

    2024年02月13日
    浏览(47)
  • 线上SQL超时场景分析-MySQL超时之间隙锁

    之前遇到过一个由MySQL间隙锁引发线上sql执行超时的场景,记录一下。 分布式事务消息表 :业务上使用消息表的方式,依赖本地事务,实现了一套分布式事务方案 消息表名 :mq_messages 数据量 :3000多万 索引 :create_time 和 status status :有两个值,1 和 2, 其中99%以上的状态都

    2024年02月05日
    浏览(52)
  • MySQL next-key锁引发的死锁问题分析与处理

    当方法A被多线程并发执行的时候会偶现死锁问题,分析问题: 因为业务上有限制:每次并发执行方法A的时候,每个线程执行到sql里面的参数:data_name_en有可能是一样的,但是空间#{tenantId}肯定都是不一样的,也就是并发时候,线程1要删除和插入的数据一定和线程2要删除和插

    2024年02月11日
    浏览(48)
  • 记一次线上问题引发的对 Mysql 锁机制分析

    最近双十一开门红期间组内出现了一次因 Mysql 死锁导致的线上问题,当时从监控可以看到数据库活跃连接数飙升,导致应用层数据库连接池被打满,后续所有请求都因获取不到连接而失败 整体业务代码精简逻辑如下: 数据库实例监控: 当时通过分析上游问题流量限流解决后

    2024年02月05日
    浏览(56)
  • MySQL的index merge(索引合并)导致数据库死锁分析与解决方案

    在DBS-集群列表-更多-连接查询-死锁中,看到9月22日有数据库死锁日志,后排查发现是因为mysql的优化-index merge(索引合并)导致数据库死锁。 index merge(索引合并):该数据库查询优化的一种技术,在mysql 5.1之后进行引入,它可以在多个索引上进行查询,并将结果合并返回。 在

    2024年02月08日
    浏览(82)
  • spark案例分析-搜索引擎日志分析案例

    1.业务分析 2.数据截图 3.代码实现:         main.py:         defs.py:

    2024年02月08日
    浏览(46)
  • 对应分析介绍及SPSS案例分析

    在开展统计分析的过程中,分类变量(定序和定类变量)是我们研究的一个重点。通常我们分析分类变量间关系时,最常用的分析方法是卡方检验,其次是逻辑回归和对数线性模型等。 如果类别变量的分类较少,我们可以通过卡方检验判断行变量和列变量间是否相互独立,同

    2024年02月13日
    浏览(48)
  • python案例讲解视频,python简单案例分析

    大家好,给大家分享一下python案例讲解视频,很多人还不知道这一点。下面详细解释一下。现在让我们来看看!   前言 Python 是一种面向对象、解释型、弱类型的脚本语言,它也是一种功能强大而完善的通用型语言。 相比其他编程语言(比如 Java),Python 代码非常简单,上手

    2024年04月11日
    浏览(45)
  • Python案例分析|文本相似度比较分析

     本案例通过设计和实现有关文本相似度比较的类Vector和Sketch,帮助大家进一步掌握设计Python类来解决实际问题的能力。 通过计算并比较文档的摘要可实现文本的相似度比较。 文档摘要的最简单形式可以使用文档中的k-grams(k个连续字符)的相对频率的向量来表示。假设字符

    2024年02月16日
    浏览(55)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包