MySQL 脏读、不可重复读、幻读

2019-03-26 数据库阅读量

起因

事务隔离性问题

脏读: 指一个线程中的事务读取到了另外一个线程中未提交的数据。
不可重复读(虚读): 指一个线程中的事务读取到了另外一个线程中提交的update的数据
幻读: 指一个线程中的事务读取到了另外一个线程中提交的insert的数据

隔离级别

隔离级别	脏读(`Dirty Read`)	不可重复读(`NoRepeatable Read`)	幻读(`Phantom Read`)
未提交读(`Read uncommitted`)	可能	可能	可能
已提交读(`Read committed`)	不可能	可能	可能
可重复读(`Repeatable read`)	不可能	不可能	可能
可串行化(`Serizlizable`)	不可能	不可能	不可能

我们可以通过以下命令 查看/设置 全局/会话 的事务隔离级别

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

// 查看当前session的事务隔离级别：
mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.04 sec)

// 查看全局的事务隔离级别。
mysql> show global variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.03 sec)

# 设定全局的隔离级别 设定会话 global 替换为 session 即可 把set语法温习一下
# SET [GLOABL] config_name = 'foobar';
# SET @@[session|global].config_name = 'foobar';
# SELECT @@[global.]config_name;

# RU
SET @@global.tx_isolation  = 0;
SET @@global.tx_isolation  = 'READ-UNCOMMITTED';
SET @@session.tx_isolation = 'READ-UNCOMMITTED';

# RC
SET @@global.tx_isolation  = 1;
SET @@global.tx_isolation  = 'READ-COMMITTED';
SET @@session.tx_isolation = 'READ-COMMITTED';

# RR
SET @@global.tx_isolation  = 2;
SET @@global.tx_isolation  = 'REPEATABLE-READ';
SET @@session.tx_isolation = 'REPEATABLE-READ';

# Serializable
SET @@global.tx_isolation  = 3;
SET @@global.tx_isolation  = 'SERIALIZABLE';
SET @@session.tx_isolation = 'SERIALIZABLE';

从上面可看到默认的隔离等级: 可重复读 REPEATABLE-READ | 2：解决脏读，不可重复读的问题，存在幻读的问题，使用 MMVC机制实现可重复读

脏读

脏读：（同时操作都没提交的读取）
脏读又称无效数据读出。一个事务读取另外一个事务还没有提交的数据叫脏读。

事务T1

mysql> use test;
Database changed

mysql> SET @@global.tx_isolation  = 'READ-UNCOMMITTED';
       SET @@session.tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| READ-UNCOMMITTED      | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.01 sec)

mysql> select * from test;
Empty set

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

mysql> insert into test values (1, 'caox1', '111111');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 111111   |
+----+----------+----------+
1 row in set (0.02 sec)

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 111111   |
|  2 | caox2    | 222222   |
+----+----------+----------+
2 rows in set (0.03 sec)

事务T2

mysql> use test;
Database changed

# 将隔离等级设置为RU
mysql> SET @@global.tx_isolation  = 'READ-UNCOMMITTED';
       SET @@session.tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| READ-UNCOMMITTED      | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.01 sec)

mysql> select * from test;
Empty set

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

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 111111   |
+----+----------+----------+
1 row in set (0.02 sec)

mysql> insert into test values (2, 'caox2', '222222');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 111111   |
|  2 | caox2    | 222222   |
+----+----------+----------+
2 rows in set (0.03 sec)

当前MySQL默认隔离等级为RR

事务T1

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caoxl    | 123456   |
+----+----------+----------+
1 row in set (0.02 sec)

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

mysql> update test set password = 654321 where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caoxl    | 654321   |
+----+----------+----------+
1 row in set (0.03 sec)

事务T2

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

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

// T1 commit之前
mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caoxl    | 123456   |
+----+----------+----------+
1 row in set (0.01 sec)

// T1 commit之后
mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caoxl    | 123456   |
+----+----------+----------+
1 row in set (0.02 sec)

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

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caoxl    | 654321   |
+----+----------+----------+
1 row in set (0.04 sec)

不可重复读(虚读)

同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致
不可重复读的侧重点在于更新修改数据

事务T1

mysql> use test;
Database changed

mysql> SET @@global.tx_isolation  = 'READ-COMMITTED';
       SET @@session.tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-COMMITTED        | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 111111   |
|  2 | caox2    | 222222   |
+----+----------+----------+
2 rows in set (0.02 sec)

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

mysql> update test set password = 999999 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 999999   |
|  2 | caox2    | 222222   |
+----+----------+----------+
2 rows in set (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

事务T2

mysql> use test;
Database changed

mysql> SET @@global.tx_isolation  = 'READ-COMMITTED';
       SET @@session.tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-COMMITTED        | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)

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

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 111111   |
|  2 | caox2    | 222222   |
+----+----------+----------+
2 rows in set (0.02 sec)

// T1事务提交前
mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 111111   |
|  2 | caox2    | 222222   |
+----+----------+----------+
2 rows in set (0.02 sec)

// T1事务提交后
mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caox1    | 999999   |
|  2 | caox2    | 222222   |
+----+----------+----------+
2 rows in set (0.03 sec)

幻读

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

