MySQL事务隔离级别

MySQL事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

1. 未提交读(READ UNCOMMITTED) - 读到其它事务未提交的数据（最新的版本）

   • 错误现象：有脏读、不可重复读、幻读现象

2. 提交读（READ COMMITTED） - 读到其它事务已提交的数据（最新已提交的版本）

   • 错误现象：有不可重复读、幻读现象

   • 使用场景：希望看到最新的有效值

3. 可重复读（REPEATABLE READ） - 在事务范围内，多次读能够保证一致性（快照建立时最新已提交版本）

   • 错误现象：有幻读现象，可以用加锁避免

   • 使用场景：事务内要求更强的一致性，但看到的未必是最新的有效值

4. 串行读(SERIALIZABLE) - 在事务范围内，仅有读读可以并发，读写或写写会阻塞其它事务，用这种办法保证更强的一致性

   • 错误现象：无

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

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

事务并发可能出现的情况

脏读现象(Dirty Read)

一个事务读到了另一个未提交事务修改过的数据

由上图可以发现，事务A、B交替执行，事务A被事务B干扰到了，因为事务A读取到事务B未提交的数据,这就是脏读。

不可重复读(Non-Repeatable Read)

一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值。（不可重复读在读未提交和读已提交隔离级别都可能会出现）

事务A又被事务B干扰到了！在事务A范围内，两个相同的查询，执行同一语句，却返回了不同的数据，这就是不可重复读。

幻读(Phantom)

一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。（幻读在读未提交、读已提交、可重复读隔离级别都可能会出现）

1. 事务A查询全表记录，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询全表记录，结果与之前相同，但插入是会有主键冲突。

2. 事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

使用 for update 避免幻读现象

for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。

只有当出现如下之一的条件，才会释放共享更新锁：
1、执行提交（COMMIT）语句
2、退出数据库（LOG　OFF）
3、程序停止运行

使用串行读避免幻读现象

快照读与当前读

• 当前读，即读取最新提交的数据
  • select … for update
  • insert、update、delete，都会按最新提交的数据进行操作
• 快照读，读取某一个快照建立时（可以理解为某一时间点）的数据
• 快照读主要体现在 select 时，不同隔离级别下，select 的行为不同
  • 在 Serializable 隔离级别下 - 普通 select 也变成当前读
  • 在 RC 隔离级别下 - 每次 select 都会建立新的快照
  • 在 RR 隔离级别下
    1. 事务启动后，首次 select 会建立快照
    2. 如果事务启动选择了 with consistent snapshot，事务启动时就建立快照
    3. 基于旧数据的修改操作，会重新建立快照

RR 下，快照建立时机 – 第一次 select 时

RR 下，快照建立时机 – 事务启动时

RR 下，快照建立时机 – 修改数据时

当前读，即读取最新提交的数据，查询时需要加锁
快照读，读取某一个快照建立时的数据，无需加锁，读取的是历史数据（原理是回滚段）

隔离级别的实现原理

使用MySQL的默认隔离级别（可重复读）来进行说明。
每条记录在更新的时候都会同时记录一条回滚操作（回滚操作日志undo log）。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。即通过回滚（rollback操作），可以回到前一个状态的值。
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

提问：回滚操作日志（undo log）什么时候删除？

MySQL会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。

提问：什么时候不需要了？

当系统里么有比这个回滚日志更早的read-view的时候。

查看当前会话隔离级别

方式一

SHOW VARIABLES LIKE 'transaction_isolation';

方式二

SELECT @@transaction_isolation;

设置隔离级别

方式1：通过set命令

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

其中level有4种值：
level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

关键词：GLOBAL

SET GLOBAL TRANSACTION ISOLATION LEVEL level;

• 只对执行完该语句之后产生的会话起作用
• 当前已经存在的会话无效

关键词：SESSION

SET SESSION TRANSACTION ISOLATION LEVEL level;

• 对当前会话的所有后续的事务有效
• 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务
• 如果在事务之间执行，则对后续的事务有效。

无关键词

SET TRANSACTION ISOLATION LEVEL level;

• 只对当前会话中下一个即将开启的事务有效
• 下一个事务执行完后，后续事务将恢复到之前的隔离级别
• 该语句不能在已经开启的事务中间执行，会报错的

方式2：通过服务启动项命令

可以修改启动参数transaction-isolation的值

比方说我们在启动服务器时指定了--transaction-isolation=READ UNCOMMITTED，那么事务的默认隔离级别就从原来的REPEATABLE READ变成了READ UNCOMMITTED。