Transaction Isolation Levels of Mysql Innodb

Transaction Isolation Levels of Mysql Innodb

数据库事务隔离级别Mysql官网

先说数据库事务隔离级别是什么,再说分哪几大块。(杂而乱)

Transaction Isolation

事务隔离是数据库处理几大基础之一,事务隔离可以做到极度安全,几大问题,脏读,不可重复读,幻读都可以解决,为什么还要出事务隔离级别,the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time. 事务隔离级别是为调节性能和问题的平衡。对于极其重要的数据,损失一点性能也不要紧。对于可能极其罕见会出现的问题,作出一点牺牲,现实世界,求利益最大化也可以。InnoDB对于隔离级别的支持通过使用不同的锁locking策略。

隔离级别基于锁策略,锁必须先说。InnoDB Locking
* Shared and Exclusive Locks 读锁和写锁
* Intention Locks
* Record Locks
* Gap Locks * Next-Key Locks * Insert Intention Locks

Shared and Exclusive Locks 读锁和写锁

最常见的读锁和写锁(共享锁,排它锁),是行级别锁。

  • 一个事务持有读锁,别的事务还可以来一起持有读锁,有想修改这行数据的事务想要写锁,是要等待,前边的事务完毕,释放。
  • 一个事务持有写锁,别的事务想对该行,读的写的,都要等待前边的事务完毕。
Intention Locks 意图锁

InnoDB支持多种粒度的锁,允许行级别锁和表级别锁共存。为了实现这个共存,Intention Locks 被用上了。这个锁的细节是讲读锁和写锁的实现过程,和原理,不影响讲解下文,细节自己看。

Record Locks 记录锁

Record Locks 锁在一个索引记录上,例如,SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;这条语句,阻止其他事务对这行记录进行,insert,update,delete操作。
Record Locks 锁在索引记录上,即使没有索引,其实InnoDB给没有索引的表创建了一个隐藏的索引Clustered and Secondary Indexes

官网有段InnoDB monitor的日志关于Record Locks

