隔离级别
所谓隔离级别(Isolation Level),就是在数据库事务中,为保证并发数据读写的正确性而提出的定义;每种关系型数据库(如Oracle、Mysql)都提供了各自特色的隔离级别实现,虽然在通常的定义中是以锁为实现单元,但实际的实现千差万别。
SQL标准
中规定,针对不同的隔离级别,并发事务可以发生不同严重程度的问题,具体情况如下:
脏读:一个事务能够读到其他事务尚未提交的修改
不可重复读:在一个事务执行过程中多次读取的数据是不一致的 ,能读到其他已提交事务对数据的更新
幻读:事务 A 根据条件查询得到了 N 条数据,但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据,这样当事务 A 再次进行查询的时候发现会有 N+M 条数据,产生了幻读。
mysql事务隔离级别
按照隔离程度从低到高,MySQL 事务隔离级别分为四个不同层次:
- 读未提交(Read Uncommitted),就是一个事务能够看到其他尚未提交事务对数据的修改,这是最低的隔离水平,允许脏读,不可重复读和幻象读(Phantom Read)出现。
- 读已提交(Read Committed),一个事务只能看到其他已经提交事务对数据的修改,也就是保证不会看到任何中间性状态;允许不可重复读和幻象读(Phantom Read)出现。
- 可重复读(Repeatable Read),保证在一个事务执行过程中多次读取到的数据是一致的,不能读到其他事务对数据的更新,这是 MySQL InnoDB 引擎的默认隔离级别,但是和一些其他数据库实现不同的是, MySQL 在可重复读级别可以禁止幻象读发生。
以上3中隔离级别都允许其他事务并发修改数据
- 串行化(Serializable),并发事务之间是串行化的,通常意味着读取需要获取共享读锁,更新需要获取排他写锁,如果 SQL 使用 WHERE 语句,还会获取区间锁(MySQL 以 GAP间隙 锁形式实现,可重复读级别中默认也会使用),这是最高的隔离级别。
可重复读:在一个事务执行过程中多次读取的数据是一致的,不能读到其他已提交事务对数据的更新
不可重复读: 在一个事务执行过程中多次读取的数据是不一致的 ,能读到其他已提交事务对数据的更新
其中“读提交”和“可重复读”比较难理解,所以用一个例子说明这几种隔离级别。假设数据表 T 中只有一列,其中一行的值为 1,下面是按照时间顺序执行两个事务的行为。
mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);
我们来看看在不同的隔离级别下,事务 A 会有哪些不同的返回结果,也就是图里面 V1、V2、V3 的返回值分别是什么。
-
若隔离级别是“读未提交”, 则 V1 的值就是 2。这时候事务 B 虽然还没有提交,但是结果已经被 A 看到了。因此,V2、V3 也都是 2。
-
若隔离级别是“读提交”,则 V1 是 1,V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以, V3 的值也是 2。
-
若隔离级别是“可重复读”,则 V1、V2 是 1,V3 是 2。之所以 V2 还是 1,遵循的就是这个要求:事务在执行期间看到的数据前后必须是一致的。
-
若隔离级别是“串行化”,则在事务 B 执行“将 1 改成 2”的时候,会被锁住。直到事务 A 提交后,事务 B 才可以继续执行。所以从 A 的角度看, V1、V2 值是 1,V3 的值是 2。
在实现上,数据库里面会创建一个ReadView视图,访问的时候以ReadView视图的逻辑结果为准。
在“可重复读”隔离级别下,这个ReadView视图是在事务启动时创建的,整个事务执行期间都用这个视图;
在“读提交”隔离级别下,在每个 SQL 语句开始执行的时候都会创建一个新的ReadView视图,所以一个事务是可以看到其他已经提交事务对数据的修改。
这里需要注意的是,“读未提交”隔离级别下直接返回记录上的最新值,没有ReadView视图概念;而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
事务隔离的实现
以最常见的 MySQL InnoDB 引擎为例,它是基于 MVCC(Multi-Versioning Concurrency Control)的实现;
多版本并发控制:
对于使用InnoDB
存储引擎的表来说,它的聚簇索引记录中都包含两个必要的隐藏列:
-
trx_id
:每次一个事务对某条记录进行改动时,都会把该事务的事务id
赋值给trx_id
隐藏列。 -
roll_pointer
:每次对某条记录进行改动时,都会把旧的版本写入到undo日志
中,然后这个隐藏列就相当于一个指针,指向前一个版本,可以通过它来找到该记录修改前的信息。
事务id
由MySQL控制,是一个递增的值,只有在对表中的记录做改动时(执行INSERT、DELETE、UPDATE这些语句时)才会为事务分配事务id,否则在一个只读事务中的事务id值都默认为0。
一条记录所有的版本都会被
roll_pointer
属性连接成一个链表,我们把这个链表称之为版本链
,版本链的头节点就是当前记录最新的值。
假设一个值从 1 被按顺序改成了 2、3、4,在回滚日志里面就会有类似下面的记录。
当前值是 4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的 read-view。如图中看到的,在视图 A、B、C 里面,这一个记录的值分别是 1、2、4;
同一条记录在系统中可以存在多个版本,这就是数据库的多版本并发控制(MVCC);
ReadView
对于使用READ COMMITTED
和REPEATABLE READ
隔离级别的事务来说,都必须保证只能读到已经提交事务对记录的修改,也就是说假如一个事务已经修改了记录但是尚未提交,其他事务不能直接读取该记录的最新版本
核心问题就是:需要判断一下版本链中的哪个版本是当前事务可见的。
为此,设计InnoDB
的开发者提出了一个ReadView
的概念,这个ReadView
中主要包含4个比较重要的内容:
-
m_ids
:表示在生成ReadView
时当前系统中活跃的读写事务的事务id
列表。 -
min_trx_id
:表示在生成ReadView
时当前系统中活跃的读写事务中最小的事务id
,也就是m_ids
中的最小值。 -
max_trx_id
:表示生成ReadView
时系统中应该分配给下一个事务的id
值。 -
creator_trx_id
:表示生成该ReadView
的事务的事务id
。
在“可重复读”隔离级别下,这个
ReadView
视图是在事务启动时创建的,整个事务存在期间 都用这个ReadView
视图。在“读提交”隔离级别下,每个 SQL 语句开始执行前都生成一个ReadView。
有了这个ReadView
,这样在访问某条记录时,只需要按照下边的规则判断记录的某个版本是否可见:
ReadView | 说明 |
---|---|
( trx_id == creator_trx_id ) | 如果被访问版本的trx_id 属性值与ReadView 中的creator_trx_id 值相同,意味着当前事务在访问它自己修改过的记录,所以该版本可以被当前事务访问。 |
( trx_id < min_trx_id ) | 如果被访问版本的trx_id 属性值小于ReadView 中的min_trx_id 值,表明生成该版本的事务在当前事务生成 ReadView 前已经提交,所以该版本可以被当前事务访问。 |
( trx_id >= max_trx_id ) | 如果被访问版本的trx_id 属性值大于或等于ReadView 中的max_trx_id 值,表明生成该版本的事务在当前事务生成 ReadView 后才开启,所以该版本不可以被当前事务访问。 |
(min_trx_id <=trx_id < max_trx_id ) | 如果被访问版本的trx_id 属性值在ReadView 的min_trx_id 和max_trx_id 之间,那就需要判断一下trx_id 属性值是不是在m_ids 列表中 :如果在,说明创建 ReadView 时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建 ReadView 时生成该版本的事务已经被提交,该版本可以被访问 |
READ COMMITTED隔离级别
时间 | # Transaction 100 | # Transaction 200 | # READ COMMITTED隔离级别的事务 |
---|---|---|---|
T1 | BEGIN; UPDATE hero SET name = ‘关羽’ WHERE number = 1; UPDATE hero SET name = ‘张飞’ WHERE number = 1; | BEGIN; | BEGIN; |
T2 | # SELECT1: Transaction 100、200均未提交 SELECT * FROM hero WHERE number = 1; # 得到的name列的值为’刘备’ | ||
T3 | COMMIT; | ||
T4 | UPDATE hero SET name = ‘赵云’ WHERE number = 1; UPDATE hero SET name = ‘诸葛亮’ WHERE number = 1; | ||
T5 | # SELECT2: Transaction 100提交,Transaction 200未提交 SELECT * FROM hero WHERE number = 1; # 得到的name列的值为’张飞’ | ||
T6 |
T2此刻,表hero
中number
为1
的记录得到的版本链表如下所示:
这个SELECT1
的执行过程如下:
- 在执行
SELECT
语句时会先生成一个ReadView
,ReadView
的m_ids
列表的内容就是[100, 200]
,min_trx_id
为100
,max_trx_id
为201
,creator_trx_id
为0
。 - 然后从版本链中挑选可见的记录,从图中可以看出,最新版本的列
name
的内容是'张飞'
,该版本的trx_id
值为100
,在m_ids
列表内,所以不符合可见性要求,根据roll_pointer
跳到下一个版本。 - 下一个版本的列
name
的内容是'关羽'
,该版本的trx_id
值也为100
,也在m_ids
列表内,所以也不符合要求,继续跳到下一个版本。 - 下一个版本的列
name
的内容是'刘备'
,该版本的trx_id
值为80
,小于ReadView
中的min_trx_id
值100
,所以这个版本是符合要求的,最后返回给用户的版本就是这条列name
为'刘备'
的记录。
T5此刻,表hero
中number
为1
的记录的版本链长这样:
这个SELECT2
的执行过程如下:
- 在执行
SELECT
语句时会又会单独生成一个ReadView
,该ReadView
的m_ids
列表的内容就是[200]
(事务id
为100
的那个事务已经提交了,所以再次生成快照时就没有它了),min_trx_id
为200
,max_trx_id
为201
,creator_trx_id
为0
。 - 然后从版本链中挑选可见的记录,从图中可以看出,最新版本的列
name
的内容是'诸葛亮'
,该版本的trx_id
值为200
,在m_ids
列表内,所以不符合可见性要求,根据roll_pointer
跳到下一个版本。 - 下一个版本的列
name
的内容是'赵云'
,该版本的trx_id
值为200
,也在m_ids
列表内,所以也不符合要求,继续跳到下一个版本。 - 下一个版本的列
name
的内容是'张飞'
,该版本的trx_id
值为100
,小于ReadView
中的min_trx_id
值200
,所以这个版本是符合要求的,最后返回给用户的版本就是这条列name
为'张飞'
的记录。
REPEATABLE READ隔离级别
时间 | # Transaction 100 | # Transaction 200 | # REPEATABLE READ隔离级别的事务 |
---|---|---|---|
T1 | BEGIN; UPDATE hero SET name = ‘关羽’ WHERE number = 1; UPDATE hero SET name = ‘张飞’ WHERE number = 1; | BEGIN; | BEGIN; |
T2 | # SELECT1: Transaction 100、200均未提交 SELECT * FROM hero WHERE number = 1; # 得到的name列的值为’刘备’ | ||
T3 | COMMIT; | ||
T4 | UPDATE hero SET name = ‘赵云’ WHERE number = 1; UPDATE hero SET name = ‘诸葛亮’ WHERE number = 1; | ||
T5 | # SELECT2: Transaction 100提交,Transaction 200未提交 SELECT * FROM hero WHERE number = 1; # 得到的name列的值仍为’刘备’ | ||
T6 |
T2此刻,表hero
中number
为1
的记录得到的版本链表如下所示:
- 在执行
SELECT
语句时会先生成一个ReadView
,ReadView
的m_ids
列表的内容就是[100, 200]
,min_trx_id
为100
,max_trx_id
为201
,creator_trx_id
为0
。 - 然后从版本链中挑选可见的记录,从图中可以看出,最新版本的列
name
的内容是'张飞'
,该版本的trx_id
值为100
,在m_ids
列表内,所以不符合可见性要求,根据roll_pointer
跳到下一个版本。 - 下一个版本的列
name
的内容是'关羽'
,该版本的trx_id
值也为100
,也在m_ids
列表内,所以也不符合要求,继续跳到下一个版本。 - 下一个版本的列
name
的内容是'刘备'
,该版本的trx_id
值为80
,小于ReadView
中的min_trx_id
值100
,所以这个版本是符合要求的,最后返回给用户的版本就是这条列name
为'刘备'
的记录。
T5此刻,表hero
中number
为1
的记录的版本链如下:
这个SELECT2
的执行过程如下:
- 因为当前事务的隔离级别为
REPEATABLE READ
,而之前在执行SELECT1
时已经生成过ReadView
了,所以此时直接复用之前的ReadView
,之前的ReadView
的m_ids
列表的内容就是[100, 200]
,min_trx_id
为100
,max_trx_id
为201
,creator_trx_id
为0
。 - 然后从版本链中挑选可见的记录,从图中可以看出,最新版本的列
name
的内容是'诸葛亮'
,该版本的trx_id
值为200
,在m_ids
列表内,所以不符合可见性要求,根据roll_pointer
跳到下一个版本。 - 下一个版本的列
name
的内容是'赵云'
,该版本的trx_id
值为200
,也在m_ids
列表内,所以也不符合要求,继续跳到下一个版本。 - 下一个版本的列
name
的内容是'张飞'
,该版本的trx_id
值为100
,也在m_ids
列表内,所以该版本也不符合要求,根据roll_pointer
跳到下一个版。 同理下一个列name
的内容为'关羽'
版本的,也不符合要求。继续跳到下一个版本。 - 下一个版本的列
name
的内容是'刘备'
,该版本的trx_id
值为80
,小于ReadView
中的min_trx_id
值100
,所以这个版本是符合要求的,最后返回给用户的版本就是这条列name
值为'刘备'
的记录。
MVCC小结
从上边的描述中我们可以看出来,所谓的MVCC
(Multi-Version Concurrency Control ,多版本并发控制)指的就是在使用READ COMMITTD
、REPEATABLE READ
这两种隔离级别的事务在执行普通的SELECT
操作时访问记录的版本链的过程,这样子可以使不同事务的读-写
、写-读
操作并发执行,从而提升系统性能。
READ COMMITTD
、REPEATABLE READ
这两个隔离级别的一个很大不同就是:生成ReadView的时机不同:
-
在“可重复读”隔离级别下,这个
ReadView
视图是在事务启动时创建的,整个事务存在期间 的普通SELECT查询操作 都重复用这个ReadView
视图。 -
在“读提交”隔离级别下,每个 普通SELECT查询操作语句开始执行前都生成一个ReadView。
幻读
为便于说明问题,我们新建一个如下表
CREATE TABLE `t` (
`id` INT ( 11 ) NOT NULL,
`c` INT ( 11 ) DEFAULT NULL,
`d` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `c` ( `c` )) ENGINE = INNODB;
insert into t values
(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25);
幻读是什么?
如果我们执行下面这条sql语句会发生什么呢, mysql又是怎么加锁的呢?
select * from t where d=5 for update;
由于字段 d 上没有索引,因此这条查询语句会做全表扫描,这个语句会命中 d=5 的这一行,对应的主键 id=5,因此 id=5 这一行会加一个写锁,那么,其他被扫描到的,但是不满足条件的 5 行记录上,会不会被加锁呢?
现在,我们就来分析一下,如果只在 d=5,也就是id=5 这一行加锁,而其他行的不加锁的话,会怎么样。
下面先来看一下这个场景(注意:这是假设的一个场景):
[图 1 假设只在 id=5 这一行加行锁]
可以看到,session A 里执行了三次查询,分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同,都是 select * from t where d=5 for update。这个语句的意思,查所有 d=5 的行,而且使用的是当前读,并且加上写锁。现在,我们来看一下这三条 SQL 语句,分别会返回什么结果。
- Q1 只返回 id=5 这一行;
- 在 T2 时刻,session B 把 id=0 这一行的 d 值改成了 5,因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行;
- 在 T4 时刻,session C 又插入一行(1,1,5),因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。
其中,Q3 读到 id=1 这一行的现象,被称为“幻读”。也就是说,幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。
幻读:事务 A 根据条件查询得到了 N 条数据,但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据,这样当事务 A 再次进行查询的时候发现会有 N+M 条数据,产生了幻读。
这里,需要对“幻读”做一个说明:
-
在Mysql的可重复读隔离级别下,普通的查询是快照读,这个
ReadView
视图是在事务启动时创建的,整个事务存在期间 都是用这个ReadView
视图,所以是不会看到别的事务插入的数据的。 -
而在共享锁或排他锁的模式下,一个事务是可以读取到其他已提交事务对数据的修改,即可以读取到记录最新的值(称之为当前读) ; 因此,幻读在 “当前读” 情况下才会出现。
幻读有什么问题?
数据一致性的问题。
我们知道,锁的设计是为了保证数据的一致性。而这个一致性,不止是数据库内部数据状态在此刻的一致性,还包含了数据和日志在逻辑上的一致性。
假设如下场景(注意是假设):“select * from t where d=5 for update” 这条语句只给 d=5 这一行,也就是 id=5 的这一行加锁”
现在,我们来分析一下执行完成后,数据库里会是什么结果。
-
经过 T1 时刻,id=5 这一行变成 (5,5,100),当然这个结果最终是在 T6 时刻正式提交的 ;
-
经过 T2 时刻,id=0 这一行变成 (0,5,5);
-
经过 T4 时刻,表里面多了一行 (1,5,5);
-
其他行跟这个执行序列无关,保持不变。
我们再来看看这时候 binlog 里面的内容:
- T2 时刻,session B 事务提交,写入了两条语句;
- T4 时刻,session C 事务提交,写入了两条语句;
- T6 时刻,session A 事务提交,写入了 update t set d=100 where d=5 这条语句。
我统一放到一起的话,就是这样的:
update t set d=5 where id=0; /*(0,0,5)*/ ## session B
update t set c=5 where id=0; /*(0,5,5)*/ ## session B
insert into t values(1,1,5); /*(1,1,5)*/ ## session C
update t set c=5 where id=1; /*(1,5,5)*/ ## session C
update t set d=100 where d=5;/*所有d=5的行,d改成100*/ ## session A
好,你应该看出问题了。这个语句序列,不论是拿到备库去执行数据同步,还是以后用 binlog 来进行数据恢复,
这三行的结果,都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)
也就是说,id=0 和 id=1 这两行,发生了数据不一致。
而MySQL的恢复机制和同步Slave机制都是通过BINLOG记录来执行IUD操作来实现的
这个问题很严重,是不行的。
到这里,我们再回顾一下,这个数据不一致到底是怎么引入的?
我们分析一下可以知道,这是我们假设“select * from t where d=5 for update 这条语句只给 d=5 这一行,也就是 id=5 的这一行加锁”导致的。
那怎么改呢?我们把扫描过程中碰到的行,也都加上写锁,再来看看执行效果。
由于 session A 把所有的行都加了写锁,所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后,session B 才能继续执行。
这样对于 id=0 这一行,在数据库里的最终结果还是 (0,5,5)。在 binlog 里面,执行序列是这样的:
insert into t values(1,1,5); /*(1,1,5)*/ ## session C
update t set c=5 where id=1; /*(1,5,5)*/ ## session C
update t set d=100 where d=5;/*所有d=5的行,d改成100*/ ## session A
update t set d=5 where id=0; /*(0,0,5)*/ ## session B
update t set c=5 where id=0; /*(0,5,5)*/ ## session B
可以看到,按照日志顺序执行,id=0 这一行的最终结果也是 (0,5,5)。所以,id=0 这一行的问题解决了。
但同时也可以看到,id=1 这一行,在数据库里面的结果是 (1,5,5),而根据 binlog 的执行结果是 (1,5,100),也就是说幻读的问题还是没有解决。
为什么我们把所有的记录都上了锁,还是阻止不了 id=1 这一行的插入和更新呢?
原因很简单。在 T3 时刻,我们给所有行加锁的时候,id=1 这一行还不存在,不存在也就加不上锁。
如何解决幻读?
产生幻读的原因是,行锁只能锁住已存在的数据行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。 因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。
顾名思义,间隙锁,锁的就是两个值之间的空隙(不包括记录本身,前后都是开区间)。比如文章开头的表 t,初始化插入了 6 个记录,这就产生了 7 个间隙。
这样,当你执行 select * from t where d=5 for update 的时候,就不止是给数据库中已有的 6 个记录加上了行锁,还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。
也就是说这时候,在一行行扫描的过程中,不仅将给行加上了行锁,还给行两边的空隙,也加上了间隙锁。
行锁、间隙锁(gap lock) 和next-key lock
在InnoDB中有三种行锁的方式:
1)记录锁(行锁):针对单个行记录添加锁。
2)间隙锁(Gap Locking):可以帮我们锁住一个范围(索引之间的空隙),但不包括记录本身。
3)Next-Key lock: 间隙锁+记录锁合称 next-key lock,每个 next-key lock 是前开后闭区间,锁住一个范围,同时锁定记录本身,可以解决幻读的问题。