快照读与当前读的学习

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 COMMITTEDREPEATABLE 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也会获得该锁,也就意味着事务T1T2在该记录上同时持有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 记录锁;