跳至主要內容

行级锁

Jin大约 9 分钟

行级锁

1、介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

分类含义支持范围
行锁（Record Lock）锁定单个行记录的锁，防止其他事务对此行进行update和delete在 RC、RR隔离级别下都支持
间隙锁（Gap Lock）锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读在RR隔离级别下都支持
临键锁（Next-Key Lock）行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap在RR隔离级别下支持

  1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持。

    image-20220610002343106
    image-20220610002343106

  2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

image-20220610002353824
image-20220610002353824
  1. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
image-20220610002425279
image-20220610002425279

2、行锁

2.1、介绍

InnoDB实现了以下两种类型的行锁：

类型含义
共享锁（S）允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
排他锁（X）允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

两种行锁的兼容情况如下:

image-20220610003229905
image-20220610003229905

常见的SQL语句，在执行时，所加的行锁如下：

SQL行锁类型说明
INSERT ...排他锁自动加锁
UPDATE ...排他锁自动加锁
DELETE ...排他锁自动加锁
SELECT（正常）不加任何锁
SELECT ... LOCK IN SHARE MODE共享锁需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE排他锁需要手动在SELECT之后加FOR UPDATE

2.2、演示

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

可以通过以下SQL，查看意向锁及行锁的加锁情况：

数据准备:

演示行锁的时候，我们就通过上面这张表来演示一下。

A. 普通的select语句，执行时，不会加锁。

# ====================行锁 客户端1演示：普通的select语句，执行时，不会加锁====================
begin;
select *
from tb_user tu;
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;
commit;

B. select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。

# ====================行锁 客户端1演示：共享锁与共享锁之间兼容====================
# 1 查看索引
show index from tb_user;
# 2 开启事务
begin;

# 3 加共享锁（S）
select *
from tb_user tu
where id = 1 lock in share mode;

# 7
commit;


# ====================行锁 客户端2演示：共享锁与共享锁之间兼容====================
# 4
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;
# 4
begin;
# 5  共享锁与共享锁是兼容的,此时不会阻塞
select *
from tb_user tu
where id = 1 lock in share mode;

# 6
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;
commit;
image-20220610004542752
image-20220610004542752

共享锁与排他锁之间互斥。

# ====================行锁 客户端1演示：共享锁与排他锁之间互斥====================

# 1 开启事务
begin;

# 2 加共享锁（S）
select *
from tb_user tu
where id = 1 lock in share mode;

# 5
commit;
# ====================行锁 客户端2演示：共享锁与排他锁之间互斥====================

# 3
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

# 4
UPDATE mysql_demo.tb_user t
SET t.age = 18
WHERE t.id = 1;
image-20220610005405099
image-20220610005405099

客户端一获取的是id为1这行的共享锁，客户端二是可以获取id为3这行的排它锁的，因为不是同一行数据。 而如果客户端二想获取id为1这行的排他锁，会处于阻塞状态，以为共享锁与排他锁之间互

斥。

C. 排它锁与排他锁之间互斥

当客户端一，执行update语句，会为id为1的记录加排他锁； 客户端二，如果也执行update语句更新id为1的数据，也要为id为1的数据加排他锁，但是客户端二会处于阻塞状态，因为排他锁之间是互斥的。 直到客户端一，把事务提交了，才会把这一行的行锁释放，此时客户端二，解除阻塞。

# ====================行锁 客户端1演示：排它锁与排他锁之间互斥====================

# 1 开启事务
begin;

# 2
UPDATE mysql_demo.tb_user t
SET t.age = 18
WHERE t.id = 1;

commit;

# ====================行锁 客户端1演示：排它锁与排他锁之间互斥====================

# 1 开启事务
begin;

# 2
UPDATE mysql_demo.tb_user t
SET t.age = 18
WHERE t.id = 1;

commit;
image-20220610005723244
image-20220610005723244

D. 无索引行锁升级为表锁

我们在两个客户端中执行如下操作:

# ====================行锁 客户端1演示：无索引行锁升级为表锁====================
select *
from tb_user tu;
# 1 开启事务
begin;

# 2 name 没有索引
UPDATE mysql_demo.tb_user t
SET t.age = 18
WHERE t.name = 'Jin';
# 5
commit;

# ====================行锁 客户端2演示：无索引行锁升级为表锁====================

