跳到主要内容

事务的隔离级别

查看与设置事务隔离级别​

-- 查看当前会话隔离级别
select @@tx_isolation;

-- 补充：
-- 查看系统当前隔离级别
select @@global.tx_isolation;

-- 设置当前会话隔离级别
set session transaction isolation level repeatable read;

-- 设置系统当前隔离级别
set global transaction isolation level repeatable read;

什么是事务?​

参考资料 一文讲清楚MySQL事务隔离级别和实现原理，开发人员必备知识点

MySQL 事务都是指在 InnoDB 引擎下，MyISAM 引擎是不支持事务的。

-- 像如下这样设置引擎
create table city(
  id int(10) auto_increment,
  name varchar(30),
  primary key (id)
)engine=innodb charset=utf8mb4;

数据库事务指的是一组数据操作，事务内的操作要么就是全部成功，要么就是全部失败，什么都不做，其实不是没做，是可能做了一部分但是只要有一步失败，就要回滚所有操作，有点一不做二不休的意思。最常见的例子就是转账，转账必须是双向的，A 支出了钱， B 收到了钱，不能只有一方支出，另一方没有收到，但是如果出现断电之类的异常就需要回滚，否则就会导致一方没收到的情况，这就是事务需要处理的问题

事务的执行过程如下，以 begin 或者 start transaction 开始，然后执行一系列操作，最后要执行 commit 操作，事务才算结束。当然，如果进行回滚操作（rollback），事务也会结束。

需要注意的是，begin 命令并不代表事务的开始，事务开始于 begin 命令之后的第一条语句执行的时候。例如下面示例中，select * from xxx 才是事务的开始

begin;
select * from xxx; 
commit; -- 或者 rollback;

事务具有下面四个特性，简称 ACID，缺一不可：

  • 原子性（Atomicity）事务必须全部执行成功，否则就是失败
  • 一致性（Consistency）事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
  • 隔离性（Isolation）如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
  • 持久性（Durability）即事务完成后，对数据库数据的修改被持久化存储。

事务日志​

参考资料 详细分析MySQL事务日志(redo log和undo log)

常用数据库都支持事务，那么事务是如何实现的呢？为什么即便在事务执行过程中发生宕机，数据库依然能够保证事务的正常呢？

Mysql 实现事务是通过事务日志来实现的，事务日志也是 Innodb 特有的日志，innodb 事务日志包括 redo log和 undo log

  • redo log 是重做日志，提供前滚操作
  • undo log是回滚日志，提供回滚操作

undo log 不是 redo log 的逆向过程，其实它们都算是用来恢复的日志：

redo log 和 undo log 的区别

redo log 通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置）

undo log 用来回滚行记录到某个版本。undo log 一般是逻辑日志，根据每行记录进行记录。

事务日志的特点

使用事务日志，可以提高事务的执行效率。怎么提高呢？存储引擎在修改数据时，只修改数据在内存中的拷贝，再把修改行为记录到事务日志中。事务日志是保存在磁盘的，所以一旦修改数据的行为写入到事务日志，就不用担心丢失了。这样，每次修改数据，就不用每次都把修改的数据写回到磁盘。所以，使用事务日志，能够提高事务的执行效率。

修改事务的行为写入到磁盘上的事务日志时，效率会低吗？不会。事务日志采用的是追加的方式，是在一小块磁盘区域的连续写入，不存在频繁移动磁头的问题，所以，写入事务日志的效率也不低。

内存中被修改的数据，什么时间写回到磁盘呢？内存中被修改的数据，会在后台慢慢写回到磁盘，尽量不影响正常的业务。

保存点​

-- 了解，事务的“保存点”
savepoint "<保存点名字>" ; -- 设置一个事务的保存点
rollback to "<保存点名字>";  -- 指定回滚到一个保存点
release savepoint "<保存点名字>"; -- 撤销保存点

隐式事务​

例如update，insert，delete这些语句

显示事务​

开启事务后数据会先写入缓存区，等提交后才写入磁盘里面，且下面开启的事务实际上是在一个会话里面的（就像 Shell 一样的概念），例如使用 set autocommit = 0 并不会影响到其它的会话

开启事务

-- 方式一：先关闭当前会话的自动提交再开启事务
set autocommit=0;   -- 设置自动提交为禁用，也可以用 set autocommit=off;
start transaction;

--- 方式二：直接使用 begin 关键字开启关键字
begin;
update ....
rollback;

上面两种方式都可以开启事务，其中 start transaction; 可以后面跟一些参数

注意：start transaction 和 set autocommit = 0 的关系

