完蛋,公司被一条 update 语句干趴了!

噢不,一条update.where无索引导致的MySQL死锁 在MySQL事务隔离级别的情况下,对于等操作语句,查询条件尽量使用索引,减少锁的范围,提高写的并发量,避免不必要的死锁发生影响业务正常运行这次死锁的说明就到这里,希望大家能看得懂并有所收获,不得不说有些知识我是略过了,大家可以自行查看官方文档补充了解! 阅读详情

昨晚在群划水的时候,看到有位读者说了这么一件事。

大概就是,在线上执行一条 update 语句修改数据库数据的时候,where 条件没有带上索引,导致业务直接崩了,被老板教训了一波

这次我们就来看看:

  • 为什么会发生这种的事故?
  • 又该如何避免这种事故的发生?

说个前提,接下来说的案例都是基于 InnoDB 存储引擎,且事务的隔离级别是可重复读。

为什么会发生这种的事故?

InnoDB 存储引擎的默认事务隔离级别是「可重复读」,但是在这个隔离级别下,在多个事务并发的时候,会出现幻读的问题,所谓的幻读是指在同一事务下,连续执行两次同样的查询语句,第二次的查询语句可能会返回之前不存在的行。

因此 InnoDB 存储引擎自己实现了行锁,通过 next-key 锁(记录锁和间隙锁的组合)来锁住记录本身和记录之间的“间隙”,防止其他事务在这个记录之间插入新的记录,从而避免了幻读现象。

当我们执行 update 语句时,实际上是会对记录加独占锁(X 锁)的,如果其他事务对持有独占锁的记录进行修改时是会被阻塞的。另外,这个锁并不是执行完 update 语句就会释放的,而是会等事务结束时才会释放。

在 InnoDB 事务中,对记录加锁带基本单位是 next-key 锁,但是会因为一些条件会退化成间隙锁,或者记录锁。加锁的位置准确的说,锁是加在索引上的而非行上。

比如,在 update 语句的 where 条件使用了唯一索引,那么 next-key 锁会退化成记录锁,也就是只会给一行记录加锁。

这里举个例子,这里有一张数据库表,其中 id 为主键索引。

假设有两个事务的执行顺序如下:

在这里插入图片描述

可以看到,事务 A 的 update 语句中 where 是等值查询,并且 id 是唯一索引,所以只会对 id = 1 这条记录加锁,因此,事务 B 的更新操作并不会阻塞。

但是,在 update 语句的 where 条件没有使用索引,就会全表扫描,于是就会对所有记录加上 next-key 锁(记录锁 + 间隙锁),相当于把整个表锁住了。

假设有两个事务的执行顺序如下:

可以看到,这次事务 B 的 update 语句被阻塞了。

这是因为事务 A的 update 语句中 where 条件没有索引列,所有记录都会被加锁,也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁,相当于锁住了全表。

因此,当在数据量非常大的数据库表执行 update 语句时,如果没有使用索引,就会给全表的加上 next-key 锁, 那么锁就会持续很长一段时间,直到事务结束,而这期间除了 select ... from语句,其他语句都会被锁住不能执行,业务会因此停滞,接下来等着你的,就是老板的挨骂。

那 update 语句的 where 带上索引就能避免全表记录加锁了吗?

并不是。

关键还得看这条语句在执行过程种,优化器最终选择的是索引扫描,还是全表扫描,如果走了全表扫描,就会对全表的记录加锁了。

又该如何避免这种事故的发生?

我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1,开启安全更新模式。

官方的解释:
If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

大致的意思是,当 sql_safe_updates 设置为 1 时。

update 语句必须满足如下条件之一才能执行成功:

  • 使用 where,并且 where 条件中必须有索引列;
  • 使用 limit;
  • 同时使用 where 和 limit,此时 where 条件中可以没有索引列;

delete 语句必须满足如下条件之一才能执行成功:

  • 使用 where,并且 where 条件中必须有索引列;
  • 同时使用 where 和 limit,此时 where 条件中可以没有索引列;

如果 where 条件带上了索引列,但是优化器最终扫描选择的是全表,而不是索引的话,我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引,以此避免有几率锁全表带来的隐患。

