MySQL5.7事务隔离级别官方文档翻译

前言

这段时间梳理知识体系,在MySQL事务隔离级别的区别上有些疑问,观摩B站和百度上的视频和帖子还是不能完全理解.于是想到了在官网上寻找答案,过程中产生了本篇译文.
翻译形式为机翻+本人的理解,抛砖引玉,欢迎大家提出宝贵的意见和建议.

原文地址:https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

正文

Transaction isolation is one of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.

事务隔离是数据库处理的基础之一。隔离指的是缩略词ACID中的I；隔离级别是一种设置，当多个事务同时进行更改和执行查询时，可以微调性能与结果的可靠性、一致性和再现性之间的平衡。

InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.

InnoDB 引擎提供SQL:1992标准定义的全部四种事务隔离级别:
READ UNCOMMITTED——读未提交
READ COMMITTED——读已提交
REPEATABLE READ——可重复读
SERIALIZABLE——串行化
InnoDB的默认隔离级别是REPEATABLE READ

A user can change the isolation level for a single session or for all subsequent connections with the SET TRANSACTION statement. To set the server’s default isolation level for all connections, use the --transaction-isolation option on the command line or in an option file. For detailed information about isolation levels and level-setting syntax, see Section 13.3.6, “SET TRANSACTION Statement”.

用户可以使用SET TRANSACTION 语句来设置单个会话或者接下来所有连接的隔离级别.设置服务器的默认隔离级别可以在命令行或者配置文件中使用–transaction-isolation选项.关于隔离级别的命令的详细信息,请参见 Section 13.3.6, “SET TRANSACTION Statement”.

InnoDB supports each of the transaction isolation levels described here using different locking strategies. You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID compliance is important. Or you can relax the consistency rules with READ COMMITTED or even READ UNCOMMITTED, in situations such as bulk reporting where precise consistency and repeatable results are less important than minimizing the amount of overhead for locking. SERIALIZABLE enforces even stricter rules than REPEATABLE READ, and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks.

InnoDB使用不同的锁定策略支持每一种事务隔离级别。对于关键数据上的操作，在ACID合规性非常重要的情况下，可以使用默认的REPEATABLE READ级别来强制保持高度的一致性.在批量报告等情况下，可以通过READ-COMMITTED，甚至READ-UNCOMMITED级别来放款一致性要求，在这种情况下，精确的一致性和可重复的结果不如最小化锁定开销那么重要。SERIALIZABLE比REPEATABLE READ执行更严格的规则，主要用于特殊情况，例如XA事务，以及解决并发和死锁问题。

The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used.

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

REPEATABLE-READ(可重复读)

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 14.7.2.3, “Consistent Nonlocking Reads”.

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

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition or a range-type search condition.

• For a unique index with a unique search condition, InnoDB locks only
  the index record found, not the gap before it.
• For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 14.7.1, “InnoDB Locking”.

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

• 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定之前的间隔。
• 对于其他搜索条件，InnoDB会锁定扫描的索引范围，使用gap lock或next-key
  lick阻止其他会话插入该范围覆盖的间隙。有关gap锁和next-key lock的信息，请参阅第14.7.1节“InnoDB锁”。

READ-COMMITTED

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads, see Section 14.7.2.3, “Consistent Nonlocking Reads”.

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

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

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

Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps. For information about phantom rows, see Section 14.7.4, “Phantom Rows”.
Only row-based binary logging is supported with the READ COMMITTED isolation level. If you use READ COMMITTED with binlog_format=MIXED, the server automatically uses row-based logging.
Using READ COMMITTED has additional effects:

• For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.
• For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.

由于禁用了gap lock，可能会出现幻读问题，因为其他会话可能会在间隙中插入新行。有关幻读的信息，请参阅第14.7.4节“幻影行”。
READ-COMMITTED级别仅支持基于行的binog。如果将READ-COMMITTED与binlog_format=MIXED一起使用，服务器将自动使用基于行的日志记录。
使用READ COMMITTED还有其他效果：