set autocommit = 0：关闭当前会话中事务的自动提交，需要手动 commit 或者 rollback，相当于开启一个全局的事务。在 mysql 的事务中，默认 autocommit = 1，每一次 sql 操作都被认为是一个单次的事务，被隐式提交

-- 可以直接查看这个参数
show variables like 'autocommit';

start transaction：挂起 autocommit 的状态，开启一个事务上下文。首先数据库会隐式提交之前的还未被提交的操作，同时开启一个新事务。挂起 autocommit 的意思是保存 autocommit 的当前状态，然后 start transaction，直到 commit or rollback 结束本次事务，再恢复之前挂起的 autocommit 的状态。

如果 start transaction 前 autocommit = 1，则完成本次事务后 autocommit 还是 1 如果 start transaction 前 autocommit = 0，则完成本次事务后 autocommit 还是 0，接下来的操作你仍需手动 commit 才可以提交。

结束事务（这一步在应用层）

commit;    --成功：提交事务
rollback;   -- 失败：回滚

在提交前是留在内存里的，所以可以使用回滚还原数据

使用例

-- mysql是默认开启事务自动提交的
set autocommit = 0; /* 关闭自动提交 */
-- 手动处理事务
start transaction ;-- 标记一个事务的开始，从这个之后的sql都在同一个事务内

-- 一些操作
insert xx;
insert xx;


-- 注意！！！ 这个 commit 和 rollback 是在程序中判断的(if-else)
-- 所以不是一起执行的意思