Transaction data for a record lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`  
trx id 10078 lock_mode X locks rec but not gap  
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;
Gap Locks 间隙锁 (重要 跟后边密切相关)

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

A gap might span a single index value, multiple index values, or even be empty.

Gap Locks锁是一个相邻行与行之间的锁,(或者第一行记录跟“0”的之间的间隙,或者最后行记录跟无穷之间的间隙(不好理解)),总之是间隙,只锁间隙。举例来说,SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;这个语句会阻止其他事务插入c1为10-20之间任何一个,比如插入 c1=15,不管有没有15这一行,都被阻止,直到前边的事务完毕。

一个gap lock 可能跨度是一个索引值index value,比如1,也可能是多个索引值,10-20 10,还可能是空0-1(理解有歧义,英文也说不清楚)继续。

Gap lock是性能和并发问题的权衡的一部分,只在一部分隔离级别中使用。

那些使用唯一索引去搜索一个唯一的一行是不需要gap lock间隙锁的。(其中不包括的情况,什么情况,搜索条件只包括那些多字段唯一索引。)例如,

SELECT * FROM child WHERE id = 100;

  • 如果 id 字段有唯一索引,这个语句值只用到了一个index-record lock 锁,其他的session插入到该行之前的gap间隙没有关系。

  • 如果id 不是索引,或者不唯一,这条语句就会加上preceding gap(前间隙锁)

这里需要说一下,不同事务的冲突锁是可以加在一个gap间隙上的。比如,事务A有一个共享间隙锁在一个gap间隙上,事务B也可以有一个排他间隙锁在相同的间隙上。为什么有冲突的gap间隙锁可以被允许,如果一行记录被删掉了,因为有这个间隙被多个事务持有的多个间隙锁包含,必须要合并,以前的几个间隙不存在了,(官网没有例子无法理解)。

间隙锁只阻止其他事务想这个间隙插入操作。不阻止不同事务获得这个相同间隙的间隙锁。因此,间隙共享锁和间隙排它锁作用一样。

Gap间隙锁可以被闲式禁用。如果把隔离级别切换成READCOMMITTED或者enable innodblocksunsafefor_binlog 过时的系统变量,在这个环境下间隙锁在search和index scan的时候不可用,只有在外键约束检查和重复key检查可用。

这里还有其他影响(涉及隔离级别,可以稍后再看),在禁用间隙锁的环境下,Record lock锁,会在不匹配的行上释放锁在评估了where条件之后。对于Update语句,InnoDB做的是个“semi-consistent” read“半一致性”读,InnoDB回最新的数据给mysql,mysql可以确定这一行是否符合where条件(不知道InnoDB跟Mysql什么关系,又低了一层)。

Next-Key Locks

Next-Key Locks 锁是一个一行记录上的record lock和这行之前的gap锁。

InnoDB是怎样执行行级别的锁的,当InnoDB搜索一个表的索引,InnoDB会在符合条件的行记录上加锁共享锁,或者排他锁。因此,这个行级别的锁实际上是行记录锁。

InnoDB以什么方式执行行级别锁,在搜到的多行记录上设置上共享锁或排他锁,以这种方式执行行级别锁。因此。行级别的锁实际上是index-record lock(Record Lock) 锁。另外,一个next-key锁 在一行记录上,也会影响这一行和他前边一行的间隙。实际上,next-key是一个index-record锁加上一个gap前间隙锁。如果一个session有一个共享锁或者排它锁在记录R上,其他的session是不能插入新的记录的,在这个索引序列中R之前。

假设一个索引包含这几个值10,11,13,20.

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对于最后一个间隔,next-key锁锁在这个超过索引最大值的间隙上,这个next-key锁可能包括以下几个间隙“间隔”,“supremum”无穷大是个伪记录,这个伪记录有个比任何索引值都大的值,这不是个真实的记录,So,实际上这个next-key锁只锁在了这个最大的20上。

默认情况下,InnoDB运行在REPEATABLE READ的隔离级别。在这种情况下,InnoDB在search和index scans上使用next-key锁,为了避免幻读问题。

官网就上述示例给出的InnoDB monitor日志

Transaction data for a next-key lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`  
trx id 10080 lock_mode X  
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0  
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0  
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

后边还有两个重要性稍微小一点锁,自己去看吧。Insert Intention Locks,AUTO-INC Locks InnoDB Locking


Phantom Rows 幻读

幻读出现在一个事务执行相同的查询语句,但是结果不同。例如,一个SELECT被执行两次,第二次返回一行,第一次返回一行。这就是幻读现象。

假设child表上id字段有索引,你想要读并且锁住id值大于100的所有行,并且你有想要修改其中几个自读的值的意图在查询后。

SELECT * FROM child WHERE id > 100 FOR UPDATE;

这条语句,从第一行大于100的记录开始扫描索引,假设,这个表包含id值为90和102的,如果这个锁加在了搜出的记录上,没有锁住插入模式在间隙上,这里的间隙是90-102,其他的session就可以插入一行新的101.如果你将要执行SELECT在这个相同的事务里边,你将会看到一条新的101,这就是幻读。如果我们把这返回的许多行看做一条(一个结果,一个date item),这个新的幻象101就违反了事务的隔离原则。隔离原则是,一个事务应该能够运行在,它所读过的数据在事务进行中不被改变的情况下。

为了避免幻读的问题,InnoDB使用next-key锁,next-key锁是 index-row锁和gap间隙锁。InnoDB以什么方式执行行级别锁,在搜到的多行记录上设置上共享锁或排他锁,以这种方式执行行级别锁。因此。行级别的锁实际上是index-record lock(Record Lock) 锁。另外,一个next-key锁 在一行记录上,也会影响这一行和他前边一行的间隙。实际上,next-key是一个index-record锁加上一个gap前间隙锁。如果一个session有一个共享锁或者排它锁在记录R上,其他的session是不能插入新的记录的,在这个索引序列中R之前。

