MVCC能否解决幻读

参考:

https://blog.csdn.net/qq_35590091/article/details/107734005

https://blog.csdn.net/ashic/article/details/53735537

https://blog.csdn.net/QAQ123666/article/details/105084758 

 

 

 

 

 

 

MVCC能否解决幻读?

 “幻读”指,同一个事务里面连续执行两次同样的sql语句,可能导致不同结果的问题,第二次sql语句可能会返回之前不存在的行。

先给出结论:不能笼统的说能不能解决,因为有的情况下可以解决,但是有的情况下解决不了。


可以解决的情况

mysql里面实际上有两种读,一种是“快照读”,比如我们使用select进行查询,就是快照读,在“快照读"的情况下是可以解决“幻读”的问题的。使用的就是MVCC,具体来说如下图,几个事务并发执行:

可以看到,尽管别的事务已经提交插入和更新,但是事务A的select读取的还是一样的。具体就是mvcc利用历史版本信息(快照)来控制他能读取的数据的范围。具体的可以看看我的关于MVCC浅析的文章。


另外一种读是:“当前读”。对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式,此外,下面两个语句也是当前读:

1、select * from table where ? lock in share mode; (加共享锁)

2、select * from table where ? for update; (加排它锁)

因此总结一下,下面几个语句都是当前读,都会读取最新的快照数据,都会加锁(除了第一个加共享锁,其他都是互斥锁):

select * from table where ? lock in share mode; 
select * from table where ? for update; 
insert; 
update; 
delete;

 

在执行这几个操作时会读取最新的记录,即使是别的事务提交的数据也可以查询到。比如要update一条记录,但是在另一个事务中已经delete掉这条数据并且commit了,如果update就会产生冲突,所以在update的时候需要知道最新的数据。读取的是最新的数据,并且需要加锁(排它锁或者共享锁)。

举个例子,下面是在可重复读级别下,事务1在update后,对该数据加锁,事务B无法插入新的数据,这样事务A在update前后数据保持一致,避免了幻读,可以明确的是,update锁的肯定不只是已查询到的几条数据,因为这样无法阻止insert,有同学会说,那就是锁住了整张表呗,其实不是,其实这里的锁,是next-key locking(就是一个行锁+范围锁)实现的.行锁不必说,就是更新的时候锁住这一行,这样别的事务就不能同时进行修改操作了。范围锁(gap lock)锁则是防止插入。


什么是next key lock?

所谓的next key lock就是一个行锁(record lock)+范围锁(gap lock),比如某一个辅助索引(比如上面的class_id),如果它有1,3,5这几个值,那么当我们使用next key lock的锁住class_id=1的时候,实际上锁住了(-无穷,1],或者锁住class_id=3的时候,实际上锁住的是(1,3],也就是一个左开右闭的区间。如果此时别的事务要在这个区间内插入数据,就会被阻塞住。这个锁一直到事务提交才会释放。因此,即使出现了上面图片里面这种情况,也可以保证前后两次去读的内容一致,因为对这个辅助索引上的锁是:“next key lock”,他会锁住一个区间。

 但是注意,对于可重复读默认使用的就是next key lock,但是对于“唯一索引”,比如主键的索引,next key lock会降级成行锁,而不会锁住一个区间。因此,如果上面的事务1的update使用的是主键,事务2也使用主键进行插入,那么实际上事务2根本不会被阻塞,可以立即插入并返回。而对于非唯一索引,next key lock则不会降级。


什么情况MVCC也会出现幻读?

下面这样的情况:

1.a事务先select,b事务insert确实会加一个gap锁,但是如果b事务commit,这个gap锁就会释放(释放后a事务可以随意操作),

2.a事务再select出来的结果在MVCC下还和第一次select一样,

3.接着a事务不加条件地update,这个update会作用在所有行上(包括b事务新加的),

4.a事务再次select就会出现b事务中的新行,并且这个新行已经被update修改了.