commit ;-- 提交：持久化   (成功
rollback ;-- 回滚：回到原来的状态   (失败

-- 事务结束
set autocommit = 1; -- 改回自动提交

这里主要说的是隔离性，它是用于并发控制的。

事务为什么需要隔离？​

参考资料 事务隔离级别小记 参考资料 彻底搞懂 MySQL 事务的隔离级别

一个事务必须与其它事务进行隔离的程度。较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。

也就是说，这个事务是用于并发控制的

数据库并发操作存在的异常情况：

更新丢失​

更新丢失（Lost update）：两个事务都同时更新一行数据但是第二个事务却中途失败退出导致对数据两个修改都失效了这是系统没有执行任何锁操作因此并发事务并没有被隔离开来

两次更新问题​

两次更新问题（Second lost updates problem）：无法重复读取特例，有两个并发事务同时读取同一行数据然后其中一个对它进行修改提交而另一个也进行了修改提交这就会造成第一次写操作失效

脏读取​

脏读取（Dirty Reads）：一个事务开始读取了某行数据但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险很可能所有操作都被回滚

不可重复读取​

不可重复读取（Non-repeatable Reads）：一个事务对同一行数据重复读取两次但是却得到了不同结果。例如在两次读取中途有另外一个事务对该行数据进行了修改并提交

幻读​

幻读（Phantom Reads）：也称为幻像（幻影）。事务在操作过程中进行两次查询，第二次查询结果包含了第一次查询中未出现的数据（这里并不要求两次查询SQL语句相同）这是因为在两次查询过程中有另外一个事务插入数据造成的

不可重复读和幻读的区别​

从总的结果来看，似乎两者都表现为两次读取的结果不一致。

但如果从控制的角度来看, 两者的区别就比较大

  • 对于前者，只需要锁住满足条件的记录，即避免不可重复读需要锁行就行
  • 对于后者，要锁住满足条件及其相近的记录，即避免幻影读则需要锁表

不可重复读重点在于 update 和 delete，而幻读的重点在于 insert

如果使用锁机制来实现这两种隔离级别，在可重复读中，该 SQL 第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复 读了。但这种方法却无法锁住 insert 的数据，所以当事务 A 先前读取了数据，或者修改了全部数据，事务 B 还是可以 insert 数据提交，这时事务 A 就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要 Serializable 隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

上文说的，是使用悲观锁机制来处理这两种问题，但是 MySQL、ORACLE、PostgreSQL 等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的 MVCC（多版本并发控制）来避免这两种问题。

事务隔离级别​

参考资料 一文讲清楚MySQL事务隔离级别和实现原理，开发人员必备知识点

为了解决上述的问题，SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

  1. 读未提交（READ UNCOMMITTED）
  2. 读提交 （READ COMMITTED）
  3. 可重复读 （REPEATABLE READ）
  4. 串行化 （SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

读未提交​

参考资料 廖雪峰的官方网站 Read Uncommitted

READ UNCOMMITTED 也称未授权读取。允许脏读取但不允许更新丢失，如果一个事务已经开始写数据则另外一个数据则不允许同时进行写操作但允许其他事务读此行数据。该隔离级别可以通过 “排他写锁” 实现。

事务隔离的最低级别，仅可保证不读取物理损坏的数据。与READ COMMITTED 隔离级相反，它允许读取已经被其它用户修改但尚未提交确定的数据。也就是 “脏读” 下面是具体的例子

一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

首先，准备好 students 表的数据，该表仅一行记录：

+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
时刻事务A事务B
2BEGIN;BEGIN;
3UPDATE students SET name = 'Bob' WHERE id = 1;
4SELECT * FROM students WHERE id = 1;
5ROLLBACK;
6SELECT * FROM students WHERE id = 1;
7COMMIT;

这两个事务第一步都先把事务等级切换成 READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

然后开启两个终端分别按上面步骤执行

读提交​

READ COMMITTED 也称授权读取。不允许脏读取，但是还是可能存在不可重复读取。这可以通过 “瞬间共享读锁” 和 “排他写锁” 实现，读取数据的事务允许其他事务继续访问该行数据，但是未提交写事务将会禁止其他事务访问该行。

SQL Server、Oracle等多数数据库默认都是该级别。在此隔离级下，SELECT 命令不会返回尚未提交（Committed） 的数据，也不能返回脏数据。

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

使用的依旧是上面那个 students 表

然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

时刻事务A事务B
2BEGIN;BEGIN;
3SELECT * FROM students WHERE id = 1;
4UPDATE students SET name = 'Bob' WHERE id = 1;
5COMMIT;
6SELECT * FROM students WHERE id = 1;
7COMMIT;

这两个事务第一步都先把事务等级切换成 READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

可重复读取​

参考资料 既然MySQL中InnoDB使用MVCC，为什么REPEATABLE-READ不能消除幻读？ - 黄杰的回答

REPEATABLE READ 可重复读是 InnoDB默认级别。在同一个事务内的查询都是事务开始时刻一致的。

可重复读禁止不可重复读取和脏读取。但是有时可能出现幻影数据，这可以通过 “共享读锁” 和 “排他写锁” 实现，读取数据事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

innodb 的 RR 是否解决幻读​

参考资料 mysql innodb RR级别到底有没有解决幻读？

既然 MySQL中 InnoDB 使用 MVCC，为什么 REPEATABLE-READ 不能消除幻读？

答：mysql innodb只在一定程度上避免了一些幻读，但明没有真正解决幻读。

快照读（只针对 Select 操作）：

1：一个 Session 永远读不到另外一个 Session 提交的数据，避免了幻读

2：一个 Session 在执行过程中另外一个 Session 插入了一条记录并提交，那么在当前 Session 重复插入的时候唯一索引冲突，明明没数据为什么冲突了？再次查询并无多数据，但是冲突提醒变相的出现了幻读。

3：一个 Session 在执行过程中另外一个 Session 插入了一条记录并提交，那么在当前 Session 中修改另外一个 Session 插入的数据提示会无数据受影响，但是再次查询多了条数据？出现幻读。（下面那个例子就是这种情况）

当前读（select for update）：

1：一个 Session 在当前读过程中没有用到索引，其他 Session 无法插入数据，因为 Update 锁了全表，避免幻读。

2：一个 Session 在当前读过程中用到了范围索引，那么其他 Session 也会因为行锁（临键锁或者间隙锁）的情况无法插入，避免幻读。

举个例子

首先，准备好 students 表的数据，该表仅一行记录：

+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
时刻事务A事务B
2BEGIN;BEGIN;
3SELECT * FROM students WHERE id = 99;
4INSERT INTO students (id, name) VALUES (99, 'Bob');
5COMMIT;
6SELECT * FROM students WHERE id = 99;
7UPDATE students SET name = 'Alice' WHERE id = 99;
8SELECT * FROM students WHERE id = 99;
9COMMIT;

这两个事务第一步都先把事务等级切换成 REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

注意：这里的第六步读到的记录仍然为空，因为 Repeatable Read 下在同一个事务内的查询都是与事务开始时刻一致，所以在 B事务过程中是不会读到期间 A 中 insert 的值的（因为要保证可重复读），而 innoDB 的幻读主要问题就是这里，一开始 B 事务的 select 读取不出 A 事务 insert 的值，但是这里却能更新这条 select 查询不到的数据，然后 B 事务再次 select 这条数据却神奇的出现了

串行 Serializable​

Serializable 是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

也称可串行读。提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行（就是直接阻塞了），所以肯定没有并发问题了。

虽然 Serializable 隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用 Serializable 隔离级别。

隔离级别的解决程度​

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这上述问题的解决程度。

隔离级别脏读不可重复读幻读
读未提交可能可能可能
读提交不可能可能可能
可重复读不可能不可能可能
串行化不可能不可能不可能