MySQL InnoDB事务隔离级别笔记

 

TL;DR

MySQL InnoDB可以设定ANSI SQL-92中规定的四个事务隔离级别,事务并发度和事务隔离级别成反比,事务隔离级别越高,并发度越低。

是否有锁操作,取决于当前的读取是快照读,还是当前读,快照读读取的是可见的历史版本,无需上锁,简单的读操作是快照读(SELECT无附加语句),其他都是当前读,需要加锁。

对无索引的数据,InnoDB会锁定全表的记录,但是会在扫描过程中释放不符合筛选规则的记录的锁定。

对于有索引的数据,需要具体分析,在RR这一级别,InnoDB通过GAP锁机制避免了幻读问题。

ANSI SQL-92标准

事务隔离级别在ANSI SQL-92标准中有了明确的定义,即我们熟知的READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE,级别由低到高。

在大量博文中经常提到的比如脏读(Dirty read)不可重复读(Non-repeatable read)幻读(Phantom),标准也有提及。

脏读(Dirty read)

简而言之,即两个或多个事务中有一个事务更改了数据,无论提交与否,都会读取到数据的改动。

在ANSI SQL-92标准中,READ UNCOMMITTED会可能出现脏读。

不可重复读(Non-repeatable read)

两个或多个事务中有一个事务更改了数据,只有提交之后,才会读取到数据的改动。

在ANSI SQL-92标准中,READ COMMITTED不会出现脏读,但是会可能出现不可重复读。

幻读(Phantom)

幻读发生的前提是一个事务改动(新增)并提交处于其他某一事务查询条件范围内,并且其他事务重复使用同一条件进行查询。

在ANSI SQL-92标准中,REPEATABLE READ不会出现脏读,不会出现不可重复读,但是会可能出现幻读。

在最高的事务隔离级别SERIALIZABLE中,以上问题都不会出现。

MySQL InnoDB的RR事务隔离级别

InnoDB已经是MySQL首选存储引擎,内容会针对默认的隔离级别展开。

MySQL InnoDB实现了标准中的4个事务隔离级别,默认的事务隔离级别是REPEATABLE READ

和标准不同的是,MySQL在这一级别通过GAP锁消除了幻读问题。

MySQL的并发度和事务隔离级别成反比,事务隔离级别越高,并发度越低。

幻读的解决方案:GAP锁

GAP锁用于锁定记录的间隙,防止事务并发时同一搜索条件下,出现事务中前后两次读取结果不同的情况。

来看测试表test:

现有数据:

事务A(ID 381797)尝试读取v1=10的记录:

这是一个当前读,由于v1是一个唯一索引,v1=10这一个条件处于id=5,v1=4与id=8,v1=100的记录之间的间隙,所以为了防止两次读取数据不一样,需要在这之间增加GAP锁。

同时事务B(ID 381798)尝试写入一条记录:

v1=6处于间隙之中,通过查询当前锁情况:

后来事务B需要等待先来事务A释放这一区间上的GAP锁,最终锁等待超时。

快照读

通过锁定,是可以实现RR事务隔离级别的。关键在于,对于需要改动的数据或者数据范围,不要过早的释放锁,在事务完成之后才进行释放。

简单读取操作是S锁,其他操作可以是X锁,S锁和X锁不能同时存在。

《事务处理原理》一书中提到两阶段锁定(2PL,two-phase locking),即一个事务必须在释放它的任何锁之前获得它所有的锁。如果用锁定的方式实现RR,在整个事务中,其他事务的简单读操作也会被阻塞。

InnoDB实现了MVCC的方式,可以实现事务中简单读不需锁定的效果,即实现了快照读。

参考

ANSI SQL-92

Innodb中的事务隔离级别和锁的关系

MySQL 加锁处理分析

InnoDB’s gap locks

深入分析事务的隔离级别

MySQL 5.7 Reference Manual / 14.5.1 InnoDB Locking

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">