Locking Read

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 ... for share
select ... for update

It has the potential to produce a deadlock, depending on the isolation level of the transaction. The opposite of a non-locking read.

`select ... for share`

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 ... FOR SHARE, 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.)

SELECT ... FOR UPDATE requires the SELECT privilege and at least one of the DELETE, LOCK TABLES, or UPDATE privileges.

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

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.

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:

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

MySQL InnoDB 排他锁（exclusive lock）

用法：

select … for update;

例如：

select * from goods where id = 1 for update;

排他锁的申请前提：没有线程对该结果集中的任何行数据使用排他锁（write lock）或共享锁（read lock），否则申请会阻塞。

for update仅适用于InnoDB，且必须在事务块（BEGIN/COMMIT）中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

场景分析

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 FOR SHARE:

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

After the FOR SHARE 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.

select for update

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, FOR SHARE 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:

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:

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.

什么 SQL 语句会加行级锁？

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁，所以后面的内容都是基于 InnoDB 引擎的。

所以，在说 MySQL 是怎么加行级锁的时候，其实是在说 InnoDB 引擎是怎么加行级锁的。

普通的 select 语句是不会对记录加锁的（除了串行化隔离级别），因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。

如果要在查询时对记录加行级锁，可以使用下面这两个方式，这两种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁(S型锁)
select ... lock in share mode;

//对读取的记录加独占锁(X型锁)
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin 或者 start transaction 开启事务的语句。

除了上面这两条锁定读语句会加行级锁之外，update 和 delete 操作都会加行级锁，且锁的类型都是独占锁(X型锁)。

//对操作的记录加独占锁(X型锁)
update table .... where id = 1;

//对操作的记录加独占锁(X型锁)
delete from table where id = 1;

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。

唯一索引等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。
当查询的记录是「不存在」的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。

Scenario 1 - 查询的记录存在 - 产生记录锁，不产生间隙锁

测试环境：

环境：MySQL，InnoDB，默认的隔离级别（RR）

数据表：

CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据：

delete from test;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');

在进行测试之前，我们先来看看test表中存在的隐藏间隙：

(-infinity, 1)
(1, 5)
(5, 7)
(7, 11)
(11, +infinity)

间隙锁都是左开右开原则。

我们现在进行以下几个事务的测试：

SELECT @@transaction_ISOLATION;
SET session transaction ISOLATION LEVEL REPEATABLE READ;
/* 开启事务1 */
BEGIN;
SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;

/* 事务2 */
BEGIN;
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张'); # 正常执行
ROLLBACK;

BEGIN;
INSERT INTO `test` (`id`, `name`) VALUES (8, '小东'); # 正常执行
ROLLBACK;

/* 提交事务1，释放事务1的锁 */
COMMIT;

上述的案例，由于主键是唯一索引，而且是只使用一个索引查询，并且只锁定一条记录，所以以上的例子，只会对 id = 5 的数据加上记录锁，而不会产生间隙锁。

我们通过下面的方式验证我们的分析：

