事务隔离级别-Transaction Isolation Levels

本文是对MySQL官方文档：《 14.5.2.1 Transaction Isolation Levels》的翻译，其中备注、一~五标题和其它小标题是博主根据理解所加。

一、事务的隔离性

事务隔离性（ Isolation）是数据库处理数据的基础性能之一。隔离性（ Isolation）与原子性（ Atomicity）、一致性（Consistency）、持久性（Durability）合称为数据库事务的四大特性，简称为ACID。隔离级别则在事务中起到一个微调设置的作用，它用于在数据的性能与可靠性、一致性和再现性之间做出平衡，而这些数据可能是多个事务同时修改、查询的结果。

二、InnoDB for Isolation

MySQL数据库支持的存储引擎有很多，其中使用场景中比较常见的InnoDB和MyISAM，但是后者不支持事务。InnoDB存储引擎支持《SQL:1992标准》所描述的所有四种事务隔离级别：读未提交（ READ UNCOMMITTED ），读已提交（ READ COMMITTED ），可重复读（ REPEATABLE READ ）和序列化（ SERIALIZABLE ），其中可重复读（ REPEATABLE READ ）是InnoDB的默认隔离级别。

三、隔离级别修改

用户可以使用sql语句： SET TRANSACTION 来改变隔离级别，不仅可以为单独一次会话改变，而且可以全局的改变。如果想为所有数据库连接设置默认的隔离级别，可以通过使用参数 transaction-isolation 在命 令行或配置文件中修改。关于更详细的设置、操作隔离级别语法参考 Section 13.3.6, “SET TRANSACTION Syntax” 。

【备注】

1、设置隔离级别：

    SET SESSION TRANSACTION ISOLATION LEVEL isolation_level_name;
    SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level_name;

其中isolation_level_name包括： READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ 和 SERIALIZABLE ；

或者使用以下命令：

SET GLOBAL tx_isolation='isolation_level_name';       
SET SESSION tx_isolation='isolation_level_name';

其中 isolation_level_name包括： READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ和 SERIALIZABLE；

2、设置默认隔离级别：

①cd到mysql安装目录，例如我的是 /usr/local/opt/mysql@5.6；

②编辑 my.cnf 文件，在 [mysqld] 增加修改参数： transaction-isolation=isolation_level_name

其中 isolation_level_name 包括： READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ 和 SERIALIZABLE ；

3、查询隔离级别：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

四、隔离级别使用简要

InnoDB存储引擎是通过使用不同的锁（locking）策略，来做到对这里描述的每一个隔离级别都支持的。

1、在视ACID合规性非常重要的核心数据进行操作的时候，您可以通过使用默认的级别： REPEATABLE READ来强制执行高度一致性；

2、在有些情况，比如批量报告（ bulk reporting），数据的高度一致性和可重复读与减少大量的锁开销相比没有那么重要时，您可以使用 READ COMMITTED甚至用 READ UNCOMMITTED来放宽数据的一致性规则；

3、 序列化（ SERIALIZABLE ）隔离级别比可重复读（ REPEATABLE READ）使用更严格的规则；在一些比较特殊的场景才会使用该隔离级别，比如说XA事务（ XA transactions）或者需要解决一些并发、死锁（ deadlock）问题的场景。

【备注】

XA官方文档是这样写的： A standard interface for coordinating distributed transactions, allowing multiple databases to participate in a transaction while maintaining ACID compliance . 

意思是：它是一个协调分布式事务的MySQL标准接口，这个可以接口在保证ACID合规性的前提下，允许集群数据库（ multiple databases）参与到同一个事务当中去。

五、事务隔离级别

接下来的内容描述了MySQL是如何支持不同事务隔离级别的。列举了四种隔离级别，不仅有很常用的，也有平时还少使用的。

1、REPEATABLE READ

①这是InnoDB的默认隔离级别。一致性读（ consistent read）在同一个事务中读取都是第一次读取时建立的快照（ snapshot）。这就意味着，在同一个事务中假如您执行了多个非锁定的查询语句，查询到的结果都是彼此一致的。关于非锁定的一致性读（ consistent read）可以参考 Section 14.5.2.3, “Consistent Nonlocking Reads” 。

②对于加锁读（ locking read ）操作（ SELECT  with  FOR UPDATE  or  LOCK IN SHARE MODE），更新操作（UPDATE）和删除操作（DELETE）这几种操作，数据库选择对数据的加锁策略取决于，以上执行的数据表是否有唯一索引（ unique index ），而且查询条件中是否涉及到索引字段或范围查询。

-对于查询条件中有唯一索引的，InnoDB值锁定查询到的数据，而不锁定数据前面的间隙（gap）。

-对于其他的查询情况，InnoDB会锁定索引扫描的范围，InnoDB通过使用间隙锁（gap lock）或者next-key lock来阻塞其他会话向扫描范围内的gap插入数据。对于gap lock和next-key lock的详细介绍参考 Section 14.5.1, “InnoDB Locking” 。

