鉴于出现频率，下文中常见术语释义

锁定读：是指SELECT(带有 FOR UPDATE 或FOR SHARE) 、UPDATE 和 DELETE 这三种语句的数据读取

InnoDB 提供 SQL:1992 标准描述的所有四个事务隔离级别

  • READ UNCOMMITTED
  • READ COMMITTED（其他主流DB的默认级别，如Oracle）
  • REPEATABLE READ（默认）
  • SERIALIZABLE

按照使用频率顺序，逐个介绍各个级别实现细节，重点关注RR与RC！！！

1. RR（默认，可重复读）

REPEATABLE READ，这是 InnoDB 的默认隔离级别。同一事务中的多个一致性读取，将会读取到首次读取时建立的快照。也就是说，如果在同一事务中发出多个普通（非锁定）SELECT 语句，这些 SELECT 语句也相互一致。

对于锁定读，指（带有 FOR UPDATE 或 FOR SHARE 的SELECT ）、UPDATE 和 DELETE ，采用何种锁取决于：查询条件是对唯一索引(unique or primary?)的定值查询(=or in ?)还是范围查询。

  • 对于具有定值搜索条件的唯一索引，仅锁定找到的索引记录，而不锁定它之前的间隙。
  • 对于其他搜索条件，将锁定扫描到的索引范围，使用间隙锁或临键锁来阻止其他会话插入该范围所覆盖的间隙。

例子：

  1. # 创建表t（无自定义索引，有隐式聚簇索引）
  2. CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
  3. INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
  5. # Session A
  6. START TRANSACTION;
  7. UPDATE t SET b = 5 WHERE b = 3;
  9. # Session B
  10. UPDATE t SET b = 4 WHERE b = 2;

2. RC（读已提交）

READ COMMITTED，每次一致读取（即使在同一个事务中），也会设置并读取自己的新快照。（建议与RR的快照读进行对比记忆）

对于锁定读，指（带有 FOR UPDATE 或 FOR SHARE 的 SELECT）、UPDATE 语句和 DELETE ，InnoDB 仅锁定索引记录，而不锁定它们之前的gap，因此允许在锁定记录旁边自由插入新记录。由于间隙锁已禁用，因此可能会出现幻像行问题。

另外，该隔离级别下，binlog仅支持基于RAW格式。如已设置MIXED格式，服务器会自动使用基于RAW的日志记录。(因为不锁gap，所以不能使用STATEMENT格式的binlog，如多个update同时操作某个间隙可能会出现不一致情况 )

再详细点，使用RC有如下效果

  • 对于 UPDATE 或 DELETE 语句，InnoDB 仅对其操作的行持有锁。在 MySQL 评估 WHERE 条件后，不匹配行的记录锁将被释放。因此大大降低了死锁的可能性。（同时也提高了性能）
  • 对于 UPDATE 语句，如果一行已经被锁定，InnoDB 执行“半一致性”读取，MySQL会在其它事务最新提交的版本中，匹配WHERE 条件所涉及的行，如果行匹配（则必须被更新），MySQL 再次读取该行，这一次 InnoDB 要么锁定它，要么等待锁定它。

当 InnoDB 执行每一个 UPDATE 时，它首先为每一行获取一个排他锁，然后决定是否修改它。如果不修改，会释放锁。否则，会保留锁直到事务结束。

下面提供一个例子

# 创建表t（无自定义索引，有隐式聚簇索引）
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);

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2;

Session A执行时锁情况如下：

若是RR(默认)级别：
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

若是RC级别：
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)

Session B执行时锁情况如下：

当隔离级别是RR时，Session B锁状态
x-lock(1,2); block and wait for first UPDATE to commit or roll back

当隔离级别是RC时，Session B锁状态
（立即获取到了锁）
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

但是，在RC级别下，如果 WHERE 条件包含索引列，则在获取和持有记录锁时只考虑索引列。
在以下示例中，第一个 UPDATE 在 b = 2 的每一行上获取并保留一个 x 锁。第二个 UPDATE 在尝试获取相同记录上的 x 锁时阻塞，因为它也使用了在列 b 上定义的索引。

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

# Session A -- 锁住b=2的行，实际是两行全锁
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B -- 阻塞中……
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

# PS: 也就是说，如果b列没有索引，则Session B不会阻塞

3. RU（读未提交）

READ UNCOMMITTED，该隔离级别的读取，是不一致的。因此这也被称为脏读。

SERIALIZABLE（串行化）

该隔离级别极少使用，大致原理是加锁，即普通的一致性select，会加记录锁或临键锁