上面这样,事务2提交之后,事务1再次执行update,因为这个是当前读,他会读取最新的数据,包括别的事务已经提交的,所以就会导致此时前后读取的数据不一致,出现幻读。

参考:

https://www.cnblogs.com/CoderAyu/p/11525408.html

 

 

 

 

 

Mysql(Innodb)如何避免幻读

 

幻读Phantom Rows

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

幻读问题是指一个事务的两次不同时间的相同查询返回了不同的的结果集。例如:一个 select 语句执行了两次,但是在第二次返回了第一次没有返回的行,那么这些行就是“phantom” row.

read view(或者说 MVCC)实现了一致性不锁定读(Consistent Nonlocking Reads),从而避免了(非当前读下)幻读

实验1:

开两个窗口设置

set session tx_isolation='REPEATABLE-READ';
select @@session.autocommit;select @@global.tx_isolation,@@session.tx_isolation;

create table read_view(text varchar(50));
insert into read_view values('init');

 

两个会话开始事务

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

 

SESSION_A执行一个查询,这个查询可以访问任何表,这个查询的目的是创建一个当前时间点的快照
START TRANSACTION WITH CONSISTENT SNAPSHOT;也可以达到同样的效果

SESSION_A>select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

 

 

SESSION_B 插入一条记录并提交

SESSION_B>insert into read_view values('after session A select');
Query OK, 1 row affected (0.01 sec)

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

 

SESSION_A

SESSION_A>select * from read_view;
+------+
| text |
+------+
| init |
+------+
1 row in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+------------------------+
| text                   |
+------------------------+
| init                   |
| after session A select |
+------------------------+
2 rows in set (0.00 sec)

由于 SESSION_A 第一次的查询开始于 SESSION_B 插入数据前,所以创建了一个以SELECT操作的时间为基准点的 read view,避免了幻读的产生

所以在 SESSION_A 的事务结束前,无法看到 SESSION_B 对表 read_view 做出的任何更改 (insert,delete,update)

实验2

两个会话开始事务

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

 

SESSION_B 在 SESSION_A 创建read view 前插入数据

SESSION_B>insert into read_view values('before Session_A select');
Query OK, 1 row affected (0.00 sec)

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

 

SESSION_A

SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| init                    |
| after session A select  |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)

SESSION_A>commit
    -> ;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| init                    |
| after session A select  |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)

 

由于 SESSION_A 第一次查询开始于 SESSION_B 对表做出更改并提交后,所以这次的 read view 包含了 SESSION_B 所做出的更改

在官方文档中写道
http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

一致性读是通过 MVCC 为查询提供了一个基于时间的点的快照。这个查询只能看到在自己之前提交的数据,而在查询开始之后提交的数据是不可以看到的。一个特例是,这个查询可以看到于自己开始之后的同一个事务产生的变化。这个特例会产生一些反常的现象

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

在默认隔离级别REPEATABLE READ下,同一事务的所有一致性读只会读取第一次查询时创建的快照

实验3

两个会话开始事务

SESSION_A开始事务并创建快照
SESSION_A>START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)


SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| init                    |
| after session A select  |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)


SESSION_B>insert into read_view values('anomaly'),('anomaly');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

SESSION_B>update read_view set text='INIT' where text='init';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| init                    |
| after session A select  |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)

SESSION_A更新了它并没有"看"到的行
SESSION_A>update read_view set text='anomaly!' where text='anomaly';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| init                    |
| after session A select  |
| before Session_A select |
| anomaly!                |
| anomaly!                |
+-------------------------+
5 rows in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| INIT                    |
| after session A select  |
| before Session_A select |
| anomaly!                |
| anomaly!                |
+-------------------------+
5 rows in set (0.00 sec)

 

 

观察实验步骤可以发现,在倒数第二次查询中,出现了一个并不存在的状态

the anomaly means that you might see the table in a state that never existed in the database

