14.7 InnoDB Locking and Transaction Model

14.7 InnoDB的锁和事务模型

[TOC]

To implement a large-scale, busy, or highly reliable database application, to port substantial code from a different database system, or to tune MySQL performance, it is important to understand InnoDB locking and the InnoDB transaction model.

This section discusses several topics related to InnoDB locking and the InnoDB transaction model with which you should be familiar.

Section 14.7.1, “InnoDB Locking” describes lock types used by InnoDB.

Section 14.7.2, “InnoDB Transaction Model” describes transaction isolation levels and the locking strategies used by each. It also discusses the use of autocommit, consistent non-locking reads, and locking reads.

Section 14.7.3, “Locks Set by Different SQL Statements in InnoDB” discusses specific types of locks set in InnoDB for various statements.

Section 14.7.4, “Phantom Rows” describes how InnoDB uses next-key locking to avoid phantom rows.

Section 14.7.5, “Deadlocks in InnoDB” provides a deadlock example, discusses deadlock detection, and provides tips for minimizing and handling deadlocks in InnoDB.

路过的小伙伴, 请帮忙点小星星Star支持: InnoDB的锁和事务模型 - Github版本地址

想要实现高吞吐、高可靠的大型数据库应用系统; 想要从其他数据库迁移到MySQL; 想要进行MySQL性能调优; 那么学习和掌握InnoDB的锁和事务模型就非常有用。

本节讲解InnoDB锁和InnoDB事务模型,对应的主题包括:

14.7.1, “InnoDB Locking”, 介绍InnoDB中使用的锁类型。
14.7.2, “InnoDB Transaction Model”, 描述事务隔离级别(transaction isolation level), 以及每种隔离级别使用的锁策略。还会介绍如何使用自动提交(autocommit), 一致性非锁定读(consistent non-locking read), 和锁定读(locking read)。
14.7.3, “Locks Set by Different SQL Statements in InnoDB”, 讨论InnoDB为各种语句设置的锁。
14.7.4, “Phantom Rows”, 幻影行, 描述了InnoDB怎么使用 next-key locking 来解决幻读。
14.7.5, “Deadlocks in InnoDB”, 通过一个死锁示例, 来讨论死锁问题, 并介绍一些在InnoDB中降低和减少死锁的技巧。

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html

14.7.1 InnoDB Locking

This section describes lock types used by InnoDB.

本节介绍 InnoDB 中各种类型的锁。

Shared and Exclusive Locks

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks.

A shared (S) lock permits the transaction that holds the lock to read a row.
An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:

A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.
A request by T2 for an X lock cannot be granted immediately.

If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.

共享锁与排他锁

InnoDB实现了标准的行级锁(row-level locking), 包括两种类型: 共享锁(shared lock, 简称S锁)和排他锁(exclusive lock, 简称X锁)。【排他锁】有时候也被称为【互斥锁】。

共享锁(S): 允许持有锁的事务读取这一行。
排他锁(X): 允许持有锁的事务更新或删除这一行。

如果事务 T1 持有了行 r 的共享锁(S), 则另一个事务 T2 请求对行 r 上锁时, 将按以下方式进行处理:

如果T2请求S锁, 则可以立即获得授予。结果为: T1 和 T2 都持有行 r 上的 S 锁。
如果T2请求X锁, 则不能立即授予, 需要进入队列排队等待。

如果事务 T1 在行 r 上持有排他锁(X), 则另一个事务 T2 对 r 行的任何上锁请求, 都不能立即授予。此时, 事务 T2 必须等事务 T1 释放行 r 的锁。

Intention Locks

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES ... WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

For example, SELECT ... LOCK IN SHARE MODE sets an IS lock, and SELECT ... FOR UPDATE sets an IX lock.

The intention locking protocol is as follows:

Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

Table-level lock type compatibility is summarized in the following matrix.

意向锁

InnoDB支持多粒度锁(multiple granularity locking), 允许行锁(row lock)和表锁(table lock)并存。例如, LOCK TABLES ... WRITE 语句对指定的表设置排他锁(X锁)。为了使多粒度级别的锁变得切实可行, InnoDB使用了【意向锁】(intention lock), 或者称为【意图锁】。意向锁是表级锁(table-level lock), 表明当前事务稍后要对表中的行进行哪种类型的锁定(想要上共享锁还是排他锁)。意向锁分为两种类型:

共享意向锁(IS, intention shared lock): 表明事务打算在表中的某些行上设置共享锁。
排他意向锁(IX, intention exclusive lock): 表明事务打算对表中的某些行设置排他锁。

例如, SELECT ... LOCK IN SHARE MODE 设置的是 IS 锁, 而 SELECT ... FOR UPDATE 设置的是 IX 锁。

意向锁的协议如下:

事务在获取表中某行的共享锁之前, 必须先获取该表的IS锁, 或者限制更强的锁。
事务在获取表中某行的排它锁之前, 必须先获取该表的IX锁。

意向锁与其他锁的兼容性汇总如下:

	`X`	`IX`	`S`	`IS`
`X`	冲突	冲突	冲突	冲突
`IX`	冲突	`兼容`	冲突	`兼容`
`S`	冲突	冲突	`兼容`	`兼容`
`IS`	冲突	`兼容`	`兼容`	`兼容`

A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

Transaction data for an intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

如果请求的锁与现有锁兼容, 则立即授予请求的事务; 但如果与现有的锁冲突, 则不授予该锁。请求的事务需要等待, 直到有冲突的锁被释放。如果锁请求与现有锁冲突, 并因为会导致 “死锁” 而无法授予, 则会直接报错。

意向锁除了全表请求之外(例如 LOCK TABLES ... WRITE ), 不阻塞任何其他请求。意向锁的主要目的是表明某个事务正在锁定表中的行, 或者要锁定表中的行。

在 SHOW ENGINE INNODB STATUS 语句和 InnoDB monitor 输出的事务信息中, 意向锁类似这样:

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

Record Locks

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See 【Section 14.6.2.1, “Clustered and Secondary Indexes”】.

Transaction data for a record lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

记录锁

记录锁(Record Lock), 也是对索引记录(index record)上的锁。例如, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 语句会阻止其他事务插入/更新/或删除 t.c1 值为 10的行。

记录锁始终锁定索引记录, 即使没有定义索引的表也是如此。对于没有设置索引的表, InnoDB会自动创建一个隐藏的聚集索引(clustered index, 也称为聚簇索引), 并使用此索引来执行记录锁。具体的情况请查看Section 14.6.2.1, “Clustered and Secondary Indexes”。

在 SHOW ENGINE INNODB STATUS 语句和 InnoDB monitor 输出的事务信息中, 记录锁类似这样:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     ''O;;
 2: len 7; hex b60000019d0110; asc        ;;

Gap Locks

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

A gap might span a single index value, multiple index values, or even be empty.

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap:

间隙锁

间隙锁(Gap Lock):

是对索引记录之间的间隙进行锁定,
或者是对第一个索引记录之前的间隙进行锁定,
或者是对最后一个索引记录之后的间隙进行锁定。

例如, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 会阻止其他事务将 15 这个值插入到 t.c1 列中, 无论该列中是否存在这个值, 因为这个范围内所有值之间的间隙都被锁定了。

一个间隙可能跨越单个索引值, 多个索引值, 甚至为空。

间隙锁是性能和并发之间的一种权衡, 只会在某些事务隔离级别中使用。

对于使用唯一索引(unique index)来锁定唯一行的语句, 不需要使用间隙锁。 (如果是多列组成的唯一索引, 并且搜索条件中仅包含部分列, 这时也会产生间隙锁。) 例如, 如果id列具有唯一索引, 则下面的SQL语句只会对 id = 100 的行使用记录锁, 而不关心其他会话是否在前面的间隙中插入新行:

SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated). Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

There are also other effects of using the READ COMMITTED isolation level or enabling innodb_locks_unsafe_for_binlog. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

如果 id 列没有索引, 或者不是唯一索引, 则该语句会把前面的间隙一起锁了。

值得注意的是, 不同的事务可以在同一个间隙上持有冲突锁。例如, 事务A可以在间隙上持有一个共享间隙锁(gap S-lock), 而事务B可以在同一间隙上持有排他间隙锁(gap X-lock)。允许冲突的间隙锁的原因是, 如果从索引中清除一条记录, 则必须将不同事务在该记录上持有的间隙锁进行合并。

InnoDB 中的间隙锁是“纯抑制性的(purely inhibitive)”, 唯一目的是阻止其他事务在间隙中插入。间隙锁可以共存(co-exist)。一个事务持有的间隙锁, 并不会阻止其他事务对相同的间隙进行锁定。共享间隙锁和排他间隙锁之间也没有区别。彼此不冲突, 作用也一样。

