>

MySQL事务隔断品级,中隔开等级

- 编辑:www.bifa688.com -

MySQL事务隔断品级,中隔开等级

参考自:https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html

1. 数据库事务ACID特性

原文地址Transaction Isolation Levels

第一部分:概述

数据库事务的4个特性:
原子性(Atomic):
事务中的多个操作,不可分割,要么都成功,要么都失败; All or Nothing.
一致性(Consistency): 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后,总金额不变;
隔离性(Isolation): 多个事务之间就像是串行执行一样,不相互影响;
持久性(Durability): 事务提交后被持久化到永久存储.

事务隔离级别

事务隔离是数据库基础能力之一。隔离(Isolation)表示ACID中的I;隔离级别是一个配置项,它可以用于调整当多个事务同时进行更新和查询操作时,MySQL的性能和可靠性、一致性、结果的可重复性之间的平衡。

InnoDB提供了四种隔离级别,SQL:1992标准中描述了这个四个级别:未提交读(READ_UNCOMMITTED),提交读(READ_COMMITTED),可重复读(REPEATABLE_READ),串行化(SERIALIZABLE)。InnoDB的默认级别是可重复读(REPEATABLE_READ)。

用户可以使用SET_TRANSACTION语句来更改当前会话中的隔离级别。如果要更改全局的隔离级别,对所有会话生效,在命令行或者配置文件中使用--transaction-isolation选项。更多隔离级别的设置语法,可以查看SET TRANSACTION Syntax。

InnoDB使用不同的锁机制来实现上述不同的隔离级别。对于需要保证ACID重要数据上的操作,可以使用默认的REPEATABLE_READ实现高级别的一致性。对于精确的一致性和结果可重复性要求不那么高的情形,比如大量数据的操作,减少锁的数量更加重要,这种情况下可以使用READ_COMMITTED甚至READ_UNCOMMITTED来降低一致性。SERIALIZABLE实现了比REPEATED_READ更严格的规则,它主要用于一些特殊的场合,比如XA事务,以及解决并发和死锁相关问题。

以下描述了MySQL如何支持不同的隔离级别。顺序按照最常用到最不常用的。

  • REPEATED READ

    这是InnoDB的默认隔离级别,同一个事务中的连续读(Consistent reads)会读到第一个读操作建立的快照(snapshot)。这也就是说如果在同一个事务中进行简单的多次SELECT操作,这些操作相互是一致的。更多可以查看一致无锁读(Consistent Nonlocking Reads)。

    对于加锁读(SELECT with FOR UPDATE or LOCK IN SHARE MODE)、UPDATE、DELETE等操作,锁取决于操作语句是否使用了唯一索引并且使用唯一检索条件,还是使用了范围检索条件。

    • 对于使用唯一索引并且使用唯一检索条件的情形,InnoDB只会给指定的这行记录索引加锁,不会在前面的间隙上枷锁。
    • 对于使用范围检索条件的情形,InnoDB会给扫描到的索引范围都加上锁,使用间隙锁和next-key锁来阻止其他会话往这个范围内插入数据。有关间隙锁和next-key锁,可以查看InnoDB Locking。
  • READ COMMITTED

    这个级别下,在同一个事务中的连续读操作,每一个读都会设置自己独有的最新快照并从中读取数据。有关连续读操作,查看Consistent Nonlocking Reads。

    对于加锁读(SELECT with FOR UPDATE or LOCK IN SHARE MODE)、UPDATE、DELETE等操作,InnoDB只会给索引记录加锁,而不会给记录前面的间隙加锁,这样就允许了其他事务往这些记录间插入数据。间隙锁只会用来做外键约束检查以及重复key检查。

    由于没有使用间隙锁,会出现幻读问题,因为其他会话可能往间隙中插入了数据。有关幻读,查看Phantom Rows。

    如果你使用了READ COMMITTED级别,你必须同时使用行级别的二进制日志。

    使用READ COMMITTED级别还有其他的影响:

    • 对于UPDATE和DELETE语句,InnoDB会锁住将要更新或者删除的行。MySQL计算WHERE语句后,就会把不匹配的行上的锁释放掉。这个大大降低了死锁的概率,但是仍然有可能会发生。
    • 对于UPDATE操作,如果记录已经被锁住,InnoDB会做"半一致性(semi-consistent)"读,它返回最近一次提交版本的记录给MySQL,然后MySQL会决定这个记录是否和UPDATE语句的WHERE条件匹配。如果记录匹配(需要被更新),MySQL会重新读取这行记录,并且不会加锁也不等待锁。

    考虑下面这个例子,先建立一个表:

    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;
    

    在这种情况下,表没有索引,因此查找和索引遍历操作会使用隐藏的聚合索引来给记录加锁(查看Clustered and Secondary Indexes)。
    假设此时一个用户使用如下语句进行UPDATE操作:

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

    紧接着,另外一个用户使用如下语句进行UPDATE操作:

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

    InnoDB执行这些UPDATE时,会先对每一行都加上排它锁,然后决定是否更改它们。如果InnoDB没有修改这些行,那么会立即立即释放锁;否则,InnoDB会持有锁直到事务结束。这样的操作对事务处理的影响如下:

    当使用默认的REPEATABLE READ级别时,如果第一个UPDATE拿到了排它锁,并且没有释放任何一个:

    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
    

    那么,第二个UPDATE在试图获取任意行(因为第一个update锁住了所有的行)的锁时都会阻塞,知道第一个UPDATE操作提交或者回滚:

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

    当使用READ COMMITTED级别时,第一个UPDATE操作获取排他锁,并且立即释放它不修改的行的锁:

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

    对于第二个UPDATE,InnoDB会做"半一致性(semi-consistent)"读,它返回最近一次提交版本的记录给MySQL,然后MySQL会决定这个记录是否和UPDATE语句的WHERE条件匹配:

    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 COMMITTED隔离级别的效果和被废弃的innodb_locks_unsafe_for_binlog配置项是一样的,除了以下两点:

    • 设置innodb_locks_unsafe_for_binlog是一个全局的配置,会影响所有的会话。但隔离级别可以选择设置为对所有会话有效或者对单个会话有效。
    • innodb_locks_unsafe_for_binlog只能在服务启动时的时候设置。但隔离级别可以在启动时和运行时设置。
  • READ UNCOMMITTED

    SELECT操作使用不加锁的方式,不过有可能会返回较早的版本数据。因此使用这种级别时,读取是不一致的。这个也叫做脏读。除此之外,这个级别和READ COMMITTED级别一样。

  • SERIALIZABLE

    这个级别和REPEATED READ级别类似,不同地方在于,如果autocommit没有设置,InnoDB会隐世的把SELECT转换为SELECT ... LOCK IN SHARE MODE。如果autocommit设置了,SELECT就是一个事务。It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions.(不会翻译-_-||)(如果想要在有其他事务修改数据时阻塞住当前[SELECT]操作,不要设置autocommit)

