MySQL（十八）MySQL事务(二)：事务的隔离级别

标签：事务隔离 0.00 MySQL account sec mysql +----+----------+---------+

MySQL（十八）MySQL事务(二)：事务的隔离级别

MySQL是一个客户端/服务器架构的软件，可以有若干个客户端与之连接，连接上之后都可以被称作是一个会话，每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是事务的一部分，因此对于服务器来说需要同时处理多个事务。由于事务具有隔离性，所以理论上事务对某个数据进行访问的时候，其他事务应该排队，等事务提交之后，其他事务才能继续执行，但是这样对性能影响太大。也就是需要在隔离性和并发性之间做取舍。

1 数据准备

CREATE TABLE `student1` (
	`stu_no` INT,
	`name` VARCHAR(20),
	`class` VARCHAR(20),
	PRIMARY KEY(stu_no)
)ENGINE=INNODB CHARSET=utf8;

INSERT INTO student1 VALUES(1, 'A', '1班')

2 数据并发问题

如果事务不能保证串行执行的情况下可能会出现的问题：

2.1 脏写(dirty write)，也称作丢失更新

如果一个事务修改了另一个未提交事务 修改过的数据，就发生了脏写。

如上脏写示意图，AB均开启事务，B首先将数据name='A'改为了李四，然后事务A又改成张三并提交刷盘，然后事务B进行回滚操作到自己事务的初始状态name='A'，事务A的更新就丢失了，出现了脏写。

2.2 脏读

一个事务读取了另一个事务更新完但还没有提交的数据被称作脏读。若后续事务回滚，则第一个事务读取到的内容就是临时且无效的。

2.3 不可重复读

一个事务读取了一个字段的同时，另一个事务对其进行了更新，当第一个事务再读的时候，发现字段值不同了。

2.4 幻读

一个事务在一张表中读取了一个字段之后，另一个事务插入了一些新的行，如果前一个事务再次读取同一个表，就会发现多了几行，这就被称作幻读。新插入的几行被称作幻影记录。

如果删除了几行，导致读的记录变少了的情况算幻读吗？

不算，因为幻读强调是插入，即读到了之前没有读过的记录，删除的这几条严格来说是不可重复读。

3 SQL的四种隔离级别

上面介绍了并发执行事务可能出现的问题，这些问题并不需要全部解决，否则并发性能太差，因此可以按照业务场景解决问题较轻的，按严重程度排序：

脏写 > 脏写 > 不可重复读 > 幻读

可以牺牲一部分隔离性来换取事务的并发性，SQL标准中设置了四个隔离级别，隔离级别越低，事务的并发程度就越高：

读未提交（READ UNCOMMITTED）：在该隔离级别下，所有事务都能看到其他未提交事务的执行结果。不能避免脏读、不可重复读和幻读问题。
读已提交（READ COMMITTED）：在该隔离级别下，所有事务都只能看到其他已经提交事务的执行结果，可以避免脏读，但是不能解决不可重复读和幻读。这也是ORACLE数据库默认的隔离级别。
可重复读(REPEATABLE READ)：在该隔离级别下，一个事务读取数据后，另一个事务对数据进行了修改提交，前一个事务读取的还是之前的结果，可以避免不可重复读和脏读，但是不能解决幻读。
可串行化(SERIALIZABLE)：确保事务串行执行，没有任何其他事务对表进行修改操作，所有的并发问题都可以解决，但效率低下。

没有涉及脏写问题，是因为脏写问题太严重了：事务提交修改还能出错，因此上面的四种隔离模式都能避免脏写问题

4 MYSQL支持的隔离级别

上面的四种隔离模式是SQL标准规定的，对于具体的数据库支持是不同的，比如ORACLE只支持读已提交和可串行化，并且默认的隔离级别是读已提交，而MySQL支持四种隔离级别，而且默认的隔离级别是可重复度。

4.1 MYSQL的隔离级别的设置与查看

MySQL的隔离级别的查看按照版本方式如下：

# 5.7之前
mysql> show variables like 'tx_isolation';

# 5.7之后
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
# 版本混用

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

可以按照下面的语句修改隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
> READ COMMITTED
> READ UNCOMMITTED
> REPEATABLE READ
> SERIALIZABLE

或者：

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION
> READ-COMMITTED
> READ-UNCOMMITTED
> REPEATABLE-READ
> SERIALIZABLE