这里A的前后两次读,均为快照读,而且是在同一个事务中。但是B先插入直接提交,此时A再update,update属于当前读,所以可以作用于新插入的行,并且将修改行的当前版本号设为A的事务号,所以第二次的快照读,是可以读取到的,因为同事务号。这种情况符合MVCC的规则,如果要称为一种幻读也非不可,算为一个特殊情况来看待吧。


With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

 

 

在 read commit 隔离级别下,同一事务的每个一致性读sets and reads its own fresh snapshot.

实验4

修改事务隔离级别
set session tx_isolation='READ-COMMITTED'
两个会话开始事务

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)


SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| INIT                    |
| after session A select  |
| before Session_A select |
| anomaly!                |
| anomaly!                |
+-------------------------+
5 rows in set (0.00 sec)

SESSION_B>insert into read_view values('hehe');
Query OK, 1 row affected (0.00 sec)

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| INIT                    |
| after session A select  |
| before Session_A select |
| anomaly!                |
| anomaly!                |
| hehe                    |
+-------------------------+
6 rows in set (0.00 sec)

 

 

read commit 每次读取都是新的快照

 

InnoDB通过Nextkey lock解决了当前读时的幻读问题

Innodb行锁分为:

类型说明
Record Lock: 在索引上对单行记录加锁.
Gap Lock: 锁定一个范围的记录,但不包括记录本身.锁加在未使用的空闲空间上,可能是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间.
Next-Key Lock: 行锁与间隙锁组合起来用就叫做Next-Key Lock。锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题

实验5

创建表

(mysql@localhost) [fandb]> create table t5(id int,key(id));
Query OK, 0 rows affected (0.02 sec)

SESSION_A>insert into t5 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

 

开始实验

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    4 |
|    7 |
|   10 |
+------+
4 rows in set (0.00 sec)

SESSION_A>select * from t5 where id=7 for update;
+------+
| id   |
+------+
|    7 |
+------+
1 row in set (0.00 sec)


SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t5 values(2);
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(12);
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(5); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(7); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(9); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted


SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)


SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    4 |
|    7 |
|   10 |
+------+
4 rows in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
|    7 |
|   10 |
|   12 |
+------+
6 rows in set (0.00 sec)

 

当以当前读模式select * from t5 where id=7 for update;获取 id=7的数据时,产生了 Next-Key Lock,锁住了4-10范围和 id=7单个record
从而阻塞了 SESSION_B在这个范围内插入数据,而在除此之外的范围内是可以插入数据的。
在倒数第二个查询中,因为 read view 的存在,避免了我们看到 2和12两条数据,避免了幻读
同时因为 Next-Key Lock 的存在,阻塞了其他回话插入数据,因此当前模式读不会产生幻读(select for update 是以当前读模式获取数据)

###尽量使用唯一索引,因为唯一索引会把Next-Key Lock降级为Record Lock

实验6

创建表
(mysql@localhost) [fandb]> create table t6(id int primary key);
Query OK, 0 rows affected (0.02 sec)

SESSION_A>insert into t6 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

开始实验

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
|  1 |
|  4 |
|  7 |
| 10 |
+----+
4 rows in set (0.00 sec)

SESSION_A>select * from t6 where id=7 for update;
+----+
| id |
+----+
|  7 |
+----+
1 row in set (0.00 sec)


SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t6 values(5); --插入成功没有阻塞
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t6 values(8); --插入成功没有阻塞
Query OK, 1 row affected (0.00 sec)

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
|  1 |
|  4 |
|  7 |
| 10 |
+----+
4 rows in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
|  1 |
|  4 |
|  5 |
|  7 |
|  8 |
| 10 |
+----+
6 rows in set (0.00 sec)
 

当 id 列有唯一索引,Next-Key Lock 会降级为 Records Lock

 

posted on 2020-10-25 14:18  秦羽的思考  阅读(13723)  评论(3编辑  收藏  举报