MySQL事务隔离级别（二）

搞清楚MySQL事务隔离级别

首先创建一个表 account。创建表的过程略过（由于 InnoDB 存储引擎支持事务，所以将表的存储引擎设置为 InnoDB）。表的结构如下：

 

 为了说明问题，我们打开两个控制台分别进行登录来模拟两个用户（暂且成为用户 A 和用户 B 吧），并设置当前 MySQL 会话的事务隔离级别。

一. read uncommitted（读取未提交数据）

具体用户 A 的操作如下：

set session transaction isolation level read uncommitted；
start transaction;
select * from account;

 

 

用户 B 的操作如下：

1 2 3 4 5 6 7 8 9 10

set session transaction isolation level read uncommitted; start transaction; update account set  account=account+200 where  id =1 ;

随后我们在 A 用户中查询数据，结果如下：

结论一

我们将事务隔离级别设置为 read uncommitted，即便是事务没有 commit，但是我们仍然能读到未提交的数据，这是所有隔离级别中最低的一种

那么这么做有什么问题吗？

那就是我们在一个事务中可以随随便便读取到其他事务未提交的数据，这还是比较麻烦的，我们叫脏读。我不知道这个名字是怎么起的，为了增强大家的印象，可以这么想，这个事务好轻浮啊，饥渴到连别人没提交的东西都等不及，真脏，呸！

实际上我们的数据改变了吗？

答案是否定的，数据库里面的数据并没有改变，因为只有事务 commit 后才会更新到数据库。

 

二.  read committed（可以读取其他事务提交的数据）--- 大多数数据库默认的隔离级别

同样的办法，我们将用户 B 所在的会话当前事务隔离级别设置为 read commited。

 

在用户 A 所在的会话中我们执行下面操作：

update account set account=account-200 where id=1;

我们将 id=1 的用户 account 减 200。然后查询，发现 id=1 的用户 account 变为 800。

在 B 用户所在的会话中查询：

select * from account；

结果如下：

我们会发现数据并没有变，还是 1000。

接着在会话 A 中我们将事务提交：commit;

在会话 B 中查询结果如下：

结论二：

当我们将当前会话的隔离级别设置为 read committed 的时候，当前会话只能读取到其他事务提交的数据，未提交的数据读不到。

那么这么做有什么问题吗？

那就是我们在会话 B 同一个事务中，读取到两次不同的结果。这就造成了不可重复读，就是两次读取的结果不同。这种现象叫不可重复读。

 

三. repeatable read（可重读）---MySQL 默认的隔离级别

现在有个需求，就是老板说在同一个事务中查询结果必须保持一致，如果你是数据库，你会怎么做？数据库是这么做的。

在会话 B 中我们当前事务隔离级别为 repeatable read。具体操作如下：

1 2	set session transaction isolation level repeatable read; start transaction;

接着在会话 B 中查询数据：

我们在 A 用户所在会话中为表 account 添加一条数据：

1 2 3 4 5 6 7 8

insert into  account(id,account) value(3,1000); commit;

然后我们查询看数据插入是否成功：

 回到 B 用户所在的会话，我们查询结果：

 用户 B 在他所在的会话中想插入一条新数据 id=3，value=1000。来我们操作下：

什么？竟然插不进去，说我数据重复？

用户 B 当然不服啊，因为查询到数据只有两条啊，为什么插入 id=3 说我数据重复了呢？

我再看一遍，莫非我眼花了？

试想一下，在实际中用户 A 和用户 B 肯定是相互隔离的，彼此不知道操作什么。用户 B 碰到这种现象，肯定会炸毛的啊，明明不存在的数据，插入却说主键 id=3 数据重复了。

结论三：

当我们将当前会话的隔离级别设置为 repeatable read 的时候，当前会话可以重复读，就是每次读取的结果集都相同，而不管其他事务有没有提交。

有什么问题吗？

管他呢，老板的要求满足了。要一个事务中读取的数据一致（可重复读）。我只能这么做啊，打肿脸装胖子。数据已经发生改变，但是我还是要保持一致。但是，出现了用户 B 面对的问题，这种现象叫幻读（记得当时就在这个地方纠结好久，到底什么是幻读啊）。

 

四. serializable（串行化）

同样，我们将用户 B 所在的会话的事务隔离级别设置为 serializable 并开启事务。

1 2 3

set  session transaction isolation level serializable; start transaction;

在用户 B 所在的会话中我们执行下面操作：