当InnoDB扫描一个索引(scans a index),也可能给,搜索出的最后一行加gap后间隙锁。就像之前的例子一样,为了避免其他事务插入任何大于100的数据,InnoDB把这个gap后间隙锁加在了102上。

你可以使用next-key锁去在自己的应用中实现一个“唯一性检查”,如果在共享模式下读数据,没有看到一行跟你正要插入的一样的数据,然后你可以安全的insert了,并且你知道这个next-key在之前的read中就已经设置好了,阻止其他sessoin的插入一个跟你相同的数据。因此,next-key锁使你能够锁住something不存在的在当前表中。


Mysql InnoDB 支持的隔离级别 按常用程度排序

  • REPEATABLE READ
  • READ COMMITTED
  • READ UNCOMMITTED
  • SERIALIZABLE

下文多次提到一个名词(动词)“一致读”“连贯读”consistent read 解释一下

consistent read

consistent read是一个使用快照的读操作,使用快照为避免查询结果基于当前时间点,不管在这个时间内其他事务怎么修改数据。如果查询的数据已经被其他事务改变了,原始数据会被基于undo log重新构造.这个技术是为避免一些锁的问题,这会降低并发性能,并发是怎样被降低的,是强制其他事务等待持有锁的事务结束,降低了并发。

当使用 REPEATABLE READ 隔离级别时,这快照是基于第一次读操作的时间。当使用READ COMMITTED隔离级别,这个快照每次“一致读”都会被重置。

“一致读”是InnoDB处理SELECT语句的默认模式,在 READ COMMITTED和REPEATABLE READ隔离级别中。因为一个“一致读”不会设置任何锁,其他session是可以任意修改过当前正在“一致读“的数据。

详解
意思相同Consistent Nonlocking Reads

consistent read 意思是InnoDB使用了多版本为在一个时间点呈现一个查询快照。这个查询可以看到这个时间点之前这个数据被事务提交的变化。并且在这个时间点后没有改变也没有暂未提交的事务。对于这个规则,这里有个例外,这个查询可以看到当前事务对该数据的修改,当前事务中,前边的语句产生的修改。这个例外会造成异常,如果你在更新一个表里的几行,一个SELECT语句看到这几行最新的版本,但是还可能看到所有行的老版本。如果这时,其他session同时update相同的表,这个异常意味着你可能看到了这个表在实际数据库中从没存在过的状态。

如果这个事务的隔离级别是REPEATABLE_READ默认级别,事务中的所有consistent read读取的是第一次查询建立的快照。你可以获取一个更新的快照,通过commit之后再查一次。

当使用READ COMMITTED隔离级别,这个快照在每次“一致读”都会被重置。

“一致读”是InnoDB处理SELECT语句的默认模式,在 READ COMMITTED和REPEATABLE READ隔离级别中。因为一个“一致读”不会设置任何锁,其他session是可以任意修改过当前正在“一致读“的数据。

假设你正在运行默认的隔离级别REPEATABLE READ.当你发起一个consistent read时,InnoDB给你的事务一个时间点,关于你的查询看到数据库的时间点。如果另外一个事务删除了一行,并且提交了,在你的时间点被分配之后,你没有看到这一行已经被删除。insert和update仍然被同样的对待。

注意

在这个事务中,这个数据库状态的快照应用在SELECT语句中,不一定应用于DML数据库修改语言,(update delete insert ,for update,..).如果你插入或者修改了几行,并且提交了,一个DELETE或者UPATE语句被另一个并发REPEATABLE READ事务发起的,可能会影响你刚刚提交的几行。即使这个session可能没有查询他们。如果一个事务确实更新或者删除了几行被其他事务提交的,这些改变会变得可见对于当前事务。例如,你可能会遇到下面这种情况:

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';  
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';  
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';  
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';  
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';  
-- Returns 10: this txn can now see the rows it just updated.

