快照读与当前读的学习

想不到一个好的ID
66 阅读34分钟

前置知识

MVCC

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。

MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

  • 快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

  • 当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：  简单的select操作，属于快照读，不加锁。

  • select * from table where ?;

当前读：  特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

  • select * from table where ? lock in share mode;

  • select * from table where ? for update;

  • insert into table values (…);

  • update table set ? where ?;

  • delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

1. 快照读

快照读，顾名思义，就是读取快照数据，也就是说当某个数据正在被修改的时候，也可以进行读取该数据，保证读写不冲突。

  • 快照读就是读的是快照生成的数据

  • 快照读的实现方式：undolog和多版本并发控制MVCC

当我们对记录做了变更操作时，就会产生undo记录，undo记录中存储的是老版数据，当一个的事务读取数据时，为了能够读取到老版本的数据，需要顺着undo列找到满足其可见性的记录，这个找满足可见行的记录依赖。就是说每次都是读取undolog中的数据。

所有普通的SELECT语句（plain SELECT）在READ COMMITTED、REPEATABLE READ隔离级别下都算是快照读，比如：

SELECT * FROM t;
SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col2

快照读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。

1.1 生成快照的时机

1.1.1 在读已提交隔离级别下，快照是什么时候生成的？

SQL语句开始执行的时候。

若一个事务中存在多个select查询，每个select开始执行的时候都会生成readView

1.1.2 在可重复读隔离级别下，快照是什么时候生成的？

事务开始的时候

若一个事务中存在多个select查询，在事务开始的时候生成readView。所以多个select(如果查询条件相同)查询的结果是一样的。

2. 当前读（Locking Reads）

当前读，读取的是最新版本，当前时刻很好理解。执行语句的时刻，库里（磁盘+buffer）是什么样子就是什么样子。并且对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题。

  • Mysql实现当前读是通过共享锁+独占锁+Next-Key Lock实现的。

  • 每次对行数据进行读取的时候，加共享锁。此时就不允许修改，但是允许其他事务读取，所以每次都可以读到最新的数据。

  • 每次对行数据进行修改的时候，加独占锁，不允许其他事务读取和修改。这种情况下其他事务读取的数据也一定是最新的数据。

  • 每次对范围行数据进行读取的时候，对这个范围加一个范围共享锁。

  • 每次对范围行数据进行修改的时候，读这个范围加一个范围独占锁。

2.1 共享锁和独占锁

我们前面说过，并发事务的读-读情况并不会引起什么问题，不过对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，所以设计MySQL的设计者给锁分了个类：

  • 共享锁，英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。

  • 独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。

假如事务T1首先获取了一条记录的S锁之后，事务T2接着也要访问这条记录：

  • 如果事务T2想要再获取一个记录的S锁，那么事务T2也会获得该锁，也就意味着事务T1和T2在该记录上同时持有S锁。

  • 如果事务T2想要再获取一个记录的X锁，那么此操作会被阻塞，直到事务T1提交之后将S锁释放掉。

如果事务T1首先获取了一条记录的X锁之后，那么不管事务T2接着想获取该记录的S锁还是X锁都会被阻塞，直到事务T1提交。

所以我们说S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的，画个表表示一下就是这样：

兼容性XS
X不兼容不兼容
S不兼容兼容

2.2 当前读的语句

我们前面说在采用加锁方式解决脏读、不可重复读、幻读这些问题时，读取一条记录时需要获取一下该记录的S锁，其实这是不严谨的，有时候想在读取记录时就获取记录的X锁，来禁止别的事务读写该记录，为此设计MySQL的大佬提出了两种比较特殊的SELECT语句格式：

2.2.1 对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE;

也就是在普通的SELECT语句后边加LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S锁（比方说别的事务也使用SELECT ... LOCK IN SHARE MODE语句来读取这些记录），但是不能获取这些记录的X锁（比方说使用SELECT ... FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的S锁释放掉。

