mysql范围查询跳过排它锁_抱歉，没早点把这么全面的InnoDB锁机制发给你

三、幻读

这一部分，我们将通过幻读，逐步展开对InnoDB锁的探究。

1、幻读概念

解释了不同概念的锁的作用域，我们来看一下幻读到底是什么。

幻读在RR条件下是不会出现的。因为RR是Repeatable Read，它是一种事务的隔离级别，直译过来也就是“在同一个事务中，同样的查询语句的读取是可重复”，也就是说他不会读到”幻影行”(其他事务已经提交的变更)，它读到的只能是重复的(无论在第一次查询之后其他事务做了什么操作，第二次查询结果与第一次相同)。

上面的例子都是使用for update，这种读取操作叫做当前读，对于普通的select语句均为快照读。

当前读，又叫加锁读，或者阻塞读。这种读取操作不再是读取快照，而是读取最新版本并且加锁。快照读不会添加任何锁。

官方文档对于幻读的定义是这样的：

原文：The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom”row.

手动无脑翻译：所谓的幻影行问题是指，在同一个事务中，同样的查询语句执行多次，得到了不同的结果，这就是幻读。例如：如果同一个SELECT语句执行了两次，第二次执行的时候比第一次执行时多出一行，则该行就是所谓的幻影行。

其中这一句：

“The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.”

这看起来应该是不可重复读的定义，同样的查询得到了不同的结果(两次结果不是重复的)，但是后面的举例给出了幻读真正的定义，第二次比第一次多出了一行。

也就是说，幻读的出现有这样一个前提，第二次查询前其他事务提交了一个INSERT插入语句。而不可重复读出现的前提是第二次查询前其他事务提交了UPDATE或者DELETE操作。

mysql的快照读，使得在RR的隔离级别上在next-Key的作用区间内，制造了一个快照副本，这个副本是隔离的，无论副本对应的区间里的数据被其他事务如何修改，在当前事务中，取到的数据永远是副本中的数据。

RR级别下之所以可以读到之前版本的数据，是由于数据库的MVCC(Multi-Version Concurrency Control，多版本并发控制)。

参见InnoDB Multi-Versioning

https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

有些文章中提到“RR也不能完全避免幻读”，实际上官方文档实际要表达的意义是“在同一个事务内，多次连续查询的结果是一样的，不会因其他事务的修改而导致不同的查询结果”，这里先给出实验结论：

当前事务如果未发生更新操作(增删改)，快照版本会保持不变，多次查询读取的副本是同一个；

当前事务如果发生更新(增删改)，再次查询时，会刷新快照版本。

2、RC级别下的幻读

RC情况下会出现幻读。首先设置隔离级别为RC：

SET SESSION tx_isolation='READ-COMMITTED';

RC(Read Commit)隔离级别可以避免脏读，事务内无法获取其他事务未提交的变更，但是由于能够读到已经提交的事务，因此会出现幻读和不重复读。也就是说，RC的快照读是读取最新版本数据，而RR的快照读是读取被next-key锁作用区域的副本。

3、RR级别下能否避免幻读？

我们先来模拟一下RR隔离级别下没有出现幻读的情况：

开启第一个事务并执行一次快照查询：

这两个事务的执行，有两个问题：

为什么之前的例子中，在第二个事务的INSERT被阻塞了，而这次却执行成功了。

这是因为原来的语句中带有for update，这种读取是当前读，会加锁。而本次第一个事务中的SELECT仅仅是快照读，没有加任何锁。所以不会阻塞其他的插入。

数据库中的数据已经改变，为什么会读不到？

这个就是之前提到的next-key lock锁定的副本。RC及以下级别才会读到已经提交的事务。更多的业务逻辑是希望在某段时间内或者某个特定的逻辑区间中，前后查询到的数据是一致的，当前事务是和其他事务隔离的。这也是数据库在设计实现时遵循的ACID原则。

再给出RR条件下出现幻读的情形，这种情形不需要两个事务，一个事务就已经可以说明：

至于RR隔离级别下到底会不会出现幻读，就需要看幻读的定义中的查询到底是连续的查询还是不连续的查询。如果认为RR级别下可能会出现幻读，那该级别下也会出现不重复读。

RR隔离级别下，虽然不会出现幻读，但是会因此产生其他的问题。

前提：当前数据表中只存在(1,1),(5,5),(10,10)三组数据。

如果数据库隔离级别不是默认，可以执行SET SESSION tx_isolation='REPEATABLE-READ';(该语句不是全局设置)更新为RR。

然后执行下列操作：

4、更新丢失(Lost Update)

更新丢失

除了上述这类问题外，RR还会有丢失更新的问题。如下表给出的操作：

这个例子里，事务一的更新是无效的，尽管在这个事务里程序认为还存在(10,10)记录。事务一中更新之前的SELECT操作是快照读，所以读到了快照里的(10,10)，而UPDATE中的WHERE子句是当前读，取得是最新版本的数据，所以matched: 0 Changed: 0