# View the current locks
select * from performance_schema.data_locks\G;
***************************[ 1. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140437116521608:1584:140437369148848
ENGINE_TRANSACTION_ID | 14478934
THREAD_ID             | 302
EVENT_ID              | 16
OBJECT_SCHEMA         | test
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | <null>
OBJECT_INSTANCE_BEGIN | 140437369148848
LOCK_TYPE             | TABLE
LOCK_MODE             | IX # X 意向锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | <null>
***************************[ 2. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140437116521608:44:4:3:140437360800288
ENGINE_TRANSACTION_ID | 14478934
THREAD_ID             | 302
EVENT_ID              | 16
OBJECT_SCHEMA         | test
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY # 该锁在 Primary index 上加的
OBJECT_INSTANCE_BEGIN | 140437360800288
LOCK_TYPE             | RECORD
LOCK_MODE             | X,REC_NOT_GAP # 这里说明是 X 记录锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | 5 # 锁的记录是 5

总结来说，如果有其他事务，对 id 为 5 的记录进行更新或者删除操作的话，这些操作都会被阻塞，因为更新或者删除操作也会对记录加 X 型的记录锁，而 X 锁和 X 锁之间是互斥关系。

从这里我们也可以得知，加锁的对象是针对索引，因为这里查询语句扫描的 B+ 树是聚簇索引树，即主键索引树，所以是对主键索引加锁。将对应记录的主键索引加记录锁后，就意味着其他事务无法对该记录进行更新和删除操作了。

分析

TIP

本文章的「唯一索引」是用「主键索引」作为案例说明的，加锁只加在主键索引项上。

然后，很多同学误以为如果是二级索引的「唯一索引」，加锁也是只加在二级索引项上。

其实这是不对的，所以这里特此说明下，如果是用二级索引（不管是不是非唯一索引，还是唯一索引）进行锁定读查询的时候，除了会对二级索引项加行级锁（如果是唯一索引的二级索引，加锁规则和主键索引的案例相同），而且还会对查询到的记录的主键索引项上加「记录锁」。

在下面的「非唯一索引」的案例中，我就是用二级索引作为例子，在后面的章节我有说明，对二级索引进行锁定读查询的时候，因为存在两个索引（二级索引和主键索引），所以两个索引都会加锁。

为什么唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的 next-key lock 会退化成记录锁？

原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。

幻读的定义就是，当一个事务前后两次查询的结果集，不相同时，就认为发生幻读。所以，要避免幻读就是避免结果集某一条记录被其他事务删除，或者有其他事务插入了一条新记录，这样前后两次查询的结果集就不会出现不相同的情况。

由于主键具有唯一性，所以其他事务插入 id = 1 的时候，会因为主键冲突，导致无法插入 id = 1 的新记录。这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。
由于对 id = 1 加了记录锁，其他事务无法删除该记录，这样事务 A 在多次查询 id = 1 的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。

Scenario 2 - 查询的记录不存在 - 产生间隙锁

数据：

delete from test;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('8', '小明');
INSERT INTO `test` VALUES ('11', '小红');

我们再来测试如果我们锁住不存在的数据时，会怎样：

/* 开启事务1 */
BEGIN;
SELECT * FROM `test` WHERE `id` = 6 FOR UPDATE;

/* 开启事务2 */
BEGIN;
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张1'); # 不阻塞
ROLLBACK;

BEGIN;
INSERT INTO `test` (`id`, `name`) VALUES (7, '小张1'); # 阻塞，因为间隙锁位于 (5,8)
ROLLBACK;

BEGIN;
INSERT INTO `test` (`id`, `name`) VALUES (9, '小白'); # 不阻塞
ROLLBACK;


/* 提交事务1，释放事务1的锁 */
COMMIT;

我们可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁，间隙锁的上下区间分别为从该不存在的记录向上和向下找到的记录。

我们仍然通过下面的方式验证我们的分析：

# View the current locks
select * from performance_schema.data_locks\G;
***************************[ 1. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140437116521608:1584:140437369148848
ENGINE_TRANSACTION_ID | 14478972
THREAD_ID             | 302
EVENT_ID              | 46
OBJECT_SCHEMA         | test
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | <null>
OBJECT_INSTANCE_BEGIN | 140437369148848
LOCK_TYPE             | TABLE
LOCK_MODE             | IX # X 意向锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | <null>
***************************[ 2. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140437116521608:44:4:7:140437360800288
ENGINE_TRANSACTION_ID | 14478972
THREAD_ID             | 302
EVENT_ID              | 46
OBJECT_SCHEMA         | test
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY # 锁在了 primary index
OBJECT_INSTANCE_BEGIN | 140437360800288
LOCK_TYPE             | RECORD
LOCK_MODE             | X,GAP # 间隙锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | 8 # (5,8)

分析

接下来，如果有其他事务插入 id 值为 6、7 这一些记录的话，这些插入语句都会发生阻塞。

注意，如果其他事务插入的 id = 5 或者 id = 8 的记录话，并不会发生阻塞，而是报主键冲突的错误，因为表中已经存在 id = 5 和 id = 8 的记录了。

为什么唯一索引等值查询并且查询记录「不存在」的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的 next-key lock 会退化成「间隙锁」？

原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。

为什么 id = 5 记录上的主键索引的锁不可以是 next-key lock？如果是 next-key lock，就意味着其他事务无法删除 id = 5 这条记录，但是这次的案例是查询 id = 2 的记录，只要保证前后两次查询 id = 2 的结果集相同，就能避免幻读的问题了，所以即使 id =5 被删除，也不会有什么影响，那就没必须加 next-key lock，因此只需要在 id = 5 加间隙锁，避免其他事务插入 id = 2 的新记录就行了。
为什么不可以针对不存在的记录加记录锁？锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录。

唯一索引范围查询

范围查询和等值查询的加锁规则是不同的。

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。
情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
- 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
- 当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

接下来，通过几个实验，才验证我上面说的结论。

Scenario 1 - 针对「大于」的范围查询的情况

数据：

delete from test;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
INSERT INTO `test` VALUES ('15', '小蓝');

我们继续在 id 唯一索引列上做以下的测试：

SELECT @@transaction_ISOLATION;
SET session transaction ISOLATION LEVEL REPEATABLE READ;

/* 开启事务1 */
BEGIN;
SELECT * FROM `test` WHERE id > 12 FOR UPDATE;

事务 A 加锁变化过程如下：

最开始要找的第一行是 id = 15，由于查询该记录不是一个等值查询（不是大于等于条件查询），所以对该主键索引加的是范围为 (12, 15] 的 next-key 锁；
由于是范围查找，就会继续往后找存在的记录，虽然我们看见表中最后一条记录是 id = 15 的记录，但是实际在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊的记录的名字叫 supremum pseudo-record ，所以扫描第二行的时候，也就扫描到了这个特殊记录的时候，会对该主键索引加的是范围为 (15, +∞] 的 next-key 锁。
停止扫描。

我们仍然通过下面的方式验证我们的分析：

# View the current locks
select * from performance_schema.data_locks\G;
***************************[ 1. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:1064:140641149404416
ENGINE_TRANSACTION_ID | 2607
THREAD_ID             | 48
EVENT_ID              | 24
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | <null>
OBJECT_INSTANCE_BEGIN | 140641149404416
LOCK_TYPE             | TABLE
LOCK_MODE             | IX # X的意向锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | <null>
***************************[ 2. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:1:140641413670944
ENGINE_TRANSACTION_ID | 2607
THREAD_ID             | 48
EVENT_ID              | 24
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X # next-key Lock
LOCK_STATUS           | GRANTED
LOCK_DATA             | supremum pseudo-record # 范围是[15, +∞]
***************************[ 3. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:10:140641413670944
ENGINE_TRANSACTION_ID | 2607
THREAD_ID             | 48
EVENT_ID              | 24
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X # next-key Lock
LOCK_STATUS           | GRANTED
LOCK_DATA             | 15 # 范围是 (12, 15]

从上图中的分析中，也可以得到事务 A 在主键索引上加了两个 X 型的next-key 锁：

在 id = 12 这条记录的主键索引上，加了范围为 (12, 15] 的 next-key 锁，意味着其他事务即无法更新或者删除 id = 12 的记录，同时无法插入 id 值为 13、14 的这一些新记录。
在特殊记录（supremum pseudo-record）的主键索引上，加了范围为 (15, +∞] 的 next-key 锁，意味着其他事务无法插入 id 值大于 15 的这一些新记录。

Scenario 2 - 针对「大于等于」的范围查询的情况

数据：

delete from test;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
INSERT INTO `test` VALUES ('15', '小蓝');

我们继续在 id 唯一索引列上做以下的测试：

SELECT @@transaction_ISOLATION;
SET session transaction ISOLATION LEVEL REPEATABLE READ;

/* 开启事务1 */
BEGIN;
SELECT * FROM `test` WHERE id >= 11 FOR UPDATE;

事务 A 加锁变化过程如下：

最开始要找的第一行是 id = 11，由于查询该记录是一个等值查询（等于 11），所以该主键索引的 next-key 锁会退化成记录锁，也就是仅锁住 id = 11 这一行记录。
由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 15，于是对该主键索引加的是范围为 (11, 15] 的 next-key 锁；
接着扫描到第三行的时候，扫描到了特殊记录（ supremum pseudo-record），于是对该主键索引加的是范围为 (15, +∞] 的 next-key 锁。
停止扫描。

我们仍然通过下面的方式验证我们的分析：

# View the current locks
select * from performance_schema.data_locks\G;
***************************[ 1. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:1064:140641149404416
ENGINE_TRANSACTION_ID | 2613
THREAD_ID             | 48
EVENT_ID              | 31
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | <null>
OBJECT_INSTANCE_BEGIN | 140641149404416
LOCK_TYPE             | TABLE
LOCK_MODE             | IX # X的意向锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | <null>
***************************[ 2. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:5:140641413670944
ENGINE_TRANSACTION_ID | 2613
THREAD_ID             | 48
EVENT_ID              | 31
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X,REC_NOT_GAP # X的记录锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | 11 # 锁在 11
***************************[ 3. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:1:140641413671288
ENGINE_TRANSACTION_ID | 2613
THREAD_ID             | 48
EVENT_ID              | 31
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413671288
LOCK_TYPE             | RECORD
LOCK_MODE             | X # next-key Lock
LOCK_STATUS           | GRANTED
LOCK_DATA             | supremum pseudo-record # [15, +∞)
***************************[ 4. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:10:140641413671288
ENGINE_TRANSACTION_ID | 2613
THREAD_ID             | 48
EVENT_ID              | 31
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413671288
LOCK_TYPE             | RECORD
LOCK_MODE             | X # next-key Lock
LOCK_STATUS           | GRANTED
LOCK_DATA             | 15 # [12, 15)

通过前面这个实验，我们证明了：

针对「大于等于」条件的唯一索引范围查询的情况下，如果条件值的记录存在于表中，那么由于查询该条件值的记录是包含一个等值查询的操作，所以该记录的索引中的 next-key 锁会退化成记录锁。

Scenario 3 - 针对「小于或者小于等于」的范围查询的情况

Scenario 3.1 - 针对「小于」的范围查询时，查询条件值的记录「不存在」表中的情况

数据：

delete from test;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
INSERT INTO `test` VALUES ('15', '小蓝');

我们继续在 id 唯一索引列上做以下的测试：

SELECT @@transaction_ISOLATION;
SET session transaction ISOLATION LEVEL REPEATABLE READ;

/* 开启事务1 */
BEGIN;
SELECT * FROM `test` WHERE id < 6 FOR UPDATE

事务 A 加锁变化过程如下：

最开始要找的第一行是 id = 1，于是对该主键索引加的是范围为 (-∞, 1] 的 next-key 锁；
由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，所以对该主键索引加的是范围为 (1, 5] 的 next-key 锁；
由于扫描到的第二行记录（id = 5），满足 id < 6 条件，而且也没有达到终止扫描的条件，接着会继续扫描。
扫描到的第三行是 id = 7，该记录不满足 id < 6 条件的记录，所以 id = 7 这一行记录的锁会退化成间隙锁，于是对该主键索引加的是范围为 (5, 7) 的间隙锁。
由于扫描到的第三行记录（id = 7），不满足 id < 6 条件，达到了终止扫描的条件，于是停止扫描。

我们仍然通过下面的方式验证我们的分析：

# View the current locks
select * from performance_schema.data_locks\G;
***************************[ 1. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:1064:140641149404416
ENGINE_TRANSACTION_ID | 2630
THREAD_ID             | 48
EVENT_ID              | 47
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | <null>
OBJECT_INSTANCE_BEGIN | 140641149404416
LOCK_TYPE             | TABLE
LOCK_MODE             | IX # X 意向锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | <null>
***************************[ 2. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:2:140641413670944
ENGINE_TRANSACTION_ID | 2630
THREAD_ID             | 48
EVENT_ID              | 47
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X # next-key Lock
LOCK_STATUS           | GRANTED
LOCK_DATA             | 1 # (-∞ ,1]
***************************[ 3. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:3:140641413670944
ENGINE_TRANSACTION_ID | 2630
THREAD_ID             | 48
EVENT_ID              | 47
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X # next-key Lock
LOCK_STATUS           | GRANTED
LOCK_DATA             | 5 # (1, 5]
***************************[ 4. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:11:140641413671288
ENGINE_TRANSACTION_ID | 2630
THREAD_ID             | 48
EVENT_ID              | 47
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413671288
LOCK_TYPE             | RECORD
LOCK_MODE             | X,GAP # X 间隙锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | 7 # (5, 7)

虽然这次范围查询的条件是「小于」，但是查询条件值的记录不存在于表中（ id 为 6 的记录不在表中），所以如果事务 A 的范围查询的条件改成 <= 6 的话，加的锁还是和范围查询条件为 < 6 是一样的。大家自己也验证下这个结论。

因此，针对「小于或者小于等于」的唯一索引范围查询，如果条件值的记录不在表中，那么不管是「小于」还是「小于等于」的范围查询，扫描到终止范围查询的记录时，该记录中索引的 next-key 锁会退化成间隙锁，其他扫描的记录，则是在这些记录的索引上加 next-key 锁。

Scenario 3.2 - 针对「小于等于」的范围查询时，查询条件值的记录「存在」表中的情况

假设事务 A 执行了这条范围查询语句，注意查询条件值的记录（id 为 5）存在于表中。

数据：

delete from test;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
INSERT INTO `test` VALUES ('15', '小蓝');

我们继续在 id 唯一索引列上做以下的测试：

SELECT @@transaction_ISOLATION;
SET session transaction ISOLATION LEVEL REPEATABLE READ;

/* 开启事务1 */
BEGIN;
SELECT * FROM `test` WHERE id <= 5 FOR UPDATE

事务 A 加锁变化过程如下：

最开始要找的第一行是 id = 1，于是对该记录加的是范围为 (-∞, 1] 的 next-key 锁；
由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，于是对该记录加的是范围为 (1, 5] 的 next-key 锁。
由于主键索引具有唯一性，不会存在两个 id = 5 的记录，所以不会再继续扫描，于是停止扫描。

我们仍然通过下面的方式验证我们的分析：

# View the current locks
select * from performance_schema.data_locks\G;
***************************[ 1. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:1064:140641149404416
ENGINE_TRANSACTION_ID | 2651
THREAD_ID             | 48
EVENT_ID              | 63
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | <null>
OBJECT_INSTANCE_BEGIN | 140641149404416
LOCK_TYPE             | TABLE
LOCK_MODE             | IX
LOCK_STATUS           | GRANTED
LOCK_DATA             | <null>
***************************[ 2. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:2:140641413670944
ENGINE_TRANSACTION_ID | 2651
THREAD_ID             | 48
EVENT_ID              | 63
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X
LOCK_STATUS           | GRANTED
LOCK_DATA             | 1
***************************[ 3. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:3:140641413670944
ENGINE_TRANSACTION_ID | 2651
THREAD_ID             | 48
EVENT_ID              | 63
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X
LOCK_STATUS           | GRANTED
LOCK_DATA             | 5

从上可知，可以得到事务 A 在主键索引上加了两个 X 型 next-key 锁，分别是：

在 id = 1 这条记录的主键索引上，加了范围为 (-∞, 1] 的 next-key 锁；
在 id = 5 这条记录的主键索引上，加了范围为(1, 5 ] 的 next-key 锁。

Scenario 3.3 - 再来看针对「小于」的范围查询时，查询条件值的记录「存在」表中的情况

如果事务 A 的查询语句是小于的范围查询，且查询条件值的记录（id 为 5）存在于表中。

数据：

delete from test;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
INSERT INTO `test` VALUES ('15', '小蓝');

我们继续在 id 唯一索引列上做以下的测试：

SELECT @@transaction_ISOLATION;
SET session transaction ISOLATION LEVEL REPEATABLE READ;

/* 开启事务1 */
BEGIN;
select * from user where id < 5 for update;

事务 A 加锁变化过程如下：

最开始要找的第一行是 id = 1，于是对该记录加的是范围为 (-∞, 1] 的 next-key 锁；
由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，该记录是第一条不满足 id < 5 条件的记录，于是该记录的锁会退化为间隙锁，锁范围是 (1,5)。
由于找到了第一条不满足 id < 5 条件的记录，于是停止扫描。

我们仍然通过下面的方式验证我们的分析：

# View the current locks
select * from performance_schema.data_locks\G;
***************************[ 1. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:1064:140641149404416
ENGINE_TRANSACTION_ID | 2651
THREAD_ID             | 48
EVENT_ID              | 63
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | <null>
OBJECT_INSTANCE_BEGIN | 140641149404416
LOCK_TYPE             | TABLE
LOCK_MODE             | IX
LOCK_STATUS           | GRANTED
LOCK_DATA             | <null>
***************************[ 2. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:2:140641413670944
ENGINE_TRANSACTION_ID | 2651
THREAD_ID             | 48
EVENT_ID              | 63
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X
LOCK_STATUS           | GRANTED
LOCK_DATA             | 1
***************************[ 3. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140641175646112:3:4:3:140641413670944
ENGINE_TRANSACTION_ID | 2651
THREAD_ID             | 48
EVENT_ID              | 63
OBJECT_SCHEMA         | test2
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY
OBJECT_INSTANCE_BEGIN | 140641413670944
LOCK_TYPE             | RECORD
LOCK_MODE             | X
LOCK_STATUS           | GRANTED
LOCK_DATA             | 5

从上图中的分析中，可以得到事务 A 在主键索引上加了 X 型的范围为 (-∞, 1] 的 next-key 锁，和 X 型的范围为 (1, 5) 的间隙锁。

因此，通过前面这三个实验，可以得知。

在针对「小于或者小于等于」的唯一索引（主键索引）范围查询时，存在这两种情况会将索引的 next-key 锁会退化成间隙锁的：

当条件值的记录「不在」表中时，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的主键索引中的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的主键索引上加 next-key 锁。
当条件值的记录「在」表中时：
- 如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的主键索引中的 next-key 锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的主键索引上，加 next-key 锁。
- 如果是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的主键索引中的 next-key 锁「不会」退化成间隙锁，其他扫描到的记录，都是在这些记录的主键索引上加 next-key 锁。

Scenario 4 - 范围查询

数据：

delete from test;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
INSERT INTO `test` VALUES ('13', '小蓝');

我们继续在 id 唯一索引列上做以下的测试：

SELECT @@transaction_ISOLATION;
SET session transaction ISOLATION LEVEL REPEATABLE READ;

/* 开启事务1 */
BEGIN;
SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 8 FOR UPDATE;

/* 事务2 */
BEGIN;
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张1'); # 正常执行
ROLLBACK;

BEGIN;
INSERT INTO `test` (`id`, `name`) VALUES (6, '小白'); # 阻塞
ROLLBACK;

BEGIN;
INSERT INTO `test` (`id`, `name`) VALUES (10, '小东'); # 阻塞
ROLLBACK;

/* 提交事务1，释放事务1的锁 */
COMMIT;

从上面我们可以看到，[5, 11]这个区间，都不可插入数据，其它区间，都可以正常插入数据。

我们仍然通过下面的方式验证我们的分析：

# View the current locks
select * from performance_schema.data_locks\G;
***************************[ 1. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140437116521608:1584:140437369148848
ENGINE_TRANSACTION_ID | 14478950
THREAD_ID             | 302
EVENT_ID              | 32
OBJECT_SCHEMA         | test
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | <null>
OBJECT_INSTANCE_BEGIN | 140437369148848
LOCK_TYPE             | TABLE
LOCK_MODE             | IX
LOCK_STATUS           | GRANTED
LOCK_DATA             | <null>
***************************[ 2. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140437116521608:44:4:3:140437360800288
ENGINE_TRANSACTION_ID | 14478950
THREAD_ID             | 302
EVENT_ID              | 32
OBJECT_SCHEMA         | test
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY # 锁在了 primary index
OBJECT_INSTANCE_BEGIN | 140437360800288
LOCK_TYPE             | RECORD
LOCK_MODE             | X,REC_NOT_GAP # 说明为 X 记录锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | 5 # 锁在了 5 -> [5]
***************************[ 3. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140437116521608:44:4:4:140437360800632
ENGINE_TRANSACTION_ID | 14478950
THREAD_ID             | 302
EVENT_ID              | 32
OBJECT_SCHEMA         | test
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY # 锁在了 primary index
OBJECT_INSTANCE_BEGIN | 140437360800632
LOCK_TYPE             | RECORD
LOCK_MODE             | X # 说明为 next-key 锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | 7 # next-key 锁锁在了 (5, 7]
***************************[ 4. row ]***************************
ENGINE                | INNODB
ENGINE_LOCK_ID        | 140437116521608:44:4:5:140437360800976
ENGINE_TRANSACTION_ID | 14478950
THREAD_ID             | 302
EVENT_ID              | 32
OBJECT_SCHEMA         | test
OBJECT_NAME           | test
PARTITION_NAME        | <null>
SUBPARTITION_NAME     | <null>
INDEX_NAME            | PRIMARY # 锁在了 primary index
OBJECT_INSTANCE_BEGIN | 140437360800976
LOCK_TYPE             | RECORD
LOCK_MODE             | X,GAP # 说明是 X 间隙锁
LOCK_STATUS           | GRANTED
LOCK_DATA             | 11 # 说明该间隙锁锁的范围是 (7, 11)

说明：上面的select for update 共加了三把锁

记录锁 [5]
next-key 锁 (5, 7]
间隙锁 (7, 11)

非唯一索引等值查询

// TODO

非唯一索引范围查询

// TODO

普通索引的间隙锁

数据准备

创建 test1 表：

# 注意：number 不是唯一值

CREATE TABLE `test1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT '数字',
  PRIMARY KEY (`id`),
  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

在这张表上，我们有 id number 这两个字段，id 是我们的主键，我们在 number 上，建立了一个普通索引，为了方便我们后面的测试。现在我们要先加一些数据：

INSERT INTO `test1` VALUES (1, 1);
INSERT INTO `test1` VALUES (5, 3);
INSERT INTO `test1` VALUES (7, 8);
INSERT INTO `test1` VALUES (11, 12);

在进行测试之前，我们先来看看test1表中 number 索引存在的隐藏间隙：

(-infinity, 1]
(1, 3]
(3, 8]
(8, 12]
(12, +infinity]

案例说明

我们执行以下的事务（事务1最后提交），分别执行下面的语句：

/* 开启事务1 */
BEGIN;
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `test1` (`number`) VALUES (0); # 正常执行

/* 事务3插入一条 number = 1 的数据 */
INSERT INTO `test1` (`number`) VALUES (1); # 被阻塞

/* 事务4插入一条 number = 2 的数据 */
INSERT INTO `test1` (`number`) VALUES (2); # 被阻塞

/* 事务5插入一条 number = 4 的数据 */
INSERT INTO `test1` (`number`) VALUES (4); # 被阻塞

/* 事务6插入一条 number = 8 的数据 */
INSERT INTO `test1` (`number`) VALUES (8); # 正常执行

/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `test1` (`number`) VALUES (9); # 正常执行

/* 事务8插入一条 number = 10 的数据 */
INSERT INTO `test1` (`number`) VALUES (10); # 正常执行

/* 提交事务1 */
COMMIT;

【MySQL】Lock Demo（锁实验）- Read

Locking Read

`select ... for update`

MySQL InnoDB 排他锁（exclusive lock）

场景分析

Locking Read Examples

select for update

什么 SQL 语句会加行级锁？

唯一索引等值查询

Scenario 1 - 查询的记录存在 - 产生记录锁，不产生间隙锁

分析

Scenario 2 - 查询的记录不存在 - 产生间隙锁

分析

唯一索引范围查询

Scenario 1 - 针对「大于」的范围查询的情况

Scenario 2 - 针对「大于等于」的范围查询的情况

Scenario 3 - 针对「小于或者小于等于」的范围查询的情况

Scenario 3.1 - 针对「小于」的范围查询时，查询条件值的记录「不存在」表中的情况

Scenario 3.2 - 针对「小于等于」的范围查询时，查询条件值的记录「存在」表中的情况

Scenario 3.3 - 再来看针对「小于」的范围查询时，查询条件值的记录「存在」表中的情况

Scenario 4 - 范围查询

非唯一索引等值查询

非唯一索引范围查询

普通索引的间隙锁

Reference

FEATURED TAGS

FRIENDS

TOC

Locking Read

select ... for share

select ... for update

MySQL InnoDB 排他锁（exclusive lock）

场景分析

Locking Read Examples

select for share

select for update

什么 SQL 语句会加行级锁？

唯一索引等值查询

Scenario 1 - 查询的记录存在 - 产生记录锁，不产生间隙锁

分析

Scenario 2 - 查询的记录不存在 - 产生间隙锁

分析

唯一索引范围查询

Scenario 1 - 针对「大于」的范围查询的情况

Scenario 2 - 针对「大于等于」的范围查询的情况

Scenario 3 - 针对「小于或者小于等于」的范围查询的情况

Scenario 3.1 - 针对「小于」的范围查询时，查询条件值的记录「不存在」表中的情况

Scenario 3.2 - 针对「小于等于」的范围查询时，查询条件值的记录「存在」表中的情况

Scenario 3.3 - 再来看针对「小于」的范围查询时，查询条件值的记录「存在」表中的情况

Scenario 4 - 范围查询

非唯一索引等值查询

非唯一索引范围查询

普通索引的间隙锁

Reference

FEATURED TAGS

FRIENDS

`select ... for share`

`select ... for update`