2.2.2 对读取的记录加X锁：

SELECT ... FOR UPDATE;

也就是在普通的SELECT语句后边加FOR UPDATE，如果当前事务执行了该语句，那么它会为读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁（比方说别的事务使用SELECT ... LOCK IN SHARE MODE语句来读取这些记录），也不允许获取这些记录的X锁（比方也说使用SELECT ... FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的S锁或者X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的X锁释放掉。

2.3 哪些形式的SQL属于当前读：

  • select...lock in share mode ( 对读取的记录加S锁)
  • select...for update (对读取的记录加X锁)
  • 常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；

其中，当前读的 SQL 语句的 where 从句的不同也会影响加锁，包括是否使用索引，索引是否是唯一索引等等。

为什么常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读？

例如，假设要update一条记录，但是另一个事务已经delete这条数据并且commit了，如果不加锁就会产生冲突。所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题。

2.4 当前读的实现方式

当前读使用next-key locks实现

next-key locks = record lock + gap lock

3.MySQL是如何解决幻读的

3.1 什么是幻读

在一次事务里面，多次查询之中，后者查询出来的结果集多于前者的结果集的情况叫做幻读。而多出来的叫做幻行

Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。

这点很重要,第二次select多出来的情况下才属于幻读，不一致和少的情况下不属于幻读

如果Session B中是删除了一些符合id > 0的记录而不是插入新记录，那Session A中之后再根据id > 0的条件读取的记录变少了，这种现象算不算幻读呢？

  • 明确说一下，这种现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

3.2 为什么要解决幻读

在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。

3.3 MySQL 是如何解决幻读的

MySQL在 InnoDB存储引擎且隔离级别为RR的情况下解决了幻读的问题。

  • 在快照读读情况下，mysql通过mvcc来避免幻读。

  • 在当前读读情况下，mysql通过next-key lock来避免幻读。

3. 快照读的demo

3.1 版本链

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中有主键或者非NULL的UNIQUE键时都不会包含row_id列）：

  • trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。
  • roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

比方说我们的表student现在只包含一条记录：

mysql> select * from student;
+----+------+-------+
| id | name |  sex  |
+----+------+-------+
|  1 | evan | major |
+----+------+-------+

假设两个事务id分别为20、40的事务对这条记录进行UPDATE操作，操作流程如下：

发生时间编号SessionA（trx=20）SessionB（trx=40）
1begin;
2begin;
3update student set name='bob' where id = 1;
4update student set name='pop' where id = 1;
5commit;
6update student set name='eva' where id = 1;
7update student set name='jay' where id = 1;
8commit;

每次对记录进行改动，都会记录一条undo日志，每条undo日志都有一个roll_pointer属性，可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。

3.2 ReadView

对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；对于使用SERIALIZABLE隔离级别的事务来说，设计InnoDB的大佬规定使用加锁的方式来访问记录；

对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。为此，设计InnoDB的大佬提出了一个ReadView的概念，这个ReadView中主要包含4个比较重要的内容：

  • m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。

  • min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。

  • max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。

    • 注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。

  • creator_trx_id：表示生成该ReadView的事务的事务id。

    • 只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。

有了这个ReadView，这样在访问某条记录时，只需要按照下面的步骤判断记录的某个版本是否可见：

  • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
  • 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上面的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

我们还是以表student为例来，假设现在表student中只有一条由事务id为10的事务插入的一条记录：

mysql> select * from student;
+----+------+-------+
| id | name |  sex  |
+----+------+-------+
|  1 | evan | major |
+----+------+-------+

接下来看一下READ COMMITTED和REPEATABLE READ所谓的生成ReadView的时机不同到底不同在哪里。

3.3 READ COMMITTED —— 每次读取数据前都生成一个ReadView

比方说现在系统里有两个事务id分别为20、40的事务在执行：

# Transaction 20
BEGIN;

UPDATE student SET name = 'bob' WHERE id = 1;