如果上述例子中的操作是对同一条记录做修改，就会引起更新丢失。例如，事务一和二同时开启，事务一先执行update test set code=100 where id=10;，事务二再执行update test set code=200 where id=10;，事务一的更新就会被覆盖。

这就是经典的丢失更新问题，英文叫Lost Update，又叫提交覆盖，因为是最后执行更新的事务提交导致的覆盖。还有一种更新丢失叫做回滚覆盖，即一个事务的回滚把另一个事务提交的数据给回滚覆盖了，但是目前市面上所有的数据库都不支持这种stupid的操作，因此不再详述。

乐观锁与悲观锁

这种情况下，引入我们常见的两种方式来解决该问题：

乐观锁：在UPDATE的WHERE子句中加入版本号信息来确定修改是否生效；

悲观锁：在UPDATE执行前，SELECT后面加上FOR UPDATE来给记录加锁，保证记录在UPDATE前不被修改。SELECT ... FOR UPDATE是加上了X锁，也可以通过SELECT ... LOCK IN SHARE MODE加上S锁，来防止其他事务对该行的修改。

无论是乐观锁还是悲观锁，使用的思想都是一致的，那就是当前读。乐观锁利用当前读判断是否是最新版本，悲观锁利用当前读锁定行。

但是使用乐观锁时仍然需要非常谨慎，因为RR是可重复读的，一定不能在UPDATE之前先把版本号使用快照读获取出来。

四、InnoDB对不同语句执行时

的加锁状况

如果一个SQL语句要对二级索引(非主键索引)设置X模式的Record锁，InnoDB还会检索出相应的聚簇索引(主键索引)并对它们设置锁定。

1、SELECT ... FROM...不加锁

SELECT ... FROM是快照读取，除了SERIALIZABLE的事务隔离级别，该SQL语句执行时不会加任何锁。

SERIALIZABLE级别下，SELECT语句的执行会在遇到的索引记录上设置S模式的next-key锁。但是对于唯一索引，只锁定索引记录，而不会锁定gap。

2、UPDATE系列

S锁读取(SELECT ... LOCK IN SHARE MODE)，X锁读取(SELECT ... FOR UPDATE)、更新UPDATE和删除DELETE这四类语句，采用的锁取决于搜索条件中使用的索引类型。

如果使用唯一索引，InnoDB仅锁定索引记录本身，不锁定间隙；

如果使用非唯一索引，或者未命中索引，InnoDB使用间隙锁或者next-key锁来锁定索引范围，这样就可以阻止其他事务插入锁定范围。

UPDATE语句

UPDATE ... WHERE ... 在搜索遇到的每条记录上设置一个独占的next-key锁，如果是唯一索引只锁定记录。

当UPDATE修改聚簇索引时，将对受影响的二级索引采用隐式锁，隐式锁是在索引中对二级索引的记录逻辑加锁，实际上不产生锁对象，不占用内存空间。

例如update test set code=100 where id=10;执行的时候code=10的索引(code是二级索引，见文中给出的建表语句)会被加隐式锁，只有隐式锁产生冲突时才会变成显式锁(如S锁、X锁)。即此时另一个事务也去更新id=10这条记录，隐式锁就会升级为显示锁。

这样做的好处是降低了锁的开销。

UPDATE可能会导致新的普通索引的插入。当新的索引插入之前，会首先执行一次重复索引检查。在重复检查和插入时，更新操作会对受影响的二级索引记录采用共享锁定(S锁)。

DELETE语句

DELETE FROM ... WHERE ... 在搜索遇到的每条记录上设置一个独占的next-key锁，如果是唯一索引只锁定记录。

3、INSERT

INSERT区别于UPDATE系列单独列出，是因为它的处理方式较为特别。

插入行之前，会设置一种插入意向锁，插入意向锁表示插入的意图。如果其它事务在要插入的位置上设置了X锁，则无法获取插入意向锁，插入操作也因此阻塞。

INSERT在插入的行上设置X锁。该锁是一个Record锁，并不是next-key锁，即只锁定记录本身，不锁定间隙，因此不会阻止其他会话在这行记录前的间隙中插入新的记录。具体的加锁过程见下文。

五、可能的死锁场景

1、Duplicate key error引发的死锁

并发条件下，唯一键索引冲突可能会导致死锁，这种死锁一般分为两种：一种是rollback引发，另一种是commit引发。

rollback引发的Duplicate key死锁

我命名为insert-insert-insert-rollback死锁：

当事务一执行回滚时，事务二和事务三发生了死锁。InnoDB的死锁检测一旦检测到死锁发生，会自动失败其中一个事务，因此看到的结果是一个失败另一个成功。

为什么会死锁？

死锁产生的原因是事务一插入记录时，对(2,2)记录加X锁，此时事务二和事务三插入数据时检测到了重复键错误，事务二和事务三要在这条索引记录上设置S锁，由于X锁的存在，S锁的获取被阻塞。

