[本图来源](https://qimok.cn/669.html)

InnoDB的隔离性(二)

  在MySQL的隔离性(一)文章中,我们知道了MySQL的四种隔离性是基于 传统锁的并发控制(LBCC) 和 多版本并发控制(MVCC) 实现,但没有具体说明这两种方式的实现方式;并且基于传统锁的并发控制,InnoDB存储引擎的行级读锁与行级写锁是互斥的,但是在RC和RR隔离级别下,读写却不互斥,是因为加入了MVCC技术,所以,我们可以知道MVCC解决了读写不互斥的并发问题,提高了并发度,但是MVCC并没有解决幻读,即使在RR隔离级别下,使用普通的SELECT操作也存在幻读的问题.
  本文将重点说一说MySQL提供了哪些锁、引入的MVCC是怎么样的、幻读是怎么解决的.

基于锁的并发控制 --- LBCC

锁的粒度可以分为表锁\行锁\页锁,页锁不详细说明：
  表锁:表锁指的是锁住的是整张表,所以锁的粒度很大,影响了并发度,一般是DDL操作使用;MyISAM存储引擎是表锁级别,基本依赖于MySQL实现;由于直接锁住的是全表,所以实现的开销很小,不会出现死锁问题.
  行锁:行锁指的是锁住的是某一行或者某几行,所以锁的粒度很小,提高了并发度,常用于实现事务的不同隔离级别;InnoDB存储引擎是行锁级别,由于锁住的是行,所以实现的开销很大,会出现死锁问题.

  锁的兼容性可以分为共享锁和排他锁 :无论是表级锁还是行级锁,使用的共享锁和排他锁的兼容性都是一样的,如下:

	X	S
S	冲突	兼容
X	冲突	冲突

  本文将重点说说InnoDB存储引擎的行级锁,行锁是比较重量级的知识点,也是比较难、比较绕的一个话题.

MySQL实现的各种锁

共享锁(Share Locks)、排他锁(Exclusive Locks) ---兼容性

共享锁(S锁,读锁)和排他锁(X锁,写锁)是InnoDB存储引擎实现标准的行级锁定.
官方说法① :
共享锁(S锁) : 允许持有锁读取行的事务
排他锁(X锁) : 允许持有锁的事务进行更新或删除行
解释官方说法① :
共享锁(S锁) : 对当前事务中例如行数据R持有S锁,其他事务对行数据R申请S锁并不阻塞,申请X锁将会阻塞.
排他锁(X锁) : 对当前事务例如行数据R持有X锁,其他事务对行数据R申请S锁或X锁将会阻塞.

意向锁(Intention Locks) ---表级锁

这里需要特别说明的是 :
意向锁是表级锁,只与表锁有冲突,不与行锁有冲突!!!
意向锁是表级锁,只与表锁有冲突,不与行锁有冲突!!!
意向锁是表级锁,只与表锁有冲突,不与行锁有冲突!!!

官方说法① :
意向锁 : 允许行锁和表锁共存;意向锁是表级锁,指示事务稍后对表中的行需要哪种类型的锁(共享锁或排他锁).
官方说法② :
意向共享锁(IS)指示一个事务打算设置一个共享意向锁后再加S锁.
意向排他锁(IX)指示一个事务打算设置一个排他意向锁后再加X锁.

下列表格中的X锁和S锁指的都是表锁,意向锁不会与行级的共享 / 排他锁互斥

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

【PS : 把上面的表拆分为两个表,更加清晰些】

意向锁与意向锁 :

	IX	IS
IX	兼容	兼容
IS	兼容	兼容

【PS : 意向锁之间都是互相兼容的 : 为什么都是互相兼容的?可以这么理解,MySQL加意向锁都是基于行锁,也就是说是因为行锁才有了意向锁(加行锁前先加意向锁),如果两个事务都是意向锁,说明这两个事务都是行锁级别,修改的是两条不同的数据,所以意向锁没必要互斥】

意向锁与表锁 :

	IX	IS
S	冲突	兼容
X	冲突	冲突

【PS :意向锁的作用 : 如果存在事务1 2,事务1先持有行级写锁,事务2再持有表级写锁,这两个事务肯定是要互斥的,那么有什么办法可以让事务2知道事务1持有行锁而阻塞呢?有一种低效的做法就是去检测表中的每一行是否存在排他锁.很明显这是一个效率很差的做法,但是有了意向锁之后,情况就不一样了,意向锁的作用就体现出来了 : 事务1 加行级锁的时候先加意向排他锁(表级锁),此时事务2想持有表锁,但是发现事务1已经持有意向排他锁了,所以互斥了,这里只需要一次判断就解决了.简单明了的说 我当前使用的是行锁,但是我会先加意向锁,加意向锁就是想告诉表锁当前表的一些行数据被我锁住了,除了是意向共享锁(IS)与表级S锁是兼容外,其他的你都要进入阻塞,等我释放锁你才可以持有】

解释官方说法① :
  这里实现的"共存"是说行锁与表锁不互斥、表锁与表锁互斥的意思,举个例子 : 存在事务1 2,事务1锁住表中的一行(行级写锁).事务2锁住整个表(表级写锁),事务1既然锁住了某一行,其他事务就不可能修改这一行.这与事务2锁住整个表就能修改表中的任意一行形成了冲突,所以才有了意向锁.
  有了意向锁,前面例子中的事务1在申请行锁(写锁)之前,数据库会自动先给事务1申请意向排他锁IX.当事务2去申请表的写锁时就会失败,因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞.这个例子的事务1是申请行级写锁(X锁)前先加意向排他锁(表锁),事务2是申请表级写锁(X锁),所以X锁与IX锁冲突了.
  说白了,意向锁的作用就是加行锁前会加一层意向锁(表锁),当有另一事务申请表锁时,将被阻塞(除了IS与S锁).
解释官方说法② :
意向共享锁(IS) : 在加行级读锁(S锁)前会先加意向共享锁(IS)后再加行级读锁.
意向排他锁(IX) : 在加行级写锁(X锁)前会先加意向排他锁(IX)后再加行级写锁.
知道了意向锁的作用后,我们可以总结为:在InnoDB存储引擎中,加行锁前会申请意向锁,以阻塞其他申请表级锁的事务.

记录锁(Record Locks) ---行级锁

在RC隔离级别下,仅采用Record Lock.
官方说法① :
记录锁定是对索引记录的锁定.
官方说法② :
记录锁始终锁定索引记录,即使没有定义索引的表也是如此.在这种情况下,InnoDB存储引擎会隐藏的聚集索引,将该索引用于记录锁定.

解释官方说法 :
我们知道,索引分为聚集索引和辅助索引 :

• 对于存在辅助索引(包括唯一索引)条件的DML操作,MySQL通过记录锁来优先对辅助索引中符合条件数据和相对应的聚集索引行数据;
• 对于存在主键ID条件的DML操作,MySQL通过记录锁来对聚集索引中符合查询主键ID条件数据加锁,以阻止其他事务更新、删除 符合主键ID条件的这行数据;(这里记住并没有去锁住对应的辅助索引数据)
• 对于普通列的DML操作,由于是全表扫描,在RC 和 RR 隔离级别 用的锁模式并不一样.【PS : 接下来先看看 RC 隔离级别下是怎么锁的】

  在RC隔离级别下,即没有索引条件而扫描全表,MySQL锁住的只是符合条件的行,而不是全表数据,官方的说法是 :
  官方的说法① 对于锁定读取(SELECT 使用FOR UPDATE或LOCK IN SHARE MODE),UPDATE 语句和DELETE 语句,InnoDB存储引擎仅锁定索引记录,而不锁定它们之间的间隙,因此允许在锁定记录旁边自由插入新记录.间隙锁定仅用于外键约束检查和重复键检查.
  也就是说 : 在 RC 隔离级别下,只锁住符合条件的数据,而且没有引入间隙锁的,所以是存在幻读问题的.
  官方的说法② 对于锁定读取(SELECT 使用FOR UPDATE或LOCK IN SHARE MODE),UPDATE或 DELETE语句,InnoDB仅对其锁定读取或更新或删除的行持有锁.MySQL评估WHERE条件后,将释放不匹配行的记录锁.这大大降低了死锁的可能性,但是仍然可以发生.
  也就是说 : 在没有索引条件下会进行全表扫描,InnoDB存储引擎会对where条件不符合的行数据释放锁,只锁住符合条件的行数据.在这里,即使对不符合条件的行会释放锁,但是对全表数据进行加锁\释放锁操作还是降低了并发度的,所以,锁定读取或UPDATE或DELETE的时候尽量走索引.
  小结 : RC 隔离级别下,使用的是记录锁,并且只锁住符合条件的数据行.那么 RR 隔离级别下 使用的是什么锁呢 : 就是 记录锁 \ 间隙锁 \ 临键锁.
【PS : 下文有实践验证在 RC 隔离级别下的锁过程】

间隙锁(Gap Locks)、临键锁(Next-Key Locks) ---- RR隔离级别

备注:文章中,如果没有特别说明,我们把间隙锁记为开区间,把 Next-key Lock 记为前开后闭区间.

在 RR 隔离级别下,Next-Key Lock 锁来避免Phantom Problem(幻读).

• 间隙锁 : 锁的就是两个值之间的空隙,间隙锁遵循开区间;
• 临键锁 : 间隙锁和记录锁结合成为临键锁,临键锁遵循前开右闭原则.
• 临键锁 可以解决了幻读的问题,接下来看看 官方说法 :

间隙锁 :
官方的说法① : 间隙锁是对索引记录之间的间隙的锁定,或者是对第一个或最后一个索引记录之前的间隙的锁定.

• 间隙可能跨越单个索引值,多个索引值，甚至为空.
• 对于使用唯一索引来锁定唯一行来锁定行的语句,不需要间隙锁定(这不包括搜索条件仅包含多列唯一索引的某些列的情况,在这种情况下,会发生间隙锁定.)

官方的说法② : 间隙锁可以共存.一个事务进行的间隙锁定不会阻止另一事务对相同的间隙进行间隙锁定.共享和专用间隙锁之间没有区别.它们彼此不冲突,并且执行相同的功能.
解释官方说法 :
  间隙锁是InnoDB存储引擎中行锁的一种,但是这种锁锁住的却不止一行数据,他锁住的是多行,是一个数据范围.间隙锁的主要作用是为了防止出现幻读,但是它会把锁定范围扩大.对于具有等值条件且为唯一索引的DML操作,临键锁会降级为记录锁.
  间隙锁可以共存,其实可以简单的理解,间隙锁都是为了防止其他事务的INSERT导致当前事务出现幻读的问题,那么多个事务的间隙锁也都是为了防止其他事务INSERT,所以间隙锁是可以共存的.

临键锁 :
官方的说法① : 临键锁是索引记录上的记录锁定和索引记录之前的间隙上的间隙锁定的组合
官方的说法② : InnoDB存储引擎执行行级锁定的方式是,当它搜索或扫描表索引时,会在遇到的索引记录上设置共享或互斥锁.因此,行级锁实际上是索引记录锁.索引记录上的临键锁也会影响该索引记录之前的"间隙".即,临键锁是索引记录锁定加上索引记录之前的间隙上的间隙锁定.如果一个会话R在索引中的记录上具有共享或排他锁,则另一会话不能R在索引顺序之前的间隙中插入新的索引记录.
解释官方说法 :
  临键锁是由记录锁和间隙锁结合起来;对于等值且非唯一索引的DML语句,根据临键锁的前开右闭原则加间隙锁,对符合条件的数据加记录锁,同样的,对于范围查询且为索引的DML语句(范围查询中唯一索引也加间隙锁),也是根据临键锁的前开右闭原则加间隙锁.对于被间隙锁锁住的范围,这些范围在其他事务就不能做INSERT了.

【PS : 以下实践RR隔离级别下的加锁方式,会基于2个"原则",2个"优化"】
临键锁的加锁规则 ： 2个"原则",2个"优化":
原则1 : 临键锁遵循前开后闭区间原则.
原则2 : 查询过程中访问到的数据才会加记录锁(也就是说符合条件的数据).
优化1 : 索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 转为间隙锁.
优化2 : 索引上的等值查询,给唯一索引加锁的时候,next-key lock 转为记录锁.

  在RR隔离级别下,引入了间隙锁、临键锁,接下来看看官方的说法是 :
官方的说法① :对于锁定读取(ELECT带有FOR UPDATE或LOCK IN SHARE MODE),UPDATE和 DELETE语句,锁定取决于该语句使用的是具有唯一搜索条件的唯一索引还是范围类型搜索条件 :

• 对于具有唯一搜索条件的唯一索引,InnoDB存储引擎仅锁定找到的索引记录,而不锁定其前的间隙
• 对于其他搜索条件,InnoDB存储引擎使用间隙锁 临键锁 来锁定扫描的索引范围,以阻止其他会话插入该范围所覆盖的间隙.
  解释官方说法 :
  在RR隔离级别下,InnoDB存储引擎引入了间隔锁、临键锁(又称范围锁)来解决了幻读;
• 对于具有等值条件且为唯一索引的DML操作,临键锁会降级为记录锁,InnoDB存储引擎只锁住符合索引的那一行记录,也就是说只对这一行符合条件的数据加记录锁,而不加间隙锁,例如上面的优化2.
• 对于非等值条件(例如范围查询)且非唯一索引,InnoDB存储引擎会加间隙锁,被间隙锁加的数据范围不能INSERT操作.例如上面的原则1 2和优化1.
  下文中会实践验证在 RR 隔离级别下的锁过程.

插入意向锁(Insert Intention Locks) ---行锁

  插入意向锁是一种特殊的间隙锁,而不是上面的意向锁,插入意向锁与插入意向锁不冲突;但与间隙锁或者临键锁冲突,解决了幻读问题.
官方的说法① :
  插入意向锁是一种通过INSERT行插入之前的操作设置的间隙锁.此锁以这种方式发出信号,表明要插入的意图是 : 如果多个事务未插入间隙中的相同位置,则不必等待彼此插入的多个事务.假设有索引记录,其值分别为4和7.单独的事务分别尝试插入值5和6,在获得插入行的排他锁之前,每个事务都使用插入意图锁来锁定4和7之间的间隙,但不要互相阻塞,因为行是无冲突的.
解释官方说法 :
  这个锁表示插入的意向,只有在 INSERT 的时候才会有这个锁;插入意向锁和插入意向锁之间互不冲突,所以可以在同一个间隙中有多个事务同时插入不同索引的记录;插入意向锁只会和间隙锁或临键锁冲突,正如上面所说,间隙锁唯一的作用就是防止其他事务插入记录造成幻读,那么间隙锁是如何防止幻读的呢?正是由于在执行 INSERT 语句时需要加插入意向锁,而插入意向锁和间隙锁冲突,从而阻止了插入操作的执行.

显示加读锁 ---锁定读取

注意 : 仅当禁用自动提交时（START TRANSACTION通过autocommit以0开始事务或设置 为0才可以进行 显示加读锁）

  在InnoDB存储引擎下,对普通的查询(SELECT),不会加锁,但是InnoDB存储引擎提供了查询的时候可以明确的加锁 : SELECT... LOCK IN SHARE MODE(意向共享锁,IS) 和 SELECT...FOR UPDATE(意向排他锁,IX),前者在走索引查询无需回表的情况下 只锁住 辅助索引数据,不锁住对应的 聚集索引行数据;后者都会锁住符合条件的辅助索引行和对应的聚集索引行数据;这两者SELECT的显示加锁方式,在RC隔离级别下,只锁住了符合条件的数据,在RR隔离级别下,由于加入了间隙锁、临键锁,所以会进行范围锁.

• SELECT ... LOCK IN SHARE MODE :
  官方的说法① :
    在读取的任何行上设置共享模式锁定.其他会话可以读取行,但是在事务提交之前不能修改它们.如果这些行中的任何一个被尚未提交的另一个事务更改,则查询将等待直到该事务结束,然后使用最新值.
• SELECT ... FOR UPDATE :
  官方的说法② :
    对于索引记录,搜索遇到的情况,锁定行和任何关联的索引条目,就像您UPDATE为这些行发出语句一样
  解释官方说法 :
• SELECT ... LOCK IN SHARE MODE :
  在SELECT中先加意向共享锁(IS),后再加行级共享锁(S),其他事务不能对加了行级共享锁的数据进行UPDATE\DELETE;同一事务中,每次的SELECT ... LOCK IN SHARE MODE都会进行一次当前读,也就是读取到最新的版本数据.例如在RC 隔离级别下,事务1进行一次SELECT ... LOCK IN SHARE MODE,由于只是锁住符合条件的行数据,所以当另一个事务插入一条符合条件的数据的时候是不阻塞的,此时事务1再进行一次SELECT ... LOCK IN SHARE MODE,就会阻塞了,直到事务2提交或者回滚释放锁,事务1才可读取到最新的数据.
• SELECT ... FOR UPDATE :
  在SELECT中先加意向排他锁(IX),后再加行级排他锁(X),其他事务不能对加了行级排他锁的数据进行UPDATE\DELETE\SELECT
  下文中会实践验证在 RC\RR 隔离级别下SELECT显示加锁的锁过程.

插入自增锁(AUTO-INC)

  自增锁是一种特殊类型的表锁,当执行INSERT操作且表中存在自增列的时候就会申请自增锁.
  当插入表中有自增列时,数据库需要自动生成自增值,在生成自增值之前,它会先为该表加 AUTO_INC 表锁,其他事务的插入操作阻塞,这样保证生成的自增值肯定是唯一的.但只要并发度不高,单批量插入数据的时候,只能一条一条插入.

• AUTO_INC 锁互不兼容,也就是说同一张表同时只允许有一个自增锁.
• 自增锁不遵循二段锁协议,它并不是事务结束时释放,而是在 INSERT 语句执行结束时释放,这样可以提高并发插入的性能.
• 自增值一旦分配了就会 +1,如果事务回滚,自增值也不会减回去,所以自增值可能会出现中断的情况.
    显然,AUTO_INC 表锁会导致并发插入的效率降低,为了提高插入的并发性.我们可以通过参数 innodb_autoinc_lock_mode 控制分配自增值时的并发策略.

1. innodb_autoinc_lock_mode = 0

• 使用传统的 AUTO_INC 表锁,并发性比较差.

2. innodb_autoinc_lock_mode = 1

• MySQL 默认采用这种方式，是一种比较折中的方法
• MySQL 将插入语句分成三类 : Simple inserts、Bulk inserts、Mixed-mode inserts.
• Simple inserts : 通过分析 INSERT 语句可以明确知道插入数量,例如 INSERT INTO t3 VALUES(50,50,50), (60,60,60)
• Bulk inserts : 通过分析 INSERT 语句无法确定插入数量,例如 INSERT INTO table SELECT.
• Mixed-mode inserts : 不确定是否需要分配自增值的, 例如 INSERT INTO t3 VALUES(1,1,1), (NULL,2,2), (5,5,5), (NULL, 6,6) 或 INSERT ... ON DUPLICATE KEY UPDATE
• 这种模式的好处是既平衡了并发性,又能保证同一条 INSERT 语句分配的自增值是连续的.

3. innodb_autoinc_lock_mode = 2

• 全部都用轻量级锁 mutex,并发性能最高,按顺序依次分配自增值,不会预分配.
• 缺点是不能保证同一条 INSERT 语句内的自增值是连续的,这样在复制（replication）时,如果 binlog_format 为 statement-based（基于语句的复制）就会存在问题,因为是来一个分配一个,同一条 INSERT 语句内获得的自增值可能不连续,主从数据集会出现数据不一致.所以在做数据库同步时要特别注意这个配置.

实践是检验理论的唯一标准

准备测试数据 : 其中id为主键ID、c列为普通非唯一索引列、d列为非索引列

// 创建t3表
CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

// 插入数据
INSERT INTO t3 VALUES(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25),(30,30,30),(35,35,35),(40,40,40);

实践验证RC隔离级别下SELECT显示加锁的流程

// 设置 读提交 隔离级别 (设置成功后重新开启一个cmd窗口)
set global transaction isolation level read committed;

// 查询隔离级别
select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

RC隔离级别下,纪录锁是基本单位,所以以下的测试中说到的X锁、S锁都是使用纪录锁

• SELECT... LOCK IN SHARE MODE(意向共享锁,IS) :

验证一 : 走索引查询无需回表的情况下 只锁住 辅助索引数据,不锁住对应的 聚集索引行数据 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5
  T2时刻可能会存在一些疑惑 : 在RC 隔离级别下,走聚集索引的话只锁住聚集索引上的行数据,而不会索引对应的辅助索引数据,所以T2时刻只是锁住了聚集索引的行数据,并不会阻塞的.(可参考记录锁)

验证二 : 走索引且回表查询的情况下,锁住了辅助索引数据和对应的聚集索引行数据 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4

验证三 : 同一事务中,每次的SELECT ... LOCK IN SHARE MODE都会进行一次当前读,也就是读取到最新的版本数据 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5
  意向锁与意向锁不冲突,意向锁与行锁不冲突,意向锁只与表锁有冲突(详细参考意向锁).
  T2时刻可能会存在一些疑惑 : MySQL的插入提供了插入意向锁,插入意向锁不与意向锁、行锁冲突,与间隙锁、临键锁冲突,所以此时是可以插入的,并且如果有事务3 INSERT (55,20,55) 也是可以插入的,只是要等事务2 3 commit或者rollback,事务1的S锁才不阻塞.(详细参考插入意向锁)
  同一事务中,T1、T5时刻出现不同的查询结果集,这种现象就是RC级别的不可重复读现象(当然,不可重复读现象是因为每次的SELECT都会新建一个快照导致的).
  T1时刻对 条件 c = 20 对应的 辅助索引 和 聚集索引 上的数据持有S锁,但是没有阻止T2时刻INSERT数据,导致了T1、T5时刻查询到不同的结果集,这种是幻读现象.
总结 : RC隔离级别下 SELECT... LOCK IN SHARE MODE 并不能解决不可重复读、幻读问题.

• SELECT... FOR UPDATE(意向排他锁,IX) :

验证一 : 查询条件为聚集索引ID 会锁住符合条件的聚集索引行数据 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5 T6 T7 T8
  对于SELECT... LOCK IN SHARE MODE , 回表查询就会阻塞,反之就不会阻塞.
  对于 UPDATE\DELETE 查询条件如果为 辅助索引,则会先再辅助索引上加X锁,后在对于的聚集索引数据上加X锁(具体参考记录锁),所以都会阻塞

验证二 : 查询条件为辅助索引 会锁住符合条件的辅助索引以及对于的聚集索引行数据 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4
T1时刻可以看出与 IS (SELECT... LOCK IN SHARE MODE)的最大区别了

验证三 : 查询条件为普通列 , 即全表扫描 会锁住符合条件的聚集索引行数据

  验证三就不演示图了,跟验证一的结果是一样的,区别就是性能问题,查询为普通列的话要进行全表扫描后,锁住符合条件的聚集索引数据行,在全表扫描过程中,对不符合条件数据的会释放锁(具体参考记录锁),所以与验证一相比,同样锁住的是聚集索引上的数据行,但是查询条件为普通列的时候肯定比主键ID性能低.

验证四 : 对INSERT操作是否解决了幻读问题 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5
同SELECT... LOCK IN SHARE MODE 一样,存在不可重复读、幻读现象

IX(SELECT... FOR UPDATE) 和 IS(SELECT... LOCK IN SHARE MODE) 小结 (RC 隔离级别下) :
  两者最大的区别就是 IS 走辅助索引且无需回表查询的时候是不锁住聚集索引上的数据行的,而 IX 走辅助索引且无论有没有回表查询都会锁住聚集索引上的数据行.
  两者的共同点,走主键ID、普通列查询的时候,只锁住聚集索引上相对应的数据行.
如果IS 走辅助索引且回表查询,那么会锁住聚集索引上相对应的数据行.

实践验证RC隔离级别下INSERT\UPDATE\DELETE的锁流程

// 设置 读提交 隔离级别 (设置成功后重新开启一个cmd窗口)
set global transaction isolation level read committed;

// 查询隔离级别
select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

RC隔离级别下,纪录锁是基本单位,所以以下的测试中说到的X锁、S锁都是使用纪录锁

• UPDATE :

验证一 : 对于存在辅助索引(包括唯一索引)条件的DML操作,MySQL通过记录锁来优先对辅助索引中符合条件数据的加锁,后再锁住对应的聚集索引行数据看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5

验证二 : 对于存在主键ID条件的DML操作,MySQL通过记录锁来对聚集索引中符合查询主键ID条件数据加锁,以阻止其他事务更新、删除 符合主键ID条件的这些行数据;(这里记住并没有去锁住对应的辅助索引数据)看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4

• INSERT :

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5 T6 T7
  根据插入自增锁,只锁住自增值,不锁整张表,也就是说只锁住id = 45,不锁住全表.同时需要对 c = 20 这行数据插入到 辅助索引中,所以辅助索引中 c = 20 也加X锁了.

几种DML操作的小结 :

1. UPDATE ：
   - 当前事务在修改条件为辅助索引的时候,会对符合条件的辅助索引和相对应的聚集索引上的数据行上加X锁,导致其他事务想对这些持有X锁的数据(辅助索引、聚集索引)做DML操作的时候阻塞了
   - 当前事务在修改条件为主键ID的时候,会对符合条件的聚集索引上的数据行上加X锁,导致其他事务想对这些持有X锁的数据(聚集索引)做DML操作的时候阻塞了
   - 当前事务在修改条件为普通列的时候,会继续全表扫描找到符合条件的数据,并对符合条件的聚集索引上的数据行上加X锁,导致其他事务想对这些持有X锁的数据(聚集索引)做DML操作的时候阻塞了
2. DELETE :
   - 当前事务在删除条件为辅助索引的时候,也跟UPDATE的结果阻塞情况是一样的.
   - 当前事务在删除条件为主键ID的时候,会对符合条件的辅助索引和相对应的聚集索引上的数据行上加X锁(辅助索引、聚集索引).
   - 当前事务在删除条件为普通列的时候,会锁住该行数据存在的索引列对应的辅助索引数据和聚集索引数据(辅助索引、聚集索引).
   - 可执行测试
3. SELECT... FOR UPDATE :
   - 当前事务在查询条件为辅助索引的时候,也跟UPDATE的结果阻塞情况是一样的.
   - 当前事务在查询条件为主键ID的时候,也跟UPDATE的结果阻塞是一样的.
   - 当前事务在查询条件为普通列的时候,也跟UPDATE的结果阻塞是一样的.
4. SELECT... LOCK IN SHARE MODE :
   - 当前事务在查询条件为辅助索引的时候,如果是回表查询,也跟UPDATE的结果阻塞情况是一样的.
   - 当前事务在查询条件为主键ID的时候,也跟UPDATE的结果阻塞是一样的.
   - 当前事务在查询条件为普通列的时候,也跟UPDATE的结果阻塞是一样的.
5. INSERT ：
   - 对插入到辅助索引中的数据行,如果存在于辅助索引,这些数据会持有X锁.

  这五种操作都会进行加锁操作,在RC RR隔离级别下,统称为 当前读,当前读为了读取最新版本数据,所以需要加锁防止其他事务修改,下面MVCC有解释.

准备测试数据 : 其中id为主键ID、a列为唯一索引、c列为普通非唯一索引列、d列为非索引列

// 创建t4表
CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `a` (`a`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

// 插入数据
INSERT INTO t4 VALUES(0,0,0,0),(5,5,5,5),(10,10,10,10),(15,15,15,15),(20,20,20,20),(25,25,25,25),(30,30,30,30),(35,35,35,35),(40,40,40,40);

// 设置 可重复读 隔离级别 (设置成功后重新开启一个cmd窗口)
set global transaction isolation level repeatable read;

// 查询隔离级别
select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

实践验证RR隔离级别下SELECT显示加锁的流程

RR隔离级别下,临键锁是基本单位
我们知道,INSERT操作会申请插入意向锁,插入意向锁与间隙锁、临键锁冲突的,不与行锁冲突,这点很重要,因为它解决了幻读问题
RR隔离级别下,锁的分析是基于以下2个原则和2个优化分析
原则1 : 临键锁遵循前开后闭区间原则.
原则2 : 查询过程中访问到的数据才会加记录锁(也就是说符合条件的数据).
优化1 : 索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 转为间隙锁.
优化2 : 索引上的等值查询,给唯一索引加锁的时候,next-key lock 转为记录锁.

验证一 : 对于具有等值条件且为唯一索引的DML操作,临键锁会降级为记录锁,InnoDB只锁住符合索引的那一行记录,也就是说只对这一行符合条件的数据加记录锁,而不加间隙锁,例如上面的优化2 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5

验证二 : 对于搜索范围条件且为唯一索引的DML操作,同样的使用间隙锁、临键锁,根据原则1、原则2、优化1 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5 T6 T7 T8

验证三 : 等值非唯一索引查询(例如c列),是如何加间隙锁、临键锁、纪录锁的 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5,而第二图右边依次是T6 T7 T8

验证四 : RR隔离级别引入间隙锁、临键锁,是否解决了幻读问题? 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3 T4 T5
对于T1时刻,查询到的符合数据会加记录锁,而在查找期间,在左右两边查询到第一个不符合数据就终止继续往下查了(除非是普通列需要扫描全表),分别在这两个数据之间加间隙锁,从而避免了幻读.

验证五 :对于查询不到数据,是否也有加间隙锁、临键锁、记录锁? 看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图分为事务 1 和 事务 2 , 执行顺序为 T1 T2 T3
  对于T1时刻,根据B+Tree范围查询规则,找到左边第一个不等于11的值为10,右边第一个不等于11的值为15,根据原则1 原则2 优化1 得出 (10,15);再T2时刻c = 11 的数据行插入阻塞了,再次验证了幻读问题的解决.

验证六 : 普通列查询(例如d列),是如何加间隙锁、临键锁、纪录锁的,由于是全表扫描,根据原则2、优化2,所以会锁住全表的数据,相当于锁表啦,可以自己测试看看

验证七 : 主键ID查询,是如何加间隙锁、临键锁、纪录锁的,其实跟唯一索引是差不多的,可以自己测试看看

  SELECT... FOR UPDATE(IX) : SELECT... FOR UPDATE(IX) 与 SELECT... LOCK IN SHARE MODE(IS) 的最大区别就是 IS索引查询且不回表的情况下只锁住辅助索引上的数据,当前 如果有符合条件的数据,相对应的聚集索引上的数据也是要加记录锁的,知道了这个最大区别,就可以分析SELECT... FOR UPDATE(IX) 在RR 隔离级别下的锁流程了,这里就不具体分析SELECT... FOR UPDATE(IX)了.

实践验证RR隔离级别下UPDATE的锁流程

验证一 : 对于存在辅助索引(不包括唯一索引)条件的DML操作,InnoDB是如何加间隙锁、临键锁、记录锁的看下图 : ↓ ↓ ↓ ↓ ↓ ↓

验证二 : 修改的字段是索引值,且在辅助索引中已经持有间隙锁、临键锁、记录锁了看下图 : ↓ ↓ ↓ ↓ ↓ ↓

为了演示这种情况 ,插入一条c = 20的数据: INSERT INTO T4(id,a,c,d) VALUES(9,9,20,9);

验证三 : 对于存在唯一索引条件的DML操作,InnoDB是如何加间隙锁、临键锁、记录锁的看下图 : ↓ ↓ ↓ ↓ ↓ ↓

上图左边是事务 1 右图是事务 2 , 执行顺序为 T1 T2 T3 T4
  对于修改条件是唯一索引,对唯一索引和聚集索引上相对应的数据加记录锁(X锁).

验证四 : 对于普通列条件但修改的数据是索引列的DML操作,InnoDB是如何加间隙锁、临键锁、记录锁的看下图 : ↓ ↓ ↓ ↓ ↓ ↓

  由于修改条件是普通列,所以需要全表扫描,间隙锁是对扫描到的范围加锁的,由于是在聚集索引上全表扫描,所以此时聚集索引上的数据全部加锁了.由于修改的数据是c列,所以还会在修改数据的c列索引上和符合查询条件数据的c列索引上加锁.

小结 :

1. UPDATE ：
   • 当前事务在修改条件为辅助索引的时候,会查找左右第一个不等于修改条件值形成一个范围锁,而对符合修改条件值的数据和对于聚集所有上的数据都会加上记录锁
   • 当前事务在修改条件为普通列且修改数据是普通列的时候,会在聚集索引上扫描修改条件值的数据,并对符合数据在聚集索引上加记录锁
   • 当修改数据是索引列的时候,会在修改数据的索引上加记录锁.例如 update t4 set c = 20 where d = 30; 那么 c = 20 在辅助索引上会加记录锁.

  如果理解了MySQL各种锁的机制,并对上面的实践有充分的理解,那么在RR隔离级别下的INSERT\DELETE也都相差不多的.

多版本并发控制 --- MVCC

  MySQL引入的 MVCC多版本控制并发 技术,在 RC 和 RR 隔离级别下才使用此技术,而在读未提交隔离级别下,由于可读到未提交的数据,所以读写锁并不互斥,也就没有引入MVCC了.
  MVCC的主要作用是实现了读写锁不互斥.InnoDB实现的MVCC结合了undo log、read view、三个隐式字段.
  undo log : 提供事务回滚,记录了事务执行过程中的回滚段,也就是说一行原始数据可能有多个不同版本的镜像数据.
  read view : 主要作用是判断当前版本数据的可见性;read view通过列表(trx_list)记录了当前系统正活跃的事务ID(即使有其他新事务,也会加入到列表中),并且还有两个属性 : up_limit_id(记录trx_list列表中事务ID最小的ID) , low_limit_id (目前已出现过的事务ID的最大值+1).

MVCC的两种核心读操作 : 快照读 和 当前读 :
  快照读 : 对于普通的SELECT(不包括 SELECT... LOCK IN SHARE MODE 和 SELECT... FOR UPDATE),就会有快照读;InnoDB会给一个快照读创建一个read view,可以通过read view作为判断条件来找出当前事务能够看到哪个版本的数据,可能是最新的数据,也有可能是该行记录的undo log里面的某个版本的数据,取决于是RC或者RR哪种隔离级别.

• 在 RC 隔离级别下, 每次进行一次 普通的SELECT(不包括 SELECT... LOCK IN SHARE MODE 和 SELECT... FOR UPDATE),就会进行一次快照读,所以每次的SELECT读取的结果可能不同,这就是不可重复读现象.
• 在 RR 隔离级别下, 当开启一个事务后,就会进行一次快照,当前事务进行SELECT,不会进行快照读了,所以每次的SELECT读取的结果都是一样的,这就解决了RC隔离级别下的不可重复读现象.

  当前读 : 读取的是最新版本的数据(已提交的最新数据).当前读会加锁,防止在读的时候其他事务修改数据而导致读取的不是最新的数据;在MySQL中一下的语句都会执行当前读:

• SELECT... LOCK IN SHARE MODE
• SELECT... FOR UPDATE
• INSERT \ UPDATE \ DELETE
  举个例子 : 存在两个事务1 2,且 c列的值为10 事务1 执行 UPDATE ... set c = c + 1 where id = 10,此时事务1暂不commit , 事务2 执行 UPDATE ... set c = c + 1 where id = 10,此时由于事务1没有提交(二阶段提交)而导致事务2阻塞,事务1提交后c列的值为11,并且释放锁,事务2获取锁,执行SQL语句,此时事务2的UPDATE语句就会进行一次当前读,获取到最新的数据c列已经是11了,所以事务2执行提交后c列为12,这就是当前读.

RC隔离级别下测试读写不互斥,复现不可重复读现象

// 设置 读提交 隔离级别 (设置成功后重新开启一个cmd窗口)
set global transaction isolation level read committed;

// 查询隔离级别
select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

上图中 左边是事务1 右边是事务2 依次执行顺序是 T1 T2 T3
  RC隔离级别下,使用MVCC实现了读写不互斥,并且同一事务中每次的SELECT操作都会进行一次快照读,出现了不可重复读现象.

RR隔离级别下测试读写不互斥,复现可重复读现象

// 设置 可重复读 隔离级别 (设置成功后重新开启一个cmd窗口)
set global transaction isolation level repeatable read;

// 查询隔离级别
select @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

上图中 左边是事务1 右边是事务2 依次执行顺序是 T1 T2 T3 T4 T5
  RR隔离级别下,使用MVCC实现了读写不互斥,在开启事务的那一刻起,就进行了一次快照读,而在同一事务中每次的重复查询,都不会快照读,这就是可重复读现象.

MVCC小结

• MVCC 实现了 读锁与写锁不互斥,在并发读写数据库时,可以做到在读操作时不用阻塞写操作,写操作也不用阻塞读操作,提高了数据库并发读写的性能.
• MVCC 的快照读 : 创建一个read view,结合undolog、当前事务ID等来判断数据版本的可见性.
• MVCC 的当前读 : 读取的是最新版本的数据(已提交的最新数据).当前读会加锁,防止在读的时候其他事务修改数据而导致读取的不是最新的数据.
• MySQL只在RC 和 RR 隔离级别下使用了MVCC技术 :
  ①:在RC隔离级别下,在当前事务中每次的普通SELECT,都会进行一次新的快照读,所以存在不同的SELECT可能查询到不同的结果,这就是不可重复读现象.
  ②:在RR隔离级别下,在当前事务开启的那一刻,就进行一次快照读,每次的普通SELECT,都不会进行新的快照读了,所以在当前事务中的每次SELECT都总是查询到相同的数据,这就是可重复读现象,解决了RC隔离级别下的不可重复读现象.
• MVCC的引入并没有解决了幻读的问题,而是解决了RC隔离级别不可重复读现象

结束语

  MySQL提供了各种锁、MVCC实现了不同隔离级别下的并发,不同隔离级别的并发度不一样,具体使用哪种隔离级别根据业务具体分析.
  希望看完这篇文章的你有所收获!

参考连接

• MySQL 官方文档
• 解决死锁之路 - 了解常见的锁类型
• 详解 MySql InnoDB 中意向锁的作用
• 【MySQL笔记】正确的理解MySQL的MVCC及实现原理