图片来自抱图网。大概是在线执行update语句修改数据库数据的时候，where条件没有建立索引，导致业务直接崩溃，老大给我上了一课。这次我们来看看：为什么会发生这样的事故？以及如何避免呢？让我们谈谈前提。以下案例均基于InnoDB存储引擎，事务的隔离级别是可重复的。读。为什么会发生这样的事故？InnoDB存储引擎默认的事务隔离级别是“可重复读”，但是在这个隔离级别下，多个事务并发时会出现幻读。所谓幻读是指在同一个事务下，连续两次执行同??一条查询语句，而第二次查询语句可能会返回之前不存在的行。因此，InnoDB存储引擎自己实现了行锁，通过next-key锁（记录锁和间隙锁的组合）锁住记录本身与记录之间的“间隙”，防止其他事务在记录之间插入新记录记录，以免出现幻读现象。当我们执行更新语句时，实际上会在记录上加一个排他锁（X锁）。如果其他事务修改持有独占锁的记录，就会被阻塞。另外这个锁不会在update语句执行完后释放，而是在事务结束的时候释放。在InnoDB事务中，锁定一条记录的基本单位是next-key锁，但会因为某些条件退化为间隙锁或记录锁。锁的位置准确的说，锁是加在索引上而不是行上。比如在update语句的where条件中使用了唯一索引，next-key锁就会退化为记录锁，即只会锁定一行记录。这里有一个例子，这里是一张数据库表，其中id是主键索引。假设两个事务的执行顺序如下：可以看出，事务A的update语句中的where是等价查询，id是唯一索引，所以只有id=1的记录才会被锁定。因此，事务B的Update操作不会阻塞。但是如果update语句的where条件没有使用索引，则会扫描全表，所以会在所有记录上加next-key锁（记录锁+间隙锁），相当于给整张表加锁.假设两个事务的执行顺序如下：可以看出这次事务B的更新语句被阻塞了。这是因为事务A的update语句中的where条件没有索引列，所有的记录都会被加锁，即这条update语句产生了4个记录锁和5个间隙锁，相当于给整张表加锁。因此，当对数据量非常大的数据库表执行update语句时，如果不使用索引，则会对整个表加一个next-key锁，锁会持续很长时间，直到交易结束。期间除了select...from语句外，其他语句都会被锁住无法执行，业务也会因此停滞。接下来等待你的就是老板的骂声。在update语句的where处加一个索引可以防止整表被锁吗？不是，关键看优化器在执行这条语句的过程中是选择索引扫描还是全表扫描。如果进行全表扫描，则整个表的记录都会被锁定。如何避免此类事故？我们可以将MySQL中的sql_safe_updates参数设置为1来开启安全更新模式。官方解释：如果设置为1，MySQL将中止在WHERE子句或LIMIT子句中不使用键的UPDATE或DELETE语句。（具体而言，UPDATE语句必须有一个WHERE子句，该子句使用键或LIMIT子句，或两者都有。DELETE语句必须有两者。）这使得在键未正确使用且可能会更改的情况下捕获UPDATE或DELETE语句成为可能或删除大量行。默认值为0。粗略地说，当sql_safe_updates设置为1时，更新语句必须满足以下条件之一才能执行成功：使用where，并且where条件中必须有索引列。使用限制。同时使用where和limit，where条件中可能没有索引列。delete语句必须满足以下条件之一才能执行成功：使用where，并且where条件中必须有索引列。同时使用where和limit，where条件中可能没有索引列。如果where条件带的是索引列，但是优化器最后扫描的是全表而不是索引，我们可以使用forceindex([index_name])告诉优化器使用哪个索引，从而避免加锁的可能全表隐患由表构成。总结不要低估更新语句。在生产机器上使用不当可能会导致业务停滞甚至崩溃。当我们要执行更新语句时，确保where条件包含索引列，并在测试机上确认该语句是否使用了索引扫描，以防止因扫描全表而导致表中所有记录被锁定。我们可以在MySQL中开启sql_safe_updates参数，可以防止where条件在update操作的时候不带索引列。如果发现即使where条件中包含了列索引列，优化器仍然进行全标准扫描，那么我们就需要使用forceindex([index_name])来告诉优化器使用哪个索引。这次就这些了，下次小心点，别再被老大骂了。作者：小林编辑：陶佳龙来源：转载自公众号小林编码（ID：CodingLin）
