MySQL事务隔离级别详解

目录一、 4种隔离级别1、读未提交(Read Uncommitted)2、读提交(Read Committed)3、可重复读(Repeated Read)4、串行读(Serializable)二、不同隔离级别的示例三、隔离级别的设置与查看1、设置隔离级别2、查看当前隔离级别四、事务隔离的实现原理1、隔离级别实现的原理2、为什么建议尽量不要使用长事务？3、如何避免长事务对业务的影响？五、隔离级别引起

勤天

1732人浏览 · 2022-02-17 11:18:39

勤天 · 2022-02-17 11:18:39 发布

一、 4种隔离级别

1、读未提交(Read Uncommitted)

2、读提交(Read Committed)

3、可重复读(Repeated Read)

一、 4种隔离级别

InnoDB默认是可重复读的（REPEATABLE READ）。

1、读未提交(Read Uncommitted)

一个事务还没提交时，它做的变更就能被别的事务看到

允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

2、读提交(Read Committed)

一个事务提交之后，它做的变更才会被其他事务看到

只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

3、可重复读(Repeated Read)

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

4、串行读(Serializable)

串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后面访问的事务必须等前一个事务执行完成，才能继续执行。

完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
读未提交（Read uncommitted）	可能	可能	可能
读提交（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
串行化（Serializable ）	不可能	不可能	不可能

二、不同隔离级别的示例

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

我们来看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么：

1）若隔离级别是“读未提交”，则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。

2）若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A看到。所以， V3 的值也是 2。

3）若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。

4）若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

1、在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

2、在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。

3、这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；

4、而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

总结来说，存在即合理，哪个隔离级别都有它自己的使用场景，你要根据自己的业务情况来定。我想你可能会问那什么时候需要“可重复读”的场景呢？我们来看一个数据校对逻辑的案例。

假设你在管理一个个人银行账户表。一个表存了每个月月底的余额，一个表存了账单明细。这时候你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

三、隔离级别的设置与查看

1、设置隔离级别

1）在my.ini中设置级别

vim /etc/my.ini
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

2）使用set transaction 设置单个会话或者所有新进连接的隔离级别

SET [SESSION | GLOBAL] \
TRANSACTION ISOLATION LEVEL \
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

（1）global，意思是此语句将应用于之后的所有session，而当前已经存在的session不受影响

（2）选择session，将应用于当前session内此点之后的所有事务（包括已开始但未提交的）。

（3）什么都不写，将应用于当前session内的下一个还未开始的事务。

2、查看当前隔离级别

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

四、事务隔离的实现原理

1、隔离级别实现的原理

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上一次的最新值，通过回滚操作，都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录：

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。

如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

对于 read-viewA，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

你一定会问，回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

2、为什么建议尽量不要使用长事务？

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有200GB 的库。最终只好为了清理回滚段，重建整个库。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，这个我们会在后面讲锁的时候展开。

3、如何避免长事务对业务的影响？

1）从应用开发端来看：

（1）确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1，否则事务一直等着被提交占用资源。

（2）确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。

（3）业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

2）其次，从数据库端来看：

（1）监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；

（2）Percona 的 pt-kill 这个工具不错，推荐使用；

（3）在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；

（4）如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

五、隔离级别引起的问题

1、脏读

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据（出现在读未提交隔离级别下）

##############  session 1 ##############
mysql> select @@session.tx_isolation;
+-----------------------+
| @@session.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

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