事务一回滚，由于S锁和S锁是可以兼容的，因此事务二和事务三都获得了这条记录的S锁。此时其中一个事务希望插入，则该事务期望在这条记录上加上X锁，然而另一个事务持有S锁，S锁和X锁互相是不兼容的，两个事务就开始互相等待对方的锁释放，造成了死锁。

事务二和事务三为什么会加S锁，而不是直接等待X锁？

事务一的insert语句加的是隐式锁(隐式的Record锁、X锁)，但是其他事务插入同一行记录时，出现了唯一键冲突，事务一的隐式锁升级为显示锁。

事务二和事务三在插入之前判断到了唯一键冲突，是因为插入前的重复索引检查，这次检查必须进行一次当前读，于是非唯一索引就会被加上S模式的next-key锁，唯一索引就被加上了S模式的Record锁。

因为插入和更新之前都要进行重复索引检查而执行当前读操作，所以RR隔离级别下，同一个事务内不连续的查询，可能也会出现幻读的效果(但个人并不认为RR级别下也会出现幻读，幻读的定义应该是连续的读取)。而连续的查询由于都是读取快照，中间没有当前读的操作，所以不会出现幻读。

commit引发的Duplicate key死锁

delete-insert-insert-commit死锁：

这种情况下产生的死锁和insert-insert-insert-rollback死锁产生的原理一致。

2、数据插入的过程

经过以上分析，一条数据在插入时经过以下几个过程：

假设数据表test.test中存在(1,1)、(5,5)和(10,10)三条记录。

事务开启，尝试获取插入意向锁。例如，事务一执行了select * from test where id>8 for update，事务二要插入(9,9)，此时先要获取插入意向锁，由于事务一已经在对应的记录和间隙上加了X锁，因此事务二被阻塞，并且阻塞的原因是获取插入意向锁时被事务一的X锁阻塞。

获取意向锁之后，插入之前进行重复索引检查。重复索引检查为当前读，需要添加S锁。

如果是已经存在唯一索引，且索引未加锁。直接抛出Duplicate key的错误。如果存在唯一索引，且索引加锁，等待锁释放。

重复检查通过之后，加入X锁，插入记录

3、GAP与Insert Intention冲突引发死锁

update-insert死锁

仍然是表test，当前表中的记录如下:

使用show engine innodb status查看死锁状态。先后出现lock_mode X locks gap before rec insert intention waiting和lock_mode X locks gap before rec字眼，是gap锁和插入意向锁的冲突导致的死锁。

回顾select...for update的加锁范围

首先回顾一下两个事务中的select ... for update做了哪些加锁操作：

code=5时，首先会获取code=5的索引记录锁(Record锁)，根据之前gap锁的介绍，会在前一个索引和当前索引之间的间隙加锁，于是区间(1,5)之间被加上了X模式的gap锁。除此之外RR模式下，还会加next-key锁，于是区间(5,10]被加了next-key锁。

因此，code=5的加锁范围是，区间(1,5)的gap锁，{5}索引Record锁，(5,10]的next-key锁。即区间(1,10)上都被加上了X模式的锁。

同理，code=10的加锁范围是，区间(5,10)的gap锁，{10}索引Record锁，(10,+∞)的next-key锁。

由gap锁的特性，兼容矩阵中冲突的锁也可以被不同的事务同时加在一个间隙上。上述两个select ... for update语句出现了间隙锁的交集，code=5的next-key锁和code=10的gap锁有重叠的区域——(5,10)。

死锁的成因

当事务一执行插入语句时，会先加X模式的插入意向锁，即兼容矩阵中的IX锁。但是由于插入意向锁要锁定的位置存在X模式的gap锁。兼容矩阵中IX和X锁是不兼容的，因此事务一的IX锁会等待事务二的gap锁释放。

事务二也执行插入语句，与事务一同样，事务二的插入意向锁IX锁会等待事务一的gap锁释放。

两个事务互相等待对方先释放锁，因此出现死锁。

六、总结

除了以上给出的几种死锁模式，还有很多其他死锁的场景。

无论是哪种场景，万变不离其宗，都是由于某个区间上或者某一个记录上可以同时持有锁，例如不同事务在同一个间隙gap上的锁不冲突；不同事务中，S锁可以阻塞X锁的获取，但是不会阻塞另一个事务获取该S锁。这样才会出现两个事务同时持有锁，并互相等待，最终导致死锁。

其中需要注意的点是，增、删、改的操作都会进行一次当前读操作，以此获取最新版本的数据，并检测是否有重复的索引。这个过程除了会导致RR隔离级别下出现死锁之外还会导致其他两个问题：

第一个是可重复读可能会因为这次的当前读操作而中断，(同样，幻读可能也会因此产生)；

第二个是其他事务的更新可能会丢失(解决方式：悲观锁、乐观锁)。