UPDATE student SET name = 'pop' WHERE id = 1;

# Transaction 40
BEGIN;

# 更新了一些别的表的记录
...

此刻，表student中id=1的记录得到的版本链表如下所示：

假设现在有一个READ COMMITTED隔离级别的事务开始执行：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 20、40未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'evan'

这个SELECT1的执行过程如下：

  • 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[20, 40]，min_trx_id为20，max_trx_id为41，creator_trx_id为0。
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'pop'，该版本的trx_id值为20，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
  • 下一个版本的列name的内容是'bob'，该版本的trx_id值也为20，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
  • 下一个版本的列name的内容是'evan'，该版本的trx_id值为10，小于ReadView中的min_trx_id值20，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'evan'的记录。

之后，我们把事务id为20的事务提交一下，就像这样：

# Transaction 20
BEGIN;

UPDATE student SET name = 'bob' WHERE id = 1;

UPDATE student SET name = 'pop' WHERE id = 1;

COMMIT;

然后再到事务id为40的事务中更新一下表student中id为1的记录：

# Transaction 40
BEGIN;

# 更新了一些别的表的记录
update student set name='eva' where id = 1;
update student set name='jay' where id = 1;

使用READ COMMITTED隔离级别的事务中继续查找这个id=1的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 20、40均未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'evan'

# SELECT2：Transaction 20提交，Transaction 40未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'pop'

这个SELECT2的执行过程如下：

  • 在执行SELECT语句时会又会单独生成一个ReadView，该ReadView的m_ids列表的内容就是[40]（事务id为20的那个事务已经提交了，所以再次生成快照时就没有它了），min_trx_id为40，max_trx_id为41，creator_trx_id为0。
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'jay'，该版本的trx_id值为40，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
  • 下一个版本的列name的内容是'eva'，该版本的trx_id值为40，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
  • 下一个版本的列name的内容是'pop'，该版本的trx_id值为20，小于ReadView中的min_trx_id值40，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'pop'的记录。

以此类推，如果之后事务id为40的记录也提交了，再此在使用READ COMMITTED隔离级别的事务中查询表student中id值为1的记录时，得到的结果就是'jay'了。

总结一下就是：使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

3.4 REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。

比方说现在系统里有两个事务id分别为20、40的事务在执行：

# Transaction 20
BEGIN;

UPDATE student SET name = 'bob' WHERE id = 1;

UPDATE student SET name = 'pop' WHERE id = 1;

# Transaction 40
BEGIN;

# 更新了一些别的表的记录
...

假设现在有一个 REPEATABLE READ隔离级别的事务开始执行：

# 使用REPEATABLE READ隔离级别的事务
BEGIN;

# SELECT1：Transaction 20、40未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'evan'

这个SELECT1的执行过程如下：

  • 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[20, 40]，min_trx_id为20，max_trx_id为41，creator_trx_id为0。
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'pop'，该版本的trx_id值为20，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
  • 下一个版本的列name的内容是'bob'，该版本的trx_id值也为20，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
  • 下一个版本的列name的内容是'evan'，该版本的trx_id值为10，小于ReadView中的min_trx_id值20，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'evan'的记录。

之后，我们把事务id为20的事务提交一下，就像这样：

# Transaction 20
BEGIN;

UPDATE student SET name = 'bob' WHERE id = 1;

UPDATE student SET name = 'pop' WHERE id = 1;

COMMIT;

然后再到事务id为40的事务中更新一下表student中id=1的记录：

# Transaction 40
BEGIN;

# 更新了一些别的表的记录
update student set name='eva' where id = 1;
update student set name='jay' where id = 1;


# 使用REPEATABLE READ隔离级别的事务
BEGIN;

# SELECT1：Transaction 20、40均未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'evan'

# SELECT2：Transaction 20提交，Transaction 40未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'jay'