mysql> insert into ttd values(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


############session 2（会话2）:##############

# 在REPEATABLE-READ 级别下 不会出现脏读
mysql> select * from ttd;
Empty set (0.00 sec)     

#设置级别为读未提交
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

#读到了session 1未提交，但修改的数据
mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |

+------+
1 row in set (0.00 sec)

结论:session 2 在READ-UNCOMMITTED 下读取到session 1 中未提交事务修改的数据.

2、不可重复读

是指在一个事务内（前提是在事务中，如果一个session是事务，另一个session不是事务则不影响），多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。

这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（发生在读提交隔离级别下）

############session 1（会话1）:##############

#设置隔离级别为 读提交
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set (0.00 sec)

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

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


############session 2（会话2）:##############

mysql> insert into ttd values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

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

############session 1（会话1）:##############

#和第一次的结果不一样,READ-COMMITTED 级别出现了不重复读
mysql> select * from ttd;
+------+
| id   |
+------+
|    1 | 
|    2 |
+------+
2 rows in set (0.00 sec)

原因是因为session1 是 read committed （读提交）级别，在没有commit的情况下读到的两次数据不一致

可重复读级别就不会出现上述下现象

############session 1（会话1）:##############
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

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

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

############session 2（会话2）:##############

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

mysql> insert into ttd values(3);
Query OK, 1 row affected (0.00 sec)

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


############session 1（会话1）:##############

#和第一次的结果一样,REPEATABLE-READ级别出现了重复读，并没有读到3，直到session1 commit之后才能读到3
mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |    --------
|    2 |
+------+
2 rows in set (0.00 sec)

3、幻读

第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

mysql>CREATE TABLE `demo` (
    `id` bigint(20) NOT NULL default '0',
    `value` varchar(32) default NULL,
    PRIMARY KEY (`id`)
    
) ENGINE=InnoDB

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

实验一：

Session A	Session B
START TRANSACTION;	START TRANSACTION;
SELECT * FROM demo; empty set
	INSERT INTO demo VALUES (1, 'a');
SELECT * FROM demo; empty set
	COMMIT;
SELECT * FROM demo; empty set
INSERT INTO demo VALUES (1, 'a'); ERROR 1062 (23000): Duplicate entry '1' for key 1

刚刚明明告诉我没有这条记录的，在REPEATABLE-READ（可重复读）级别下出现了幻读

实验二：

Session A	Session B
START TRANSACTION;	START TRANSACTION;
SELECT * FROM demo; +------+-------+ \| \| id \| value \| \| +------+-------+ \| \| 1 \| a \| \| +------+-------+
	INSERT INTO demo VALUES (2, 'b');
\| SELECT * FROM demo; \| +------+-------+ \| \| id \| value \| \| +------+-------+ \| \| 1 \| a \| \| +------+-------+
	COMMIT;
\| SELECT * FROM demo; \| +------+-------+ \| \| id \| value \| \| +------+-------+ \| \| 1 \| a \| \| +------+-------+
\| UPDATE t_bitfly SET value='z'; \| Rows matched: 2 Changed: 2 Warnings: 0
\| SELECT * FROM demo; \| +------+-------+ \| \| id \| value \| \| +------+-------+ \| \| 1 \| z \| \| \| 2 \| z \| \| +------+-------+ \| (怎么多出来一行)

本事务中第一次读取出一行，做了一次更新后，另一个事务里提交的数据就出现了。也可以看做是一种幻读。

当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。

4、在可重复读级别出现前后数据不一致现象

Session A	Session B
START TRANSACTION;	START TRANSACTION;
\| SELECT * FROM demo; \| +----+-------+ \| \| id \| value \| \| +----+-------+ \| \| 1 \| a \| \| +----+-------+
	INSERT INTO demo VALUES (2, 'b');
	COMMIT;
\| SELECT * FROM demo; \| +----+-------+ \| \| id \| value \| \| +----+-------+ \| \| 1 \| a \| \| +----+-------+
\| SELECT * FROM demo LOCK IN SHARE MODE; \| +----+-------+ \| \| id \| value \| \| +----+-------+ \| \| 1 \| a \| \| \| 2 \| b \| \| +----+-------+
\| SELECT * FROM demo FOR UPDATE; \| +----+-------+ \| \| id \| value \| \| +----+-------+ \| \| 1 \| a \| \| \| 2 \| b \| \| +----+-------+
\| SELECT * FROM demo; \| +----+-------+ \| \| id \| value \| \| +----+-------+ \| \| 1 \| a \| \| +----+-------+