14.7 InnoDB的锁和事务模型

路过的小伙伴, 请帮忙点小星星Star支持: https://github.com/cncounter/translation/

InnoDB的锁和事务模型 - Github版本地址

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

[TOC]

14.7.1 InnoDB中的锁

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

共享锁与排他锁

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 的锁。

意向锁

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锁。

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

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

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

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

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

记录锁

记录锁(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 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;

如果 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 Lock), 是索引记录锁加上前面的间隙锁组合而成的。

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

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

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

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

最后一组区间, 临键锁的范围是: 大于当前的最大值, 一直到正无穷(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 Lock)是在插入新行之前, 由 INSERT 操作设置的一种间隙锁。 这个锁表明插入的意图信号, 执行方式为: 如果多个事务想在同一间隙中插入记录, 只要不在同一个位置, 则不需要阻塞或等待。 例如, 索引中有2个值分别为 4 和 7。 假设有两个事务, 分别插入 5 和 6时, 在获取要插入行的排他锁之前, 每个事务都使用插入意向锁来锁定4和7之间的间隙, 但他们不会互相阻塞, 因为具体的行是不冲突的。

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

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

# A客户端
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 |
+-----+

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

# B客户端
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

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

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

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

空间索引使用的谓词锁

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事务模型

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)是数据库的基础特征。 隔离(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

【可重复读】是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

在【读已提交】隔离级别下, 即使在同一事务中, 每次一致性读都会设置和读取自己的新快照。 有关一致性读的信息, 请参考 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;

这种情况下, 因为没有索引, 所以查询和索引扫描时, 会使用隐藏的聚集索引来作为记录锁。

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

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

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

# 会话B
UPDATE t SET b = 4 WHERE b = 2;

InnoDB 执行 UPDATE 时, 会为其读取到的每一行先设置一个排他锁(exclusive lock), 然后再确定是否需要对其进行修改。 如果 InnoDB不需要修改, 则会释放该行的锁。 否则, InnoDB将保留这个行锁直到事务结束。 这会影响事务的处理过程, 如下所示。

假如使用默认的 REPEATABLE READ 隔离级别时, 第一个 UPDATE 会先在其扫描读取到的每一行上设置X锁, 并且不会释放任何一个:

# REPEATABLE READ 隔离级别; 会话A
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

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

# REPEATABLE READ 隔离级别; 会话B
x-lock(1,2); block and wait for first UPDATE to commit or roll back

如果使用 READ COMMITTED 隔离级别, 则第一个 UPDATE 会在扫描读取到的每一行上获取X锁, 然后释放不需要修改行上的X锁:

# READ COMMITTED 隔离级别; 会话A
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

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

# READ COMMITTED 隔离级别; 会话B
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

但是, 如果 WHERE 条件中包括了索引列, 并且 InnoDB 使用了这个索引, 则获取和保留记录锁时只考虑索引列。 在下面的示例中, 第一个 UPDATE 在所有 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;

# 会话 A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# 会话 B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

使用 READ COMMITTED 隔离级别, 与设置 innodb_locks_unsafe_for_binlog 选项的效果基本一样【该选项已废弃】, 但也有一些不同:

• innodb_locks_unsafe_for_binlog 是一个全局设置, 会影响所有会话, 而隔离级别既可以对所有会话进行全局设置, 也可以对每个会话单独设置。
• innodb_locks_unsafe_for_binlog 只能在服务器启动时设置, 而隔离级别可以在启动时设置, 也可以在运行过程中更改。

因此, READ COMMITTED 比 innodb_locks_unsafe_for_binlog 更方便, 也更灵活。

READ UNCOMMITTED

【读未提交】隔离级别下, SELECT语句以非锁定的方式执行, 但可能会用到某一行的早期版本。 所以使用此隔离级别时, 不能保证读取的一致性, 这种现象称为脏读(dirty read)。 其他情况下, 此隔离级别类似于 READ COMMITTED。

SERIALIZABLE

【串行化】这个隔离级别类似于 REPEATABLE READ, 如果禁用了 autocommit, 则 InnoDB 会隐式地将所有普通的 SELECT 语句转换为 SELECT ... LOCK IN SHARE MODE。

如果启用了自动提交(autocommit), 则 SELECT 就单独在一个事务中。 因此被认为是只读的, 如果以一致性非锁定读取方式执行, 不需要阻塞其他事务就可以实现串行化。

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

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 锁。

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>

客户端编程语言中的事务

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

14.7.2.3 非锁定一致性读

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

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

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

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

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

提示

数据库状态的快照适用于事务中的 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 这时候可以看到刚刚更新的行.

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

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

在下面的示例中, 只有在 Session B 提交了事务, 并且Session A自己也提交了事务后, A才能看到B新增的行, 因为这时候A的时间点会比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    |
           ---------------------

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

SELECT * FROM t LOCK IN SHARE MODE;

使用 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 锁定读

如果在一个事务中, 先查询数据, 然后再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 设置的锁都会被释放。

注意

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

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

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

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

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

锁定读使用示例

示例1:

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

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

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

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

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

示例2:

又比如, 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;

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

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

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

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

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, 一致性读, 读取的是快照, 一般不上锁; 只有事务隔离级别是 SERIALIZABLE 才会上锁。 对于 SERIALIZABLE 级别, 会在搜索到的索引记录上设置共享的临键锁。 但是, 对于使用唯一索引来查询唯一行的SQL语句, 则只需要设置一个索引记录锁。

• 对于 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE, 会对扫描到的行上锁, 但不在结果集中的行一般会立即释放(比如不符合 WHERE 子句中的过滤条件)。 但某些情况下行锁可能不会立即释放, 因为在查询执行期间, 可能会丢失结果行与其原始数据源之间的联系。 例如, 在 UNION 语句中, 表中被扫描到(并锁定)的行在计算是否符合结果集之前, 可能会被插入到临时表中。在这种情况下, 临时表中的行与原始表中的行之间的关系会丢失, 所以要等查询执行完之后行锁才会被释放。

• SELECT ... LOCK IN SHARE MODE 在搜索遇到的索引记录上设置共享临键锁。 但如果是通过唯一索引来检索唯一行, 则只需要锁定单个索引记录。

• SELECT ... FOR UPDATE 在搜索到的每条记录上设置排他临键锁。 例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。

  对于搜索遇到的索引记录, SELECT ... FOR UPDATE 会阻塞其他会话执行 SELECT ... LOCK IN SHARE MODE, 以及阻塞某些隔离级别的事务读取数据。 一致性读将忽略 read view 中记录上设置的任何锁。

• UPDATE ... WHERE ... 在搜索到的每条记录上设置排他临键锁。 例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。

• 当 UPDATE 修改聚集索引记录时, 将对受影响的二级索引记录进行隐式锁定。 在插入新的二级索引记录前执行重复项检查时, 以及在插入新的二级索引记录时, UPDATE 操作还会对受影响的二级索引记录设置共享锁。

• DELETE FROM ... WHERE ... 在搜索到的每条记录上都设置排他临键锁。 例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。

• 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;

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

  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;

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

  如果表中包含了键值为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;

  会话1的第一个操作获取该行的排他锁。 会话2和会话3的操作都会导引起重复键错误, 然后会请求该行的共享锁。 会话1提交后, 释放该行的排他锁, 并授予会话2和会话3排队请求的共享锁。 这时候, 会话2和会话3就会发生死锁: 由于对方持有了共享锁, 所以两个会话都无法得到该行的排他锁。

• INSERT ... ON DUPLICATE KEY UPDATE 和简单的 INSERT 语句不同, 在发生重复键错误时, 会在要更新的行上设置排他锁, 而不是共享锁。 对重复的主键值(primary key value)采用排他索引记录锁。 对重复的唯一键值(unique key value)设置排他临键锁。

• 如果唯一键上没有冲突, 则 REPLACE 的处理方式和 INSERT 一样。 如果有冲突, 则会在要替换的行上设置排他临键锁。

• 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 表的行上设置共享临键锁。

• 在指定了 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不设置任何锁。

• 如果定义了 FOREIGN KEY 约束, 那么所有 insert, update, 以及 delete 都需要检查约束条件, 设置共享记录锁。 在约束检查失败的情况下, InnoDB也会设置锁。

• LOCK TABLES 设置表锁, 但设置的是比 InnoDB 更高层级的 MySQL 锁。 如果是默认值 innodb_table_locks = 1, 并且 autocommit = 0, 则 InnoDB 能感知到表锁, MySQL层级也会感知到行级锁。

  否则, InnoDB的自动死锁检测就无法探测到涉及这类表锁的死锁。 同样, 在这种情况下, 上层的MySQL也感知不到行级锁, 可能会对其他会话持有行级锁的表中去设置表锁。 但这并不影响事务完整性, 如 14.7.5.2 死锁检测 所述。

• 如果是默认的 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 problem)。 示例: 同一个 SELECT语句执行两次, 但第二次返回的结果比第一次查询多出了1行, 那么这1行就是 "幻影行(Phantom Row)"。

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