MySQL遵循SQL:1992标准,提供READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ和SERIALIZABLE四种事务隔离级别。InnoDB默认使用的事务隔离级别是REPEATABLE READ。

2. 隔离性

用户可以自己修改会话或全局级别的事务隔离级别,语法如下:

其中 隔离性 分为了四种:

SET [GLOBAL | SESSION] TRANSACTION
transaction_characteristic [, transaction_characteristic] ...
transaction_characteristic:
ISOLATION LEVEL level
| READ WRITE
| READ ONLY
level:
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE

READ UNCOMMITTED:可以读取未提交的数据,未提交的数据称为脏数据,所以又称脏读。此时:幻读,不可重复读和脏读均允许;
READ COMMITTED:只能读取已经提交的数据;此时:允许幻读和不可重复读,但不允许脏读,所以RC隔离级别要求解决脏读;
REPEATABLE READ:同一个事务中多次执行同一个select,读取到的数据没有发生改变;此时:允许幻读,但不允许不可重复读和脏读,所以RR隔离级别要求解决不可重复读;
SERIALIZABLE: 幻读,不可重复读和脏读都不允许,所以serializable要求解决幻读;

你也可以在启动时添加--transaction-isolation启动项或者将其写入配置文件,来设置相应的全局事务隔离级别。

3. 几个概念

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ和SERIALIZABLE这四种事务隔离级别所提供的事务一致性是越来越强的,但是并发性却是却来越差的。

脏读:可以读取未提交的数据。RC 要求解决脏读;

 

不可重复读:同一个事务中多次执行同一个select, 读取到的数据发生了改变(被其它事务update并且提交);

第二部分:事务隔离级别

可重复读:同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现);RR各级级别要求达到可重复读的标准;

提到事务隔离级别就必须先明确以下三种读:

幻读:同一个事务中多次执行同一个select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete/insert并且提交)。SERIALIZABLE要求解决幻读问题;

脏读:读到了其他事务已修改但未提交的数据
不可重复读:由于其他事务的修改,导致同一事务中两次查询读到的数据不同
幻读:由于其他事务的修改,导致同一事务中两次查询读到的记录数不同