这个SELECT2的执行过程如下：

  • 因为当前事务的隔离级别为REPEATABLE READ，而之前在执行SELECT1时已经生成过ReadView了，所以此时直接复用之前的ReadView，之前的ReadView的m_ids列表的内容就是[20,40]，min_trx_id为20，max_trx_id为41，creator_trx_id为0。
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'jay'，该版本的trx_id值为40，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
  • 下一个版本的列name的内容是'eva'，该版本的trx_id值为40，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
  • 下一个版本的列name的内容是'pop'，该版本的trx_id值为20，而m_ids列表中是包含值为20的事务id的，所以该版本也不符合要求，同理下一个列name的内容是'bob'的版本也不符合要求。继续跳到下一个版本。
  • 下一个版本的列name的内容是'evan'，该版本的trx_id值为10，小于ReadView中的min_trx_id值20，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name的内容为'evan'的记录。

也就是说两次SELECT查询得到的结果是重复的，记录的列name的内容为'evan'，这就是可重复读的含义。如果我们之后再把事务id为40的记录提交了，然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个id为1的记录，得到的结果还是'evan'。

3.5 MVCC小结

从上面的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同

  • READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView
  • REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView

4. 当前度的demo

4.1 具体场景分析

我们使用下面这张student 表作为实例，其中 id 为主键，stu_no（学号）为二级唯一索引，stu_name（姓名）为二级非唯一索引，score（分数）无索引。

4.2 UPDATE 语句加锁分析

下面，我们先来分析 UPDATE 相关 SQL 在使用较为简单 where 从句情况下加锁情况。其中的分析原则也适用于 UPDATE,DELETE 和 SELECT ... FOR UPDATE等当前读的语句。

4.2.1 聚簇索引，查询命中

UPDATE t SET score = 9.2 WHERE id = 10

RC隔离级别下

指定主键，并且有此记录，加行锁

RR隔离级别下

指定主键，并且有此记录，加行锁

4.2.2 聚簇索引，查询未命中

UPDATE t SET score = 9.2 WHERE id = 16

RC隔离级别下

指定主键，无此记录，不需要加锁

RR隔离级别下

指定主键，无此记录，会在 id = 16 前后两个索引之间[10,18]加上间隙锁。

间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入新行，导致幻读，所以加间隙 S 锁和加间隙 X 锁没有任何区别。

值得注意的是，间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入新行，导致幻读，所以加间隙 S 锁和加间隙 X 锁没有任何区别。

4.2.3 二级唯一索引，查询命中

UPDATE t SET score = 9.2 WHERE stu_no = '411402'

RC隔离级别下

在 InnoDB 存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁。

RR隔离级别下

在 InnoDB 存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁。

4.2.4 二级唯一索引，查询未命中

UPDATE t SET score = 9.2 WHERE stu_no = '411408'

RC隔离级别下

未命中不会加锁

RR隔离级别下

  • 会在二级索,[411406,Suprenum]加了间隙锁

为什么不在主键上也加 GAP 锁呢？欢迎留言说出你的想法。

4.2.5 二级非唯一索引，查询命中

 UPDATE t SET score = 9.2 WHERE stu_name = 'eva'

RC隔离级别下

二级唯一索引和二级非唯一索引的加锁情况是一致的，都是在涉及的二级索引和对应的主键索引上加上排他记录锁

RR隔离级别下

但是在 RR 隔离等级下，加锁的情况产生了变化，它不仅对涉及的二级索引和主键索引加了排他记录锁，还在非唯一二级索引上加了三个间隙锁，锁住了两个 eva 索引值相关的三个范围。

那为什么唯一索引不需要加间隙锁呢？间隙锁的作用是为了解决幻读，防止其他事务插入相同索引值的记录，而唯一索引和主键约束都已经保证了该索引值肯定只有一条记录，所以无需加间隙锁。

要注意的是，上图虽然画着 4 个记录锁，三个间隙锁，但是实际上间隙锁和它右侧的记录锁会合并成 Next-Key 锁。

