FOR UPDATE

FOR UPDATE 是 SELECT 语句的可选参数

MySQL :: MySQL 5.7 Reference Manual :: 13.2.9 SELECT Statement

如果将 FOR UPDATE 和支持页锁或行锁的数据库引擎一起使用（比如 InnoDB），被查询的行将被写锁定，直到当前事务结束使用 LOCK IN SHARE MODE 将设置共享锁，允许其他事务读取检查的行，但不更新或删除它们
当使用类似的语句 CREATE TABLE new_table SELECT ... FROM old_table 不能在 SELECT 中使用 FOR UPDATE

如果您尝试执行此操作，则该语句将被拒绝，并显示 Can't update table 'old_table' while 'new_table' is being created.

锁定读

MySQL :: MySQL 5.7 Reference Manual :: 14.7.2.4 Locking Reads

如果查询数据后希望在同一事务中插入或更新相关数据，则常规 SELECT 语句无法提供足够的保护，其他事务可以更新或删除您刚才查询的同一行

InnoDB 引擎提供了两种锁定读的方式来提供额外的安全保证：

SELECT ... LOCK IN SHARE MODE 在读取到的所有行上设置共享锁（shared mode lock）其他事务可以进行读取，但是在事务提交之前都不能进行修改，如果查询过程中这些行中的任意行被另一个尚未提交的事务修改，则查询将等待该事务结束，然后使用更新后的值
SELECT ... FOR UPDATE 对于查询到的索引记录，锁定行和任何关联的索引条目，就像为这些行进行 UPDATE 语句一样对其他事务被阻止更新这些行、进行查询、在共享模式下锁定或读取特定事务隔离级别的数据一致读（快照读）将忽略在读取视图中存在的记录上设置的任何锁（记录的旧版本无法锁定，因为它们是通过在记录的内存副本上应用 undo log 来重建的）

这些子句在处理树结构或图结构数据时非常有用，无论是在单个表中还是在多个表中拆分；可以从一个位置遍历边或树枝到另一个位置，同时保留返回并更改任何这些“指针”值的权利

当事务被提交或回滚时，由 LOCK IN SHARE MODE 和 FOR UPDATE 查询设置的所有锁都会被释放

FOR UPDATE 不会传递

外部语句中的锁定读取子句不会锁定嵌套子查询中表的行，除非在子查询中也指定了锁定读取子句

例如，以下语句不会锁定表 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;

locking read

A SELECT statement that also performs a locking operation on an InnoDB table. Either SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE. It has the potential to produce a deadlock, depending on the isolation level of the transaction. The opposite of a non-locking read*. Not allowed for global tables in a read-only transaction.

SELECT ... FOR SHARE replaces SELECT ... LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.

See Section 14.7.2.4, “Locking Reads”.

See Also deadlock, isolation level, locking, non-locking read, read-only transaction.

一致性非锁定读

上面介绍锁定读中，有一句解释

Consistent reads ignore any locks set on the records that exist in the read view.

一致读将忽略在读取视图中存在的记录上设置的任何锁

这里的一致读指的就是一致性非锁定读（Consistent Nonlocking Reads）

一致读取意味着 InnoDB 使用多版本控制在某个时间点（timepoint）向查询显示数据库的快照，查询会看到在此时间点之前提交的事务所做的修改，而不会看到以后或未提交的事务进行的修改

您可以通过提交事务，然后使用一致快照执行另一个 SELECT 或开启一个新事务来推进时间点

不同隔离级别的快照策略：

REPEATABLE READ：第一次读取设置快照；提交事务后的读取则会使用最新数据
READ COMMITTED：事务中的每个一致读取都会设置并读取自己的新快照

举一个例子说明一致性读

事务 A 只有在 B 提交了事务，并且 A 也提交了事务时才看到由 B 插入的行

事务 A	事务 B
开启事务	开启事务
SELECT * FROM t;（empty set）
	INSERT INTO tVALUES (1, 2);
SELECT * FROM t;（empty set）
	COMMIT;
SELECT * FROM t;（empty set）
COMMIT;
SELECT * FROM t;（1,2）

如果您想查看数据库的“最新”状态，请使用 READ COMMITTED 隔离级别或使用锁定读

需要注意 DML 对数据可见的影响

数据库状态的快照应用于事务中的 SELECT 语句，而不一定应用于 DML 语句

如果插入或修改某些行，然后提交该事务，则从另一个并发的 REPEATABLE READ 事务发出的 DELETE 或 UPDATE 语句可能会影响那些刚刚提交的行，即使会话无法查询到这些数据（因为快照）

如果事务确实更新或删除了由其他事务提交的行，则这些更改对当前事务可见

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- 0 行
DELETE FROM t1 WHERE c1 = 'xyz';
-- 可能会删除最近由其他事务提交的匹配到的行

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- 0 行
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- 如果另一个事务刚刚提交了 abc 值的 10 行
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- 10 行；这个事务现在可以看到它刚刚更新的行

合理使用 FOR UPDATE

业务中有时需要查询数据，对数据进行 check 之后再更新，就需要使用锁机制

