数据库隔离级别以及Mysql实操 一文中,我描述了为了解决并发事务间的冲突,实现事务的隔离性,SQL标椎定义了四种隔离级别,今天就通过这篇文章来看下SQL标准中每种隔离级别的实现原理以及InnoDB引擎又是如何实现的。
解决并发问题最直觉的方法就是加锁了,而标准SQL事务隔离级别的实现就是依赖于锁的。
隔离级别 | 实现 |
---|---|
未提交读 | 事务对当前读取到的数据不加锁;事务在更新的瞬间对其加行级共享锁(读锁),直到事务结束才释放。 更新时加共享锁,会阻塞其他事务的更新,但是不会阻塞读。 由于在更新时没有加排他锁(写锁)并且其他事务读的时候也没有尝试加锁,导致其他事务是可以读到修改的,即脏读。 |
提交读 | 事务对当前读到的数据加行级共享锁,一旦读完该行就释放锁;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。 由于更新时加了排他锁,所以当前事务提交前,其他事务是读不到修改的,这就解决了脏读。 由于读完数据后就释放了锁,所以之后另外一个事务还能修改该行,修改后再读到就是修改之后的数据,这就造成一个事务内读取两次读到的数据是不同的了,即不可重复读。 |
可重复读 | 事务开始读取时,对其加行级共享锁,事务结束后才释放;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。 由于直到事务结束后才释放读锁,所以在事务结束前,其他事务无法修改该行,所以一个事务多次读取到的数据肯定是相同的,就不会存在不可重复读的问题了。 但是这个隔离级别下,由于只能锁住已存在的行,对insert进来的新数据,还是能读到的,即幻读。 |
串行化 | 事务在读取时,加表级共享锁,事务结束后才释放;事务在修改数据时,加表级排他锁。 这个级别下由于加了表锁,所以事务提交前就写不进来新数据,就不存在幻读的问题了。 |
通过锁虽然能实现事务间的隔离,但是开销还是太大了,系统性能肯定是扛不起高并发的,为了优化这个问题,尽量避免使用锁,提出了MVCC方式来解决事务并发问题。
MVCC在InnoDB中是通过两个隐式字段
、undo log
、Read View
实现的。
InnoDB会在每一行加上两个隐式字段:
DB_TRX_ID
: 6bytes,最近修改事务的ID,记录这行记录最后一次修改的事务的IDDB_ROLL_PTR
: 7bytes,回滚指针,指向这条记录的上一个版本(存储于rollback segment中)实际上还有两个字段,但是与MVCC无关。
DB_ROW_ID
: 隐藏的自增ID(隐藏主键),如果没有主键,则InnoDB会自动以DB_ROW_ID
产生一个聚簇索引undo log分为两种:
purge:为了实现MVCC,删除只是设置下记录的deleted_bit,并不真正删除,InnoDB 有专门的purge线程来回收标记删除的记录,为了不影响MVCC的工作,purge线程也维护一个自己的read view,如果某个记录的DB_TRX_ID相对于purge线程read view可见,那么这条记录就能被安全的删除。
执行流程如下:
1> 比如数据库中当前有一条记录:
name | age | DB_ROW_ID | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|---|
n1 | 11 | 1 | 1 | null |
2> 新来一个事务 2修改了记录:update name=n2 where age = 11
,流程如下:
3> 又来一个事务 3修改记录:update name=n3 where age=11
,流程如下:
ReadView中有四个比较重要的内容:
有了这个ReadView,就可以这样判断一条记录是否对该事务可见:
如果某个版本的记录不可见就顺着版本链寻找下一个版本,依次判断是否可见,直到遍历到最后。
现在我们已经了解了undo log与ReadView,那么就来看下MVCC到底是如何实操的。
我们假设当前数据结构如下:
假设 事务20 与 事务30 并发执行,那么对于事务20,它的ReadView中m_ids=[20,30],min_trx_id=20,max_trx_id=31,creator_trx_id=20,对于事务30,它的ReadView 中m_ids=[20,30],min_trx_id=20,max_trx_id=31,creator_trx_id=30
如果此时 事务20 去读取数据,当前版本链中,数据最新版本的DB_TRX_ID为10,它小于 事务20 ReadView的min_trx_id,所以这个版本对 事务20 是可见的。
接着 事务30 修改了这行记录,数据结构就变成了下面这样:
这时 事务 20 再去读这行记录,当前版本链中,数据最新版本的DB_TRX_ID为30,30在 事务20 的m_ids中,所以这个版本数据对 事务20 不可见,继续顺着版本链读上一个版本,上一个版本DB_TRX_ID为10,可见,所以 事务20 就读到了 上一个版本的数据。
在了解InnoDB四种隔离级别的实现之前,我们先明确几个概念
一致性非锁定读是InnoDB在RC和RR两个级别处理SELECT的默认模式,这个过程不用加锁,所以其他事务可以并发修改和读取。
隐式锁定 InnoDB在事务执行过程中采用两阶段锁协议,InnoDB根据隔离级别在需要的时候自动加锁,直到事务提交或回滚之后才释放锁,所有的锁都在同一时刻释放。
显示锁定 通过特定的语句显式锁定:
select ... for update
select ... lock in share mode
InnoDB中,RC与RR两个隔离级别生成ReadView时机是不同的 * RC - 每次读取记录前都生成一个ReadView,而这就导致不可重复读问题 * RR - 在第一次读取时生成一个ReadView,这就解决了可重复读问题
事务隔离级别 | 实现 |
---|---|
未提交读 | 事务对读都不加锁,都是当前读; 事务在更新的瞬间对其加行级共享锁(读锁),直到事务结束才释放。 |
提交读 | 事务对读不加锁,都是快照读;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。 |
可重复读 | 事务读不加锁,都是快照读;事务在更新时,加Next-Key Lock直到事务结束才释放 |
串行化读 | 事务在读取时,加表级共享锁,直到事务结束才释放,都是当前读;写入时加表级排他锁,直到事务结束才释放 |
我们再思考两个问题:
答案是仍然存在,原因是InnoDB在这个级别每次读取记录前都生成一个ReadView。
我们先运行一个例子:
事务A | 事务B |
---|---|
begin; | |
select * from users; Empty set (0.00 sec) | |
begin; | |
insert into users(name,age) values('n1', 1); | |
commit; | |
select * from users; Empty set (0.00 sec) |
OK,看起来是解决了,这个例子中事务B的ID>=事务A的ReadView的max_trx_id,所以事务B写入的数据对事务A是不可见的。
不过先别着急下结论,再看下下面的这个例子:
事务A | 事务B |
---|---|
begin; | |
select * from users; Empty set (0.00 sec) | |
begin; | |
insert into users(name,age) values(‘n1’, 1); | |
commit; | |
update users set name=‘n2’ where id=1; | |
select * from users; +—-+——+——+ | id | name | age | +—-+——+——+ | 1 | n2 | 1 | +—-+——+——+ 1 row in set (0.00 sec) |
这个例子中第二次查询给查出来了,原因在于update是当前读,执行update后生成了一个新的快照,而这个快照对事务A是可见的,所以给查出来了。
如果想第二次select查询结果跟第一次一致,还依赖间隙锁(Gap Lock),事务A的第一个
select * from users;
要显式加锁,即:
select * from users lock in share mode;
这样事务B在执行insert语句时会被阻塞住直到事务A提交。
那么什么是间隙锁呢?
举个例子,age字段有普通索引,对于如下sql:
update users set name='n3' where age = 30;
不止会锁住30这一行记录,而且还会锁住两侧的区间(10,30]和(30,positive infinity)
( 表示包括这个, [ 表示不包括这个,间隙锁遵循前开后闭原则,就是说update … age=10,insert age=30的话是不会撞到锁的。
注意,如果age没有索引,那么会给所有行上一个Gap Lock!但是如果age为唯一索引,就只锁一行了。
Record Lock与Gap Lock的结合,既锁住行也锁住索引之间的间隙。