你可以使你的时间点前进通过提交事务,然后再查询一次。

这叫做 多版本 并发控制。

在下边的示例中,session A 看到了 session B插入的行当B提交之后,A也提交,所以时间点经过B提交被推动了。

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time  
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;  
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

如果你想要看最新状态的数据库,使用READ COMMITTED隔离级别,或者用locking read.

SELECT * FROM t LOCK IN SHARE MODE;  

dirty read 脏读(consistent read对立的)

一个读操作获取了不可靠的数据,这个数据已经被其他事务修改但是还没有提交,脏读问题只可能出现在read uncommitted隔离级别。

这种类型的操作不遵循ACID原则,这被认为非常有风险的,因为这数据有可能被回滚,或者在持有事务提交之前被再一次更新。事务允许脏读应该是使用的数据要求不是太精确的情况下。

脏读对立的是一致读consistent read,InnoDB确保consistent read在一个事务不会读取一个被其他事务修改的信息,即使另一个事务同时提交。

REPEATABLE READ

InnoDB默认级别,一个事务中多个相同的SELECT语句一致读操作,第一次执行建立一份返回结果的快照,然后后边的几次执行都是直接返回快照数据。这种常见的标准的SELECT语句读称之为“Consistent Nonlocking Reads”无锁读操作。

对应的有锁读Locking Reads,可能不太常见“SELECT * FROM user FOR UPDARE”,"SELECT * FROM user LOCK IN SHARE MODE"。

对于有锁读操作,还有UPDATE和DELETE,三种操作,的锁,locking 取决于这条语句的where条件使用的是带索引的唯一值比如 user 表 id,带有索引,且条件唯一,where id=10,还是一个泛型的查找比如like,或者where username="username" username 不唯一且不带索引,where id <10,不唯一。两类,下边详细阐述。

  • 第一种情况,带索引,条件唯一, InnoDB只在搜索的这一条上加锁。
  • 第二种情况,InnoDB会锁住一个范围的多条数据。InnoDB 锁住扫描出的多行,使用gap locks 或者 next-key locks 来阻止其他session对保护间隙的插入。

READ COMMITTED

每一个”一致读“,即使在相同的事务中,都会设置和读取它自己的最新快照(都会查询最新的数据,并更新自己的快照)。

对于locking read(SELECT FOR UPDATE or LOCK IN SHARE MODE),UPDATE,DELETE,InnoDB只锁index records,不锁gap之前的间隙,并且因此允许自由插入新纪录 挨着锁的记录。间隙锁只被用在外键约束检查,重复key检查。

由于间隙锁被禁用了,有可能出现幻读,也就是其他session可以在间隙插入新的行。

如果你使用READ COMMITTED隔离级别,必须使用row-based binary logging.

使用READ COMMITTED有附加作用:

  • 对于UPDATE或者DELETE语句,InnoDB只持有行级别锁for updates or deleltes. Record locks对于没有符合的行就释放了,在MySQL评估WHERE条件语句之后。这很大程度上减少了死锁,但是仍会发生。

  • 对于UPDATE语句,如果一行已经被锁了,InnoDB执行一个半一致读操作a “semi-consistent” read,返回最新的提交版本给MySQL,以便让MySQL可以决定是否这一行符合UPDATE的WHERE条件语句。如果这一行符合(必须是被改过的),MySQL再读这一行,这个半一致读的时间内,InnoDB要么锁上它,要么等待锁。

下边是个例子,

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;  
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);  
COMMIT;  

Suppose that one client performs an UPDATE using these statements:

SET autocommit = 0;  
UPDATE t SET b = 5 WHERE b = 3;  

Suppose also that a second client performs an UPDATE by executing these statements following those of the first client:

