一条update语句引发的现场事故!
昨晚在群划水的时候,看到有位读者说了这么一件事。
大概就是,在线上执行一条 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]) 可以告诉优化器使用哪个索引。
这次就说到这啦,下次要小心点,别再被老板挨骂啦。
吃海参有什么禁忌?今日头条美食美邦今日头条美食美邦海参虽然价格贵,但还是有很多人照吃不误,原因就是海参含大量营养成分,特别是胶原蛋白,它是美容的珍品,同时对骨组织和肌腱等的形成也有重要作用,又有人说
新生儿要不要刻意睡脑形?医学上如果宝宝睡偏头了(扁头综合征)才需要睡头型,不过大部分家长问这个问题都是想如何睡出好看的头型,但是怎样的头型算好看呢?这个好像没有统一标准,我就说个北方的育儿传统要让新生儿头
为什么今年股市很多10倍股,股民却亏得很惨?四五千只股票,今天这几只涨,明天那几只涨。谁能随时跟上轮动,踏准节奏。跟不上轮动,大盘涨到4000点,照样亏损。因为只有几百只还涨点!几千只股还下跌!卷商好多还在发行价!市场没钱没
有没有好用的通达信指标主图副图指标公式?这是我自己一直在用的主图副图公式,感觉非常不错,分享给大家,这是原码幅图VAR1LWINNER(5,CLOSE)100VAR2LWINNER(10,CLOSE)100VAR3LWI
长了一张风流帅气的脸,情史却很干净,娱乐圈这8位男星太稀缺了娱乐圈中的感情,分分合合总是让人琢磨不透。毕竟明星们面临着太多的诱惑,所谓常在河边走哪有不湿鞋,稍不留神就有可能绯闻缠身,甚至身败名裂。但有这样一群男演员,他们明明长着一张帅气的脸
评论丨马珂坚韧的壮美坚韧的壮美评廖静仁中短篇小说集门虚掩文马珂门虚掩是新华出版社为作家廖静仁新近出版的一套中短篇小说选集。洋洋86万余字,分上下两卷,每卷43万字。书封上印着这样的文字著名作家廖静仁由
忙碌小哥为何放慢了脚步?众芳社区工会联合会标志性小二级工会是这样打造的头图众芳社区工会联合会开会讨论下一阶段重点工作远香湖保利大剧院嘉定图书馆辖区内能坐拥这么多嘉定新城标志性项目,时常让马陆镇众芳社区副书记朱春华感到骄傲。同时,身兼众芳社区工会联合会
美航母抵近中国,俄罗斯两肋插刀,向日本海试射导弹,敢于硬碰硬美国海军现有11艘航空母舰,除了10艘尼米兹级航空母舰之外,还有1艘福特级福特号航空母舰,根据公开的资料显示,目前美国海军在亚太地区有尼米兹号航空母舰的战斗部署,实际上美国海军第七
俄罗斯喝白酒的人比较多,那他们得脑梗死脑出血的人多吗?你应该问俄罗斯假酒多吗!我的母亲在世时常常用白酒擦洗油漆桌面,很干净。说明白酒应该也可以洗除血管中的脂肪,当然适量更好。有很多老人喝酒吃肥肉,活到八十几岁。我的爷爷喝酒,吃茶,抽旱
过年南阳人喝酒有多疯?外地朋友聊一聊?1开局三个。2先主家开始倒酒,喝三个倒俩,或者喝五个倒4个,多喝少倒。然后挨着主陪主客,副陪副客开始倒。有的时候顺序不一定是固定的。3倒酒中间上鱼,鱼头酒。一般都是转到主客位,服务
同样的手机,实体店和京东质量一样吗?同样的手机,实体店和京东质量一样吗?同样的一款手机,实体店和京东质量应该是一样的而同一款手机现在的实体店和网上京东淘宝价位也是一样的,网上靠手机销售量走货,实体店靠一些像VIVOO