Mysql(Innodb)如何避免幻读

程序员文章站 2022-06-14 16:40:23

...

幻读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

转自：http://blog.csdn.net/ashic/article/details/53735537

Mysql(Innodb)如何避免幻读

幻读Phantom Rows

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

实验1:

实验2

实验3

实验4

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

实验5

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

实验6

mysql幻读和不可重复读的区别介绍

MySQL可重复读级别能够解决幻读吗

MySQL InnoDB如何保证事务特性

Mysql如何避免全表扫描的方法

使用wordpress的$wpdb类读mysql数据库做ajax时出现的问题该如何解决

mysql幻读和不可重复读的区别介绍

MySQL 是如何解决幻读的

MySQL数据库的隔离级别之可重复读为什么能够有效防止幻读现象的出现

[MySQL] mysql的事务隔离和幻读和死锁问题

MySQL问答系列之如何避免ibdata1文件大小暴涨