这里一定要区分 不可重复读 和 幻读:

1.READ UNCOMMITTED

不可重复读的重点是修改:
同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了

这种隔离级别下select语句是不加事务锁的,因此会产生脏读,这种事务隔离级别是应当完全避免的。除select语句以外的其他语句加锁模式与READ COMMITTED一样。

幻读的重点在于新增或者删除:
同样的条件的select, 第1次和第2次读出来的记录数不一样

2.READ COMMITTED

从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大:
对于前者, 在RC下只需要锁住满足条件的记录,就可以避免被其它事务修改,也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读;
对于后者, 要锁住满足条件的记录及所有这些记录之间的gap,也就是需要 gap lock。

同REPEATABLE READ一样,这种隔离级别下也实现了一致性非锁定读,但区别在于此隔离级别下的一致性读是语句级的,即只能避免脏读,不能避免不可重复读和幻读。其实现方式大致是:

而ANSI SQL标准没有从隔离程度进行定义,而是定义了事务的隔离级别,同时定义了不同事务隔离级别解决的三大并发问题:

  • select语句检测要锁定的索引记录上是否有独占锁。
  • 如果有独占锁那么到undo中寻找最近的前镜像。
  • 如果没有独占锁那么添加S模式的record lock。

Isolation Level

Dirty Read

Unrepeatable Read

Phantom Read

Read UNCOMMITTED

YES

YES

YES

READ COMMITTED

NO

YES

YES

READ REPEATABLE

NO

NO

YES

SERIALIZABLE

NO

NO

NO

在这种隔离级别下,InnoDB只使用record lock类型的行锁,不使用gap锁。

参见:你真的明白事务的隔离性吗? (姜承尧)

此外:如果你使用READ COMMITTED事物隔离级别,那么binlog模式必须修改为row模式!

4. 数据库的默认隔离级别

关于具体的MVCC实现方式,MySQL官网并未提供具体的实现步骤,可以选择去查看源码,也可以参考Oracle和SQL Server的实现机制。

除了MySQL默认采用RR隔离级别之外,其它几大数据库都是采用RC隔离级别。

3.REPEATABLE READ

但是他们的实现也是极其不一样的。Oracle仅仅实现了RC 和 SERIALIZABLE隔离级别。默认采用RC隔离级别,解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。

这是MySQL的默认事务隔离级别。在一个事务当中第一次读会建立一个snapshot,同事务的相同select语句会读取这个snapshot来实现一致性非锁定读。

MySQL的实现:MySQL默认采用RR隔离级别,SQL标准是要求RR解决不可重复读的问题,但是因为MySQL采用了gap lock,所以实际上MySQL的RR隔离级别也解决了幻读的问题。那么MySQL的SERIALIZABLE是怎么回事呢?其实MySQL的SERIALIZABLE采用了经典的实现方式,对读和写都加锁。

这种隔离级别下可以避免脏读、不可重复读和幻读。

5. MySQL 中RC和RR隔离级别的区别

对于select for update/select lock in share mode/update/delete这些锁定读,加行锁模式取决于索引的类型:

MySQL数据库中默认隔离级别为RR,但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢?我们该如何选择呢?为什么MySQL将RR作为默认的隔离级别呢?

  • 对唯一索引的访问只会添加record lock,而不会使用gap lock(即也没有next-key lock)。
  • 对非唯一索引的访问使用gap lock或者next-key lock,如果访问的记录不存在就是gap lock,否则就是next-key lock。

5.1 RC 与 RR 在锁方面的区别

4.SERIALIZABLE

1> 显然 RR 支持 gap lock(next-key lock),而RC则没有gap lock。因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR;

这种事务隔离级下select语句即便不加lock in share mode也使用lock_mode=S的行锁,select自成事务,锁直到事务结束才释放。

2> RC 隔离级别,通过 where 条件过滤之后,不符合条件的记录上的行锁,会释放掉(虽然这里破坏了“两阶段加锁原则”);但是RR隔离级别,即使不符合where条件的记录,也不会是否行锁和gap lock;所以从锁方面来看,RC的并发应该要好于RR;另外 insert into t select ... from s where 语句在s表上的锁也是不一样的,参见下面的例子2;

这种隔离级别下可以避免脏读、不可重复读和幻读。

例子1:

DML语句的加锁模式与REPEATABLE READ一样。

下面是来自 itpub 的一个例子:

官网对于这个隔离级别的解释是只有将autocommit设置为0后select才会被隐式转换为lock in share mode的加锁模式,但是经测验发现在此模式下只要为select语句开启事务就会阻塞其他事物的更改,因此官网解释有误。

