mysql 快照读 幻读,InnoDB的MVCC如何解决不可重复读和快照读的幻读,当前读用next-key解决幻读...

InnoDB默认的隔离级别是RR(可重复读)，可以解决脏读和不可重复读，只解决了快照读情况下的幻读问题,当前读情况下解决幻读问题得靠next-key锁。

mysql如何实现避免幻读:

在快照读读情况下，mysql通过mvcc来避免幻读。

在当前读读情况下，mysql通过next-key来避免幻读

- 快照读, 读取专门的快照 (对于RC，快照(ReadView)会在每个语句中创建。对于RR，快照是在事务启动时创建的)

```

简单的select操作即可(不需要加锁,如: select ... lock in share mode, select ... for update)

```

针对的也是select操作

- 当前读, 读取最新版本的记录, 没有快照。 在InnoDB中，当前读取根本不会创建任何快照。

```

select ... lock in share mode

select ... for update

insert

update

delete

针对如下操作, 会让如下操作阻塞:

```

insert

update

delete

```

- 在RR(可重复读)级别下, 快照读是通过MVVC(多版本控制)和undo log来实现的,

当前读是通过手动加record lock(记录锁)和gap lock(间隙锁)来实现的。所以从上面的显示来看，如果需要实时显示数据，还是需要通过加锁来实现。这个时候会使用next-key技术来实现。所以说

事务的隔离级别：

快照读的情况下解决幻读问题:

脏读：事务A读取了事务B提交的数据，但是B事务由于某种原因导致事务回滚，但是A读取的仍然是事务B回滚之前的数据

不可重复读：事务A读取了一条数据，这时事务B将该条数据修改，事务A再次读取该条数据时，和最开始读取的数据不一致

幻读：事务A读取了一批数据，例如select * from user where age =10;读取出了5调数据。这时事务B又向user表插入了一条数据

insert into user(age)values(10)。事务A再次查询时，会发现多了一条数据，这就是幻读。

什么是MVCC？

多版本并发控制。InnoDB为每行记录添加了一个版本号(系统版本号)，每当修改数据时，版本号加一。

在读取事务开始时，系统会给事务一个当前版本号，事务会读取版本号<=当前版本号的数据，这时就算另一个事务插入一个数据，并立马提交，新插入这条数据的版本号会比读取事务的版本号高，因此读取事务读的数据还是不会变。

例如：

此时books表中有5条数据，版本号为1

事务A，系统版本号2：select * from books；因为1<=2所以此时会读取5条数据。

事务B，系统版本号3：insert into books ...，插入一条数据，新插入的数据版本号为3，而其他的数据的版本号仍然是2，插入完成之后commit，事务结束。

事务A，系统版本号2：再次select * from books；只能读取<=2的数据，事务B新插入的那条数据版本号为3，因此读不出来，解决了幻读的问题。

当前读 (current read)解决幻读问题:

1.打开客户端1查看隔离级别及初始数据

mysql> SELECT @@SESSION.tx_isolation;

+------------------------+

| @@SESSION.tx_isolation |

+------------------------+

| REPEATABLE-READ |

+------------------------+

1 row in set (0.00 sec)

mysql> select * from test_transaction;

+----+-----------+-----+--------+--------------------+

| id | user_name | age | gender | desctiption |

+----+-----------+-----+--------+--------------------+

| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |

| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |

| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |

+----+-----------+-----+--------+--------------------+

3 rows in set (0.00 sec)

mysql>

2.打开客户端2查看隔离级别及初始数据

mysql> SELECT @@SESSION.tx_isolation;

+------------------------+

| @@SESSION.tx_isolation |

+------------------------+

| REPEATABLE-READ |

+------------------------+

1 row in set (0.00 sec)

mysql> select * from test_transaction;

+----+-----------+-----+--------+--------------------+

| id | user_name | age | gender | desctiption |

+----+-----------+-----+--------+--------------------+

| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |

| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |

| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |

+----+-----------+-----+--------+--------------------+

3 rows in set (0.00 sec)

mysql>

3.在客户端2中开启事务, 然后查询数据

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_transaction;

+----+-----------+-----+--------+--------------------+

| id | user_name | age | gender | desctiption |

+----+-----------+-----+--------+--------------------+

| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |

| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |

| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |

+----+-----------+-----+--------+--------------------+

3 rows in set (0.00 sec)

mysql>

4.在客户端1中插入一条id为4的新数据 (直接自动提交)

mysql> insert into test_transaction (`id`,`user_name`,`age`,`gender`,`desctiption`) values (4, '死侍', 18, 0, 'A bad boy');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test_transaction;

+----+-----------+-----+--------+--------------------+

| id | user_name | age | gender | desctiption |

+----+-----------+-----+--------+--------------------+

| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |

| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |

| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |

| 4 | 死侍 | 18 | 0 | A bad boy |

+----+-----------+-----+--------+--------------------+

4 rows in set (0.00 sec)

mysql>

5.在客户端2事务中再次查询数据, 发现数据没有变化(表示可以重复读, 并且克服了幻读)!! 但是在客户端2事务中插入一条id为4的新数据, 发现提示数据已经存在!!!

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_transaction;

+----+-----------+-----+--------+--------------------+

| id | user_name | age | gender | desctiption |

+----+-----------+-----+--------+--------------------+

| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |

| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |

| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |

+----+-----------+-----+--------+--------------------+

3 rows in set (0.00 sec)

mysql> select * from test_transaction;

+----+-----------+-----+--------+--------------------+

| id | user_name | age | gender | desctiption |

+----+-----------+-----+--------+--------------------+

| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |

| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |

| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |

+----+-----------+-----+--------+--------------------+

3 rows in set (0.00 sec)

mysql> insert into test_transaction (`id`,`user_name`,`age`,`gender`,`desctiption`) values (4, '死侍', 18, 0, 'A bad boy');

1062 - Duplicate entry '4' for key 'PRIMARY'

mysql>

//并且, 此时`update/delete`也是可以操作这条在事务中看不到的记录的!

6.那么这是什么问题呢?

可以参考MySQL官方文档 -- 一致性非阻塞读

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction.

个人认为应该翻译为: 数据库状态的快照适用于事务中的SELECT语句, 而不一定适用于所有DML语句。 如果您插入或修改某些行, 然后提交该事务, 则从另一个并发REPEATABLE READ事务发出的DELETE或UPDATE语句就可能会影响那些刚刚提交的行, 即使该事务无法查询它们。 如果事务更新或删除由不同事务提交的行, 则这些更改对当前事务变得可见。

当然, 使用隔离性的最高隔离级别SERIALIZABLE也可以解决幻读, 但该隔离级别在实际中很少使用!