Mysql rr和rc隔离

时间:2021-04-14 23:03:40
 REPEATABLE READ

This is the default isolation level for InnoDB. For consistent reads, there is an important 

difference from the READ COMMITTED isolation level: All consistent reads within the same 

transaction read the snapshot established by the first read. This convention means that if you 

issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT 

statements are consistent also with respect to each other. See Section 14.3.2.3, “Consistent 

Nonlocking Reads”.

这是默认的隔离级别是InnoDB,对于一致性读,这里有一个重要的区别和READ COMMITTED isolation level

所有的一致性读在相同的事务里读取第一次读创建的快照。

这意味着如果你执行多个普通的(非锁定读) 

SELECT 语句在相同的实例,SELECT 语句是一致性读。

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE 

statements, locking depends on whether the statement uses a unique index with a unique search 

condition, or a range-type search condition. For a unique index with a unique search condition, 

InnoDB locks only the index record found, not the gap before it. For other search conditions, 

InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by 

other sessions into the gaps covered by the range.

对于lockding reads(SELECT  FOR UPDATE or LOCK IN SHARE MODE),

UPDATE, and DELETE语句, lockding 依赖是否语句使用一个唯一索引进行一个唯一搜索条件,

或者一个range-type 搜索条件,  对于一个唯一索引 使用一个唯一搜索条件,

InnoDB 只锁 找到的index record,不是 gap before it.

对于其他的搜索条件,InnoDB locks 定索引范围扫描,使用gap locks 或者next-key locks 

来堵塞 其他会话插入到这个区间

READ COMMITTED

一个Oracle 类似的隔离级别一致的非锁定读,

每个一致读, 甚至在相同的会话,设置和读取它自己最新鲜的快照

对于锁定读(SELECT with FOR UPDATE or LOCK IN SHARE MODE),

UPDATE statements, and DELETE statements

InnoDB 只锁定 index records,不是 gaps before them,

因此允许新记录*插入到紧挨着锁定的记录