前置知识
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锁
也是不兼容的,画个表表示一下就是这样:
兼容性 | X | S |
---|---|---|
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) |
---|---|---|
1 | begin; | |
2 | begin; | |
3 | update student set name='bob' where id = 1; | |
4 | update student set name='pop' where id = 1; | |
5 | commit; | |
6 | update student set name='eva' where id = 1; | |
7 | update student set name='jay' where id = 1; | |
8 | commit; |
每次对记录进行改动,都会记录一条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隔离级别下
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 记录锁;