mysql的读已提交和可重复读（Read Committed和Repeatable Read隔离级别）

1、共享锁和排他锁

1.1、共享锁

共享锁也叫S锁/读锁， 作用是锁住当前事务 select 的数据行，其它事务可以读这些数据行，但不能写。

使用：在查询语句后面显式增加 LOCK IN SHARE MODE

SELECT ... LOCK IN SHARE MODE;
1

1.2、排他锁

排他锁也叫X锁/写锁，作用是锁住事务中使用了排他锁的数据行，其他事务对这些数据行,既不能读也不能写。

使用：

1、MySql 的 InnoDB 引擎会为 insert、update、delete 操作中涉及的数据自动加排他锁（根据where条件语句）

2、对于一般的 select 语句，InnoDB 不会加任何锁，可加 FOR UPDATE，显式地加排他锁

SELECT ... FOR UPDATE;
1

ps：事务A提交前，事务B不能修改事务A中加过排他锁的数据行，也不能通过 for update 或 lock in share mode 的方式查询这些数据行；但可以直接通过普通 select 查询到数据(但查到的只是已提交过的数据)，因为普通查询没有任何锁机制。

1.3、总述

综上，若查询操作不显式加锁，普通 select 语句无锁（无锁的实质是MVCC；Serializable隔离级别的select有锁），insert、update、delete操作有排他锁。

2、MVCC

多版本并发控制（Multiversion Concurrency Control）是一种提升并发性的技术。最早的数据库系统，只有读读之间可以并发，读写、写读、写写都要阻塞。引入MVCC后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

在内部实现中，InnoDB 通过 undo log 保存每条数据的多个版本，并且能够找回数据的历史版本提供给用户读，每个事务读到的数据版本可能是不一样的。

MVCC只在 Read Committed 和 Repeatable Read 两个隔离级别下工作。
MVCC 的实现依赖隐藏字段、Read View、Undo log，我们逐个介绍。

2.1、隐藏字段

InnoDB存储引擎在每行数据的后面添加了三个隐藏字段：

1、DB_TRX_ID (6字节)：表示最近一次对本记录行作修改（insert | update）的事务ID。InnoDB会把 delete 操作认作 update，不过会更新一个另外的删除位，将行表示为deleted。并非真正删除。

2、DB_ROLL_PTR (7字节)：回滚指针，指向当前记录行的 undo log 信息

3、DB_ROW_ID (6字节)：随着新行插入而单调递增的行ID。理解：当表没有主键或唯一非空索引时，innodb就会使用这个行ID自动产生聚簇索引。如果有，聚簇索引就不会包含这个行ID了。DB_ROW_ID跟MVCC关系不大。

2.2、Read View

Read View 读视图，跟快照、snapshot 是一个概念。主要是用来做可见性判断的, 里面保存了对本事务不可见的其他活跃事务。首先我们来认识一下 Read View 里面的变量：

1、low_limit_id：目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。

2、up_limit_id：活跃事务列表trx_ids中最小的事务ID（如果trx_ids为空，则up_limit_id 为 low_limit_id）

3、trx_ids：Read View创建时其他未提交的活跃事务ID列表。意思就是创建Read View时，将当前未提交事务ID记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。
注意：Read View中trx_ids的活跃事务，不包括当前事务自己和已提交的事务（即只包括正在内存中的事务）

4、creator_trx_id：当前创建事务的ID，是一个递增的编号

class ReadView {
  /* ... */
private:
  trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */

  trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */

  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */

  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */

  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */

