MySQL隔离级别详解

最新推荐文章于 2024-04-22 10:38:53 发布

置顶懒惰的gler

最新推荐文章于 2024-04-22 10:38:53 发布

阅读量446

点赞数

分类专栏： MySQL 文章标签： MySQL 事务隔离级别

本文链接：https://blog.csdn.net/u010858605/article/details/89185291

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、数据库事务的ACID原理

原子性（atomicity）：整个事务中的所有操作，要么全部完成，要么全部不完成。

一致性（consistency）：事务在执行之前和执行之后，数据库都必须处于一致性状态。

隔离性（isolation）：并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。

持久性（durability）：事务提交后，该事务对数据库所作的更改便持久地保存在数据库之中，且不会被回滚。

二、MySQL的四种隔离级别

读未提交（READ UNCOMMITTED）:一个事务可以读取到另一个事务更新却未提交的数据。

读已提交（READ COMMITTED）：一个事务只能读取到别的事务已经提交的数据。

可重复读（REPEATABLE READ）：在一个事务中，多次读取同一个数据都是不变的。

序列化（SERIALIZABLE）：所有的事务排队顺序执行。

四种事务隔离级别从上往下，级别越高，并发性越差，安全性越高。

各个隔离级别可能出现的读取现象

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
序列化	不可能	不可能	不可能

三、MySQL四种隔离级别分析

测试数据及SQL准备

新建user表，其中id为主键，user_id为普通索引。

mysql> select * from user;
+----+---------+-----------+-------+
| id | user_id | user_name | money |
+----+---------+-----------+-------+
| 1  | 1       | Jack      |    50 |
+----+---------+-----------+-------+

MySQL默认隔离级别为可重复读，查看隔离级别：

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
|REPEATABLE-READ |
+----------------+

MySQL设置隔离级别：

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

其中，SESSION是在当前会话起作用，GLOBAL是在当前会话后开启的会话起作用。

MySQL设置手动提交事务：

set autocommit  = 0;

1. 读未提交

开启两个事务A和B，分别设置两个事务的隔离级别为READ UNCOMMITTED，手动提交事务。

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

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

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> select * from user; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 50 \| +----+---------+-----------+-------+	——
——	mysql> update user set money = 100 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 100 \| +----+---------+-----------+-------+	——

可以看出，事务A读取到事务B未提交的数据，出现脏读，这时候如果事务B进行了回滚，事务A将使用一个脏的数据。

READ UNCOMMITTED与锁