间隙锁可以被显式禁用。将事务隔离级别设置为 READ COMMITTED, 或者启用了系统变量 innodb_locks_unsafe_for_binlog(已废弃), 则会发生这种情况。间隙锁在搜索和索引扫描时会被禁用, 只用于外键约束检查和重复键检查。

使用 READ COMMITTED 隔离级别或启用 innodb_locks_unsafe_for_binlog 时还有其他效果。 MySQL在 WHERE 条件计算完成后, 会立即释放不匹配行的记录锁。对于 UPDATE 语句, InnoDB执行“半一致性读(semi-consistent)”, 将最新的提交版本返回给MySQL, 以便MySQL确定该行是否与 UPDATE 的 WHERE 条件匹配。

Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:

临键锁

临键锁(Next-Key Lock), 是索引记录锁加上前面的间隙锁组合而成的。

InnoDB行级锁的执行方式, 是搜索或扫描索引时, 会在遇到的索引记录上设置共享锁或互斥锁。因此, 行级锁本质上是索引记录锁。索引记录上的临键锁也会影响该索引记录之前的“间隙”。即: 临键锁=记录锁+间隙锁。如果一个会话(session)在索引记录R上持有共享临键锁或排他临键锁, 按照索引的排序方向, 其他会话无法在 R 之前的间隙中插入新的索引记录。

假定一个索引包含 10, 11, 13, 和 20 这4个值; 此时该索引的临键锁可以包括这些区间(interval):

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

其中, 小括号表示开区间, 不包含端点; 方括号表示闭区间,包括端点。

For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.

Transaction data for a next-key lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

最后一组区间, 临键锁的范围是: 大于当前的最大值, 一直到正无穷(positive infinity)。无穷大并不包含真正的索引记录, 实际上, 这个临键锁仅锁定当前最大索引值之后的间隙。

默认情况下, InnoDB 在 REPEATABLE READ 事务隔离级别下运行。在这种隔离级别下, InnoDB使用临键锁来进行搜索和索引扫描, 防止幻影行。

在 SHOW ENGINE INNODB STATUS 语句和 InnoDB monitor 输出的事务信息中, 临键锁类似这样:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     ''O;;
 2: len 7; hex b60000019d0110; asc        ;;

Insert Intention Locks

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

The following example demonstrates a transaction taking an insert intention lock prior to obtaining an exclusive lock on the inserted record. The example involves two clients, A and B.

Client A creates a table containing two index records (90 and 102) and then starts a transaction that places an exclusive lock on index records with an ID greater than 100. The exclusive lock includes a gap lock before record 102:

插入意向锁

插入意向锁(Insert Intention Lock)是在插入新行之前, 由 INSERT 操作设置的一种间隙锁。这个锁表明插入的意图信号, 执行方式为: 如果多个事务想在同一间隙中插入记录, 只要不在同一个位置, 则不需要阻塞或等待。例如, 索引中有2个值分别为 4 和 7。假设有两个事务, 分别插入 5 和 6时, 在获取要插入行的排他锁之前, 每个事务都使用插入意向锁来锁定4和7之间的间隙, 但他们不会互相阻塞, 因为具体的行是不冲突的。

下面通过示例演示了一个事务在获得插入记录的排他锁之前, 获取插入意向锁。这个示例涉及两个客户端: A和B。

A客户端创建一个的表, 包含两条索引记录(90 和 102), 然后开启事务, 将排他锁放置在ID大于100的索引记录上。排他锁包含一个在记录102之前的间隙锁:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

Client B begins a transaction to insert a record into the gap. The transaction takes an insert intention lock while it waits to obtain an exclusive lock.

B客户端开启事务后, 尝试将记录101插入间隙。在等待获得排他锁时, 会先获取插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

Transaction data for an insert intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

在 SHOW ENGINE INNODB STATUS 语句和 InnoDB monitor 输出的事务信息中, 插入意向锁类似这样:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     "" ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC Locks

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

For more information, see Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

自增锁

自增锁(AUTO-INC Lock)是一种特殊的表级锁, 具有 AUTO_INCREMENT 列的表, 由需要插入记录的事务获取。在最简陋的场景下, 如果一个事务正往表中插入值, 那么其他事务必须等待他完成之后才能往该表中插入新值, 以便第一个事务插入的行取得连续的自增主键值。

innodb_autoinc_lock_mode 选项, 用来控制自增锁的算法。允许我们自己权衡, 是选择可预测的自增序列呢, 还是选择insert操作的高并发性能。

更多信息请参考: Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”。

Predicate Locks for Spatial Indexes

InnoDB supports SPATIAL indexing of columns containing spatial columns (see Section 11.4.8, “Optimizing Spatial Analysis”).

To handle locking for operations involving SPATIAL indexes, next-key locking does not work well to support REPEATABLE READ or SERIALIZABLE transaction isolation levels. There is no absolute ordering concept in multidimensional data, so it is not clear which is the “next” key.

To enable support of isolation levels for tables with SPATIAL indexes, InnoDB uses predicate locks. A SPATIAL index contains minimum bounding rectangle (MBR) values, so InnoDB enforces consistent read on the index by setting a predicate lock on the MBR value used for a query. Other transactions cannot insert or modify a row that would match the query condition.

空间索引使用的谓词锁

InnoDB 支持地理空间列的 SPATIAL 索引。详情请参考 Section 11.4.8, “Optimizing Spatial Analysis”。

对 SPATIAL 索引记录上锁时, 临键锁并不能很好地支持 REPEATABLE READ 或 SERIALIZABLE 事务隔离级别。因为多维数据中没有绝对的排序顺序, 因此无法判定谁是 “下一个” 键值。

为了在事务隔离级别中支持具有 SPATIAL 索引的表, InnoDB使用了谓词锁(Predicate Lock)。 SPATIAL 索引记录包含MBR值(minimum bounding rectangle, 最小边界矩形), 因此 InnoDB 在匹配MBR值的索引记录上设置谓词锁, 来对索引强制执行一致性读。其他事务不能插入或修改匹配查询条件的行。

14.7.2 InnoDB Transaction Model

In the InnoDB transaction model, the goal is to combine the best properties of a multi-versioning database with traditional two-phase locking. InnoDB performs locking at the row level and runs queries as nonlocking consistent reads by default, in the style of Oracle. The lock information in InnoDB is stored space-efficiently so that lock escalation is not needed. Typically, several users are permitted to lock every row in InnoDB tables, or any random subset of the rows, without causing InnoDB memory exhaustion.

14.7.2 InnoDB事务模型

InnoDB的事务模型(transaction model), 目标是将多版本数据库(multi-versioning database)的最佳属性与传统的两阶段锁定(two-phase locking)相结合。默认情况下, InnoDB使用行级锁, 并以非锁定一致性读(nonlocking consistent read)的方式来执行查询, 类似Oracle数据库。 InnoDB中的锁信息, 以节省空间的方式存储, 因此不需要锁升级(lock escalation)。支持多个用户锁定InnoDB表中的每一行, 或者任意多行, 都不会让InnoDB的内存耗尽。

14.7.2.1 Transaction Isolation Levels

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.

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.

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”.

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.

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

14.7.2.1 事务隔离级别

事务隔离(Transaction isolation)是数据库的基础特征。隔离(Isolation)就是ACID中的I；隔离级别是一个可配置项, 用于在多个事务进行并发修改和并发查询时, 调节性能、可靠性(reliability)、一致性(consistency)和可重复性(reproducibility)之间的平衡。

InnoDB支持《SQL:1992标准》中定义的四个事务隔离级别:

READ UNCOMMITTED(读未提交),
READ COMMITTED(读已提交),
REPEATABLE READ(可重复读), InnoDB 默认的隔离级别。
SERIALIZABLE(串行化)。

用户可以改变当前会话的隔离级别(控制自己会话的可见性), 也可以更改后续所有连接的隔离级别, 使用 SET TRANSACTION 语句即可。要设置服务器的默认隔离级别, 可在命令行或配置文件中使用 --transaction-isolation 选项。设置隔离级别的详细信息, 请参见 Section 13.3.6, “SET TRANSACTION Statement”。

InnoDB对每个事务隔离级别使用不同的锁策略。

可使用默认的 REPEATABLE READ 级别来实现一致性, 比如 ACID 规范很重要的关键数据处理。
在批处理报表之类的场景下, 可以使用 READ COMMITTED 甚至 READ UNCOMMITTED 来放宽一致性约束, 这时候精确的一致性和可重复的结果, 相对来说不如降低锁的开销重要。
SERIALIZABLE 比 REPEATABLE READ 的限制更严格, 主要用于特殊情况, 例如 XA 事务, 或者对并发和死锁问题进行故障诊断和排查等场景。