所以实际情况有两个 Next-Key 锁 和 一个间隙锁(eva50,evan60)

4.2.6 二级非唯一索引，查询未命中

UPDATE t SET score = 9.2 WHERE stu_name = 'coco'

RC隔离级别下

查询未命中不会加锁

RR隔离级别下

查询未命中，会在二级索引 bob 和 eva 之间加间隙

4.2.7 无索引

当 Where 从句的条件并不使用索引时，则会对全表进行扫描，在 RC 隔离等级下对所有的数据加排他记录锁。在RR 隔离等级下，除了给记录加锁，还会对记录和记录之间加间隙锁。和上边一样，间隙锁会和左侧的记录锁合并成 Next-Key 锁。

UPDATE t SET score = 9.2 WHERE score = 22

RC隔离级别下

在 RC 隔离等级下对所有的数据加排他记录锁。

RR隔离级别下

在RR 隔离等级下，除了给记录加锁，还会对记录和记录之间加间隙锁。

4.2.8 聚簇索引，范围查询

UPDATE t SET score = 9.2 WHERE id <= 25

RC隔离级别下

RC 场景下与等值查询类似，只会在涉及的 id = 10，id = 18 和 id = 25 索引上加排他记录锁。

RR隔离级别下

而在 RR 隔离等级下则有所不同，它会加上间隙锁，和对应的记录锁合并称为 Next-Key 锁。除此之外，它还会在(25, 30] 上分别加 Next-Key 锁。这一点是十分特殊的，具体原因还需要再探究。

4.2.9 二级非唯一索引，范围查询

此处给score加上非唯一索引，其他的都没有加索引

create index score_index on t(score);

RC隔离级别下

image.png

RR隔离级别下

4.2.10 修改索引值

 UPDATE t SET stu_name = 'rose' WHERE id = 40

UPDATE 语句修改索引值的情况可以分开分析，首先 Where 从句的加锁分析如上文所述，多了一步 Set 部分的加锁。

下图展示了 UPDATE t SET stu_name = 'rose' WHERE id = 40 在 RC 和 RR 隔离等级下的加锁情况。除了在主键 id 上进行加锁，还会对二级索引上的 Bob和rose上进行加X锁。

4.3 DELETE 语句加锁分析

4.3.1 聚簇索引，查询命中

delete from t WHERE id = 10

RC隔离级别下

指定主键，并且有此记录，只需要在该条记录上加 record lock(x锁)

RR隔离级别下

指定主键，并且有此记录，只需要在该条记录上加 record lock(x锁)

4.3.2 二级唯一索引，查询命中

delete from t WHERE stu_no = '411402'

RC隔离级别下

在 InnoDB 存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁。

因此delete语句会选择走istu_no列的索引进行where条件的过滤，在找到stu_no = '411402' 的记录后，首先会将unique索引上的stu_no = '411402' 索引记录加上X锁，同时，会根据读取到的id列，回主键索引，然后将聚簇索引上的id=25 对应的主键索引项加X锁。

为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t set id = 100 where id=25; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

结论： 若stu_no列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于stu_no unique索引上的stu_no = '411402' 的记录，另一把锁对应于聚簇索引上id=10的记录。

RR隔离级别下

在 InnoDB 存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁。

4.3.3 二级非唯一索引，查询命中

DELETE FROM t WHERE stu_name = 'eva'

RC隔离级别下

根据此图，可以看到，首先， stu_name列索引上，满足stu_name = 'eva' 查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。

结论： 若stu_name 列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

RR隔离级别下

RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。那么RR隔离级别下，如何防止幻读呢？

但是在 RR 隔离等级下，加锁的情况产生了变化，它不仅对涉及的二级索引和主键索引加了排他记录锁，还在非唯一二级索引上加了三个间隙锁，锁住了两个 eva 索引值相关的三个范围。

那为什么唯一索引不需要加间隙锁呢？间隙锁的作用是为了解决幻读，防止其他事务插入相同索引值的记录，而唯一索引和主键约束都已经保证了该索引值肯定只有一条记录，所以无需加间隙锁。