MySQL5.6, 隔离级别RR,autocommit=off;

 

表结构:

第三部分:总结

mysql> show create table t1G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  `e` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_t1_bcd` (`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

一般来说我们没必要去修改默认的事务隔离级别,当然如果你的数据库并不在意幻读和不可重复读,可以修改未read committed隔离级别,这样可以增加并发减少阻塞,据说淘宝也是这么干的。Oracle默认的事务隔离级别也是read committed,同样不可避免幻读和不可重复读。

表数据:

关于MySQL的锁机制,可以参考:

mysql> select * from t1;
 --- --- --- --- ------ 
| a | b | c | d | e    |
 --- --- --- --- ------ 
| 1 | 1 | 1 | 1 | a    |
| 2 | 2 | 2 | 2 | b    |
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 5 | 2 | 3 | 5 | e    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
| 8 | 8 | 8 | 8 | h    |
 --- --- --- --- ------ 
8 rows in set (0.00 sec)

操作过程:
session 1:

delete from t1 where b>2 and b<5 and c=2;

执行计划如下:

mysql> explain select * from t1 where b>2 and b<5 and c=2G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: idx_t1_bcd
          key: idx_t1_bcd
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using index condition
1 row in set (0.00 sec)

session 2:

delete from t1 where a=4

结果 session 2 被锁住。
session 3:

mysql> select * from information_schema.innodb_locks;
 --------------- ------------- ----------- ----------- ------------- ------------ ------------ ----------- ---------- ----------- 
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
 --------------- ------------- ----------- ----------- ------------- ------------ ------------ ----------- ---------- ----------- 
| 38777:390:3:5 | 38777       | X         | RECORD    | `test`.`t1` | PRIMARY    |        390 |         3 |        5 | 4         |
| 38771:390:3:5 | 38771       | X         | RECORD    | `test`.`t1` | PRIMARY    |        390 |         3 |        5 | 4         |
 --------------- ------------- ----------- ----------- ------------- ------------ ------------ ----------- ---------- ----------- 

根据锁及ICP的知识,此时加锁的情况应该是在索引  idx_t1_bcd 上的b>2 and b<5之间加gap lock, idx_t1_bcd 上的c=2 加 X锁主键 a=3 加 x 锁。
应该a=4上是没有加X锁的,可以进行删除与更改。
但是从session3上的结果来,此时a=4上被加上了X锁。
求大牛解惑,谢谢。


要理解这里为什么 a=4 被锁住了,需要理解 gap lock,锁处理 RR 隔离级别和RC隔离级别的区别等等。

这里的原因如下:

很简单,我们注意到:key_len: 4 和 Extra: Using index condition
这说明了,仅仅使用了索引 idx_t1_bcd 中的 b 一列,没有使用到 c 这一列。c 这一列是在ICP时进行过滤的。所以:

delete from t1 where b>2 and b<5 and c=2 其实锁定的行有:

mysql> select * from t1 where b>2 and b<=6;
 --- --- --- --- ------ 
| a | b | c | d | e    |
 --- --- --- --- ------ 
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
 --- --- --- --- ------ 
4 rows in set (0.00 sec)

所以显然 delete from t1 where a=4 就被阻塞了。那么为什么 delete from t1 where a=6 也会被阻塞呢???

这里 b<=6 的原因是,b 列中没有等于 5 的记录,所以 and b<5 实现为锁定 b<=6 的所有索引记录,这里有等于号的原因是,如果我们不锁定 =6 的索引记录,那么怎么实现锁定 <5 的gap 呢?也就是说锁定 b=6 的索引记录,是为了实现锁定 b< 5 的gap。也就是不能删除 b=6 记录的原因
而这里 b >2 没有加等于号(b>=2) 的原因,是因为 b>2的这个gap 是由 b=3这个索引记录(的gap)来实现的,不是由 b=2索引记录(的gap) 来实现的,b=2的索引记录的gap lock只能实现锁定<2的gap,b>2的gap锁定功能,需要由 b=3的索引记录对应的gap来实现(b>2,b<3的gap)。
所以我们在session2中可以删除:a=1,2,5,8的记录,但是不能删除 a=6(因为该行的b=6)的记录。

如果我们使用 RC 隔离级别时,则不会发生阻塞,其原因就是:

RC和RR隔离级别中的锁处理不一样,RC隔离级别时,在使用c列进行ICP where条件过滤时,对于不符合条件的记录,锁会释放掉,而RR隔离级别时,即使不符合条件的记录,锁也不会释放(虽然违反了“2阶段锁”原则)。所以RC隔离级别时session 2不会被阻塞。

Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

