基于英文原版翻译,如果有错误欢迎留言指正。
英文原版地址: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.
Transaction isolation (事务的隔离)是数据库处理的基础之一,Isolation(隔离)是ACID中的I;the isolation level (事务的隔离级别)是当多个事务同时在进行更改和执行查询时,针对performance(性能),reliability(可靠性),consistency(一致性),reproducibility(再现性),在这四个特性之间进行平衡调整的的设置
注:在计算机科学中,再现性是指只要程序执行时的环境和初始条件相同,当程序重复执行时,不论它是从头到尾不停顿地执行,还是“停停走走”地执行,都将获得相同的结果。再现性是程序是否可以并行执行重要的准则之一。广义上,再现性:在改变了的测量条件下,对同一被测量的测量结果之间的一致性,称为测量结果的再现性。再现性又称为复现性、重现性
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 standard (SQL标准) 所有的事务隔离级别,分别是RED_UNCOMMITED,READ_COMMITTED,REPEATABLE_READ 和 SERIALIZABLE。InnoDB默认的隔离级别是REPEATABLE_READ
注:这里提一下,Oracle只实现了两个,READ_COMMITTED(默认),SERIALIZABLE
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 Syntax”.
你可以使用SQL语句SET TRANSACTION用改变 the isolation level (隔离级别),可以改变单个session或者后续所有session连接,另外你也可以用使用命令行参数-transaction-isolation或者配置文件。想要查询更详细的信息,请参考 13.3.6
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使用不同的锁定策略来支持每一个the transaction isolation levels(事务隔离级别) 。
你可以使用默认的REPEATABLE_READ 级别来保证强一致性,当你操作非常重要的数据的。
或者你可以放宽一致性规则使用READ_COMMITED或者甚至READ_UNCOMMITTED,在对precise consistency(精确一致性)和repeatable results(可重复结果)要求没那么高的场合,因为这样可以获得更高的效率(锁的开销更小)。
SERIALIZABLE比REPEATABLE_READ 更为严格, 主要用于特殊情况,例如XA transactions (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如何支持的不同的 transaction levels(事务隔离级别),排列顺序从最常用到最不常用
REPEATABLE READ
This is the default isolation level for InnoDB
. Consistent reads within the same transaction read the snapshotestablished 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.5.2.3, “Consistent Nonlocking Reads”.
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.
InnoDB默认的事务隔离级别,Consistent reads
-
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.5.1, “InnoDB Locking”. -
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.5.2.3, “Consistent Nonlocking Reads”.
For locking reads (
SELECT
withFOR UPDATE
orLOCK IN SHARE MODE
),UPDATE
statements, andDELETE
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.Because gap locking is disabled, phantom problems may occur, as other sessions can insert new rows into the gaps. For information about phantoms, see Section 14.5.4, “Phantom Rows”.
Only row-based binary logging is supported with the
READ COMMITTED
isolation level. If you useREAD COMMITTED
withbinlog_format=MIXED
, the server automatically uses row-based logging.Using
READ COMMITTED
has additional effects: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.8.2.1, “Clustered and Secondary Indexes”) rather than indexed columns.
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 eachUPDATE
, it first acquires an exclusive lock for each row that it reads, and then determines whether to modify it. IfInnoDB
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.When using the default
REPEATABLE READ
isolation level, the firstUPDATE
acquires an x-lock on each row that it reads and does not release any of them: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 firstUPDATE
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 firstUPDATE
acquires an x-lock on each row that it reads and releases those for rows that it does not modify: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)
For the second
UPDATE
,InnoDB
does a “semi-consistent” read, returning the latest committed version of each row that it reads to MySQL so that MySQL can determine whether the row matches theWHERE
condition of theUPDATE
: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
However, if the
WHERE
condition includes an indexed column, andInnoDB
uses the index, only the indexed column is considered when taking and retaining record locks. In the following example, the firstUPDATE
takes and retains an x-lock on each row where b = 2. The secondUPDATE
blocks when it tries to acquire x-locks on the same records, as it also uses the index defined on column 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 deprecatedinnodb_locks_unsafe_for_binlog
configuration option, with these exceptions:READ COMMITTED
therefore offers finer and more flexible control thaninnodb_locks_unsafe_for_binlog
.-
For
UPDATE
orDELETE
statements,InnoDB
holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated theWHERE
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 theWHERE
condition of theUPDATE
. If the row matches (must be updated), MySQL reads the row again and this timeInnoDB
either locks it or waits for a lock on it. -
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 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 likeREAD COMMITTED
. -
SERIALIZABLE
This level is like
REPEATABLE READ
, butInnoDB
implicitly converts all plainSELECT
statements toSELECT ... LOCK IN SHARE MODE
ifautocommit
is disabled. Ifautocommit
is enabled, theSELECT
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 plainSELECT
to block if other transactions have modified the selected rows, disableautocommit
.)