select * from account;

结果如下：

 

如果在等待期间我们用户 B 所在的会话事务提交，那么用户 A 所在的事务的写操作将提示操作成功。

结论四：

当我们将当前会话的隔离级别设置为 serializable 的时候，其他会话对该表的写操作将被挂起。可以看到，这是隔离级别中最严格的，但是这样做势必对性能造成影响。所以在实际的选用上，我们要根据当前具体的情况选用合适的。

 

 

 ==========================================================================================================================================================================================================================================================================

 

 

MySQL事务主要用于处理一个包含操作量比较大、复杂的业务。比如说，删除一个学生，我们除了要删除该学生的基本信息，同时也要删除考试记录、违规记录等。诸多的操作组成一个事务。事务是用来管理insert、update、delete 基本指令的。当MySQL使用innodb引擎的前提下才支持事务操作。

事务的基本特点

原子性

一个事务的执行所有的操作，结果只有两种：要么全部执行、要么全部不执行。事务在执行的过程中，当在某一个节点执行发生错误的时候，事务会被执行rollback操作，将数据恢复到执行该事务之前的状态。A去银行转账，要么转账成功、要么转账失败。

一致性

从事务开始执行到执行完成后，数据库的完整性约束完全没有收到破坏。A转账给B，不可能发生这种情况：A转账成功、B没有收到款。

隔离性

在同一时间点，数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交( read uncommitted )、读提交( read committed )、可重复读( repeatable read|默认方式 )和串行化( serializable )。

持久性

事务成功执行后，事务的所有操作对数据库的更新是永久的，不能回滚。

隔离性的类别

read uncommitted | 读未提交

read committed | 读已提交

repeatable read | 可重复读

serializable | 串行化

在MySQL数据库中，引擎默认使用 repeatable read 可重复读

# SELECT @@tx_isolation 或者 SELECT @@transaction_isolation
# MySQL 8.x 
# transaction_isolation在MySQL 5.7.20中添加了作为别名 tx_isolation，现已弃用，并在MySQL 8.0中删除。
# 应调整应用程序transaction_isolation以优先使用 tx_isolation。
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.01 sec)

事务的并发问题

 

脏读

事务A读取了事务B更新的数据，然后事务B在某些因素下执行了回滚，那么事务A读取的数据就是不合理的，即脏数据。

 

## (1)事务A的操作
## 设置为隔离方式为[读未提交 | read uncommitted]
## 开启事务并查询id为1的score的值 
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    80 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

## (2)事务B的操作
## 开启事务并将id为1的score修改成 75
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    75 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

## (3)事务A的操作
## 再次读取id为1的score值 75
mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    75 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

## (4) 事务B的操作
## 事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

上述四个步骤中，事务A在事务B前读取的score的值为80，在事务B执行修改后读取score的值为75，事务B再进行回滚操作，那么事务A在两次读取的score的值是不一致的，那么就是脏读。

不可重复读

事务A需要重复多次读取某组数据，事务A在事务B对该组数据修改提交前后进行读取，很显然、两次读取的数据是不一致的，即不可重复读。侧重于元数据的修改。

## 使用[读已提交]的模式实践
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

## (1) 事务A查询id为1的score 80
mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    80 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

## (2) 事务B修改id为1的score并提交事务 75
mysql> update score set score=75 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

## (3) 事务A再次查询id为1的score的值 75
mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    80 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    75 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

从上述的三个步骤中显而易见可以看出，事务A在事务B修改并提交的前后读取同一条数据的值得不一样的，具有不可重复读问题。

幻读

事务A在修改每一条元数据的时候，事务B在此时添加了一条新记录，事务A在处理的过程中突然多了一条数据，即幻读。侧重于数据的删除与修改。

## 将事务隔离的模式设置为[可重复读]
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

## (1)事务A读取scor数据表
mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    75 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

## (2)事务B新增删除一条数据并提交
mysql> delete from score where id=1;
Query OK, 1 row affected (0.01 sec)

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

## (3)事务A再次读取score数据表
mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    75 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

可见，事务A在事务B删除并提交前后读取的数据一样，出现了幻读。

事务隔离级别的影响

 

 

事务隔离性说明

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

事务隔离级别为读提交时，写数据只会锁住相应的行（当在写时锁住相应的行，其他事务也就读不到了，知道写完commit之后释放锁，其他事务才可以读到）

事务隔离级别为串行化时，读写数据都会锁住整张表（将读上锁，写也上锁）