2、READ COMMITTED

每次一致性读（ consistent read），即使在同一事务中，它都会设置并读取自己的最新的快照（ snapshot）。关于一致性读可以参考 Section 14.5.2.3, “Consistent Nonlocking Reads” 。（ 【备注】 其实这个链接和五、1、①中的是相同的。）

对于加锁读（ locking read）操作 （ SELECT  with  FOR UPDATE  or  LOCK IN SHARE MODE），更新操作 （UPDATE）和删除操作 （DELETE）这几种操作， InnoDB仅仅对涉及索引的记录加锁，而不锁定它们前面的间隙 （gap）， next-key lock允许在锁定记录旁边自由插入新记录。在这种隔离级别，间隙锁定（ gap lock）仅用于外键约束校验和重复键校验 。

因为禁用了间隙锁定，所以可能会出现幻象（ phantom ）问题，因为其他会话可以将新行插入到间隙中（【备注】比如 phantom read：幻读 ）。对于幻象（ phantom ）更详细介绍可参考 Section 14.5.4, “Phantom Rows” 。

假如您使用READ COMMITTED隔离级别，则必须使用基于行的二进制日志记录。

使用READ COMMITTED还有一些额外的效果：

-对于 UPDATE 和 DELETE 操作，InnoDB只会为它所操作的行持有锁，而其他不匹配的锁定行会在MySQL通过 where 条件评估后被释放掉。这一做法极大地减少了死锁发生的可能性，但是依然会发生。

-对于 UPDATE 操作，假如一条数据行已经被加锁，InnoDB会执行一个“半一致性”（“semi-consistent”）读取，给MySQL返回一个最新提交（commit）的版本号，以至于MySQL可以判断是否这行数据与要 UPDATE 的行所匹配。假如匹配（则必定会被 UPDATE ），MySQL会再次读取它，而且这次InnoDB会要么为它加锁要么等待一个锁给它。

举个例子，使用下面的sql语句：

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;

在这种情况下，该表没有索引，因此搜索和索引扫描使用的是隐式聚簇索引进行记录锁定 (参考  Section 14.8.2.1, “Clustered and Secondary Indexes” )而不是索引列。

设想一下，一个会话对上表执行了如下 UPDATE 语句：

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

然后，另一个会话在Session A之后执行了如下 UPDATE 语句：

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

当InnoDB执行每一次 UPDATE 时，首先它会为读取的每一行数据请求一个排它锁（ exclusive lock ），然后决定是否去修改它。假如InnoDB没有修改这行数据，会释放这个锁；否则，InnoDB会保留这个锁直到事务结束。这会影响到事务的处理，如下所示：

当使用默认的隔离级别可重复读（ REPEATABLE READ ）时，

-在①中的操作，它为读取的每一行数据请求了一个x-lock（排它锁exclusive lock的简称），而且一个都没有释放：

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 语句提交（commit）或回滚（rollback）：

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

当使用隔离级别的为读已提交（ READ COMMITTED ）时，

在①中的操作，它为读取的每一行数据请求了一个x-lock（排它锁exclusive 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执行了一个“半一致性”（“semi-consistent”）读取，给MySQL返回一个最新提交（commit）的版本号，以至于MySQL可以判断是否每行数据与要 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为where中b=2的每一行数据请求到并保留了x-lock；第二次UPDATE当它试图为同样的数据去请求x-lock时便阻塞了，因为它也使用到了定义在数据列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参数只能在数据库启动时设置；而隔离级别不仅可以在数据库启动时设置，而且在数据库运行中也可以修改。

因此， 与   innodb_locks_unsafe_for_binlog相 比， READ COMMITTED 隔离级别 提供了更好更灵活的控制。

【备注】行锁 record lock详细介绍参考 https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_record_lock。

3、 READ UNCOMMITTED

查询（SELETCT）语句会被在无锁的形式下执行，但是可能会读到一行数据的早期版本（这句话意思是：SElECT操作的数据行是不被锁定的，其他事务也可以操作，这样当前事务就可能读到其它事务没有commit的数据）。因此，使用这个隔离级别不能保证一致性读取。这也被称为脏读（ dirty read ）。其他方面，它的工作方式和READ COMMITTED很像。

4、SERIALIZABLE

这个隔离级别和可重复读（REPEATABLE READ）很像，不过假如数据库自动提交（ autocommit）被关闭的话，InnoDB隐式地将普通的 SELECT操作转换为了 SELECT ... LOCK IN SHARE MODE；假如数据库自动提交是开启的话，这次 SELECT就是它自己的事务 。因此，它被认为是只读的，并且如果作为一致（非锁定）读取执行，则可以被序列化，并且不需要阻塞其他事务。（假如想让一个普通的 SELECT阻塞其它事务去修改索要查询的数据，可以把数据库自动提交关闭。）

Copyright © 2018 Ansel. All rights reserved.