往往简单的实现方式是在并发不高的场景下开启事务直接使用 FOR UPDATE 进行查询后进行修改

不过需要注意查询数据是否存在，如果数据不存在可能会严重影响性能，具体原因将会在下面进行分析，当然一句话概括也很简单：FOR UPDATE 会上 record 锁，如果数据不存在 record 锁会变为 gap 锁

真实背景

压测中发现涉及积分表写入的响应特别慢，甚至还存在死锁

逻辑流程：

写锁定读对应行（因为需要 check 余额）
检查余额，计算更新参数
插入或者更新（使用 INSERT IGNORE ... ON DUPLICATE KEY UPDATE 实现）

这是使用锁定读容易忽略的一个点，即有可能不存在对应行数据，导致锁的范围变大

记录锁和间隙锁

记录锁 Record Locks

记录锁是索引记录上的锁

例如 SELECT c1 FROM t WHERE c1=10 For UPDATE；；将会阻止任何其他事务插入、更新或删除 t.c1 值为 10 的行

记录锁总是锁定索引记录，即使定义的表没有索引；这种情况 InnoDB 会创建一个隐藏的聚集索引，并将该索引用于记录锁定

记录锁的事务数据在 SHOW ENGINE INNODB STATUS 和 InnoDB 监视器输出中显示如下

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

间隙锁定是对索引记录之间间隙的锁定，或对第一个索引记录之前（上确界）或最后一个索引记录之后（下确界）间隙的锁定

例如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;；将会防止其他事务将值 15 插入到 t.c1 列中，无论该列中是否已经存在任何此类值，因为该范围中所有现有值之间的间隙都被锁定

间隙可能跨越单个索引值、多个索引值，甚至为空

间隙锁只存在于部分隔离级别中

对于唯一索引并且只查询唯一行的语句，不会设置间隙锁

例如 SELECT * FROM child WHERE id = 100;；但是如果 id 没有索引或具有非唯一索引，则该语句会锁定前面的间隙（这里就是背景问题的根源）

间隙锁的冲突性

需要注意不同的事务可以在间隙上持有冲突的间隙锁（只有间隙锁和间隙锁之前不冲突）

例如，事务 A 可以在间隙上保持共享间隙锁（gap S-lock），而事务 B 在相同间隙上保持独占间隙锁（gap X-lock）；允许冲突的间隙锁的原因是，如果从索引中删除记录，必须合并不同事务在记录上保留的间隙锁

InnoDB 中的间隙锁是“纯抑制性的（purely inhibitive）”，这意味着它们的唯一目的是防止其他事务插入到间隙中，所以间隙锁可以共存

一个事务占用的间隙锁不会阻止另一个事务对同一间隙占用间隙锁，共享和独占间隙锁之间没有区别，它们彼此不冲突，并且具备相同的功能

验证 - 普通索引

创建 DB 和插入基本数据

CREATE TABLE `test_key` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `key` INT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key` (`key`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;

INSERT INTO `test_key`(`key`) VALUES(10),(20),(30),(40),(50);

查询 performance_schema.data_locks 可以获取当前持有锁的事务及锁相关信息（我使用的 MySQL 版本为 8.0，需要注意不同版本的区别）

1	SELECT * FROM `performance_schema.data_locks`;

隔离级别为 RR

查询命中索引

1	SELECT * FROM `test_key` WHERE `key` = 30 FOR UPDATE;

表中存在 key = 30 的数据，持有的锁：

表级别的插入意向锁（FOR UPDATE 带来）
idx_key 30 索引上的临键锁
PRIMARY 3 索引上的记录锁
RECORD 40 索引上的间隙锁

INDEX_NAME	LOCK_TYPE	LOCK_MODE	LOCK_DATA
/	TABLE	IX	/
idx_key	RECORD	X	30, 3
PRIMARY	RECORD	X,REC_NOT_GAP	3
idx_key	RECORD	X,GAP	40, 4

查询未命中索引，但有更大的索引

1	SELECT * FROM `test_key` WHERE `key` = 35 FOR UPDATE;

表中不存在 key = 30 的数据，但存在 key = 40 的数据，持有的锁：

表级别的插入意向锁
idx_key 40 索引上的间隙锁

INDEX_NAME	LOCK_TYPE	LOCK_MODE	LOCK_DATA
/	TABLE	IX	/
idx_key	RECORD	X,GAP	40, 4

查询未命中索引，没有更大的索引

1	SELECT * FROM `test_key` WHERE `key` = 60 FOR UPDATE;

表中不存在 key = 60 的数据，且没有比 60 更大的索引，持有的锁：

表级别的插入意向锁
上确界伪记录临键锁

INDEX_NAME	LOCK_TYPE	LOCK_MODE	LOCK_DATA
/	TABLE	IX	/
idx_key	RECORD	X	supremum pseudo-record

验证 - 唯一约束

同理

CREATE TABLE `test_uni_key` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `key` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_key` (`key`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;

INSERT INTO `test_uni_key`(`key`) VALUES(10),(20),(30),(40),(50);