MySQL的事务隔离级别

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

InnoDB提供了SQL：1992标准中描述的所有四个事务隔离级别：READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ和SERIALIZABLE。InnoDB的默认隔离级别是REPEATABLE READ。可以使用SET TRANSACTION语句更改单个会话或所有后续连接的隔离级别。可以在命令行或选项文件中使用transaction-isolation为所有连接设置服务器的默认隔离级别。InnoDB通过使用不同的锁策略以实现这四种事务隔离级别。在操作关键数据时，遵守事务的ACID特性是至关重要的，可以强制使用默认的REPEATABLE READ隔离级别实现高度的一致性。或者，在诸如批量报告的情况下，最小化锁开销比数据的精准一致性和可重复性更重要，此时可以使用READ COMMITTED甚至READ UNCOMMITTED降低隔离级别。SERIALIZABLE的隔离级别比REPEATABLE READ更高，主要用于特定的情况，例如XA事务和并发性和死锁问题的故障排除。

下面的列表描述了MySQL如何支持不同的事务隔离级别。从上到下按最常用到最少使用排序。

REPEATABLE READ（可重复读）

InnoDB默认的隔离级别。同一个事务中的一致性读取读取的是第一次读取建立的快照。这意味着，如果在一个事务中多次发起某一普通（非锁定）SELECT语句，则每次查询的结果都是相同的，即可重复读。

对于锁定读（SELECT with For UPDATE 或LOCK IN SHARE MODE）、UPDATE和DELETE语句，锁定取决于语句是使用具有唯一搜索条件的唯一索引还是使用范围类型的搜索条件。

• 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不是记录之前的间隙。
• 对于其他搜索条件，InnoDB锁定扫描的索引范围，使用间隙锁或临界锁来阻塞其他会话向范围覆盖的间隙中插入记录。

READ COMMITTED（读已提交）

与可重复读不同，读已提交隔离级别下即使在同一事务中，每次一致性读取都会设置并读取自己刷新的快照。

对于锁定读（SELECT with FOR UPDATE or LOCK IN SHARE MODE），UPDATE语句，DELETE语句，InnoDB只锁满足条件的记录而不锁这些记录前的间隙，因此允许在这些记录前的间隙中插入新的记录。间隙锁只用于外键约束检查和重复键检查。由于禁用间隙锁，其他事务可以向间隙中插入新记录，因此可能会出现幻读。

READ COMMITTED隔离级别只支持基于行的二进制日志记录。如果在READ COMMITTED隔离级别下使用binlog_format=MIXED，MySQL服务端将自动使用基于行的日志记录。

使用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;

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

假设一个事务执行如下更新语句：

# 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锁。并且不会释放它们中的任何一个：

给记录(1,2)设置排他锁; 保留该记录的排他锁
给记录(2,3)设置排他锁; 更新记录(2,3)为(2,5); 保留该记录的排他锁
给记录(3,2)设置排他锁; 保留该记录的排他锁
给记录(4,3)设置排他锁; 更新记录(4,3)为(4,5); 保留该记录的排他锁
给记录(5,2)设置排他锁; 保留该记录的排他锁

第二个UPDATE在试图获取任何锁时就会阻塞（因为第一个UPDATE保留了所有行的锁），并且直到第一个UPDATE提交或回滚时才继续：

试图给记录(1,2)设置排他锁; 由于事务1已持有该记录的排他锁，事务2阻塞并等待事务1的UPDATE的提交或回滚

如果使用READ COMMITTED，则第一个UPDATE在它读取的每一行上设置X锁，并释放它没有修改的行：

给记录(1,2)设置排他锁; 解锁记录(1,2)
给记录(2,3)设置排他锁; 更新记录(2,3)为(2,5); 保留该记录的排他锁
给记录(3,2)设置排他锁; 解锁记录(3,2)
给记录(4,3)设置排他锁; 更新记录(4,3)为(4,5); 保留该记录的排他锁
给记录(5,2)设置排他锁; 解锁记录(5,2)

对于第二次UPDATE，InnoDB做一个“半一致”读取，将读取的每一行的最新提交版本返回给MySQL，这样MySQL就可以判断这一行是否符合UPDATE的WHERE条件：

给记录(1,2)设置排他锁; 更新记录(1,2)为(1,4); 保留该记录的排他锁
给记录(2,3)设置排他锁; 解锁(2,3)
给记录(3,2)设置排他锁; 更新记录(3,2)为(3,4); 保留该记录的排他锁
给记录(4,3)设置排他锁; 解锁(2,3)
给记录(5,2)设置排他锁; 更新记录(5,2)为(5,4); 保留该记录的排他锁

但是，如果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更精细、更灵活的控制。

READ UNCOMMITTED（读未提交）

在此种隔离级别下，SELECT语句以非锁定的方式执行，因此可能会使用较早版本的记录行。所以，使用此隔离级别时，此类读取是不一致的。这也被称为脏读。此隔离级别的工作方式类似于READ COMMITTED。

SERIALIZABLE（序列化）

此隔离级别类似于REPEATABLE READ，如果自动提交被禁用，InnoDB将把所有普通的SELECT语句隐式地转化为SELECT ... LOCK IN SHARE MODE。如果启用自动提交，则SELECT是他自己的事务，因此，在只读的情况下，执行一致性读取（非锁定）可以序列化，并且不会阻塞其他事务。（如果其他事务修改了所选行，则需要强制阻塞普通的SELECT查询，此时应该禁用自动提交。）

参考：Transaction Isolation Levels