innodb事务的隔离级别

基于mysql5.7

四种事务隔离级别

隔离（Isolation）是缩写ACID中的I 。
隔离级别是一种用于在多个事务同时进行更改和查询时调整性能与结果的可靠性、一致性和可重复性之间平衡的设置。

InnoDB提供了四个事务隔离级别：（SQL:1992标准） （排序由宽松到严格）

• READ UNCOMMITTED 读未提交
• READ COMMITTED 读已提交
• REPEATABLE READ 可重复读
• SERIALIZABLE 序列化

innodb默认隔离级别是 REPEATABLE READ。

使用 ‘SET TRANSACTION’ 可以更改事务隔离的级别。

下表描述了MySQL如何支持不同的事务级别。从最常用的级别到最不常用的级别。

（1）REPEATABLE READ

同一事务中的一致读取读取的是由第一次读取建立的快照（ snapshot ）。这意味着如果SELECT 在同一事务中发出多个非锁定 （nonlocking）语句（SELECT锁定语句在下文中介绍），则这些 SELECT语句彼此之间也是一致的。

---

题外延伸——一致读取

是一种将某个时间节点的快照信息作为查询结果的读取操作，而不管同时运行的其他事务执行了何种更改。 如果查询的数据已被另一个事务更改，则将基于撤消日志的内容来重建原始数据。 通过强制事务等待其他事务完成，避免了一些降低并发性能的锁定问题。

• 使用REPEATABLE READ隔离级别时，快照基于执行第一次读取操作的时间。
• 使用READ COMMITTED隔离级别，快照将重置为每个一致读取操作的时间。

一致读取是InnoDB在READ COMMITTED和REPEATABLE READ隔离级别中处理SELECT语句的默认模式。 因为一致读取不会在它访问的表上设置任何锁，所以在对表执行一致读取时，其他会话可以自由地修改这些表。

---

对于锁定读取（locking reads） （SELECT使用FOR UPDATE或LOCK IN SHARE MODE）、 UPDATE和 DELETE语句，锁定取决于该语句是使用具有唯一搜索条件的唯一索引（a unique index with a unique search condition）还是范围类型搜索条件（a range-type search condition）。

• 对于具有唯一搜索条件的唯一索引（只有一个唯一索引作为搜索条件搜索一条记录时）， InnoDB仅锁定找到的索引记录（记录锁），而不加间隙锁。
• 对于其他搜索条件，InnoDB 使用间隙锁或临键锁定来锁定扫描的索引范围， 以阻塞其他会话在该范围内插入记录。
  关于间隙锁、临键锁可参考此文章

（2）READ COMMITTED

即使在同一事务中，每个一致读取（consistent read）也会设置并读取自己的新快照。

对于锁定读取（locking reads）（SELECT 使用FOR UPDATE或LOCK IN SHARE MODE）、UPDATE 语句和DELETE 语句，InnoDB 仅用记录锁 锁定索引，而 不加间隙锁，因此允许在锁定记录旁边自由插入新记录。间隙锁仅用于外键约束检查和重复键检查。

由于禁用了间隙锁，因此可能会产生幻读，因为其他会话可以在间隙中插入新行。

READ COMMITTED隔离级别仅支持基于行的二进制日志记录 。如果READ COMMITTED设置binlog_format=MIXED，服务器将自动使用基于行的日志记录。

使用READ COMMITTED具有其他效果：

• 对于UPDATE或 DELETE语句， InnoDB仅对其更新或删除的行持有锁。MySQL评估WHERE条件后，将释放不匹配行的记录锁 。这大大降低了死锁的可能性，但是仍然可以发生。
• 对于UPDATE语句，如果某行已被锁定，则InnoDB 执行“ 半一致 ”读取，将最新的提交版本返回给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;

在这种情况下，这张表没有索引，因此搜索和索引扫描使用隐藏的聚集索引来锁定记录，而不是使用索引列。

假设一个会话UPDATE使用以下语句执行 ：

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

同时假设第二个会话通过在第一个会话的语句之后执行以下语句来做UPDATE操作：

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

在InnoDB执行每一个UPDATE时，它首先要对它读取的每一行加上排他锁，然后确定是否对其进行修改。如果 InnoDB不修改该行，则释放锁。否则， InnoDB将保留该锁直到事务结束。这会影响事务处理，如下所示。

当使用默认隔离级别REPEATABLE READ 时，SessionA的Update操作将在读取的每一行上获取一个x锁（排他锁），并且不会释放其中的任何一个：

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

SessionB的UPDATE在尝试获取任何锁时会立即阻塞（因为第一个更新在所有行上都保留了锁），并且直到第一个UPDATE提交或回滚时才继续进行：

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

当使用隔离级别READ COMMITTED时，SessionA的update操作获取它读过的每行记录的锁（x-lock 排他锁）之后，会释放那些没被它修改过的记录的锁。

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)

对于SessionB的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

但是，如果WHERE条件中包括索引列，并且InnoDB使用该索引，那么在获取和保留记录锁时仅考虑该索引列。
在以下示例中，第一个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
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;

使用READ COMMITTED隔离级别的效果与启用已弃用的innodb_locks_unsafe_for_binlog配置选项相同，但以下情况除外：

• 启用innodb_locks_unsafe_for_binlog是全局设置，会影响所有会话，而隔离级别可以为所有会话全局设置，也可以针对每个会话单独设置。

• 只能在服务器启动时设置innodb_locks_unsafe_for_binlog，而隔离级别可以在启动时设置或在运行时更改。

READ COMMITTED因此提供了比innodb_locks_unsafe_for_binlog更好更灵活的控制 。

---

（3）READ UNCOMMITTED

SELECT语句以非锁定的方式执行，这意味着很可能会读取到其他事务没提交的版本（超前的版本）（but a possible earlier version of a row might be used）。 因此，使用此隔离级别的读取是不一致的。 这也称为脏读。 此外，此隔离级别的工作方式类似于READ COMMITTED。

（4）SERIALIZABLE

此级别类似于REPEATABLE READ：

• 当禁用了自动提交，InnoDB就会将所有普通的 SELECT 语句隐式转换为 SELECT … LOCK IN SHARE MODE
• 当启用了自动提交，则SELECT自身构成事务

因此，它被认为是只读的，并且如果以一致的（非锁定）读取（a consistent (nonlocking) read）方式执行并且不需要阻塞其他事务就可以被认为是序列化操作。（如果其他事务已修改所选行，则要强制普通SELECT阻止，请禁用自动提交。）

本文参考