MySQL的四种事务隔离级别

最新推荐文章于 2024-05-08 14:57:59 发布

木易三水良

最新推荐文章于 2024-05-08 14:57:59 发布

阅读量146

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_40722604/article/details/117696871

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文实验的测试环境：

mysql> select version() from dual;
+-----------+
| version() |
+-----------+
| 5.7.31    |
+-----------+
1 row in set (0.00 sec)

查询事务的隔离级别：

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

一、事务的基本要素（ACID）

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

二、事务的并发问题

1、脏读(Dirty Reads)：事务A读取了事务B未提交的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读(Non-Repeatable Reads)：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读(Phantom Reads)：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

4、更新丢失(Lost Update)：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新覆盖了由其他事务所做的更新。

例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改或删除，幻读侧重于新增。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

三、MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

四、案例

-- 设置当前客户端的事务隔离级别为读未提交
set session transaction isolation level read uncommitted;

-- 设置当前客户端的事务隔离级别为不可重复读
set session transaction isolation level read committed;

// 设置当前客户端的事务隔离级别为读未提交
set session transaction isolation level read uncommitted;

// 设置当前客户端的事务隔离级别为可重复读
set session transaction isolation level repeatable read;

// 设置当前客户端的事务隔离级别为串行化
set session transaction isolation level serializable;

start transaction;

commit;

1、读未提交

B事务执行了插入操作，但是没有提交事务。

