MySQL事务隔离级别
SQL 标准定义了四种隔离级别,MySQL 全都支持。这四种隔离级别分别是:
-
未提交读(READ UNCOMMITTED) - 读到其它事务未提交的数据(最新的版本)
- 错误现象:有脏读、不可重复读、幻读现象
-
提交读(READ COMMITTED) - 读到其它事务已提交的数据(最新已提交的版本)
-
错误现象:有不可重复读、幻读现象
-
使用场景:希望看到最新的有效值
-
-
可重复读(REPEATABLE READ) - 在事务范围内,多次读能够保证一致性(快照建立时最新已提交版本)
-
错误现象:有幻读现象,可以用加锁避免
-
使用场景:事务内要求更强的一致性,但看到的未必是最新的有效值
-
-
串行读(SERIALIZABLE) - 在事务范围内,仅有读读可以并发,读写或写写会阻塞其它事务,用这种办法保证更强的一致性
- 错误现象:无
从上往下,隔离强度逐渐增强,性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定,其中,可重复读是 MySQL 的默认级别。
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | 可能 | 可能 | 可能 |
读已提交 | 不可能 | 可能 | 可能 |
可重复读 | 不可能 | 不可能 | 可能 |
串行化 | 不可能 | 不可能 | 不可能 |
事务并发可能出现的情况
脏读现象(Dirty Read)
一个事务读到了另一个未提交事务修改过的数据
由上图可以发现,事务A、B交替执行,事务A被事务B干扰到了,因为事务A读取到事务B未提交的数据,这就是脏读。
不可重复读(Non-Repeatable Read)
一个事务只能读到另一个已经提交的事务修改过的数据,并且其他事务每对该数据进行一次修改并提交后,该事务都能查询得到最新值。(不可重复读在读未提交和读已提交隔离级别都可能会出现)
事务A又被事务B干扰到了!在事务A范围内,两个相同的查询,执行同一语句,却返回了不同的数据,这就是不可重复读。
幻读(Phantom)
一个事务先根据某些条件查询出一些记录,之后另一个事务又向表中插入了符合这些条件的记录,原先的事务再次按照该条件查询时,能把另一个事务插入的记录也读出来。(幻读在读未提交、读已提交、可重复读隔离级别都可能会出现)
-
事务A查询全表记录,另一个并发事务B往这个范围中插入/删除了数据,并静悄悄地提交,然后事务A再次查询全表记录,结果与之前相同,但插入是会有主键冲突。
-
事务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 隔离级别下
- 事务启动后,首次 select 会建立快照
- 如果事务启动选择了 with consistent snapshot,事务启动时就建立快照
- 基于旧数据的修改操作,会重新建立快照
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
。