例子2:**insert into t select ... from s where 在RC 和 RR隔离级别下的加锁过程**

下面是官方文档中的说明:

INSERT INTO T SELECT ... FROM S WHERE ...88bifa必发唯一官网, sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.

insert inot t select ... from s where ... 语句和 create table ... select ... from s where 加锁过程是相似的(RC 和 RR 加锁不一样)

1> RC 隔离级别时和 RR隔离级别但是设置innodb_locks_unsafe_for_binlog=1 时,select ... from s where 对 s 表进行的是一致性读,所以是无需加锁的;

2> 如果是RR隔离级别(默认innodb_locks_unsafe_for_binlog=0),或者是 serializable隔离级别,那么对 s 表上的每一行都要加上 shared next-key lock.

这个区别是一个很大的不同,下面是生成中的一个 insert into t select ... from s where 导致的系统宕机的案例:

一程序猿执行一个分表操作:

insert into tb_async_src_acct_201508 select * from tb_async_src_acct 

where src_status=3 and create_time>='2015-08-01 00:00:00' and create_time <= '2015-08-31 23:59:59';

表 tb_async_src_acct有4000W数据。分表的目的是想提升下性能。结果一执行该语句,该条SQL被卡住,然后所有向 tb_async_src_acct的写操作,要么是 get lock fail, 要么是 lost connection,全部卡住,然后主库就宕机了

显然这里的原因,就是不知道默认RR隔离级别中 insert into t select ... from s where 语句的在 s 表上的加锁过程,该语句一执行,所有符合 where 条件的 s 表中的行记录都会加上 shared next-key lock(如果没有使用到索引,还会锁住表中所有行),在整个事务过程中一直持有,因为表 tb_async_src_acct 数据很多,所以运行过程是很长的,所以加锁过程也是很长,所以其它所有的对 tb_async_src_acct 的insert, delete, update, DDL 都会被阻塞掉,这样被阻塞的事务就越来越多,而事务也会申请其它的表中的行锁,结果就是系统中被卡住的事务越来越多,系统自然就宕机了。

5.2 RC 与 RR 在复制方面的区别

1> RC 隔离级别不支持 statement 格式的bin log,因为该格式的复制,会导致主从数据的不一致;只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。复制时,我们最好使用:binlog_format=row

具体参见:

2> MySQL5.6 的早期版本,RC隔离级别是可以设置成使用statement格式的bin log,后期版本则会直接报错;

5.3 RC 与 RR 在一致性读方面的区别

简单而且,RC隔离级别时,事务中的每一条select语句会读取到他自己执行时已经提交了的记录,也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时,事务中的一致性读的ReadView是以第一条select语句的运行时,作为本事务的一致性读snapshot的建立时间点的。只能读取该时间点之前已经提交的数据。

具体可以参加:MySQL 一致性读 深入研究

5.4 RC 支持半一致性读,RR不支持

RC隔离级别下的update语句,使用的是半一致性读(semi consistent);而RR隔离级别的update语句使用的是当前读;当前读会发生锁的阻塞。

1> 半一致性读:

A type of read operation used for UPDATE** statements, that is a combination of read committed and consistent read. When an UPDATE statement examines a row that is already locked, InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again, and this time InnoDBeither locks it or waits for a lock on it**. This type of read operation can only happen when the transaction has the read committed isolation level, or when the innodb_locks_unsafe_for_binlog option is enabled.

简单来说,semi-consistent read是read committed与consistent read两者的结合。一个update语句,如果读到一行已经加锁的记录,此时InnoDB返回记录最近提交的版本,由MySQL上层判断此版本是否满足 update的where条件。若满足(需要更新),则MySQL会重新发起一次读操作,此时会读取行的最新版本(并加锁)。semi-consistent read只会发生在read committed隔离级别下,或者是参数innodb_locks_unsafe_for_binlog被设置为true(该参数即将被废弃)。

对比RR隔离级别,update语句会使用当前读,如果一行被锁定了,那么此时会被阻塞,发生锁等待。而不会读取最新的提交版本,然后来判断是否符合where条件。

半一致性读的优点:

减少了update语句时行锁的冲突;对于不满足update更新条件的记录,可以提前放锁,减少并发冲突的概率。

具体可以参见:

Oracle中的update好像有“重启动”的概念。

 

本文由88bifa必发唯一官网发布,转载请注明来源:MySQL事务隔断品级,中隔开等级