下面对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”.

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.

【可重复读】是InnoDB的默认隔离级别。可重复读隔离级别, 同一事务中的一致性读, 使用第一次读取时创建的快照。这意味着, 在同一事务中执行多个普通的 SELECT语句(nonlocking), 则这些 SELECT 语句之间彼此是能保证一致性的。详情请查看 14.7.2.3 非锁定一致性读。

对于UPDATE语句, DELETE语句, 以及锁定读(locking read, 即 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE语句), 根据过滤条件是否使用了唯一索引, 还是使用范围条件来确定使用的锁:

对于使用了唯一索引的唯一查询条件, InnoDB只会锁定查找到的索引记录, 而不锁定前面的间隙。
对于其他查询条件, InnoDB 会锁定扫描到的索引范围, 通过间隙锁或临键锁来阻止其他会话在这个范围中插入新值。关于间隙锁和临键锁的信息, 请参考前面的内容: 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”.

For locking reads (SELECT ... FOR UPDATE or SELECT ... 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.

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.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.

Consider the following example, beginning with this table:

在【读已提交】隔离级别下, 即使在同一事务中, 每次一致性读都会设置和读取自己的新快照。有关一致性读的信息, 请参考 14.7.2.3 非锁定一致性读。

对于锁定读(SELECT ... FOR UPDATE 或者 SELECT ... LOCK IN SHARE MODE), UPDATE 语句和 DELETE 语句, InnoDB这时候仅锁定索引记录, 而不锁定它们之间的间隙, 因此, 其他事务可以在锁定记录旁边插入新记录。这时候间隙锁仅用于外键约束检查和重复键检查。

由于禁用了间隙锁, 有可能会产生幻读问题(phantom problem), 因为其他会话可能会在间隙中插入新行。有关幻读的信息, 请参考 14.7.4 幻影行。

READ COMMITTED 隔离级别仅支持基于行的bin-log。如果将 READ COMMITTED 与 binlog_format=MIXED 一起使用, 则服务器会自动切换到基于行的bin-log。

使用 READ COMMITTED 还会有其他效果:

对于UPDATE 和 DELETE语句, InnoDB仅持有需要更新或删除行的锁。 MySQL计算完 WHERE 条件后, 会释放不匹配行的记录锁。这大大降低了死锁的可能性, 但还是有可能会发生。

对于 UPDATE 语句, 如果某行已被锁定, 则InnoDB会执行半一致读(“semi-consistent” read), 将最新的提交版本返给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;

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.

Suppose that one session performs an UPDATE using these statements:

这种情况下, 因为没有索引, 所以查询和索引扫描时, 会使用隐藏的聚集索引来作为记录锁。详情请参考【Section 14.6.2.1, “Clustered and Secondary Indexes”】

假设某个会话A通过下面的语句执行更新操作

# 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:

这时会话A还没有提交事务, 接下来第二个会话B通过下面的语句执行更新操作:

# 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.

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:

InnoDB 执行 UPDATE 时, 会为其读取到的每一行先设置一个排他锁(exclusive lock), 然后再确定是否需要对其进行修改。如果 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

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:

因为第一个 UPDATE 在所有行上都保留了锁, 第二个 UPDATE 尝试获取任何一个锁时都会立即阻塞, 直到第一个UPDATE提交或回滚之后才能继续执行:

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锁, 然后释放不需要修改行上的X锁:

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 the WHERE condition of the UPDATE:

对于第二个UPDATE, InnoDB会执行半一致读(“semi-consistent” read), 将最新的提交版本返给MySQL, 让MySQL确定该行是否符合 UPDATE 的 WHERE条件:

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, 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 在所有 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;

The effects of using the READ COMMITTED isolation level are the same as enabling the deprecated innodb_locks_unsafe_for_binlog configuration option, 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语句以非锁定的方式执行, 但可能会用到某一行的早期版本。所以使用此隔离级别时, 不能保证读取的一致性, 这种现象称为脏读(dirty read)。其他情况下, 此隔离级别类似于 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 就单独在一个事务中。因此被认为是只读的, 如果以一致性非锁定读取方式执行, 不需要阻塞其他事务就可以实现串行化。

如果要强制普通的 SELECT 语句在其他事务修改选定行时进行阻塞等待, 请禁用 autocommit。

14.7.2.2 autocommit, Commit, and Rollback

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error. See Section 14.22.4, “InnoDB Error Handling”.

A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK statement. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

Some statements implicitly end a transaction, as if you had done a COMMIT before executing the statement. For details, see Section 13.3.3, “Statements That Cause an Implicit Commit”.

A COMMIT means that the changes made in the current transaction are made permanent and become visible to other sessions. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction. Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.

14.7.2.2 autocommit, 提交以及回滚

在InnoDB中, 所有用户活动都在事务中执行。如果启用了自动提交模式(autocommit), 则每条SQL语句都会自己形成一个事务。 MySQL中的每个新会话连接, 默认都是自动提交模式, 一个SQL语句如果没有产生错误, 则会在其执行完后自动提交。如果某条SQL语句返回错误, 则根据具体的错误来决定是提交还是回滚。详情请参考 Section 14.22.4, “InnoDB Error Handling”。

启用了自动提交的会话, 也可以执行多语句事务, 通过显式的 START TRANSACTION 或者 BEGIN 语句开始, 然后以 COMMIT 或者 ROLLBACK 语句结束。具体情况请参考 Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”。

如果通过 SET autocommit = 0 禁用了自动提交模式, 则该会话会始终有一个打开的事务。 COMMIT 或者 ROLLBACK 语句则会结束当前事务并开启一个新的事务。

禁用了自动提交模式的会话, 在没有明确提交事务的情况下, 如果连接断开或者会话结束, 则MySQL会执行事务回滚。

有些语句会隐式地结束事务, 效果类似于在这种语句之前自动增加了一条 COMMIT 语句。详细信息请参考Section 13.3.3, “Statements That Cause an Implicit Commit”。

COMMIT 表示需要将当前事务所做的更改进行持久化(permanent), 并对其他会话可见。而 ROLLBACK 语句则取消当前事务中的修改。 COMMIT 和 ROLLBACK 都会释放所有在当前事务期间设置的 InnoDB 锁。

Grouping DML Operations with Transactions

By default, connection to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it. This mode of operation might be unfamiliar if you have experience with other database systems, where it is standard practice to issue a sequence of DML statements and commit them or roll them back all together.

To use multiple-statement transactions, switch autocommit off with the SQL statement SET autocommit = 0 and end each transaction with COMMIT or ROLLBACK as appropriate. To leave autocommit on, begin each transaction with START TRANSACTION and end it with COMMIT or ROLLBACK. The following example shows two transactions. The first is committed; the second is rolled back.

DML操作分组和事务

MySQL数据库的客户端连接, 默认开启自动提交模式, 每个SQL语句执行完都会自动提交。用过其他数据库系统的用户, 可能对这种操作模式不太习惯, 因为他们更常用的方式, 是执行一连串的DML语句, 然后再一起提交, 或者一起回滚。

想要使用多语句事务:

可以通过 SET autocommit = 0 语句关闭自动提交模式, 并在适当的时机以 COMMIT 或者 ROLLBACK 结束事务。
处于自动提交状态, 可以通过 START TRANSACTION 开启一个事务, 并以 COMMIT 或者 ROLLBACK 结束。

下面通过示例来演示两个事务。其中, 第一个事务被提交；第二个事务被回滚。

# 连接数据库
shell> mysql test

执行的SQL:

-- 建表
mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- 在 autocommit 开启的状态下, 启动事务:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- 关闭 autocommit 状态; 启动事务:
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- 回滚事务:
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

Transactions in Client-Side Languages

In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as COMMIT to the MySQL server as strings just like any other SQL statements such as SELECT or INSERT. Some APIs also offer separate special transaction commit and rollback functions or methods.

客户端编程语言中的事务

在MySQL客户端API中, 例如 PHP, Perl DBI, JDBC, ODBC, 或者标准C调用接口, 可以将事务控制语句(如COMMIT)当做字符串发送给MySQL服务器, 就像普通的SQL语句(SELECT 和 INSERT)一样。某些API还单独提供了提交事务和回滚的函数/方法。

14.7.2.3 Consistent Nonlocking Reads

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement), InnoDB gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

14.7.2.3 非锁定一致性读