总结

不要小看一条 update 语句,在生产机上使用不当可能会导致业务停滞,甚至崩溃。

当我们要执行 update 语句的时候,确保 where 条件中带上了索引列,并且在测试机确认该语句是否走的是索引扫描,防止因为扫描全表,而对表中的所有记录加上锁。

我们可以打开 MySQL sql_safe_updates 参数,这样可以预防 update 操作时 where 条件没有带上索引列。

如果发现即使在 where 条件中带上了列索引列,优化器走的还是全标扫描,这时我们就要使用 force index([index_name]) 可以告诉优化器使用哪个索引。

这次就说到这啦,下次要小心点,别再被老板挨骂啦。

记录一次mysql update导致锁表问题 事发情况 当天晚上生产服务一直在告警,刚刚开始只是一个数据保存接口耗时过长,紧接着整个服务接口都耗时,对外表现为整个服务不可用。 于是优先排查宽带情况,其次看代码逻辑,看错误日志(无一例外都是接口耗时过长报警)。 接着看监控发现goroutine明显增多,依然没有头绪。于是想到重启服务。 重启服务过程中,弹出一个告警日志-db连接数过多,已经超过筏值。这个时候问题才确定。 问题原因 是最初的那个数据保存接口,逻辑很简单,接口拿到请求时,insert或者update进入db内。问题在于update 阅读详情

相关推荐

SQL入门4

UPDATE语句,也是一种较常用的 SQL 语句,它可以用来(修改)更新表中已存在的记录。

C_nroll的博客 723

update的where条件把索引的字段要带上,要不然就全表锁

update的where条件把索引的字段要带上,要不然就全表锁 文章目录update的where条件把索引的字段要带上,要不然就全表锁本文主要内容背景在学习中总结一下内容关于锁的表sql加锁思考点InnoDB行锁实现方式 本文主要内容 报错了: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting tra...

Agly_Charlie的博客 8091

游戏 App 签名证书全解析:从“我就想发个包”到“我终于搞懂谁给谁盖章”(大白话生动版)

签名证书在游戏App中起着关键的安全作用,主要用于安装包签名、网络通信加密、第三方SDK集成、热更新包验证、支付安全及PC游戏完整性校验。其核心原理基于非对称加密,通过私钥签名和公钥验证确保数据来源可信且未被篡改。证书可由开发者自签或权威CA机构签发,形成信任链。常见的签名算法包括RSA、ECDSA等。签名证书保障了游戏分发、通信、交易等环节的安全性,防止伪造、篡改和冒用,是游戏开发和运营中不可或缺的安全基础。

本博客聚焦游戏开发技巧、创业实战心得及大学热门课程干货。这里既有技术深度,也有思维广度,无论你是开发者、创业者还是高校学子,都能在这里找到适合自己的成长之路! 370

mysql update where 子查询写法

经常遇到要更新一张表,where条件来自另外一张表,直接下面这种写法肯定错的: update table_a set name='aa' where b_id in (select id from table_b) 又不想先查出来,再一条一条更新,实在有点慢,如何一条语句搞定,可以用下面的写法: update table_a a,(select id from table_b where name in ('aa')) b set a.name='bb' where a.b_id=b.id and

仙道Bob的专栏 1万+

《供应链金融风控模型设计:多维度数据整合与动态风险预警机制》

注:方案突出供应链金融「三流合一」(资金流、物流、信息流)的风控逻辑,通过多维度数据整合与动态预警,解决传统风控中中小企业信用评估难、链条风险传导监测滞后的问题。◦ 构建供应链拓扑图,计算「核心企业违约对上下游企业的影响系数」(如某电子厂商违约时,其前五大供应商的风险权重自动提升40%)• 区块链应用:将核心企业应付账款上链存证,防止「一票多融」欺诈(如某供应商用同一应收账款在多家银行融资)◦ 财务数据:核心企业审计报告、上下游企业纳税数据(增值税发票频次/金额)

2501_91651383的博客 1551

基于PWM的汽车仪表步进电机微步驱动优化实践