SET autocommit = 0;  
UPDATE t SET b = 4 WHERE b = 2;  

InnoDB执行每一个UPDATE语句,首先,请求一个排它锁,之后决定是不是要修改,如果,InnoDB不修改这一行,释放锁。否则,InnoDB持有锁知道事务结束。

When using the default REPEATABLE READ isolation level, the first UPDATE acquires x-locks and does not release any of them:

x-lock(1,2); retain x-lock  
x-lock(2,3); update(2,3) to (2,5); retain x-lock  
x-lock(3,2); retain x-lock  
x-lock(4,3); update(4,3) to (4,5); retain x-lock  
x-lock(5,2); retain x-lock  

The second UPDATE blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back:

x-lock(1,2); block and wait for first UPDATE to commit or roll back  

If READ COMMITTED is used instead, the first UPDATE acquires x-locks and releases those for rows that it does not modify:

If READ COMMITTED is used instead, the first UPDATE acquires x-locks and releases those for rows that it does not modify:  

For the second UPDATE, InnoDB does a “semi-consistent” read, returning the latest committed version of each row to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE:

x-lock(1,2); update(1,2) to (1,4); retain x-lock  
x-lock(2,3); unlock(2,3)  
x-lock(3,2); update(3,2) to (3,4); retain x-lock  
x-lock(4,3); unlock(4,3)  
x-lock(5,2); update(5,2) to (5,4); retain x-lock  

READ UNCOMMITTED

SELECT语句被以nonlocking的方式执行,但是一个先前的版本可能被使用。因此,这种隔离级别下的读不是一致的。这也叫做dirty read.其他方面,这个隔离级别像READ COMMITTED

SERIALIZABLE

这个隔离级别像REPEATABLE READ,但是InnoDB隐式的转化SELECT语句成SELECT...LOCK IN SHARE MODE,在autocommit是禁用的情况下。如果autocommit是开启的,SELECT语句就是自己的事务。因此,它被人以read only所知道并且可以被序列化如果执行作为一个consistent nonlocking read 并且不需要其他transaction块。(为强制使用SELECT和transcation 块,如果其他事务已经修改了选中的行,禁用autocommit)

总结一下

脏读

  • 问题,一个读操作获取了不可靠的数据,这个数据已经被其他事务修改但是还没有提交,脏读问题只可能出现在read uncommitted隔离级别。因为这数据有可能被回滚,或者在持有事务提交之前被再一次更新。脏读是跟一致读是对立的,就是不一致读。
  • 产生原因,read uncommitted不使用consistent read 模式,consistent read用损失并发性能,的快照机制解决了脏读问题。
  • 解决方案,repetable read 和 read committed隔离级别默认采用consistent read模式,快照机制解决脏读问题。

不可重复读

  • 问题,两次查询同一个数据,两次的查询结果不一致。
  • 原因,consistent read是InnoDB处理SELECT语句的默认模式,在 READ COMMITTED和REPEATABLE READ隔离级别中。因为一个consistent read不会设置任何锁,其他session是可以任意修改过当前正在“一致读“的数据。因为read committed为了实现随时读取最新的数据,所以快照机制跟repetable read不同,每次从数据库刷新快照。
  • 解决方案,repetable read多次相同查询同一个数据,使用第一次查询快照。

幻读

  • 问题,相同的查询语句,两次查询结果集不同。返回条目不同。
  • 原因,查询期间没有gap间隙锁,没能防止其他事务在同一范围插入条目。read committed有可能出现这个问题,因为read committed默认禁用了gap lock.
  • 解决方案,使用repetable read,对待非唯一index查询,repetable read默认使用next-key锁,等于间隙锁加Record锁,应用上间隙锁ok。可能最上边的图片不对吧,官网都是以此介绍repetable read的。

乱乱哄哄,大概好了。


InnoDB Locking

Phantom Rows

Related Article