根据上面的测试，发现事务B可以更新事务A正在读取的数据。根据锁的知识，如果说事务A在读取记录的时候加了共享锁, 那么事务B是不能加排他锁对该记录进行更改的。可以推测要么事务A在读取记录时不加共享锁，要么事务B在更新记录时不加排他锁。

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> update user set money = 1000 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0	——
——	mysql> update user set money = 2000 where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
…	…
——	mysql> update user set money = 2000 where id = 1; （锁等待..）
mysql> commit; Query OK, 0 rows affected (0.00 sec)	Query OK, 1 row affected (5.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 （获得锁..）

事务B出现锁等待直到超时，开启另外一个会话查询加锁情况。

查看表的加锁情况:

select * from information_schema.INNODB_LOCKS;

事务状态：

 select * from information_schema.INNODB_TRX;

因此，在READ UNCOMMITTED 隔离级别下, 写操作是会加锁的, 而且是排他锁, 直到事务A完成后, 锁才释放,事务B才能进行写操作。

那么既然是排他锁，为什么事务B在更新后还未提交，事务A却可以读取到事务B的修改呢。在 READ UNCOMMITTED 级别运行的事务不会发出共享锁来防止其他事务修改当前事务读取的数据，既然不加共享锁了, 那么当前事务所读取的数据自然就可以被其他事务来修改。而且当前事务要读取其他事务未提交的修改, 也不会被排他锁阻止, 因为排他锁会阻止其他事务再对其锁定的数据加读写锁。

结论

READ UNCOMMITTED隔离级别下, 读不会加任何锁。而写会加排他锁，并到事务结束之后释放。

快照读（一致性非锁定读）与当前读

MySQL使用MVCC（多版本并发控制）在某个时间点向查询提供数据库的快照。查询将看到在该时间点之前提交的事务所做的更改，并且不会看到以后或未提交的其他事务所做的更改。但如果在同一事务更新表中的某些行，则select将看到更新行的最新版本。

（1）如果隔离级别为READ COMMITTED，事务中的每个一致读取都会设置并读取自己的新快照。

（2）如果隔离级别为REPEATABLE READ，则同一事务中的所有一致读取将读取该事务中第一次此类读取所建立的快照。

快照读是READ COMMITTED和REPEATABLE READ处理select语句的默认模式。它不会对其访问的表设置任何锁，因此其他会话可以在对表执行一致读取的同时自由修改这些表。

而update、insert、delete 以及加锁读（select with lock in share mode/ for update）是当前读，会读取最新的记录，也就是可以看到别的事务提交的数据。如update时首先会执行当前读，然后把返回的数据加锁，之后执行update。

2. 读已提交

读已提交是快照读，且同一次事务中的所有读取都会重新设置并读取最新快照。

开启两个事务A和B，分别设置两个事务的隔离级别为READ COMMITTED，手动提交事务。

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

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

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> select * from user; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 1000 \| +----+---------+-----------+-------+	——
——	mysql> update user set money = 2000 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 1000 \| +----+---------+-----------+-------+	——
——	mysql> commit; Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 2000 \| +----+---------+-----------+-------+ 1 row in set (0.00 sec)	——

可以看出，事务A无法读取事务B修改后未提交的数据，只有事务B提交后事务A才能够读取到。但事务A两次读取的结果不一样，这就是不可重复读的问题。

READ COMMITTED与锁

READ COMMITTED读是不加锁的，是快照读。

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> update user set money = 1000 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0	——
——	mysql> update user set money = 500 where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
——	mysql> update user set money = 500 where id = 1; （锁等待..）
mysql> commit; Query OK, 0 rows affected (0.00 sec)	Query OK, 1 row affected (5.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 （获得锁..）

事务B出现锁等待直到超时，通过select * from information_schema.INNODB_LOCKS;查询READ COMMITTED隔离级别下写加的是排他锁。

在READ COMMITTED隔离级别下，加锁读、更新语句、删除语句，mysql仅会对where条件中的索引列的记录加锁，而不会锁定它们之间的间隙，允许在加锁记录旁边自由插入新记录。

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> select * from user; \| id \| user_id \| user_name \| money \| +----+ \| 1 \| 1 \| Jack \| 2000 \| \| 2 \| 2 \| Tom \| 2000 \| \| 3 \| 3 \| Rose \| 2000 \| \| 7 \| 7 \| Lucy \| 2000 \| +----+-----	——
mysql> update user set money = 1000 where user_id > 1 and user_id <=7; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0	——
——	mysql> insert into user(id, user_id, user_name, money) values(5, 5, 'Pop', 3000); Query OK, 1 row affected (0.00 sec)
…	…

如果SQL语句where条件没有索引列，READ COMMITTED隔离级别下会先对所有的行加锁，然后释放那些不需要修改的行的锁。

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> update user set money = 1000 where user_name = 'Jack'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0	——
——	mysql> update user set money = 1000 where user_name = 'Tom'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

对于READ COMMITTED隔离级别下的加锁读（select with lock in share mode），MySQL会对数据加共享锁直到事务提交才释放锁。

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> select * from user where id = 1 lock in share mode; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 2000 \| +----+---------+-----------+-------+	——
——	mysql> update user set money = 1000 where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> commit; Query OK, 0 rows affected (0.00 sec)	——
——	mysql> update user set money = 1000 where id = 1; Query OK, 1 row affected (4.07 sec) Rows matched: 1 Changed: 1 Warnings: 0

对任何读取的行设置共享锁，其他会话可以读取，但在事务提交之前不能修改它们。

结论

READ COMMITTED隔离级别下, 读是非锁定的快照读，且事务中的每个一致读取都会设置并读取自己的新快照。而写会加排他锁，并到事务结束之后释放。加锁读、更新语句、删除语句，mysql仅对where条件中的索引列的记录加锁，不会锁间隙。对于非索引列，会先对所有行进行加锁，然后释放不满足更新条件行的锁。

3. 可重复读

可重复读是快照读，同一事务中的所有一致读取将读取该事务中第一次此类读取所建立的快照。

开启两个事务A和B，分别设置两个事务的隔离级别为REPEATABLE READ，手动提交事务。

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

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

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> select * from user; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 2000 \| \| 2 \| 2 \| Tom \| 2000 \| \| 3 \| 3 \| Rose \| 2000 \| \| 7 \| 7 \| Lucy \| 2000 \| +----+---------+-----------+-------+	——
——	mysql> insert into user(id, user_id, user_name, money) values(5, 5, 'Pop', 3000); Query OK, 1 row affected (0.00 sec)
——	mysql> commit; Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(id, user_id, user_name, money) values(5, 5, 'Pop', 3000); ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'	——
mysql> select * from user; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 2000 \| \| 2 \| 2 \| Tom \| 2000 \| \| 3 \| 3 \| Rose \| 2000 \| \| 7 \| 7 \| Lucy \| 2000 \| +----+---------+-----------+-------+	——

事务A查询没有发现主键为5的数据，插入主键为5的数据时报主键冲突，再次查询也没有主键为5的数据，出现了幻读。

update、insert、delete 以及加锁读（select with lock in share mode/ for update）是当前读，会读取最新的记录。

REPEATABLE READ与锁

REPEATABLE READ隔离级别下读是非锁定的快照读，写加的是排他锁。

在REPEATABLE READ隔离级别下，加锁读、更新语句、删除语句，mysql不仅会对where条件中的索引列的记录加锁，还会锁定它们之间的间隙。

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> select * from user; \| id \| user_id \| user_name \| money \| +----+ \| 1 \| 1 \| Jack \| 2000 \| \| 2 \| 2 \| Tom \| 2000 \| \| 3 \| 3 \| Rose \| 2000 \| \| 7 \| 7 \| Lucy \| 2000 \| +----+-----	——
mysql> update user set money = 1000 where user_id > 1 and user_id <=7; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0	——
——	mysql> insert into user(id, user_id, user_name, money) values(5, 5, 'Pop', 3000); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

但是，如果索引是唯一索引，则会降为行锁。

如果SQL语句where条件没有索引列，REPEATABLE READ隔离级别下会所有行进行加锁。

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> update user set money = 1000 where user_name = 'Jack'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0	——
——	mysql> update user set money = 1000 where user_name = 'Tom'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

与READ COMMITTED类似，对于REPEATABLE READ隔离级别下的加锁读（select with lock in share mode），MySQL同样会对数据加共享锁直到事务提交才释放锁。

结论

REPEATABLE READ隔离级别下, 读是非锁定的快照读，同一事务中的所有一致读取将读取该事务中第一次此类读取所建立的快照。而写会加排他锁，并到事务结束之后释放。加锁读、更新语句、删除语句，mysql不仅会对where条件中的索引列的记录加锁，还会锁定它们之间的间隙，如果索引是唯一索引会降级为行锁。对于非索引列，会对所有行进行加锁。

4. 序列化

开启两个事务A和B，分别设置两个事务的隔离级别为SERIALIZABLE，手动提交事务。

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

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| SERIALIZABLE |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

事务A	事务B
mysql> begin;	——
——	mysql> begin;
mysql> select * from user where id = 1; +----+---------+-----------+-------+ \| id \| user_id \| user_name \| money \| +----+---------+-----------+-------+ \| 1 \| 1 \| Jack \| 2000 \| +----+---------+-----------+-------+	——
——	mysql> update user set money = 1000 where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
…	…

事务B出现锁等待直到超时。

可以知道在隔离级别SERIALIZABLE下，读会加共享锁，写会加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。

参考资料

MySQL InnoDB 事务隔离级别官方文档

mysql(InnoDB)事务隔离级别(READ UNCOMMITTED) 与锁

懒惰的gler

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL隔离级别详解

一、数据库事务的ACID原理原子性（atomicity）：整个事务中的所有操作，要么全部完成，要么全部不完成。一致性（consistency）：事务在执行之前和执行之后，数据库都必须处于一致性状态。隔离性（isolation）：并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。持久性（durability）：事...
复制链接

扫一扫