本文深入探讨了基于PWM的微步驱动技术,用于优化汽车仪表步进电机的运行性能。通过将传统的分步驱动升级为PWM微步细分驱动,利用正弦/余弦查表法合成旋转磁场,有效消除了指针抖动与噪声,实现了指针的平滑、精准运动。文章以STM32和VID2905电机为例,提供了详细的硬件连接、代码实现及高级优化技巧,为提升汽车仪表体验提供了实用方案。

weixin_29169505的博客 514

完蛋!for update引发了血案

公司的某些业务用到了数据库的悲观锁 for update,但有些同事没有把 for update 放在 Spring 事务中执行,在并发场景下发生了严重的线程阻塞问题,为...

JAVA葵花宝典 1913

PYNQ开发板使用-使用DMA进行数据搬移(Simple DMA transfer 模式)

该篇是学习使用PYNQ开发板,实际上是对ZYNQ PL端AXI_CDMA 核的应用。实验步骤参照官网的教程,一步一步地做,但是由于在硬件资源布置方面与官方教程稍有出入,所以在SDK的源码里也进行了修改。 AXI_CDMA特性: 如果是使用ZYNQ 7系列芯片(可能其他Xilinx也是通用的),xilinx的AXI_CDMA 核有两种传输模式:轮询(poll)和中断(...

YGLeeeon的博客 3522

不是吧!开发这么多年你不会还没经历过MySQL死锁吧?

说个很早之前自己遇到过数据库死锁的问题。 有个业务主要逻辑就是新增订单、修改订单、查询订单等操作。然后因为订单是不能重复的,所以当时在新增订单的时候做了幂等性校验,做法就是在新增订单记录之前,先通过 select ... for update 语句查询订单是否存在,如果不存在才插入订单记录。

weixin_50205273的博客 475

别再只抄代码了!深入理解MQ2传感器数据手册,搞定ppm换算公式

本文深入解析MQ2烟雾传感器的工作原理与ppm换算公式,从数据手册解读到数学建模,提供详细的校准与优化方法。通过实际案例和代码示例,帮助开发者准确测量气体浓度,解决常见问题,提升传感器应用的可靠性。

weixin_42713608的博客 506

前端零代码丨UIOTOS + NodeRed:无代码搭建工控上位机的绝配组合!

用户类型价值硬件产品商软硬一体方案,无代码GUI上位机成新卖点软件服务商售前原型演示利器,交付成本降低至1/3平台开发商融合无代码技术,同质化竞争中脱颖而出🚀 总结UIOTOS的前端零代码设计(页面嵌套、属性继承、节点连线)与NodeRed的后端流程自动化,堪称工控与IoT开发的黄金组合。无论是串口通信、SCADA监控还是业务管理,均能实现“无代码交付”,真正让前端不求人,硬件交互触手可及!探索更多案例:UIOTOS官网在线体验。

2502_91192798的博客 757

面试官: 解释下什么是死锁?

开局先来个段子: 面试官: 解释下什么是死锁? 应聘者: 你录用我,我就告诉你 面试官: 你告诉我,我就录用你 应聘者: 你录用我,我就告诉你 面试官: 滚!

wdj_yyds的博客 405

微信小程序|基于小程序实现定位打卡功能

此文主要在小程序内实现打卡功能,可根据用户位置与公司设定的打卡范围实时判断打卡是否合法,并在后台对打卡记录进行管理。

商务合作 / 项目定制 / 学习交流。个人vx:lovely_wml 1万+

mysql为什么会发生死锁,如何避免死锁?

开局先来个段子: 面试官: 解释下什么是死锁? 应聘者: 你录用我,我就告诉你 面试官: 你告诉我,我就录用你 应聘者: 你录用我,我就告诉你 面试官: 滚!

wdj_yyds的博客 486

Nvidia Jetson平台 JP4.2.1版本GStreamer视频采集异常问题调试记录

Nvidia Jetson平台GStreamer in JP4.2.1不能采集但是 v4l2-ctl和 ffmpeg能正常采集视频。

jzwjzw19900922的博客 2127

SAP GUI 经典事务在 Fiori Launchpad 中的穿越之道:原理、配置与实践指南

在许多企业的 S/4HANA 或 ECC 系统里,经典 SAP GUI 事务依旧承载着关键业务。如何把这些老将自然地嵌入到 Fiori Launchpad,让用户在统一入口里无缝切换新旧 UI,是项目中经常遇到的主题。本文结合社区博文、官方文档与实际项目经验,系统梳理这一过程的技术原理、配置步骤、性能优化以及常见坑点,并给出贴近中国读者的案例解析与改进建议。阅读完毕,你将不仅知道怎么做,还会理解。

2007 年 ~ 2025 年,深耕 SAP 技术 18 年 623

MySQL死锁都经历过吧?

说个很早之前自己遇到过数据库死锁的问题。 有个业务主要逻辑就是新增订单、修改订单、查询订单等操作。然后因为订单是不能重复的,所以当时在新增订单的时候做了幂等性校验,做法就是在新增订单记录之前,先通过 select ... for update 语句查询订单是否存在,如果不存在才插入订单记录。 而正是因为这样的操作,当业务量很大的时候,就可能会出现死锁。

weixin_50205273的博客 348

Devexpress16.2.6破解工具

Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具

mysql为什么会发生死锁,怎么避免死锁?

说个很早之前自己遇到过数据库死锁的问题。 有个业务主要逻辑就是新增订单、修改订单、查询订单等操作。然后因为订单是不能重复的,所以当时在新增订单的时候做了幂等性校验,做法就是在新增订单记录之前,先通过 select ... for update 语句查询订单是否存在,如果不存在才插入订单记录。

weixin_50205273的博客 397

文档:亲测可用解决vnc灰屏,黑屏,鼠标大黑叉情况

主要解决vnc-viewer灰屏,黑屏,鼠标大黑叉等现象,详细见文档。

win10 跳过U8环境检测的IE WebControls.rar

win10 安装u8的时候 提示IE WebControls 不通过,这个工具可以欺骗u8的检查。 跳过U8环境检测的IE WebControls。提示导入失败后,重新检测通过。

Linux | 软件包管理器——yum的使用

想知道软件背后的故事吗?想知道Linux开源生态的发展吗?想知道什么yum吗?本文将带你走进yum,学会yum(๑•̀ㅂ•́)و✧

做技术人 · 产优质博客 · 找好工作 2132

Outlook Express 6 (OE6) for Win7/Win8/Win10

英文版 Outlook Express6.0, 兼容Win7/Win10/WinXP,解压就可用。

运营转DBA,数据库可不是进阶版Excel

介绍了数据库与Excel的核心差异,主要面向零基础转行人群。Excel适合少量数据处理,而数据库能高效处理百万级数据,支持多用户并发操作。文章重点介绍了SQL三大基础操作(查询、筛选排序、联表),并分享了一个先SELECT再DELETE的实用安全技巧。

DBAxiaoye的博客 384

53|工具与权限安全:最小权限、沙箱、审计与隔离

本文探讨如何通过工程手段保障AI工具的安全性,而非依赖不可靠的Prompt优化。核心策略包括:1)最小权限原则,限制AI工具仅获取必要权限;2)沙箱隔离,在独立容器中执行高风险代码;3)审计日志,记录所有操作以便追溯;4)权限分级与审批,根据风险等级实施不同控制措施(如L0-L4分级,高危操作需人工审批)。这些架构层面的防护比Prompt更可靠,能有效防止AI恶意操作导致的数据泄露或系统损坏。后续将讨论数据安全与合规问题。

qqxhb 资源共享 360

ESP32 OTA升级保障固件空中更新可靠性

本文深入探讨ESP32 OTA升级的关键技术,包括双分区回滚、固件签名验证、断点续传与差分更新、任务看门狗等机制,系统化保障空中升级的稳定性与安全性,适用于工业级IoT设备远程维护场景。

weixin_30674431的博客 310
上一篇: MySQL分库分表的最佳实践
下一篇: 项目为什么要做重构?分享一个真实的重构案例
Java技术那些事儿
博客等级 码龄5年 2306粉丝 2584原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值