一致性读(consistent read), 意味着 InnoDB 通过多版本技术, 为一个查询呈现出某个时间点上的数据库快照。查询能看到这个时间点之前所有已提交事务的更改, 而看不到这个时间点之后新开的事务、或者未提交的事务所做的更改。例外是查询可以看到同一事务中前面执行的语句所做的更改。这种例外会引起一些异常: 如果更新了表中的某些行, 则 SELECT 将看到该行被更新之后的最新版本, 但其他的行可能看到的还是旧版本。如果其他会话也更新了这张表, 则这种异常意味着我们可能会看到某种并不存在的状态。

如果是默认的 REPEATABLE READ 隔离级别, 则同一事务中的所有一致读, 都会读取该事务中第一次读取时所创建的快照。可以提交当前事务, 并在此之后执行新的查询语句来获取最新的数据快照。

使用 READ COMMITTED 隔离级别时, 事务中的每次一致性读, 都会设置并读取自己的新快照。

在 READ COMMITTED 和 REPEATABLE READ 隔离级别下, 一致性读是InnoDB 处理 SELECT 语句的默认模式。一致性读不会在读取的表上设置任何锁, 所以在读取时, 其他会话可以自由对这些表执行修改。

使用默认的 REPEATABLE READ 隔离级别, 执行普通的 SELECT 一致性读时, InnoDB 会为当前事务指定一个时间点, 根据这个时间点来确定事务中的所有查询可以看到哪些数据。如果在这个给定的时间点之后, 另一个事务删除了一行数据并提交, 那么当前事务则看不到这一行已被删除。插入和更新操作的处理方式也是一样的。

Note

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

提示

数据库状态的快照适用于事务中的 SELECT 语句, 而不一定适用于DML语句(增删改)。如果插入或修改一些行, 稍后再提交事务, 则另一个并发的 REPEATABLE READ 事务中的 DELETE 或者 UPDATE 语句可能会影响准备提交(just-committed)的这些行, 虽然那个会话可能无法读取看到它们。如果某个事务确实更新或删除已经被另一个事务提交的行, 则这些更改对于当前事务而言来说会变得可见。例如下面的这种场景:

# 场景1
SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: 没有匹配的行. ::: 查不到
DELETE FROM t1 WHERE c1 = 'xyz';
-- 删除了被另一个事务提交的某些行... ::: 但确实会删除

# 场景2
SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: 没有匹配的行.::: 查不到
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: 比如, 另一个事务 txn2 刚刚提交了 10 行 'abc' 值. ::: 但确实更新了...
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: 本事务 txn1 这时候可以看到刚刚更新的行.

You can advance your timepoint by committing your transaction and then doing another SELECT or START TRANSACTION WITH CONSISTENT SNAPSHOT.

This is called multi-versioned concurrency control.

In the following example, session A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.

我们可以通过提交事务, 然后执行另一个 SELECT 或者 START TRANSACTION WITH CONSISTENT SNAPSHOT 语句来推进时间点。

这被称为多版本并发控制(multi-versioned concurrency control)。

在下面的示例中, session A只有在Session B提交了insert, 并且A也提交了事务之后, 才会看到B插入的行, 因此时间点比B的提交时间要更新。

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
时间序
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read:

如果要查看数据库的最新状态(freshest), 可以使用 READ COMMITTED隔离级别, 或者使用锁定读取:

SELECT * FROM t LOCK IN SHARE MODE;

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot. With LOCK IN SHARE MODE, a locking read occurs instead: A SELECT blocks until the transaction containing the freshest rows ends (see Section 14.7.2.4, “Locking Reads”.

Consistent read does not work over certain DDL statements:

Consistent read does not work over DROP TABLE, because MySQL cannot use a table that has been dropped and InnoDB destroys the table.
Consistent read does not work over ALTER TABLE operations that make a temporary copy of the original table and delete the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction's snapshot was taken. In this case, the transaction returns an error: ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.

The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE:

By default, InnoDB uses stronger locks in those statements and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
To perform a nonlocking read in such cases, enable the innodb_locks_unsafe_for_binlog option and set the isolation level of the transaction to READ UNCOMMITTED, READ COMMITTED, or REPEATABLE READ to avoid setting locks on rows read from the selected table.

使用 READ COMMITTED 隔离级别时, 事务中的每次一致性读都会设置并读取自己的新快照。带有 LOCK IN SHARE MODE 的 SELECT 语句, 会发生锁定读: SELECT 可能会被阻塞, 直到包含最新行的事务结束为止。请参考 14.7.2.4 锁定读。

一致性读不支持某些DDL语句:

一致性读不能在 DROP TABLE 时生效, 因为MySQL无法使用已删除的表, 而且 InnoDB 已经销毁了这张表。
一致性读不能在 ALTER TABLE 操作时生效, 因为这个操作会创建原始表的临时副本,并在构建临时副本之后删除原始表。在事务中重新执行一致性读时, 新表中的数据行是不可见的, 因为事务在获取快照时这些行还不存在。这种情况下, 会返回错误信息: ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.

对于没有指定 FOR UPDATE 或者 LOCK IN SHARE MODE 的各种查询, 其行为有所不同, 如 INSERT INTO ... SELECT, UPDATE ... (SELECT), 以及 CREATE TABLE ... SELECT:

默认情况下, InnoDB 在这些语句中使用更强的锁, 而 SELECT 部分的行为类似于 READ COMMITTED, 即使在同一事务中, 每次一致性读都会设置并读取自己的新快照。
要在这种情况下执行非锁定读取, 请启用 innodb_locks_unsafe_for_binlog 选项, 并将事务隔离级别设置为READ UNCOMMITTED, READ COMMITTED, 或者 REPEATABLE READ, 以避免在读取数据行时上锁。

14.7.2.4 Locking Reads

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety:

SELECT ... LOCK IN SHARE MODE

Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
SELECT ... FOR UPDATE

For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

These clauses are primarily useful when dealing with tree-structured or graph-structured data, either in a single table or split across multiple tables. You traverse edges or tree branches from one place to another, while reserving the right to come back and change any of these “pointer” values.

All locks set by LOCK IN SHARE MODE and FOR UPDATE queries are released when the transaction is committed or rolled back.

14.7.2.4 锁定读

如果在一个事务中, 先查询数据, 然后再insert或update相关数据, 则常规的 SELECT 语句并不能提供足够的保护。其他事务可以更新或删除我们刚刚查到的这些行。 InnoDB 支持两种类型的锁定读(Locking Read), 可以提供额外的安全性:

SELECT ... LOCK IN SHARE MODE

在读取到的所有行上设置共享锁。其他会话可以读取这些行, 但在当前事务结束之前都不能修改。在查询时, 如果某些行被其他尚未提交的事务修改了, 那么当前查询会被一直阻塞到那些事务结束, 然后使用最新的值。
SELECT ... FOR UPDATE

对于搜索到的索引记录, 锁定数据行以及所有关联的索引条目, 就如同对这些行执行了 UPDATE 语句一样。其他事务会被阻塞, 包括修改, 使用 SELECT ... LOCK IN SHARE MODE来读取, 甚至在某些隔离级别执行读操作时, 都会阻塞。一致性读会忽略读取视图中的记录上设置的任何锁。 (因为数据行的旧版本无法被锁定, 是通过记录的内存副本加上 undo logs 来重建的)。

这类子句在处理树结构(tree-structured)或图结构(graph-structured)的数据时非常有用, 不管是单张表还是多张表。我们可以先遍历一遍, 然后再去修改其中的某些记录。

当事务被提交或者回滚时, 由 LOCK IN SHARE MODE 和 FOR UPDATE 设置的锁都会被释放。

Note

Locking reads are only possible when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0.

A locking read clause in an outer statement does not lock the rows of a table in a nested subquery unless a locking read clause is also specified in the subquery. For example, the following statement does not lock rows in table t2.

注意

只有禁用自动提交, 才可能执行锁定读。(一般使用 START TRANSACTION 语句或者设置 autocommit=0来禁用自动提交)

执行嵌套语句查询时, 外部查询中的锁定读, 不会对子查询的数据行加锁, 除非子查询也指定了锁定读。例如, 下面的语句不会锁定 t2 表中的行。

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

To lock rows in table t2, add a locking read clause to the subquery:

要锁定 t2 表中的行, 需要在子查询中也进行锁定读:

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

Locking Read Examples

Suppose that you want to insert a new row into a table child, and make sure that the child row has a parent row in table parent. Your application code can ensure referential integrity throughout this sequence of operations.

First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT, without you being aware of it.

To avoid this potential issue, perform the SELECT using LOCK IN SHARE MODE:

锁定读使用示例

示例1:

假设需要在 child 表中插入新行, 但要确保 parent 表中具有对应的记录。在应用程序代码中, 可以通过以下顺序的操作, 来确保引用完整性。

首先, 使用一致性读来查询 parent 表以检查父记录是否存在。这样可以保证将数据安全地插入到 child 表吗？不能, 因为其他会话可能在我们不知道的情况下, 在 SELECT 和 INSERT 之间, 恰好把 parent 表中的那行数据给删了。

要避免这个潜在的BUG, 可以通过 LOCK IN SHARE MODE 来执行 SELECT:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

After the LOCK IN SHARE MODE query returns the parent 'Jones', you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.

在 LOCK IN SHARE MODE 查询返回 parent 记录 'Jones' 之后, 可以安全地将记录添加到child表中, 然后提交事务。其他事务如果试图获取 parent 表中对应数据行上的排他锁, 会被阻塞并需要等待我们完成操作之后才能继续, 也就是需要先等待这两张表中的数据处于一致状态。

示例2:

For another example, consider an integer counter field in a table CHILD_CODES, used to assign a unique identifier to each child added to table CHILD. Do not use either consistent read or a shared mode read to read the present value of the counter, because two users of the database could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the CHILD table.

Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.

To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:

又比如, CHILD_CODES 表中有一个整型的 counter_field 字段, 用来为 child 表中的每条记录分配唯一ID。我们不能使用一致性读或者共享模式来读取当前计数器的值, 因为这样会有多个客户端看到相同的值, 如果两个事务尝试使用相同的id来添加数据, 则会发生重复键错误(duplicate-key error)。

在这个场景下, LOCK IN SHARE MODE 不是一种好方案, 如果有多个用户同时读取计数器, 则其中至少有一个会在更新计数器时陷入死锁状态。

要读取计数器并实现递增, 需要先执行 FOR UPDATE 对计数器的锁定读, 然后再递增计数器。例如:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

The preceding description is merely an example of how SELECT ... FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

SELECT ... FOR UPDATE 会读取最新的可用数据, 并在读到的每一行上设置排他锁。因此, 它设置的锁与 UPDATE 语句设置的锁相同。

这个示例仅仅是为了演示 SELECT ... FOR UPDATE 的工作原理。在MySQL中, 生成唯一标识的任务, 实际上可以通过一次查询就能完成:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table.

SELECT 语句只会基于当前会话来查询id信息。而且不读任何一张表。

14.7.3 Locks Set by Different SQL Statements in InnoDB

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the “gap” immediately before the record. However, gap locking can be disabled explicitly, which causes next-key locking not to be used. For more information, see Section 14.7.1, “InnoDB Locking”. The transaction isolation level also can affect which locks are set; see Section 14.7.2.1, “Transaction Isolation Levels”.

If a secondary index is used in a search and index record locks to be set are exclusive, InnoDB also retrieves the corresponding clustered index records and sets locks on them.

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

InnoDB sets specific types of locks as follows.

14.7.3 InnoDB中不同SQL语句设置的锁

锁定读(locking read), UPDATE 和 DELETE 语句, 通常会在扫描到的索引记录(index record)上设置记录锁(record lock)。不管SQL语句中是否包含WHERE条件都会上锁。因为InnoDB并不会记录确切的WHERE条件, 只知道自己扫描了哪些索引范围。一般使用临键锁(next-key lock), 这样就可以阻塞(block)其他事务将新行插入到前面的间隙(gap)中。当然, 可以显式地禁用间隙锁(gap locking), 那也就不会使用临键锁。更多信息请参考前面的小节 14.7.1 InnoDB中的锁。事务隔离级别也会影响使用的锁；请参考 Section 14.7.2.1, “Transaction Isolation Levels”。

如果搜索中用到了二级索引, 并且要设置排他锁, InnoDB还会检索相应的聚集索引记录(clustered index record)并对其上锁。

如果没找到索引, 那么MySQL会进行全表扫描(scan the entire table), 表中的每一行都将被锁定, 从而阻塞其他会话对表的所有插入。所以创建良好的索引非常重要, 执行查询时就不需要去扫描很多不必要的行。

InnoDB为各种SQL语句设置的锁介绍如下:

SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a UNION, scanned (and locked) rows from a table might be inserted into a temporary table before evaluation whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution.
SELECT ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
SELECT ... FROM, 一致性读, 读取的是快照, 一般不上锁; 只有事务隔离级别是 SERIALIZABLE 才会上锁。对于 SERIALIZABLE 级别, 会在搜索到的索引记录上设置共享的临键锁。但是, 对于使用唯一索引来查询唯一行的SQL语句, 则只需要设置一个索引记录锁。
对于 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE, 会对扫描到的行上锁, 但不在结果集中的行一般会立即释放(比如不符合 WHERE 子句中的过滤条件)。但某些情况下行锁可能不会立即释放, 因为在查询执行期间, 可能会丢失结果行与其原始数据源之间的联系。例如, 在 UNION 语句中, 表中被扫描到(并锁定)的行在计算是否符合结果集之前, 可能会被插入到临时表中。在这种情况下, 临时表中的行与原始表中的行之间的关系会丢失, 所以要等查询执行完之后行锁才会被释放。
SELECT ... LOCK IN SHARE MODE 在搜索遇到的索引记录上设置共享临键锁。但如果是通过唯一索引来检索唯一行, 则只需要锁定单个索引记录。
SELECT ... FOR UPDATE sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

For index records the search encounters, SELECT ... FOR UPDATE blocks other sessions from doing SELECT ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.
UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
When UPDATE modifies a clustered index record, implicit locks are taken on affected secondary index records. The UPDATE operation also takes shared locks on affected secondary index records when performing duplicate check scans prior to inserting new secondary index records, and when inserting new secondary index records.
DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
SELECT ... FOR UPDATE 在搜索到的每条记录上设置排他临键锁。例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。

对于搜索遇到的索引记录, SELECT ... FOR UPDATE 会阻塞其他会话执行 SELECT ... LOCK IN SHARE MODE, 以及阻塞某些隔离级别的事务读取数据。一致性读将忽略 read view 中记录上设置的任何锁。
UPDATE ... WHERE ... 在搜索到的每条记录上设置排他临键锁。例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。
当 UPDATE 修改聚集索引记录时, 将对受影响的二级索引记录进行隐式锁定。在插入新的二级索引记录前执行重复项检查时, 以及在插入新的二级索引记录时, UPDATE 操作还会对受影响的二级索引记录设置共享锁。
DELETE FROM ... WHERE ... 在搜索到的每条记录上都设置排他临键锁。例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. Suppose that an InnoDB table t1 has the following structure:
INSERT 对插入的行设置排他锁。是索引记录锁, 而不是临键锁(即没有间隙锁), 不会阻止其他会话插入新行到前面的间隙中。

在插入新行前, 会设置一个插入意向间隙锁(insert intention gap lock)。发出插入意向的信号, 如果多个事务想要在同一个索引间隙中插入新记录, 只要不是同一个槽位, 则无需等待。假设索引记录的值分别为4和7。有两个事务如果分别想要插入5和6这两个值, 在获得排它锁之前, 每个事务都会先设置插入意向锁来锁定4到7之间的间隙, 但是彼此之间没有阻塞, 因为行没有冲突。

如果出现重复键错误(duplicate-key error), 则会在重复索引记录上设置一个共享锁。如果另一个会话已经获取到排它锁, 并且有多个会话想要插入同一行的话, 则这个共享锁可能会导致死锁。加入另一个会话删除了该行, 则会发生这种情况。例如InnoDB表t1具有以下结构:
```
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
```
Now suppose that three sessions perform the following operations in order:

假设有三个会话按顺序执行以下操作:

Session 1:
```
START TRANSACTION;
INSERT INTO t1 VALUES(1);
```
Session 2:
```
START TRANSACTION;
INSERT INTO t1 VALUES(1);
```
Session 3:
```
START TRANSACTION;
INSERT INTO t1 VALUES(1);
```
Session 1:
```
ROLLBACK;
```
The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

会话1的第一个操作获取该行的排他锁。会话2和会话3的操作都会产生重复键错误, 并请求该行的共享锁。当会话1回滚时, 会释放该行的排他锁, 而会话2和会话3排队等待共享锁的请求会被授予。这时候, 会话2和会话3就会发生死锁: 由于对方持有了共享锁, 所以两个会话都无法得到该行的排他锁。

A similar situation occurs if the table already contains a row with key value 1 and three sessions perform the following operations in order:

如果表中包含了键值为1的行, 并且三个会话按以下顺序执行, 也会发生类似的情况:

Session 1:
```
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
```
Session 2:
```
START TRANSACTION;
INSERT INTO t1 VALUES(1);
```
Session 3:
```
START TRANSACTION;
INSERT INTO t1 VALUES(1);
```
Session 1:
```
COMMIT;
```
The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 commits, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

会话1的第一个操作获取该行的排他锁。会话2和会话3的操作都会导引起重复键错误, 然后会请求该行的共享锁。会话1提交后, 释放该行的排他锁, 并授予会话2和会话3排队请求的共享锁。这时候, 会话2和会话3就会发生死锁: 由于对方持有了共享锁, 所以两个会话都无法得到该行的排他锁。
INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.
REPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row to be replaced.
INSERT ... ON DUPLICATE KEY UPDATE 和简单的 INSERT 语句不同, 在发生重复键错误时, 会在要更新的行上设置排他锁, 而不是共享锁。对重复的主键值(primary key value)采用排他索引记录锁。对重复的唯一键值(unique key value)设置排他临键锁。
如果唯一键上没有冲突, 则 REPLACE 的处理方式和 INSERT 一样。如果有冲突, 则会在要替换的行上设置排他临键锁。
INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.

CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.
INSERT INTO T SELECT ... FROM S WHERE ... 在插入T的每一行上设置排他记录锁(不带间隙锁)。如果事务隔离级别为READ COMMITTED, 或者事务隔离级别不是 SERIALIZABLE但启用了 innodb_locks_unsafe_for_binlog, 则InnoDB会对S表进行搜索, 以使其保持一致性读(无锁)。其他情况下, InnoDB 会在 S 的行上设置共享临键锁, 为什么必须设置锁呢? 原因是使用基于语句的 bin-log 进行前滚恢复时, 必须以和原始操作完全相同的方式来执行每个SQL语句。

CREATE TABLE ... SELECT ... 使用共享临键锁, 或使用一致性读来执行SELECT, 类似于 INSERT ... SELECT。

使用 SELECT 来构造 REPLACE INTO t SELECT ... FROM s WHERE ... 或者 UPDATE t ... WHERE col IN (SELECT ... FROM s ...)时, InnoDB在 s 表的行上设置共享临键锁。
InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column while initializing a previously specified AUTO_INCREMENT column on a table.

With innodb_autoinc_lock_mode=0, InnoDB uses a special AUTO-INC table lock mode where the lock is obtained and held to the end of the current SQL statement (not to the end of the entire transaction) while accessing the auto-increment counter. Other clients cannot insert into the table while the AUTO-INC table lock is held. The same behavior occurs for “bulk inserts” with innodb_autoinc_lock_mode=1. Table-level AUTO-INC locks are not used with innodb_autoinc_lock_mode=2. For more information, See Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

InnoDB fetches the value of a previously initialized AUTO_INCREMENT column without setting any locks.
在指定了 AUTO_INCREMENT 属性列的表上初始化数据时, InnoDB 会在关联的索引末尾设置排他锁。

在 innodb_autoinc_lock_mode=0 的情况下, InnoDB 使用一种特殊的表锁定模式 AUTO-INC, 在这种模式下, 访问 auto-increment 计数器时, 需要获取锁并保持到当前SQL语句结束(不是整个事务)。持有 AUTO-INC 表锁时, 其他客户端无法插入该表。而对于 innodb_autoinc_lock_mode=1 的批量插入, 也会发生相同的行为。表级锁 AUTO-INC 与 innodb_autoinc_lock_mode=2 不能一起使用。更多信息请参考 Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”。

在获取先前初始化的 AUTO_INCREMENT 列值时, InnoDB不设置任何锁。
If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.
LOCK TABLES sets table locks, but it is the higher MySQL layer above the InnoDB layer that sets these locks. InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above InnoDB knows about row-level locks.

Otherwise, InnoDB's automatic deadlock detection cannot detect deadlocks where such table locks are involved. Also, because in this case the higher MySQL layer does not know about row-level locks, it is possible to get a table lock on a table where another session currently has row-level locks. However, this does not endanger transaction integrity, as discussed in Section 14.7.5.2, “Deadlock Detection”.
如果定义了 FOREIGN KEY 约束, 那么所有 insert, update, 以及 delete 都需要检查约束条件, 设置共享记录锁。在约束检查失败的情况下, InnoDB也会设置锁。
LOCK TABLES 设置表锁, 但设置的是比 InnoDB 更高层级的 MySQL 锁。如果是默认值 innodb_table_locks = 1, 并且 autocommit = 0, 则 InnoDB 能感知到表锁, MySQL层级也会感知到行级锁。

否则, InnoDB的自动死锁检测就无法探测到涉及这类表锁的死锁。同样, 在这种情况下, 上层的MySQL也感知不到行级锁, 可能会对其他会话持有行级锁的表中去设置表锁。但这并不影响事务完整性, 如 14.7.5.2 死锁检测所述。
LOCK TABLES acquires two locks on each table if innodb_table_locks=1 (the default). In addition to a table lock on the MySQL layer, it also acquires an InnoDB table lock. Versions of MySQL before 4.1.2 did not acquire InnoDB table locks; the old behavior can be selected by setting innodb_table_locks=0. If no InnoDB table lock is acquired, LOCK TABLES completes even if some records of the tables are being locked by other transactions.

In MySQL 5.7, innodb_table_locks=0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.
All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in autocommit=1 mode because the acquired InnoDB table locks would be released immediately.
You cannot lock additional tables in the middle of a transaction because LOCK TABLES performs an implicit COMMIT and UNLOCK TABLES.
如果是默认的 innodb_table_locks=1, 那么 LOCK TABLES 会在每张表上获取两个锁。除了MySQL层级的表锁外, 还获取一个 InnoDB 表锁。 MySQL 4.1.2 之前的版本不会获取 InnoDB 表锁；老版本的行为可指定 innodb_table_locks=0 来模拟。如果不获取 InnoDB 表锁, 表中的记录如果被其他事务锁定, LOCK TABLES 也会执行成功。

在MySQL 5.7中, innodb_table_locks=0 对于用 LOCK TABLES ... WRITE 显式锁定的表不起作用。但对于读模式的表锁, 以及通过 LOCK TABLES ... READ, LOCK TABLES ... WRITE(比如触发器) 隐式触发的读锁/写锁则起作用。
事务提交或中止时, 会释放其持有的所有 InnoDB 锁。所以在 autocommit=1 模式下, 对InnoDB表执行 LOCK TABLES 并没有什么意义, 因为获取的 InnoDB 表锁立即就会释放掉。
在事务执行过程中无法锁定其他表, 因为 LOCK TABLES 会执行隐式的 COMMIT' 和 UNLOCK TABLES` 。

14.7.4 Phantom Rows

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a "phantom" row.

Suppose that there is an index on the id column of the child table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:

14.7.4 幻影行

同一个事务中, 在不同的时间点执行相同的查询语句, 如果得到不同的结果集, 这种现象被称为幻读(phantom problem)。示例: 同一个 SELECT语句执行两次, 但第二次返回的结果比第一次查询多出了1行, 那么这1行就是 "幻影行(Phantom Row)"。

假设 child 表的 id 列有索引, 查询所有id值大于100 的行并进行锁定, 以便稍后进行更新:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

The query scans the index starting from the first record where id is bigger than 100. Let the table contain rows having id values of 90 and 102. If the locks set on the index records in the scanned range do not lock out inserts made in the gaps (in this case, the gap between 90 and 102), another session can insert a new row into the table with an id of 101. If you were to execute the same SELECT within the same transaction, you would see a new row with an id of 101 (a "phantom") in the result set returned by the query. If we regard a set of rows as a data item, the new phantom child would violate the isolation principle of transactions that a transaction should be able to run so that the data it has read does not change during the transaction.

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the "gap" before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

这个查询从第一条id值大于100的记录开始扫描索引。如果表中有两行数据的id值为90和102, 在扫描范围内, 假如没有锁住(90 到 102 之间的)间隙的话, 其他会话就可能在表中插入一个id值为101的新行。在同一事务中再次执行相同的 SELECT 语句, 则查询返回的结果中会看到一个id为101的新行, 这就是幻影行。如果将这种行视为数据项, 那么这条新的幻影数据将违反事务隔离原则: 已读取的数据在事务执行过程中不能被修改。

为了防止产生幻读, InnoDB 使用了一种叫做 "临键锁(next-key locking)" 的算法, 该算法组合使用了行锁(index-row locking)和间隙锁(gap locking)。 InnoDB 行级锁的执行方式, 是搜索或扫描索引时, 会在遇到的索引记录上设置共享锁(shared lock)或排他锁(exclusive lock)。因此, 行级锁本质上是索引记录锁(index-record lock)。此外, 索引记录上的临键锁还会影响该索引记录前面的"间隙"。即, 临键锁, 是索引记录锁, 加上索引记录之前的间隙锁。如果一个会话在索引记录 R 上设置了临键锁(共享锁或排他锁), 按照索引的排序顺序, 其他会话不能在紧邻 R 之前的间隙中插入新的索引记录。

When InnoDB scans an index, it can also lock the gap after the last record in the index. Just that happens in the preceding example: To prevent any insert into the table where id would be bigger than 100, the locks set by InnoDB include a lock on the gap following id value 102.

You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table.

Gap locking can be disabled as discussed in Section 14.7.1, “InnoDB Locking”. This may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled.

InnoDB扫描索引时, 也可能会锁定索引中最后一条记录后面的间隙。前面的示例中我们演示了这种情况: 为了防止在表中插入 id 大于100的记录, InnoDB 设置的锁包括了 id 在 102 之后的间隙锁。

我们也可以用临键锁来实现唯一性检查: 以共享模式读取数据时, 如果没有看到要插入的行存在重复项, 则可以安全地插入行, 因为在读取时设置的临键锁, 可以防止其他会话在后面插入重复项。事实上, 临键锁可以“锁定”表中并不存在的内容。

禁用间隙锁的方式请参考 14.7.1 InnoDB中的锁。但可能会导致幻读问题, 因为禁用间隙锁之后, 其他会话有可能在间隙中插入新行。

14.7.5 Deadlocks in InnoDB

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.

A deadlock can occur when transactions lock rows in multiple tables (through statements such as UPDATE or SELECT ... FOR UPDATE), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue. For a deadlock example, see Section 14.7.5.1, “An InnoDB Deadlock Example”.

14.7.5 InnoDB中的死锁

死锁是指多个事务由于互相持有对方需要的锁, 谁也无法继续往下执行的情况。因为都在等待资源, 谁都不会释放自己持有的锁。

比如通过 UPDATE 或者 SELECT ... FOR UPDATE 之类的语句, 锁定多张表或者多个行时, 如果以相反的顺序来执行, 就可能会发生死锁。如果SQL语句需要锁定索引范围、或者锁定间隙时, 由于时序问题, 每个事务都只获取到了一部分锁时, 也会发生死锁。有关死锁的示例, 请参考下面的小节 14.7.5.1 InnoDB死锁示例。

To reduce the possibility of deadlocks, use transactions rather than LOCK TABLES statements; keep transactions that insert or update data small enough that they do not stay open for long periods of time; when different transactions update multiple tables or large ranges of rows, use the same order of operations (such as SELECT ... FOR UPDATE) in each transaction; create indexes on the columns used in SELECT ... FOR UPDATE and UPDATE ... WHERE statements. The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations. For more information about avoiding and recovering from deadlock conditions, see Section 14.7.5.3, “How to Minimize and Handle Deadlocks”.

When deadlock detection is enabled (the default) and a deadlock does occur, InnoDB detects the condition and rolls back one of the transactions (the victim). If deadlock detection is disabled using the innodb_deadlock_detect configuration option, InnoDB relies on the innodb_lock_wait_timeout setting to roll back transactions in case of a deadlock. Thus, even if your application logic is correct, you must still handle the case where a transaction must be retried. To see the last deadlock in an InnoDB user transaction, use the SHOW ENGINE INNODB STATUS command. If frequent deadlocks highlight a problem with transaction structure or application error handling, run with the innodb_print_all_deadlocks setting enabled to print information about all deadlocks to the mysqld error log. For more information about how deadlocks are automatically detected and handled, see Section 14.7.5.2, “Deadlock Detection”.

要减少死锁产生的可能性:

请使用事务, 尽量不要用 LOCK TABLES 语句。
让执行 insert 或 update 的事务足够小, 这样事务开启的时间不会太长；
不同的事务更新多张表或者大范围的行时, 让每个事务都保持相同的操作顺序, ；
在 SELECT ... FOR UPDATE 和 UPDATE ... WHERE 语句用到的列创建索引。

产生死锁的可能性不受隔离级别的影响, 因为隔离级别只是改变了读取操作的行为, 而死锁则是由于写操作发生的。关于如何避免死锁, 以及怎样从死锁条件中恢复, 请参考 14.7.5.3 减少死锁以及死锁处理办法。

启用(默认开启的)死锁检测(deadlock detection)时, InnoDB会自动检测到哪里产生了死锁, 并自动回滚其中的一个事务(称为受害方, victim)。如果使用 innodb_deadlock_detect 选项禁用了自动死锁检测, 则 InnoDB 只能通过 innodb_lock_wait_timeout 指定的超时时间来回滚事务。即使应用程序逻辑是完全正确的, 也需要处理事务重试等情况。我们可以使用 SHOW ENGINE INNODB STATUS 命令查看最近发生死锁的事务。如果频繁发生死锁问题, 需要进行事务结构调整, 或者需要进行错误处理时, 可以对 mysqld 的启动参数指定 innodb_print_all_deadlocks 选项, 以将死锁相关的全部信息打印到错误日志中。

关于如何进行自动死锁检测和处理的信息, 可参考 14.7.5.2 死锁检测。

14.7.5.1 An InnoDB Deadlock Example

The following example illustrates how an error can occur when a lock request would cause a deadlock. The example involves two clients, A and B.

First, client A creates a table containing one row, and then begins a transaction. Within the transaction, A obtains an S lock on the row by selecting it in share mode:

14.7.5.1 InnoDB死锁示例

下面通过示例来演示导致死锁时会发生怎样的错误。这个示例中涉及两个客户端: A和B。

首先, 客户端A创建了一张表, 并插入一条数据, 然后开启事务。在事务中, 客户端A通过共享模式查询,来获得该行的 S 锁：

# 客户端A
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

Next, client B begins a transaction and attempts to delete the row from the table:

接下来，客户端B开启事务, 并尝试从表中删除这一行：

# 客户端B
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

The delete operation requires an X lock. The lock cannot be granted because it is incompatible with the S lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks.

Finally, client A also attempts to delete the row from the table:

删除操作需要获取X锁。但由于X锁与A客户端持有的S锁不兼容, 无法立即得到授权, 需要加入该行的锁请求等待队列进行排队, 客户端B因此被阻塞。

然后，客户端A也尝试从表中删除该行：

# 客户端A
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. As a result, InnoDB generates an error for one of the clients and releases its locks. The client returns this error:

可以看到这里发生了死锁, 因为客户端A需要先获取X锁才能删除该行。但由于客户端B请求 X 锁, 正在等待客户端A释放S锁, 所以客户端A的X锁请求不能被授予。而且是B客户端先请求的 X 锁, 导致A持有的S锁也不能升级为X锁。结果就是 InnoDB 让其中一个客户端产生错误, 并释放其持有的锁。客户端返回的错误信息类似这样：

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

At that point, the lock request for the other client can be granted and it deletes the row from the table.

然后, 另一个客户端的锁请求会被授予, 接着执行, 从表中删除这一行数据。

14.7.5.2 Deadlock Detection

When deadlock detection is enabled (the default), InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.

InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above it knows about row-level locks. Otherwise, InnoDB cannot detect deadlocks where a table lock set by a MySQL LOCK TABLES statement or a lock set by a storage engine other than InnoDB is involved. Resolve these situations by setting the value of the innodb_lock_wait_timeout system variable.

If the LATEST DETECTED DEADLOCK section of InnoDB Monitor output includes a message stating, “TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,” this indicates that the number of transactions on the wait-for list has reached a limit of 200. A wait-for list that exceeds 200 transactions is treated as a deadlock and the transaction attempting to check the wait-for list is rolled back. The same error may also occur if the locking thread must look at more than 1,000,000 locks owned by transactions on the wait-for list.

For techniques to organize database operations to avoid deadlocks, see Section 14.7.5, “Deadlocks in InnoDB”.

14.7.5.2 死锁检测

InnoDB 默认会开启死锁检测(deadlock detection), 能自动检测到事务产生的“死锁”, 并自动回滚其中的一个或多个事务以打破死锁状态。 InnoDB 会尝试选择回滚较小的事务, 至于事务的大小判断, 则取决于已经 inserted, updated, 和 deleted 的行数。

默认情况下, innodb_table_locks = 1, 如果 autocommit = 0, InnoDB会感知到表锁, 上层的MySQL也能感知行级锁。否则, 如果涉及到 MySQL LOCK TABLES 语句设置的表锁, 或者由其他存储引擎设置的锁, 那么 InnoDB 无法自动检测到死锁。只能通过系统变量 innodb_lock_wait_timeout 设置的超时时间来解决这类情况。

如果 InnoDB Monitor 输出的 LATEST DETECTED DEADLOCK 一节中包含了这样的信息: “TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,” 就表明在等待列表中的事务数量达到了200个的限制。超过200个事务的等待列表将被视为死锁, 尝试检查等待列表的事务将被回滚。如果等待列表中的事务持有了超过 100万个以上的锁, 还有锁线程要来检查, 也可能会发生相同的错误。

关于如何进行数据库操作以避免死锁的技术, 请参考前面的 14.7.5 InnoDB中的死锁。

Disabling Deadlock Detection

On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

禁用死锁检测

可以使用 innodb_deadlock_detect 选项来禁用死锁检测。

在高并发系统中, 多个线程等待同一个锁时, 死锁检测会导致响应速度变慢。有时候, 依靠 innodb_lock_wait_timeout 指定的超时时间来进行事务回滚, 可能比自动死锁检测的效率更高。

14.7.5.3 How to Minimize and Handle Deadlocks

This section builds on the conceptual information about deadlocks in Section 14.7.5.2, “Deadlock Detection”. It explains how to organize database operations to minimize deadlocks and the subsequent error handling required in applications.

Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

InnoDB uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted.

You can cope with deadlocks and reduce the likelihood of their occurrence with the following techniques:

14.7.5.3 降低死锁概率, 处理死锁错误

基于上一小节 14.7.5.2 死锁检测中介绍的概念为基础。下面介绍如何组织我们的数据库操作, 以最大程度地减少死锁, 以及应用程序中如何对死锁错误进行后续处理。

死锁是事务型关系数据库中的典型问题, 但死锁并不可怕, 除非是频繁发生死锁而导致无法执行某些事务。通常, 由于死锁错误导致事务发生回滚时, 我们的应用程序需要重新执行这个事务【有些业务可以由人工触发】。

InnoDB 使用自动行级锁。即使在插入或删除单行数据的事务中, 也可能会产生死锁。因为这些操作并不是真正的“原子”操作；插入或删除行对应的(一到多个)索引记录时, 数据库会自动上锁。

下面介绍的技术手段可以用来处理死锁, 并降低产生死锁的可能性：

At any time, issue the SHOW ENGINE INNODB STATUS command to determine the cause of the most recent deadlock. That can help you to tune your application to avoid deadlocks.
If frequent deadlock warnings cause concern, collect more extensive debugging information by enabling the innodb_print_all_deadlocks configuration option. Information about each deadlock, not just the latest one, is recorded in the MySQL error log. Disable this option when you are finished debugging.
Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.
随时通过 SHOW ENGINE INNODB STATUS 命令来查看最近死锁的原因。可以帮助我们调整应用程序以避免死锁。
如果频繁发生死锁警告, 请启用 innodb_print_all_deadlocks 配置选项来收集更多 DEBUG 信息。在MySQL “错误日志”中输出每一次死锁相关的信息。调试完成后, 记得禁用此选项。
如果由于死锁而导致事务失败, 请务必重新执行事务。死锁并不可怕, 一般来说再试一次就行。
Keep transactions small and short in duration to make them less prone to collision.
Commit transactions immediately after making a set of related changes to make them less prone to collision. In particular, do not leave an interactive mysql session open for a long time with an uncommitted transaction.
If you use locking reads (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE), try using a lower isolation level such as READ COMMITTED.
保持事务小巧, 让事务持续的时间更短, 以减少发生冲突的可能性。
进行数据库更改后, 及时提交事务, 减少发生冲突的可能。特别是, 不要让长时间交互的 mysql 会话保持打开状态却不提交事务。
如果使用锁定读(SELECT ... FOR UPDATE 或者 SELECT ... LOCK IN SHARE MODE), 可以尝试切换到较低的隔离级别, 例如READ COMMITTED。
When modifying multiple tables within a transaction, or different sets of rows in the same table, do those operations in a consistent order each time. Then transactions form well-defined queues and do not deadlock. For example, organize database operations into functions within your application, or call stored routines, rather than coding multiple similar sequences of INSERT, UPDATE, and DELETE statements in different places.
Add well-chosen indexes to your tables. Then your queries need to scan fewer index records and consequently set fewer locks. Use EXPLAIN SELECT to determine which indexes the MySQL server regards as the most appropriate for your queries.
Use less locking. If you can afford to permit a SELECT to return data from an old snapshot, do not add the clause FOR UPDATE or LOCK IN SHARE MODE to it. Using the READ COMMITTED isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot.
如果在一个事务中修改多张表, 或者多组数据, 那么每次都以一致的顺序来执行这些操作。这样事务可以形成定义明确的队列, 并且不会死锁。例如, 将数据库操作封装到特定的函数或服务方法中, 或者调用保存服务, 而不要在多个地方编写零散的 INSERT, UPDATE 和 DELETE 语句。
合理添加索引。这样我们的SQL查询就只需要扫描很少的索引记录, 上锁的记录也就更少。可以使用 EXPLAIN SELECT 来确定MySQL服务器会默认使用哪个索引来执行SQL查询。
少加锁。如果可以从旧版本快照中读取数据, 就没必要使用 FOR UPDATE 或者 LOCK IN SHARE MODE 子句。经常发生死锁的话, 使用 READ COMMITTED 隔离级别会比较好, 因为同一事务中的每次一致性读, 都是从自己的新快照中读取。
If nothing else helps, serialize your transactions with table-level locks. The correct way to use LOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this:
如果没有其他办法, 那就用表级锁来让我们的事务串行化。在InnoDB这种支持事务的存储引擎中, 使用 LOCK TABLES 的正确方法是: 先通过 SET autocommit = 0(而不是 START TRANSACTION)开启事务, 然后在事务中执行 LOCK TABLES, 直到明确提交事务之后再调用 UNLOCK TABLES。例如, 需要从表t2中读取数据, 并写入表t1, 则可以按下面这种顺序来执行：
```
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
```
Table-level locks prevent concurrent updates to the table, avoiding deadlocks at the expense of less responsiveness for a busy system.

表级锁可以防止其他会话对这张表进行并发更新, 也就避免了死锁, 但代价是对高负载的系统来说, 响应速度会变慢。
Another way to serialize transactions is to create an auxiliary “semaphore” table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion. Note that the InnoDB instant deadlock detection algorithm also works in this case, because the serializing lock is a row-level lock. With MySQL table-level locks, the timeout method must be used to resolve deadlocks.
让事务串行化的另一种办法, 是创建一张“信号量(semaphore)” 辅助表, 里面只包含一行数据。在读写其他表之前, 每个事务都要先更新这行数据。这样也能保证所有事务以串行方式执行。注意, 这种情况下, InnoDB 的死锁检测算法也会生效, 因为这种序列化操作对应的是行级锁。使用 MySQL表锁时, 就只能通过超时来解决死锁问题了。

Files

14.7_innodb-locking-transaction-model.md

Latest commit

History

14.7_innodb-locking-transaction-model.md

File metadata and controls

14.7 InnoDB Locking and Transaction Model

14.7 InnoDB的锁和事务模型

14.7.1 InnoDB Locking

Shared and Exclusive Locks

共享锁与排他锁

Intention Locks

意向锁

Record Locks

记录锁

Gap Locks

间隙锁

Next-Key Locks

临键锁

Insert Intention Locks

插入意向锁

AUTO-INC Locks

自增锁

Predicate Locks for Spatial Indexes

空间索引使用的谓词锁

14.7.2 InnoDB Transaction Model

14.7.2 InnoDB事务模型

14.7.2.1 Transaction Isolation Levels

14.7.2.1 事务隔离级别

REPEATABLE READ

READ COMMITTED

READ UNCOMMITTED

SERIALIZABLE

14.7.2.2 autocommit, Commit, and Rollback

14.7.2.2 autocommit, 提交以及回滚

Grouping DML Operations with Transactions

DML操作分组和事务

Transactions in Client-Side Languages

客户端编程语言中的事务

14.7.2.3 Consistent Nonlocking Reads

14.7.2.3 非锁定一致性读

Note

提示

14.7.2.4 Locking Reads

14.7.2.4 锁定读

Note

注意

Locking Read Examples

锁定读使用示例

14.7.3 Locks Set by Different SQL Statements in InnoDB

14.7.3 InnoDB中不同SQL语句设置的锁

14.7.4 Phantom Rows

14.7.4 幻影行

14.7.5 Deadlocks in InnoDB

14.7.5 InnoDB中的死锁

14.7.5.1 An InnoDB Deadlock Example

14.7.5.1 InnoDB死锁示例

14.7.5.2 Deadlock Detection

14.7.5.2 死锁检测

Disabling Deadlock Detection

禁用死锁检测

14.7.5.3 How to Minimize and Handle Deadlocks

14.7.5.3 降低死锁概率, 处理死锁错误

相关链接

`REPEATABLE READ`

`READ COMMITTED`

`READ UNCOMMITTED`

`SERIALIZABLE`