  m_closed;                     /* 标记 Read View 是否 close */
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.3、Undo log

Undo log 中存储的是老版本数据，当一个事务需要读取记录行时，如果当前(最新)记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。

大多数对数据的变更操作包括 insert/update/delete，在InnoDB里，undo log 分为如下两类：

①.insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。

②.update undo log : 事务对记录进行 delete 和 update 操作时产生的 undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

Purge线程：为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下旧记录的deleted_bit，并不真正将旧记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。purge线程自己也维护了一个read view，如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

2.4、update的具体流程

现假设有一条记录行如下

1.事务A（事务号为2）update 该记录的 Honor 列，将其改为"FMVP"：

①. 事务A先对该行加X锁
②. 把该行数据拷贝到 undo log中，作为旧版本
③. 修改该行的Honor为"FMVP"，并且修改DB_TRX_ID为2（事务A的号）, 回滚指针指向undo log中的旧版本。（然后还会将修改后的最新数据写入redo log）
④. 事务提交，释放排他锁

2.事务B（事务号为3）修改同一个记录行，将 Name 修改为"Iguodala"：

①. 事务B先对该行加排它锁
②. 把该行数据拷贝到undo log中，作为旧版本
③. 修改该行Name为"Iguodala"，并且修改DB_TRX_ID为3（事务B的号）, 回滚指针指向undo log中最新的旧版本。
④. 事务提交，释放排他锁

不同事务或者相同事务的对同一记录行的修改，会使该记录行的undo log成为一条链表，undo log的链首是最新的旧记录，链尾是最早的旧记录。

2.5、可见性比较算法

在innodb中，创建一个新事务，执行第一个select语句时，innodb会创建一个快照（read view），快照中保存了系统当前不应该被本事务看到的其他活跃事务的id列表（即trx_ids）。当用户在这个事务中要读取某个记录行的时候，innodb会将该记录行的 DB_TRX_ID 与该 Read View 中的一些变量进行比较，判断是否满足可见性条件。

假设当前事务要读取某一个记录行，该记录行的 DB_TRX_ID（即最新修改该行的事务ID）为 trx_id；Read View 中的活跃事务列表 trx_ids 中最早的事务ID为 up_limit_id，生成这个 Read Vew 时系统出现过的最大的事务 ID+1 记为 low_limit_id（即还未分配的事务ID）。
（速记：up是早，low是迟；trx_id为最新修改）

1、如果 trx_id < up_limit_id, 那么表明“最新修改该行的事务”在“当前事务”创建快照之前就提交了，所以该记录行(trx_id)的值对当前事务是可见的。跳到步骤5。
（trx_id修改早早就提交过了，所以返回trx_id的值即可）

2、如果 trx_id >= low_limit_id, 那么表明“最新修改该行的事务”在“当前事务”创建快照之后才修改该行，所以该记录行(trx_id)的值对当前事务不可见。跳到步骤4。
（trx_id迟迟才开始修改，当前事务肯定不可见，于是向前找可见的数据）

3、如果 up_limit_id <= trx_id < low_limit_id, 表明“最新修改该行的事务”在“当前事务”创建快照时可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表trx_ids进行查找（源码中是用的二分查找，因为本身是有序的）:

(1) 如果在活跃事务列表trx_ids中能找到 id 为 trx_id 的事务，表明在“当前事务”创建快照前(即select前)，“该记录行的值”被“id为trx_id的事务”修改了，但没有提交；或者在“当前事务”创建快照后，“该记录行的值”被“id为trx_id的事务”修改了（不管有无提交）；这些情况下，这个记录行的值对当前事务都是不可见的，跳到步骤4；

(2)在活跃事务列表中找不到，则表明“id为trx_id的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见，跳到步骤5。

4、在该记录行的 DB_ROLL_PTR 指针所指向的undo log回滚段中，取出最新的的旧事务号DB_TRX_ID, 将它赋给trx_id，然后跳到步骤1重新开始判断。

5、将该可见行的值返回。

源码：

//函数：read_view_sees_trx_id。
//read_view中保存了当前全局的事务的范围：【low_limit_id， up_limit_id】

//1. 当行记录的事务ID小于Read View的最小活跃事务id，可见
　　if (trx_id < view->up_limit_id) {
　　　　return(TRUE);
　　}
//2. 当行记录的事务ID大于等于Read View的最大活跃事务id，不可见
　　if (trx_id >= view->low_limit_id) {
　　　　return(FALSE);
　　}
//3. 当行记录的事务ID二者之间，判断trx_id是否在活跃事务列表中，如果在就不可见，不在就可见
　　for (i = 0; i < n_ids; i++) {
　　　　trx_id_t view_trx_id
　　　　　　= read_view_get_nth_trx_id(view, n_ids - i - 1);
　　　　if (trx_id <= view_trx_id) {
　　　　return(trx_id != view_trx_id);
　　　　}
　　}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

RR和RC的Read View产生的区别

①. 在innodb中的Repeatable Read级别, 只有事务在begin之后，执行第一条select时, 才会创建一个快照(read view)，将当前系统中活跃的其他事务记录起来；并且事务之后都是使用的这个快照，不会重新创建，直到事务结束。
②. 在innodb中的Read Committed级别, 事务在begin之后，执行每条select语句时，快照会被重置，即会重新创建一个快照(read view)。

在同一个事务中，select只能看到快照创建前已经提交的修改和该事务本身做的修改。

3、Read Committed 读已提交

此隔离级别要求当前事务不能读到其他事务的未提交数据，我们来亲测一下。
1）新建两个连接，来模拟两个事务（注意是新建"连接"而不是新建"查询编辑器"）。下面成为连接A和连接B
2）新建表

3）由于 mysql 默认的事务隔离级别是 Repeatable Read，所以先分别在两个连接中，把隔离级别改为 Read Committed

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1

查询当前隔离级别可使用：

SELECT @@transaction_isolation;
1

4）在连接A中开启事务，查询id=1的记录

START TRANSACTION;
SELECT id,class_name,teacher_id FROM class_teacher WHERE id=1;
1
2

结果：

5）在连接B中开启事务，更新 id=1 记录，且先不提交

START TRANSACTION;
UPDATE class_teacher SET class_name='初三三班' WHERE teacher_id=30; 
1
2

6）在当前连接中再次查询 id=1 的记录

SELECT id,class_name,teacher_id FROM class_teacher WHERE id=1;
1

结果：

可以看到，两次查询结果一致。虽然两次查询之间,有另一个事务对数据进行了修改，但由于此修改事务没有提交，所以读的仍然是“旧数据”。
7）把连接B中的事务提交

COMMIT;
1

8）再次在连接A中查询

SELECT id,class_name,teacher_id FROM class_teacher WHERE id=1;
1

可以看到，这时查询到的便是修改后的数据了。
这就是“读已提交”，一句话：select到的永远是已提交过的数据，未提交的数据不算数！

4、Repeatable Read 可重复读

事务A前后进行了两次select，其间事务B插入并提交了一条(符合select条件)的数据，但事务A两次select的结果仍然保持一致，叫可重复读。
我们常常说的“读”，其实指的是“快照读”（也即上文说的普通读），具体来说就是：

select ... from ... where ...
1

快照读不加锁，依赖MVCC进行事务隔离，可保证读已提交、可重复读和部分幻读，读到的可能是历史数据。

还有另外一种读，叫“当前读”，返回最新数据。具体指的是

//加S锁
select ... from ... where ... lock in share mode
//加X锁
select ... from ... where ... for update
//加X锁
update ... set ... where ...
delete from ... where...
insert into ... 
1
2
3
4
5
6
7
8

很多人到这里会晕，这里提供一种快速厘清的方法：我们现在在研究的，是事务A中的读！（具体来说就是写在事务A中的上述6种语句）

4.1、当前读

前面说了，普通读即快照读依赖MVCC；那当前读依赖什么来进行事务隔离呢？答案是Next-Key锁。
Next-Key锁 (行级锁) = S锁/X锁 (record lock) + 间隙锁（gap lock）

事实上，“当前读”除了会上S/X锁，还会上一把间隙锁。共享锁和排他锁保证了“当前读”的读已提交和可重复读；间隙锁解决“当前读”的幻读问题。（复习一下：所谓幻读就是两次(当前)读之间被插入了一条数据）

4.1.1、间隙锁

我们通过例子来理解gap锁。
表class_teacher中有3条数据

1、把隔离级别改回Repeatable Read并开启事务A并进行update操作

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION; 
UPDATE class_teacher SET class_name='初三三班' WHERE id<4; 
1
2
3

（这里的update操作就是读，当前读！读到的就是 id=3 的记录）
这时候会锁住读到的记录的前后间隙，此例中会锁住（0,3）和（3,11）的id，亦即id在此范围的记录不能插入！

2、把隔离级别改回Repeatable Read并开启事务B并进行insert操作

START TRANSACTION; 
INSERT INTO class_teacher VALUES (1,'初三8班',99);
1
2

此时insert操作会阻塞！（同理,插入id为(3,11)的数据也会阻塞；但插入id为12的数据会成功）

阻塞是由于间隙锁的存在，当事务A提交，间隙锁解锁后，才可以正常插入之前被事务A间隙锁封住的行！

总结：由于间隙锁的存在，所以事务A中,多次根据where条件(当前)读出的结果，保持一致。这就防止了当前读的幻读的出现。
（比如上面的例子，如果事务B能成功插入 id=1 的记录的话，那事务A的第一次 update 操作（根据where id<4）能读出1条记录，第二次 update 操作却能读出2条记录，从而发生幻读）