记一次线上SQL死锁事故:如何避免死锁?

news/2025/1/23 3:20:49/

背景

   之前我参与过一个项目,在项目初期,我们是没有将读写表分离的,而是基于一个主库完成
读写操作。在业务量逐渐增大的时候,我们偶尔会收到系统的异常报警信息,DBA 通知我
数据库出现了死锁异常。
   按理说业务开始是比较简单的,就是新增订单、修改订单、查询订单等操作,那为什么会出
现死锁呢?经过日志分析,我们发现是作为幂等性校验的一张表经常出现死锁异常。我们和
DBA 讨论之后,初步怀疑是索引导致的死锁问题。后来我们在开发环境中模拟了相关操
作,果然重现了该死锁异常。

生成问题重现

    接下来我们就通过实战来重现下该业务死锁异常。首先,创建一张订单记录表,该表主要用
于校验订单重复创建:
sql">CREATE TABLE `order_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` int(11) DEFAULT NULL,
`status` int(4) DEFAULT NULL,`create_date` datetime(0) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `idx_order_status`(`order_no`,`status`) USING BTREE) ENGINE = InnoDB
为了能重现该问题,我们先将事务设置为手动提交。这里要注意一下,MySQL 数据库
Oracle 提交事务不太一样,MySQL 数据库默认情况下是自动提交事务,我们可以通过以
下命令行查看自动提交事务是否开启:
sql">mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.01 sec)
下面就操作吧,先将 MySQL 数据库的事务提交设置为手动提交,通过以下命令行可以关
闭自动提交事务:
sql">mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)
   订单在做幂等性校验时,先是通过订单号检查订单是否存在,如果不存在则新增订单记录。
知道具体的逻辑之后,我们再来模拟创建产生死锁的运行 SQL 语句。首先,我们模拟新建
两个订单,并按照以下顺序执行幂等性校验 SQL 语句(垂直方向代表执行的时间顺序):
此时,我们会发现两个事务已经进入死锁状态。我们可以在 information_schema 数据库
中查询到具体的死锁情况,如下图所示:
看到这,你可能会想, 为什么 SELECT 要加 for update 排他锁,而不是使用共享锁呢?
想下,如果是两个订单号一样的请求同时进来,就有可能出现幻读。也就是说,一开始事务
A 中的查询没有该订单号,后来事务 B 新增了一个该订单号的记录,此时事务 A 再新增一
条该订单号记录,就会创建重复的订单记录。面对这种情况,我们可以使用锁间隙算法来防
止幻读。

避免死锁的措施

知道了死锁问题源自哪儿,就可以找到合适的方法来避免它了。
避免死锁最直观的方法就是在两个事务相互等待时,当一个事务的等待时间超过设置的某一
阈值,就对这个事务进行回滚,另一个事务就可以继续执行了。这种方法简单有效,在
InnoDB 中,参数 innodb_lock_wait_timeout 是用来设置超时时间的。
另外,我们还可以将 order_no 列设置为唯一索引列。虽然不能防止幻读,但我们可以利用
它的唯一性来保证订单记录不重复创建,这种方式唯一的缺点就是当遇到重复创建订单时会
抛出异常。
我们还可以使用其它的方式来代替数据库实现幂等性校验。例如,使用 Redis 以及
ZooKeeper 来实现,运行效率比数据库更佳。

推荐阅读

  • 基于用户故事的领域驱动DDD
  • MySQL调优
  • MySQL调优之事务:高并发场景下的数据库事务调优-CSDN博客

http://www.ppmy.cn/news/1565373.html

相关文章

【计算机视觉】人脸识别

一、简介 人脸识别是将图像或者视频帧中的人脸与数据库中的人脸进行对比,判断输入人脸是否与数据库中的某一张人脸匹配,即判断输入人脸是谁或者判断输入人脸是否是数据库中的某个人。 人脸识别属于1:N的比对,输入人脸身份是1&…

兼职全职招聘系统架构与功能分析

2015工作至今,10年资深全栈工程师,CTO,擅长带团队、攻克各种技术难题、研发各类软件产品,我的代码态度:代码虐我千百遍,我待代码如初恋,我的工作态度:极致,责任&#xff…

Ubuntu 24.04 LTS 安装 Docker Desktop

Docker 简介 Docker 简介和安装Ubuntu上学习使用Docker的详细入门教程Docker 快速入门Ubuntu版(1h速通) Docker 安装 参考 How to Install Docker on Ubuntu 24.04: Step-by-Step Guide。 更新系统和安装依赖 在终端中运行以下命令以确保系统更新并…

vue3使用音频audio标签

文章目录 一、背景二、页面三、标签介绍四、代码五、代码说明场景1&#xff1a;针对加载固定格式的比如MP3文件&#xff0c;可直接使用\<audio>标签场景2&#xff1a;针对播放告警内容&#xff0c;比如中文或者英文词条情况 一、背景 项目使用vue3&#xff0c;需求针对告…

Linux初识:【版本控制器Git】【调试器gdb/cgdb使用】

目录 一.版本控制器Git 1.1版本控制器 1.2Git的操作 1.2.1从远端仓库到本地 1.2.2工作区到本地暂存区 1.2.3本地暂存区到本地仓库 1.2.4本地仓库到远程仓库 1.2.5 .gitignore 1.2.6Windows上操作&#xff08;需要安装Tortoisegit&#xff09; 1.2.7同步远端和当地 二调…

python爬虫入门(理论)

python爬虫 学习网站 一、准备 环境搭建 requests beautifulsoup4 selenium 爬虫架构 URL管理器&#xff1a;管理URL&#xff0c;存储已爬取或待爬取的URL 网页下载器&#xff1a;破解网页&#xff0c;进行下载 网页解析器&#xff1a;对网页的HTML样式、连接的URL等进…

CSS 合法颜色值

CSS 颜色 CSS 中的颜色可以通过以下方法指定&#xff1a; 十六进制颜色带透明度的十六进制颜色RGB 颜色RGBA 颜色HSL 颜色HSLA 颜色预定义/跨浏览器的颜色名称使用 currentcolor 关键字 十六进制颜色 用 #RRGGBB 规定十六进制颜色&#xff0c;其中 RR&#xff08;红色&…

计算机网络 (53)互联网使用的安全协议

一、SSL/TLS协议 概述&#xff1a; SSL&#xff08;Secure Sockets Layer&#xff09;安全套接层和TLS&#xff08;Transport Layer Security&#xff09;传输层安全协议是工作在OSI模型应用层的安全协议。SSL由Netscape于1994年开发&#xff0c;广泛应用于基于万维网的各种网络…