• 对于UPDATE或DELETE语句，InnoDB只对其更新或删除的行持有锁。在MySQL评估WHERE条件后，将释放不匹配行的记录锁。这大大降低了死锁的可能性，但死锁仍然可能发生。
• 对于UPDATE语句，如果一行已经被锁定，InnoDB将执行“半一致”读取，将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否匹配更新的WHERE条件。如果行匹配（必须被更新了），MySQL将再次读取该行，这次InnoDB将锁定该行或等待锁定。

Consider the following example, beginning with this table:

考虑以下示例，从下表开始：

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;

In this case, the table has no indexes, so searches and index scans use the hidden clustered index for record locking (see Section 14.6.2.1, “Clustered and Secondary Indexes”) rather than indexed columns.

在这种情况下，表没有索引，因此搜索和索引扫描使用隐藏的聚集索引来锁定记录（请参阅第14.6.2.1节“聚集索引和二级索引”），而不是索引列。

Suppose that one session performs an UPDATE using these statements:

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

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

Suppose also that a second session performs an UPDATE by executing this statement following those of the first session:

假设第二个会话在第一个会话之后执行下面的语句：

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

As InnoDB executes each UPDATE, it first acquires an exclusive lock for each row that it reads, and then determines whether to modify it. If InnoDB does not modify the row, it releases the lock. Otherwise, InnoDB retains the lock until the end of the transaction. This affects transaction processing as follows.

在InnoDB执行每个更新时，它首先为读取的每一行获取独占锁，然后确定是否修改它。如果InnoDB不修改该行，则会释放锁。否则，InnoDB将保留锁，直到事务结束。这会影响以下事务处理。

When using the default REPEATABLE READ isolation level, the first UPDATE acquires an x-lock on each row that it reads and does not release any of them:

使用默认的REPEATABLE READ隔离级别时，第一次更新会在其读取的每一行上获取一个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

The second UPDATE blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the first UPDATE commits or rolls back:
第二次更新在尝试获取任何锁时立即阻塞（因为第一次更新在所有行上都保留了锁），并且在第一次更新提交或回滚之前不会继续：

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

If READ COMMITTED is used instead, the first UPDATE acquires an x-lock on each row that it reads and releases those for rows that it does not modify:

如果使用READ COMMITTED代替,第一个UPDATE语句会在它读取的每一行上加上排他锁,并释放掉没有修改的行上的排他锁.

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)

However, if the WHERE condition includes an indexed column, and InnoDB uses the index, only the indexed column is considered when taking and retaining record locks. In the following example, the first UPDATE takes and retains an x-lock on each row where b = 2. The second UPDATE blocks when it tries to acquire x-locks on the same records, as it also uses the index defined on column b.

但是,如果WHERE条件包含一个被索引的列并且Innodb引擎使用了它,则在获取和保留行锁时只考虑索引列(其他条件会被忽略,也就是说,范围会比较大),在下面的例子中,第一个UPDATE在where b=2获取的每一行拿到并保持了一个排他锁.第二个UPDATE在尝试获取这些记录的时候被阻塞了,因为它也是用了在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;

The effects of using the READ COMMITTED isolation level are the same as enabling the deprecated innodb_locks_unsafe_for_binlog variable, with these exceptions:

• Enabling innodb_locks_unsafe_for_binlog is a global setting and affects all sessions, whereas the isolation level can be set globally for all sessions, or individually per session.
• innodb_locks_unsafe_for_binlog can be set only at server startup, whereas the isolation level can be set at startup or changed at runtime.

READ COMMITTED therefore offers finer and more flexible control than innodb_locks_unsafe_for_binlog.

使用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 statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.

SELECT语句是以不加锁的方式执行的(早期的版本可能使用了行锁).所以,使用这个隔离级别,这样的读操作是不一致的,也被称为脏读.其他情况,这个隔离级别和READ COMMITTED类似.

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT … LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

这个级别和REPEATABLE-READ类似,不同之处是:

• 在autocommit设置为禁用的情况下,InnoDB隐式的把所有普通的SELECT语句转换为了SELECT…LOCK IN SHARE MODE.
• 在autocommit为启用的时候,SELECT拥有自己的事务.因此它可以被认为是一个只读而且可以被序列化的非锁定一致读,并不需要去阻塞其他事务.(如果想要其他事务已经修改了读取的数据时阻塞普通的SELECTE,请关闭autocommit)