幻读会在 RU / RC / RR 级别下出现，SERIALIZABLE 则杜绝了幻读，但 RU / RC 下还会存在脏读，不可重复读，故我们就以 RR 级别来研究幻读，排除其他干扰。

注意：RR 级别下存在幻读的可能，但也是可以使用对记录手动加 X锁的方法消除幻读。
SERIALIZABLE 正是对所有事务都加 X锁才杜绝了幻读，但很多场景下我们的业务sql并不会存在幻读的风险。
SERIALIZABLE 的一刀切虽然事务绝对安全，但性能会有很多不必要的损失。
故可以在 RR 下根据业务需求决定是否加锁，存在幻读风险我们加锁，不存在就不加锁，事务安全与性能兼备，这也是 RR 作为mysql默认隔离级别的原因，所以需要正确的理解幻读。

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。
更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

事务T1

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

mysql> desc test;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(255)     | NO   | UNI | NULL    |                |
| password | varchar(255)     | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

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

mysql> select * from test where id = 1;
Empty set

// 事务T2 commit之前
mysql> insert into test values (1, 'caoxl', '123456');
1205 - Lock wait timeout exceeded; try restarting transaction

mysql> select * from test where id = 1;
Empty set

// 事务T2 commit之后
mysql> insert into test values (1, 'caoxl', '123456');
1062 - Duplicate entry '1' for key 'PRIMARY'

mysql> select * from test where id = 1;
Empty set

事务T2

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

mysql> desc test;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(255)     | NO   | UNI | NULL    |                |
| password | varchar(255)     | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

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

mysql> select * from test where id = 1;
Empty set

mysql> insert into test values (1,'caoxl','123456');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where id = 1;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | caoxl    | 123456   |
+----+----------+----------+
1 row in set (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

Update 幻读

事务T1

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | caox2    | 111222   |
|  3 | caox3    | 333333   |
|  4 | caox4    | 444444   |
+----+----------+----------+
3 rows in set (0.03 sec)

// 事务T2提交前,更新失败,提示被锁
mysql> update test set password = 123456;
1205 - Lock wait timeout exceeded; try restarting transaction

// 事务T2提交前,查询显示只有3条数据
mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | caox2    | 111222   |
|  3 | caox3    | 333333   |
|  4 | caox4    | 444444   |
+----+----------+----------+
3 rows in set (0.03 sec)

// 事务T2提交后,查询显示只有3条数据
mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | caox2    | 111222   |
|  3 | caox3    | 333333   |
|  4 | caox4    | 444444   |
+----+----------+----------+
3 rows in set (0.03 sec)

// 执行更新,提示 "4 rows affected" 也就是有4条数据受影响
mysql> update test set password = 123456;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | caox2    | 123456   |
|  3 | caox3    | 123456   |
|  4 | caox4    | 123456   |
|  5 | caox5    | 123456   |
+----+----------+----------+
4 rows in set (0.03 sec)

事务T2

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

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | caox2    | 222222   |
|  3 | caox3    | 333333   |
|  4 | caox4    | 444444   |
+----+----------+----------+
3 rows in set (0.01 sec)

mysql> insert into test values (5, 'caox5', '555555');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  2 | caox2    | 222222   |
|  3 | caox3    | 333333   |
|  4 | caox4    | 444444   |
|  5 | caox5    | 555555   |
+----+----------+----------+
4 rows in set (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

MyISAM不存在幻读现象,InnoDB存在幻读现象

step1 T1: select * from test where id = 1;

step2 T2: insert into test values (1, 'caoxl', '123456');

step3 T1: insert into test values (1, 'caoxl', '123456');

step4 T1: select * from test where id = 1;

T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。
T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。

这里有个注意点，关于幻读，在数据库规范里，RR 级别会导致幻读，
但是，由于 Mysql 的优化，MySql 的 RR 级别不会导致幻读：
在使用默认的 select 时，MySql 使用 MVCC 机制保证不会幻读；
你也可以使用锁，在使用锁时，例如 for update（X 锁），lock in share mode（S 锁），MySql 会使用 Next-Key Lock 来保证不会发生幻读。
前者称为快照读，后者称为当前读。

其他

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

InnoDB行锁分为:

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

`MySQL` 如何实现避免幻读？

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

什么是快照读和当前读 ?

**快照读(snapshot read)**：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁

1	select * from table where ?;

当前读(current read): 特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

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 ?;

什么是`mvcc` ?

mvcc全称是multi version concurrent control（多版本并发控制）
mysql把每个操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号

select: 事务每次只能读到创建版本号小于等于此次系统版本号的记录，同时行的删除版本号不存在或者大于当前事务的版本号。
update: 插入一条新记录，并把当前系统版本号作为行记录的版本号，同时保存当前系统版本号到原有的行作为删除版本号。
delete: 把当前系统版本号作为行记录的删除版本号
insert: 把当前系统版本号作为行记录的版本号

什么是`next-key`锁 ?

可以简单的理解为: record locks + gap locks

起因

事务隔离性问题

隔离级别

脏读

不可重复读(虚读)

幻读

其他

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

MySQL 如何实现避免幻读 ？

什么是快照读和当前读 ?

什么是mvcc ?

什么是next-key锁 ?

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

`MySQL` 如何实现避免幻读？

什么是`mvcc` ?

什么是`next-key`锁 ?