innodb 的隔离级别

创作

fuyao-w

浏览量

粉丝

关注

innodb 的隔离级别

fuyao-w

2020-11-26 15:34:53

15.7.2.1事务隔离级别

事务隔离是数据库处理的基础之一。隔离是I的首字母缩写 ACID ; 隔离级别是在多个事务进行更改并同时执行查询时，对结果的性能和可靠性，一致性和可重现性进行微调的设置。

InnoDB提供由SQL描述的所有四个事务隔离级别：1992标准： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。InnoDBis 的默认隔离级别REPEATABLE READ。

用户可以更改单个会话或与SET TRANSACTION语句的所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，请使用 --transaction-isolation命令行或选项文件中的选项。有关隔离级别和级别设置语法的详细信息，请参见第13.3.7节“SET TRANSACTION语法”。

InnoDB使用不同的锁定策略支持此处描述的每个事务隔离级别。REPEATABLE READ对于ACID合规性很重要的关键数据的操作，您可以强制执行与默认级别的高度一致性。或者你可以用放松的一致性规则 READ COMMITTED，甚至 READ UNCOMMITTED在情况下，如散装报告需要精确的一致性和可重复的结果比最小化锁定开销的量更重要。 SERIALIZABLE执行甚至更严格的规则REPEATABLE READ，主要用于特殊情况，例如XA事务和解决并发和死锁问题。

以下列表描述了MySQL如何支持不同的事务级别。该列表从最常用的级别变为最少使用的级别。

REPEATABLE READ

这是默认的隔离级别 InnoDB。同一事务中的一致读取读取第一次读取建立的快照。这意味着如果SELECT 在同一事务中发出多个普通（非锁定）语句，这些 SELECT语句也相互一致。请参见第15.7.2.3节“一致的非锁定读取”。

对于锁定读取（SELECT带FOR UPDATE或，FOR SHARE） UPDATE和 DELETE语句，锁定取决于语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。
- 对于具有唯一搜索条件的唯一索引， InnoDB仅锁定找到的索引记录，而不是之前的间隙。
- 对于其他搜索条件，InnoDB 使用间隙锁或下一键锁定锁定扫描的索引范围，以阻止其他会话插入范围所覆盖的间隙。有关间隙锁和下一键锁的信息，请参见第15.7.1节“InnoDB锁定”。
READ COMMITTED

即使在同一事务中，每个一致的读取也会设置和读取自己的新快照。有关一致性读取的信息，请参见第15.7.2.3节“一致性非锁定读取”。

对于锁定读取（SELECT 带FOR UPDATE或FOR SHARE），UPDATE 语句和DELETE 语句，InnoDB只锁定索引记录，而不是它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。

由于禁用了间隙锁定，因此可能会出现幻像问题，因为其他会话可以在间隙中插入新行。有关幻像的信息，请参见第15.7.4节“幻影行”。

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

使用READ COMMITTED还有其他效果：
- 对于UPDATE或 DELETE语句， InnoDB仅对其更新或删除的行保持锁定。在MySQL评估WHERE条件后，将释放非匹配行的记录锁。这大大降低了死锁的可能性，但它们仍然可以发生。
- 对于UPDATE语句，如果一行已被锁定，则InnoDB 执行“ 半一致 ”读取，将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否与该WHERE条件匹配 UPDATE。如果行匹配（必须更新），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;
```
在这种情况下，表没有索引，因此搜索和索引扫描使用隐藏的聚簇索引进行记录锁定（请参见第15.6.2.1节“聚簇和二级索引”）而不是索引列。

假设一个会话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 隔离级别时，第 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
```
第二个UPDATE块一旦尝试获取任何锁（因为第一次更新已保留所有行上的锁），并且在第一次UPDATE提交或回滚之前不会继续：
```
x-lock(1,2); block and wait for first UPDATE to commit or roll back
```
如果READ COMMITTED使用if ，则第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)
```
对于第二个UPDATE， InnoDB执行 “ 半一致 ”读取，返回它读取到MySQL的每一行的最新提交版本，以便MySQL可以确定该行是否符合以下 WHERE条件 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
```
但是，如果WHERE条件包含索引列并InnoDB使用索引，则在获取和保留记录锁时仅考虑索引列。在下面的示例中，第一个 UPDATE采用并保留每行上的x锁定，其中b = 2.第二个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。
READ UNCOMMITTED

SELECT语句以非锁定方式执行，但可能使用行的早期版本。因此，使用此隔离级别，此类读取不一致。这也称为脏读。否则，这个隔离级别就像 READ COMMITTED。
SERIALIZABLE

此级别类似REPEATABLE READ，但InnoDB隐式将所有普通SELECT 语句转换为在如果 autocommit被禁用的情况下变为SELECT ... FOR SHARE。如果 autocommit启用，则 SELECT是它自己的事务。因此，已知它是只读的，并且如果作为一致（非锁定）读取执行则可以序列化，并且不需要阻止其他事务。（SELECT如果其他事务已修改所选行，则强制平面阻止，禁用autocommit。）

暂无评论