来源于微信公众号：小泉coding

小伙伴们好，我是小泉。

昨天晚上在群滑水的情况下，见到有一位阅读者讲了那么一件事。

大约便是，线上上实行一条 update 语句改动数据库查询信息的情况下，where 标准沒有携带索引，造成 业务流程立即崩了，被老板经验教训了一波

此次咱们就一起来看看：

怎么会出现这些的安全事故？又该怎么预防这类意外的产生？

说个前提条件，下面说的例子全部都是根据 InnoDB 储存模块，且事务的隔离级别是可反复读。

1

怎么会出现这些的安全事故？

InnoDB 储存模块的默认设置事务隔离级别是「可反复读」，可是在这个隔离级别下，在好几个事务高并发的情况下，会发生幻读的难题，说白了的幻读就是指在同一事务下，持续实行2次一样的查看语句，第二次的查看语句很有可能会回到以前不会有的行。

因而 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 携带索引就能防止全表记录上锁了没有？

并并不是。

重要还得看这一条语句在实施流程中，优化器最后挑选的是索引扫描仪，或是全表扫描仪，假如离开了全表扫描仪，就会对全表的记录上锁了。

2

又该怎么预防这类意外的产生？

我们可以将 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]) 能够 告知优化器应用哪一个索引，为此防止有概率锁全表产生的安全隐患。

3

汇总

不必小瞧一条 update 语句，在生产制造机里错误操作也许会造成 业务流程停滞不前，乃至奔溃。

在我们要实行 update 语句的情况下，保证 where 标准中携带了索引列，而且在测试机确定该语句是不是走的是索引扫描仪，避免由于扫描仪全表，而对表中的全部记录再加上锁。

我们可以开启 MySQL 里的 sql_safe_updates 主要参数，那样能够防止 update 实际操作时 where 标准沒有携带索引列。

假如发觉即便在 where 标准中携带了列索引列，优化器走的或是全标扫描仪，这时候大家就需要应用 force index([index_name]) 能够 告知优化器应用哪一个索引。

此次便说到这啦，下一次要小心点，别再被老板挨骂啦。