一篇实例分析让你了解mysql的隔离级别以及不同隔离级别可能产生的并发一致性问题。

最新推荐文章于 2025-05-10 13:28:00 发布

原创最新推荐文章于 2025-05-10 13:28:00 发布 · 981 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库相关专栏收录该内容

4 篇文章

订阅专栏

mysql 事务隔离级别

读未提交 READ-UNCOMMITTED
读已提交 READ-COMMITTED
可重复读 REPEATABLE-READ
串行化 SERIALIZABLE

并发一致性问题

丢失修改

T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改。

脏读

读取到未提交的事务做的修改。即一个事务读取另外一个事务还没提交的数据

不可重复读

是指在一个事务内，多次读取同一数据，在这个事务还没有结束时，另一个事务也访问这个数据，那么在第一个事务中两次读取数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的.

幻读

事务1读取一条指定的where子句返回的结果集，然后另外一个事务2新插入一行记录，恰好可以满足事务1所查询条件，然后事务1再次对表进行检索，看到了事务2插入的数据。

复现丢失修改、脏读场景

查看mysql的事务隔离级别,可以看到mysql的默认隔离级别是可重复读（REPEATABLE-READ）

select @@transaction_Isolation;

+-------------------------+
| @@transaction_Isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.01 sec)

数据准备

mysql> select * from users;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  36 |
|  2 | Bob   |  25 |
+----+-------+-----+
2 rows in set (0.00 sec)

修改事务隔离级别为读未提交 READ-UNCOMMITTED

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)

T1事务

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

mysql> select * from users where id =1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  37 |
+----+-------+-----+
1 row in set (0.00 sec)

T2事务

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

mysql> select * from users where id = 1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  37 |
+----+-------+-----+
1 row in set (0.00 sec)

mysql> update users set age=38 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

T1事务再次查询，查询到了T2事务还未提交的数据（这就是脏读）

mysql> select * from users where id =1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  38 |
+----+-------+-----+
1 row in set (0.00 sec)

T1事务也更新数据，这时候T1事务会阻塞，因为T2事务还没提交

update users set age = 39 where id =1;

提交T2事务,提交T2事务后，T1事务完成更新

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

在继续在T2事务中查询,发现T2事务中的数据，并不是T2事务更新的结果。变成了39.T2明明是改成38了啊。这个就是丢失修改的场景

mysql> select * from users where id = 1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  39 |
+----+-------+-----+
1 row in set (0.00 sec)

复现不可重复读场景

修改事务隔离级别为读已提交

SET SESSION transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

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

事务1开启事务查询

mysql> select * from users where id =1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  41 |
+----+-------+-----+
1 row in set (0.00 sec)

事务2开启事务更新并提交

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

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

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

事务1再次查询,此时事务1中查询，和首次查询不一致。这就是不可重复读

mysql> select * from users where id =1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  40 |
+----+-------+-----+
1 row in set (0.00 sec)

复现幻读场景

修改事务隔离级别为可重读读

SET SESSION transaction_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

顺便看下可重复读下，有没有上面不可重复读场景
事务1开启事务查询

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

mysql> select * from users where id =1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  42 |
+----+-------+-----+
1 row in set (0.00 sec)

事务2开启事务更新数据并提交

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

mysql> update users set age=43 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)

事务1在查询

mysql> select * from users where id =1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  42 |
+----+-------+-----+
1 row in set (0.00 sec)

事务1提交事务然后查询 ,由此可见不会出现上面两次不一致的场景。

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

mysql> select * from users where id =1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  43 |
+----+-------+-----+
1 row in set (0.00 sec)

幻读场景复现,事务1查询age>42的数据

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

mysql> select * from users where age>42;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  43 |
|  3 | test  |  45 |
+----+-------+-----+
2 rows in set (0.00 sec)

事务2 插入一条数据age=45

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

mysql> insert into users(name,age) values('test',46);
Query OK, 1 row affected (0.00 sec)

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

事务1再次查询,还是之前的两条，并没有出现幻读。

mysql> select * from users where age>42;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  43 |
|  3 | test  |  45 |
+----+-------+-----+
2 rows in set (0.00 sec)

但是事务1 使用for update当前读查询就会出现幻读场景。当前读每次都会读取最新的数据然后进行加锁。Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁。

mysql> select * from users where age>42 for update;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  43 |
|  3 | test  |  45 |
|  4 | test  |  46 |
+----+-------+-----+
3 rows in set (0.00 sec)

继续看，这时候事务1还没提交，我们在事务2中尝试新增一条age=47的数据，可以看到事务2被阻塞了，这也从侧面论证了。事务 2 在执行插入语句的时候，判断到插入的位置被事务 1加了 next-key lock，于是事物 2 会生成一个插入意向锁，同时进入等待状态，直到事务 1 提交了事务。这就避免了由于事务 2 插入新记录而导致事务 1 发生幻读的现象。

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

mysql> insert into users(name,age) values('test2',47);
...阻塞中

回到上面，事务1中的select查询并没有出现幻读的场景，为什么呢？

因为在InnoDB使用了mvcc版本控制，在介绍mvcc之前，说下当前读和快照读
当前读：直接读取最新的数据版本，并且读取时候其他事务不能修改这个记录。会对记录进行加锁。比如：
select … from xxx for update
select … lock in share mode
update
delete
insert
快照读：读取数据时读取一个一致性视图中的数据。像不加锁的selecct操作就是快照读，即不加锁的非阻塞锁。
mysql使用MVCC机制支持快照读
具体而言，每个事务在开始时会创建一个一致性视图，该视图反应了事务开始后数据库的快照，这个一致性视图会记录当前事务开始时已经提交的数据版本。
当执行查询操作时，mysql会根据事务的一致性视图觉得可见的数据版本，只有在事务开始之前已经提交的数据版本才是可见的，未提交的或者在事务开始后修改的数据则对当前事务不可见。
RR 级别下能否防止幻读
严谨的说，RR 级别下只能防止部分幻读
首先，幻读通常指的是在同一个事务中，第二次查询发现了新增加的行，而第一次查询并没有返回这些新增加的行。
通过前面的例子，我们也看到了，在 RR 隔离级别下，由于一致性视图的存在，如果其他事务插入了新的行，在同一个事务中进行多次查询，这些新增的行将会被包含在事务的一致性视图中，确实可以避免部分幻读场景。MVCC解决的只是 RR 级别下快照读的幻读问题，而当前读的幻读问题则是通过临键锁来解决的。也就是说 RR 级别下是通过 MVCC+临键锁来解决大部分幻读问题的。