要注意的是，上图虽然画着 4 个记录锁，三个间隙锁，但是实际上间隙锁和它右侧的记录锁会合并成 Next-Key 锁。

所以实际情况有两个 Next-Key 锁 和 一个间隙锁(eva50,evan60)

GAP锁有何用？

其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。

确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。

所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。

如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生。

如图中所示，有哪些位置可以插入新的满足条件的项 (stu_name = 'eva' )，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。

  • 记录[bob,10]之前，不会插入stu_name = 'eva' 的记录；
  • [bob,10]与[eva,40]间可以插入[eva,0x]，[eva,1x]，[eva,2x]，[eva,3x]；
  • [eva,40]与[eva,50]间，可以插入新的[eva,4x]；
  • [eva,50]与[evan,60]间，可以插入新的[eva,5x],[eva,6x],[eva,7x]等
  • [evan,60]之后也不会插入满足条件的记录。

因此，为了保证[bob,10]与[eva,40]间，[eva,40]与[eva,50]间，[eva,50]与[evan,60]间不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个区间给锁起来。

Insert操作，如insert [eva,10]，首先会定位到[bob,10]与[eva,40]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert不能插入记录。

因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X锁)。同时还是增加3把GAP锁，将可能插入满足条件记录的3个区间给锁上，保证后续的Insert不能插入新的stu_name = 'eva' 的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

结论： Repeatable Read隔离级别下，stu_name列上有一个非唯一索引，对应SQL：DELETE FROM t WHERE stu_name = 'eva'; 首先，通过stu_name索引定位到第一条满足查询条件的记录，记录上加X锁，区间上加gap 锁，聚簇索引上的记录加X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[evan,60]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

4.3.4 无索引

DELETE FROM t WHERE score = 5.4

RC隔离级别下

由于score列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有三条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。

有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

结论： 若iscore列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

RR隔离级别下

由于score列上没有索引，因此只能走聚簇索引，进行全部扫描。

  • 首先，聚簇索引上的所有记录，都被加上了X锁。

  • 其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。

在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。

这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistent read如何触发：要么是read committed隔离级别；要么是Repeatable Read隔离级别，同时设置了innodb_locks_unsafe_for_binlog 参数。

结论： 在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

4.4 INSERT 语句加锁分析

接下来，我们来看一下 Insert 语句的加锁情况。

Insert 语句在两种情况下会加锁：

  • 为了防止幻读，如果记录之间加有间隙锁，此时不能 Insert；
  • 如果 Insert 的记录和已有记录造成唯一键冲突，此时不能 Insert；

除了上述情况，Insert 语句的锁都是隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁的机制来减少加锁的数量。

隐式锁的特点是只有在可能发生冲突时才加锁，减少了锁的数量。另外，隐式锁是针对被修改的 B+Tree 记录，因此都是记录类型的锁，不可能是间隙锁或 Next-Key 类型。

具体 Insert 语句的加锁流程如下：

  • 首先对插入的间隙加插入意向锁（Insert Intension Locks）

    • 如果该间隙已被加上了间隙锁或 Next-Key 锁，则加锁失败进入等待；
    • 如果没有，则加锁成功，表示可以插入；

  • 然后判断插入记录是否有唯一键，如果有，则进行唯一性约束检查

    • 如果没有锁， 判断该记录是否被标记为删除
    • 如果有锁，说明该记录正在处理（新增、删除或更新），且事务还未提交，加 S 锁等待；
    • 如果标记为删除，说明事务已经提交，还没来得及 purge，这时加 S 锁等待；
    • 如果没有标记删除，则报 duplicate key 错误；
    • 如果不存在相同键值，则完成插入
    • 如果存在相同键值，则判断该键值是否加锁

  • 插入记录并对记录加 X 记录锁；

avatar
文章
阅读
粉丝