MySql的间隙锁和不可重复读

SQL标准中DB隔离级别有：

read uncommitted：可以读到其它transaction 未提交数据
read committed：可以读到其它transaction 已提交数据
repeatable read：一个transaction中相同的查询，每次获取的结果是一样的
serialize：所有操作串行

脏读 即一个transaction 可以读到另一个的未提交数据。

不可重复读 即一个transaction 中，两次相同的查询会读到不一样的结果。

幻读 一个transaction中，之前不存在的记录，突然存在了。

隔离级别	脏读	不可重复读	幻读
RC	不存在	存在	存在
RR	不存在	不存在	存在

RC级别可以看到其它transaction的提交，所以它是不可重复读的，同时也会存在幻读
RR级别不可看到其它transaction的更改，所以它是可重复读的。但SQL标准定义的RR存在幻读。（虽然mysql的实现中做到了RR无幻读）。为什么SQL 标准定义的RR 会有幻读呢？ 首先要进一步清晰不可重复读和幻读的区别

幻读和不可重复读

幻读和不可重复读不太好区分。不可重复读针对update/delete等操作和已有的数据, 而幻读针对的是insert类型操作。一次transaction中，之前读取过的数据被其它transaction 更改提交了，并且在本transaction中能够看到。这是不可重复读。一次transaction中，之前没有的数据，再次操作时却有了，是幻读。虽然幻读也是一种不可重复读，但还是要把它们区分开讨论。这是因为，这两种问题的解决方案很不一样。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。

所以幻读和不可重复读最大的区别是如何用锁来解决他们产生的问题。

MVCC

用锁解决不可重复读的问题非常简单。只需要对transaction中读取到的数据加行锁。保证读取过程中该行不被修改即可。但这样会造成部分数据的操作变成串行。主流的数据库一般不通过锁的方式来实现可重复读，它们采取的方式叫 MVCC （multi-version concurrent control），我们以mysql 的MVCC 来说明。

在一个支持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锁 (排它锁)
 

mysql 在每一行后面加了几个字段，来标识每一行的状态， 如 row_id 记录行的聚簇索引 key , 创建/更新事务ID, 删除事务ID，回滚ID ， 如：

   ROW_ID, F1, F2, ... ROW_ID, 创建事务ID, 删除事务ID，回滚ID

INSERT 操作会把创建事务ID 置为自己的transaction ID , 删除事务ID 和回滚ID 为空

UPDATE 操作会：

    1. 创建该行的回滚记录
    2. 更新创建事务ID 为自己的ID
    3. 更新回滚ID 指向回滚记录

DELETE 操作会

1. 创建该行回滚记录
2. 更新删除事务ID为自己的transaction ID
3. 更新回滚ID

SELECT 操作会

查找删除事务ID 为空（没删除）或大于自己事务ID 的记录（自己transaction 开始之后删除的）
and
创建事务ID 小于等于自己的transaction id （确保不是自己transaction后创建的）

通过这种方式，可以发现mysql 其实实现了可重复读。并且不必加锁，因为各事务可以维护和操作不同版本的数据。

也行你认为该方式不仅解决了可重复读，还解决了幻读。 确实，假设有transaction A和B ，并且A早于B 开始，那么B中insert的记录A 是读不到的，从这个角度说是解决了幻读。但实际上并不完全对，mysql中的读其实有两种，当前读和快照读

可在不已提交隔离级别加入MVCC解决快照读的不可重复与幻读问题，但不能解决当前读的幻读问题

当前读和快照读

RR模式MYSQL 中的SELECT 操作只读取某一时间点的数据，即transaction开始时刻的数据，尽管后续有transaction 对数据做出了更改，当前transaction 也看不到。这有点类似于 MYSQL 在transaction开始的时刻打了一个快照。所以这种读叫快照读。它可重复但不是实时的。

MYSQL 中还有另外一种读叫当前读（CURRENT READ）. 这种读只读取表中当前最新的已提交的数据，可以理解为是一种READ COMMITTED。当前读一般发生在
UPDATE/DELETE/INSERT 以及 SELECT ... FOR UPDATE 和 LOCK ... IN SHARE MODE中

间隙锁 和 next-key 锁

记录锁 存在于包括 主键索引 在内的 唯一索引 中，锁定单条索引记录。 间隙锁 存在于 非唯一索引 中，锁定 开区间 范围内的一段间隔，它是基于 临键锁 实现的。

where全部命中不加间隙锁只会加记录锁，部分命中会加间隙锁。

用例子说明一下，假设我们有数据表如下：

其中number上有索引
start transaction;

select * from t4;
+--------+
| number |
+--------+
|      5 |
|     10 |
|     15 |
+--------+

select * from t4 where number=10 for update;
+--------+
| number |
+--------+
|     10 |
+--------+

上述语句用当前读，读取number=10 ， 这种情况下要避免幻读，即接下来:

• insert 操作不会插入到 number=10 t1
• update操作不能更新 number=10 这行
• delete操作不能删除number = 10这行

mysql 所用的方式很简单，通过row锁锁住 number=10的行，阻止update/delete。 通过间隙锁锁住10 可能出现的位置

因为 number 有索引，通过索引我们知道 number = 10 可能出现的位置有两处 5-10 和 10-15 ， 所以mysql 会把这两处锁住， 从其它transaction 去 insert 数据到 5-10 和10 - 15 的位置会被卡住。这就是间隙锁。我们验证一下

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into t4 values(18);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into t4 values(6);
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted
MariaDB [test]> insert into t4 values(11);
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted

除了 18 可以成功，其它两条被卡住

但要注意的是：mysql 通过间隙锁来锁住目标记录可能出现的位置，如果检索条件有索引，可以通过索引锁住目标位置，如果索引是unique 则不用锁间隙，因为不会出现间隙，如果没有索引会锁住全表

间隙锁加行锁的方式来防止当前读的幻读，在mysql中叫next key锁

为什么 DML/SELECT FOR UPDATE, SELECT FOR SHARE 是当前读

SELECT FOR UPDATE/SHARE 是当前读比较好理解。这两种读的目的就是锁住记录，不让他人更改，所以锁住快照没有意义

那么DML 为什么是当前读呢？考虑以下场景

start transaction;

select * from t4;
+--------+
| number |
+--------+
|      5 |
|     10 |
|     15 |
+--------+

select * from t4 where number=10 for update;
+--------+
| number |
+--------+
|     10 |
+--------+

这时有另外transaction 进行DML 操作，如果insert / update / delete 不是当前读, 那么 SELECT FOR UPDATE的锁仍然毫无意义..

总结：

        mysql默认的读是可重复读，但是可重复读存在幻读的问题。可重复读是通过MVCC来避免不可重复读问题的。但是还存在当前读的幻读问题，如果想解决这个问题可以通过间隙锁来实现，间隙锁主要是在索引的next指针上加锁，保证一块区域加锁。使这块区域不能插入新的数据。从而保证了幻读问题。