SELECT * FROM child WHERE id > 100 FOR UPDATE;

这个查询从第一条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 之前的间隙中插入新的索引记录。

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

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

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

14.7.5 InnoDB中的死锁

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

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

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

• 请使用事务, 尽量不要用 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 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 |
+------+

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

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

mysql> DELETE FROM t WHERE i = 1;

删除操作需要获取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

可以看到这里发生了死锁, 因为客户端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

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

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中的死锁。

禁用死锁检测

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

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

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

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

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

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

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

• 随时通过 SHOW ENGINE INNODB STATUS 命令来查看最近死锁的原因。 可以帮助我们调整应用程序以避免死锁。

• 如果频繁发生死锁警告, 请启用 innodb_print_all_deadlocks 配置选项来收集更多 DEBUG 信息。 在MySQL “错误日志”中输出每一次死锁相关的信息。 调试完成后, 记得禁用此选项。

• 如果由于死锁而导致事务失败, 请务必重新执行事务。 死锁并不可怕, 一般来说再试一次就行。

• 保持事务小巧, 让事务持续的时间更短, 以减少发生冲突的可能性。

• 进行数据库更改后, 及时提交事务, 减少发生冲突的可能。 特别是, 不要让长时间交互的 mysql 会话保持打开状态却不提交事务。