# 1 开启事务
begin;

# 3
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

# 4 阻塞，如果没有索引，此时行锁会升级为表锁(因为行锁是对索引项加的锁，而name没有索引)。
UPDATE mysql_demo.tb_user t
SET t.age = 18
WHERE t.id = 1;

commit;
image-20220610010458646
image-20220610010458646

在客户端一中，开启事务，并执行update语句，更新name为Jin的数据，也就是id为66的记录 。然后在客户端二中更新id为3的记录，却不能直接执行，会处于阻塞状态，为什么呢？

原因就是因为此时，客户端一，根据name字段进行更新时，name字段是没有索引的，如果没有索引，此时行锁会升级为表锁(因为行锁是对索引项加的锁，而name没有索引)。

接下来，我们再针对name字段建立索引，索引建立之后，再次做一个测试：

# 7 新建name索引，重复上面操作
create index ix_name on tb_user(name);

此时我们可以看到，客户端一，开启事务，然后依然是根据name进行更新。而客户端二，在更新id为3的数据时，更新成功，并未进入阻塞状态。 这样就说明，我们根据索引字段进行更新操作，就可以避免行锁升级为表锁的情况。

image-20220610011058732
image-20220610011058732

3、间隙锁&临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

  2. 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

  3. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

示例演示

A. 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

image-20220610204847238
image-20220610204847238

执行insert操作时阻塞，因为ID从24到66之间加了间隙锁

image-20220610205406376
image-20220610205406376
# ====================间隙锁 客户端1演示====================
# 1
select *
from tb_user tu;
# 2
begin;

# 3
UPDATE mysql_demo.tb_user t
SET t.profession = '爱你'
WHERE t.id = 60;

commit;

# ====================间隙锁 客户端2演示====================
#     4
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

#   5
begin ;
# 6 执行insert 时阻塞，因为ID从24到66之间加了间隙锁
INSERT INTO mysql_demo.tb_user (id, name, phone, email, profession, age, gender, status, createtime)
VALUES (65, 'Jin', '17799990023', '37483844@qq.com', '工程造价', 18, '1', '4', '2003-05-26 00:00:00');

commit;

B. 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

介绍分析一下：

我们知道InnoDB的B+树索引，叶子节点是有序的双向链表。 假如，我们要根据这个二级索引查询值为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？ 并不是，因为是非唯一索引，这个结构中可能有多个18的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是22）。此时会对18加临键锁，并对22之前的间隙加锁。

image-20220610215744614
image-20220610215744614
# ====================普通索引等值匹配 客户端1演示====================
# 1
create index ix_age on tb_user (age);
select age
from tb_user tu;
# 2
begin;

# 3
select *
from tb_user
where age  = 18 lock in share mode ;

# ====================普通索引等值匹配 客户端2演示====================
#     4
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;
# lock_data解析
# S  18, 1 : 临键锁
# S  18, 66 : 临键锁
# S,REC_NOT_GAP 1 ：行锁
# S,REC_NOT_GAP  66 :行锁
# S,GAP   19, 22  ：间隙锁

C. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

查询的条件为id>=24，并添加共享锁。 此时我们可以根据数据库表中现有的数据，将数据分为三个部分：

image-20220610221244331
image-20220610221244331

所以数据库数据在加锁是，就是将24加了行锁，66的临键锁（包含66及66之前的间隙），正无穷的临键锁(正无穷及之前的间隙)。

# ==================== 索引上的范围查询(唯一索引) 客户端1演示====================

# 1
begin;

# 2
select *
from tb_user
where id  >= 24 lock in share mode ;

commit
# ==================== 索引上的范围查询(唯一索引) 客户端2演示====================

#  3
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

# S REC_NOT_GAP 24 ：  行锁
# S supremum pseudo-record  正无穷: 临键锁
# S  66 ：临键锁

示例2：

image-20220610221545557
image-20220610221545557

查询的条件为id>=19，并添加共享锁。 此时我们可以根据数据库表中现有的数据，将数据分为三个部 分： [19] (19,25] (25,+∞] 所以数据库数据在加锁是，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临键锁(正无穷及之前的间隙)。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

贡献者: Jin
Jin 的个人经历、个人兴趣、个人感悟、个人思考
Copyright © 2025 Jin