// 窗口B的代码
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into course (cid,cname,tid) values (5,'Rust',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

A事务读到了B事务未提交的任务

// 窗口A的代码
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
+-----+--------+-----+
4 rows in set (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

2、读已提交（不可重复读）

如果此时我修改A客户端的隔离级别read commit，A事务无法读取B事务未提交的数据：

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
+-----+--------+-----+
4 rows in set (0.00 sec)

此时B事务提交事务

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

A事务执行查询操作，可以查到B事务提交的数据

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

3、可重复读

A客户端修改隔离级别为repeatable read。B事务及时执行了一个完整的事务操作，此时A事务同样无法查询到B事务已提交的数据。

mysql> set session transaction isolation level repeatable read ;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

// B事务提交事务以后在A事务内执行的查询操作
mysql> select * from course;
+-----+--------+-----+
| cid | cname  | tid |
+-----+--------+-----+
|   1 | java   |   1 |
|   2 | python |   2 |
|   3 | sql    |   3 |
|   4 | linux  |   1 |
|   5 | Rust   |   3 |
+-----+--------+-----+
5 rows in set (0.00 sec)

B事务执行了一个完整的事务操作，

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

mysql> insert into course (cid,cname,tid) values (6,'flink',3);
Query OK, 1 row affected (0.00 sec)

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

4、幻读现象

客户端A执行的操作：

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from course;
Empty set (0.00 sec)

-- 在客户端B完成了事务提交以后仍然查询不到相关的数据
mysql> select * from course;
Empty set (0.00 sec)

-- 但是你执行插入操作，其唯一键指定为和客户端B返回的唯一键相等，会报错，幻读现象
mysql>  insert into course (cid,cname,tid) values (7,"sql",1);
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'

客户端B执行的操作：

Database changed
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into course (cname,tid) values ("sql",1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from course;
+-----+-------+-----+
| cid | cname | tid |
+-----+-------+-----+
|   7 | sql   |   1 |
+-----+-------+-----+
1 row in set (0.00 sec)

mysql> commit;

可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

5、串行化

A事务修改隔离级别为serializable。

mysql> set session transaction isolation level serializable ;
Query OK, 0 rows affected (0.00 sec)

B事务执行插入操作，插入一条记录报错，表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

mysql> insert into course (cid,cname,tid) values (6,'flink',3);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

6、实现原理

首先说读未提交，它是性能最好，不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离。

串行化。读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

最后说读提交和可重复读。这两种隔离级别是比较复杂的，既要允许一定的并发，又想要兼顾的解决问题。

6.1、可重复读/读已提交

实现可重复读，MySQL 采用了 MVVC (多版本并发控制)的方式。

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为row trx_id，而这个字段就是使其产生的事务 id，记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。
在这里插入图片描述
按照上面这张图理解，一行记录现在有 3 个版本，每一个版本都记录这使其产生的事务 ID，比如事务A的transaction id 是100，那么版本1的row trx_id 就是 100，同理版本2和版本3。

快照，一致性视图，这也是可重复读和不可重复读的关键，

可重复读是在事务开始的时候生成一个当前事务全局性的快照，
读提交则是每次执行语句的时候都重新生成一次快照。

对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

当前事务内的更新，可以读到；
版本未提交，不能读到；
版本已提交，但是却在快照创建后提交的，不能读到；
版本已提交，且是在快照创建前提交的，可以读到；

6.2、并发写问题

两个事务，对同一条数据做修改。最后结果是时间靠后的那个。并且更新之前要先读数据，

这里所说的读和上面说到的读不一样，更新之前的读叫做“当前读”，总是当前版本的数据，也就是多版本中最新一次提交的那版。

假设事务A执行 update操作， update 的时候要对所修改的行加行锁，这个行锁会在提交之后才释放。而在事务A提交之前，事务B也想 update这行数据，于是申请行锁，但是由于已经被事务A占有，事务B申请不到，此时，事务B就会一直处于等待状态，直到事务A提交，事务B才能继续执行，如果事务A的时间太长，那么事务B很有可能出现超时异常。

加锁的过程要分有索引和无索引两种情况，比如下面这条语句

update user set age=11 where id = 1
id 是这张表的主键，是有索引的情况，那么 MySQL 直接就在索引数中找到了这行数据，然后干净利落的加上行锁就可以了。

update user set age=11 where age=10
表中并没有为age 字段设置索引，所以， MySQL 无法直接定位到这行数据。此处不是加表锁了。MySQL 会为这张表中所有行加行锁，没，在加上行锁后，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。

虽然最终只为符合条件的行加了锁，但是这一锁一释放的过程对性能也是影响极大的。所以，如果是大表的话，建议合理设计索引，如果真的出现这种情况，那很难保证并发度。

6.3、解决幻读

MySQL 已经在可重复读隔离级别下降低了幻读的概率。

前面刚说了并发写问题的解决方式就是行锁，而解决幻读用的也是锁，叫做间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做Next-Key锁。

假设现在表中有两条记录，并且 age 字段已经添加了索引，两条记录 age 的值分别为 10 和 30。
在这里插入图片描述

此时，在数据库中会为索引维护一套B+树，用来快速定位行记录。B+索引树是有序的，所以会把这张表的索引分割成几个区间。

在这里插入图片描述

如图所示，分成了3 个区间，(负无穷,10]、(10,30]、(30,正无穷]，在这3个区间是可以加间隙锁的。

在这里插入图片描述

在事务A提交之前，事务B的插入操作只能等待，这就是间隙锁起得作用。当事务A执行update user set name='风筝2号’ where age = 10; 的时候，由于条件 where age = 10 ，数据库不仅在 age =10 的行上添加了行锁，而且在这条记录的两边，也就是(负无穷,10]、(10,30]这两个区间加了间隙锁，从而导致事务B插入操作无法完成，只能等待事务A提交。不仅插入 age = 10 的记录需要等待事务A提交，age<10、10<age<30 的记录也无法完成，而大于等于30的记录则不受影响，这足以解决幻读问题了。

这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于30，都要等待事务A提交才可以成功插入。

五、总结

事务隔离级别为读提交时，写数据只会锁住相应的行。
事务隔离级别为可重复读时，默认隔离界别，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
read commit和repeatable read之间的区别在于，前者是只要其他事务进行了数据提交，该事务能够读到别的事务提交的数据，致使在事务内可能多次查询的结果不一致，而后者是无论其他事务有无事务数据提交，该事务都无法读到别的事务数据，在事务内多次查询的结果始终都是一致。
不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
通过以上俩个案例得知在MySQL可重复读隔离级别中并没有完全解决幻读问题，而只是解决了快照读下的幻读问题。而对于当前读的操作依然存在幻读问题，也就是说MVCC对于幻读的解决是不彻底的。

通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersionConcurrencyControl，简称MVCC或MCC），也经常称为多版本数据库。

事务隔离级别为串行化时，读写数据都会锁住整张表
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

参考文档：
MySQL事务隔离级别和实现原理（看这一篇文章就够了！）

木易三水良

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL的四种事务隔离级别

本文实验的测试环境：mysql> select version() from dual;+-----------+| version() |+-----------+| 5.7.31 |+-----------+1 row in set (0.00 sec)一、事务的基本要素（ACID） 1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说
复制链接

扫一扫