• 如果使用锁定读(SELECT ... FOR UPDATE 或者 SELECT ... LOCK IN SHARE MODE), 可以尝试切换到较低的隔离级别, 例如READ COMMITTED。

• 如果在一个事务中修改多张表, 或者多组数据, 那么每次都以一致的顺序来执行这些操作。 这样事务可以形成定义明确的队列, 并且不会死锁。 例如, 将数据库操作封装到特定的函数或服务方法中, 或者调用保存服务, 而不要在多个地方编写零散的 INSERT, UPDATE 和 DELETE 语句。

• 合理添加索引。 这样我们的SQL查询就只需要扫描很少的索引记录, 上锁的记录也就更少。 可以使用 EXPLAIN SELECT 来确定MySQL服务器会默认使用哪个索引来执行SQL查询。

• 少加锁。 如果可以从旧版本快照中读取数据, 就没必要使用 FOR UPDATE 或者 LOCK IN SHARE MODE 子句。 经常发生死锁的话, 使用 READ COMMITTED 隔离级别会比较好, 因为同一事务中的每次一致性读, 都是从自己的新快照中读取。

• 如果没有其他办法, 那就用表级锁来让我们的事务串行化。 在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;

  表级锁可以防止其他会话对这张表进行并发更新, 也就避免了死锁, 但代价是对高负载的系统来说, 响应速度会变慢。

• 让事务串行化的另一种办法, 是创建一张“信号量(semaphore)” 辅助表, 里面只包含一行数据。 在读写其他表之前, 每个事务都要先更新这行数据。 这样也能保证所有事务以串行方式执行。 注意, 这种情况下, InnoDB 的死锁检测算法也会生效, 因为这种序列化操作对应的是行级锁。 使用 MySQL表锁时, 就只能通过超时来解决死锁问题了。

相关链接

看到这里的小伙伴, 请帮忙点小星星Star支持: https://github.com/cncounter/translation/

• 14.7 InnoDB的锁和事务模型 - Github版
• InnoDB存储引擎官方文档-中文翻译-GitHub
• 官方英文文档 - 14.7 InnoDB Locking and Transaction Model