关于设置GLOBAL或SESSION的影响：

使用GLOBAL：
```
SET GLOBAL TRANSACTION ISOLATION READ-COMMITTED;
```
- 当前会话所有后续事务有效（当前事务无效）
- 对已经开启的事务执行期间运行，不会影响当前正在执行的事务

使用SESSION

SET GLOBAL TRANSACTION ISOLATION READ-COMMITTED;

当前会话生效
其他会话无效

4.2 不同隔离级别举例

数据准备

CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(15),
	balance DECIMAL(10, 2)
);
INSERT INTO account(name, balance) 
VALUES('张三', 100), ('李四', 0);

不可重复读

首先对两个会话都设置隔离模式为读未提交

mysql> set session transaction_isolation = 'read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

会话一执行事务，但是没有提交：

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

mysql> update account set balance = balance + 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

会话二能够获取到未提交数据

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | ??   |  200.00 |
|  2 | ??   |    0.00 |
+----+------+---------+
2 rows in set (0.00 sec)

此时会话一回滚，那么会话二获取的数据就是临时且无效的

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

再举一个列子，如果会话一给会话二转100，并没有提交事务：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set balance = balance - 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance = balance + 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | ??   |    0.00 |
|  2 | ??   |  100.00 |
+----+------+---------+
2 rows in set (0.00 sec)

此时事务二就读到了脏数据

mysql> select * from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | ??   |    0.00 |
|  2 | ??   |  100.00 |
+----+------+---------+
2 rows in set (0.00 sec)

而如果这时候事务二想把这些钱再转给事务一，就会发现卡住了，没有任何的执行结果

mysql> update account set balance = balance - 100 where id = 2;

这是因为四种隔离级别都解决了脏写的问题，即事务二想修改未提交的事务是不能的，因为mysql对表进行了加锁，不允许其他事务对表进行修改

读已提交

数据准备

mysql> truncate table account;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into account values(1, 'zhangsan', 100), (2, 'lisi', 0);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |  100.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

1、2均设置隔离级别为读已提交

set session transaction_isolation = 'read-committed';

1账户金额减五十，但是事务不提交

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

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2读取金额发现1的金额为100，没有读到脏数据，说明解决了脏读问题

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |  100.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

1提交事务

commit;

2再读仍然是50，说明隔离级别为读已提交时不能够解决不可重复读问题。

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |  100.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |   50.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

可重复读

设置1和2的隔离级别为可重复读。

mysql> set transaction_isolation = 'repeatable-read';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

2查看数据：

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |  100.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

1将钱加50，并且不提交事务：

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

mysql> update account set balance = balance + 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |  100.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

2查看数据，没有读到脏数据，说明可重复读解决了不可重复读问题

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |   50.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

1提交事务

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

2再次查看，发现金额为100

可重复解决了一部分幻读问题

1和2都是INNODB默认的隔离级别可重复读：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

2查看表中的数据：

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |   50.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

此时如果1添加记录再提交，并且没有提交事务

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |   50.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> insert into account(3, 'wangwu', 0);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3, 'wangwu', 0)' at line 1

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

mysql> insert into account values(3, 'wangwu', 0);
Query OK, 1 row affected (0.00 sec)

2再次查看表中数据：

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |   50.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |   50.00 |
|  2 | lisi     |    0.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

这是不是说明可重复读解决了幻读问题呢？并不是，如果这时候2向表中添加一条数据，由于禁止脏写，所以如果1不提交事务的话，2是没有办法写数据的，所以1执行commit命令的同时，可以看到不允许插入主键为2的值，表名出现了幻读问题：

mysql> insert into account values(3, 'liliu', 1);
ERROR 1062 (23000): Duplicate entry '3' for key 'account.PRIMARY'

2再次查看，这时候发现已经是三条数据了

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | zhangsan |   50.00 |
|  2 | lisi     |    0.00 |
|  3 | wangwu   |    0.00 |
+----+----------+---------+
3 rows in set (0.00 sec)

可序列化

在SERIALIZABLE隔离级别下，事务的执行会隐式地添加行(X)锁/gap(X)锁（也成间隙锁），事务一提交之后，事务二才能继续执行。

未完待续

标签：事务,隔离,0.00,MySQL,account,sec,mysql,+----+----------+---------+
From： https://www.cnblogs.com/tod4/p/17384656.html

MySQL（十八）MySQL事务(二)：事务的隔离级别