当前位置：首页 > news >正文

63 mysql 的行锁

news 2025/6/8 22:21:00

前言

我们这里来说的就是我们在 mysql 这边常见的几种锁

行共享锁, 行排他锁, 表意向共享锁, 表意向排他锁, 表共享锁, 表排他锁

意向共享锁, 意向排他锁, 主要是为了表粒度的锁获取的同步判断, 提升效率

意向共享锁, 意向排他锁这边主要的逻辑意义是数据表中是否有任意一行的行共享锁, 行排他锁被获取

假设如下sql “select * from t_user_02 for update;”, 会首先会先尝试获取 t_user_02 的表意向排他锁, 然后再遍历符合条件的每一行记录, 获取每一行记录的行排他锁

假设如下sql “select * from t_user_02 where id = ‘1’ for update;”, 会首先会先尝试获取 t_user_02 的表意向排他锁, 然后获取 id 为 ‘1’ 的记录的行排他锁

差距就在于扫描表的记录, 前者需要扫描全表, 后者只需要扫描 id = ‘1’ 的数据行

之后我们还会有一个锁粒度的调试

当然这里需要区分一些情况, 比如 “select * from t_user_02;” 的查询是无锁查询, “select * from t_user_02 for update;” 是申请排他锁查询, “select * from t_user_02 lock in share mode;” 是申请共享锁查询

在无锁查询的情况下, 是不会去尝试获取表锁, 行锁的, 是一直可以查询的

测试数据表如下

CREATE TABLE `t_user_02` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(24) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

t_user_02 的数据列表如下

表意向排他锁

我们这里调试 sql 如下这里会先获取 t_user_02 的表意向排他锁, 然后再读取的时候在获取 id 为 2 的记录的行排他锁, 我们这里先看表意向排他锁, 再看行排他锁

begin;
select * from t_user_02 where id = '2' for update;
commit;

获取表意向锁这边是在 row_search_mvcc 中, 这里是属于读取记录之前, 会先尝试获取 t_user_02 的表意向共享锁或者表意向独占锁

获取成功之后会继续往下走获取记录的相关业务流程

获取失败之后, 会挂起当前线程, 等待目标锁可以争取然后再次尝试获取目标锁

在我们这里只存在表意向共享锁, 表意向排他锁, 行共享锁, 行排他锁的场景下面, 获取表意向共享锁, 表意向排他锁是恒成功的

获取锁这边处理如下, 判断是否有已经占用的 t_user_02 的表的锁

如果没有可以直接获取给定的表意向共享锁, 表意向独占锁

如果有判断已经持有的锁是否和当前请求的锁兼容, 在我们这里的场景下只考虑表意向共享锁, 表意向独占锁, 行共享锁, 行独占锁这里是几种都兼容

表意向共享锁, 表意向排他锁主要是用于表共享锁, 表排他锁的相关地方的提升效率的处理

然后第一次迭代以后的迭代是不需要再获取表意向共享锁, 表意向排他锁了, 处理的地方如下

第二次以及以后的迭代, 走的是 ”if(!prebuilt->sql_stat_start)” 中的相关的流程了

表意向共享锁

我们这里调试 sql 如下这里会先获取 t_user_02 的表意向共享锁, 然后再读取的时候在获取 id 为 2 的记录的行共享锁, 我们这里先看表意向共享锁, 再看行共享锁

begin;
select * from t_user_02 where id = '1' lock in share mode;
commit;

和上面获取表意向共享锁类似的流程, 只是这里获取的表意向排他锁

在我们这里只存在表意向共享锁, 表意向排他锁, 行共享锁, 行排他锁的场景下面, 获取表意向共享锁, 表意向排他锁是恒成功的

行排他锁

我们这里调试 sql 如下这里会先获取 t_user_02 的表意向排他锁, 然后再读取的时候在获取 id 为 2 的记录的行排他锁, 我们这里来看行排他锁

begin;
select * from t_user_02 where id = '2' for update;
commit;

行共享锁的获取是在获取了当前行的数据之后, 再来获取的

select 中没有 “for update;”, “lock in share mode;” 的场景是 “prebuilt->select_lock_type == LOCK_NONE” 的场景

我们这里是带 “for update”, 获取行排他锁

然后其次就是尝试获取锁之后的处理, 获取成功之后移动游标, 调用栈返回

如果获取行排他锁不成功, 走 lock_wait_or_error, 等待目标锁可以争取然后再次尝试获取目标锁

行锁的实际这边如下, 分为 fastpath 和 slowpath

行锁这边是以 page 为单位的, 一个 page 公用一把锁, lock 中有 bitmap 来维护每一条记录的锁是否被占用, 以及其他信息

“if(lock == null)” 这里是目标页还没有任何锁的情况, 直接创建锁, 获取锁, 这里可以直接响应 LOCK_REC_SUCCESS_CREATED, 是因为外层 lock_clust_rec_read_check_and_lock 有一个 lock_mutex_enter 有一个全局的同步

然后下面就是 “else if(!impl)” 的处理是如果目标锁是关联当前事务, 尝试直接获取给定的记录的行锁, “lock_rec_set_nth_bit(lock, heap_no)” 就是获取目标记录的行锁

我们再来看一下 slowpath

slowpath 这边主要是 fastpath 尝试获取锁失败的场景下面

如果目标记录已经被其他事务持有和当前目标锁冲突的锁, 则 DB_LOCK_WAIT, 上游 row_search_mvcc 走 wait 的流程

否则可以尝试获取目标锁, 然后响应给上游获取锁成功

行共享锁, 和行排他锁这边的冲突规则主要如下

行共享锁兼容于行共享锁, 行共享锁不兼容于行排他锁

行排他锁不兼容于行共享锁, 行排他锁不兼容于行排他锁

行共享锁

我们这里调试 sql 如下这里会先获取 t_user_02 的表意向共享锁, 然后再读取的时候在获取 id 为 2 的记录的行共享锁, 我们这里看行共享锁

begin;
select * from t_user_02 where id = '1' lock in share mode;
commit;

和获取行排他锁的流程基本上一致, 这里不多赘述

行锁锁索引字段的情况

我们这里更新测试表结构如下

CREATE TABLE `tz_test_04` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`field1` varchar(128) DEFAULT NULL,`field2` varchar(128) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,KEY `field_1_2` (`field1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

然后执行 sql 如下 “select * from tz_test_04 where field1 = 'field5' for update;”

从 row_search_mvcc 这里的上下文可以看出当前 rec 是一条索引记录, 然后后面走的流程锁定的也是这部分满足条件的索引记录

因此后面需要尝试获取索引记录的锁的情况, 如果锁不兼容, 则会阻塞

这是在索引匹配 field1 = ‘field5’ 的记录上面增加的索引的行临键锁, 然后它还会在具体的数据记录上面增加行排他锁, 在下一个索引记录上面增加间隙锁

在数据记录上面增加行排他锁, 这里的 clust_rec 表示的是具体的数据记录, rec 表示是当前索引记录

遍历到不匹配 field1 = ‘field5’ 的第一个索引的地方, 在该记录上面增加了一个间隙锁

因此如下三个 sql 都会阻塞, 第一行是获取索引的行排他锁冲突, 第二行是获取数据的行排他锁冲突, 第三行是与在索引 field1=’field9’ 上面的间隙锁冲突

第四行, 第五行是与在索引 field1=’field5’ 上面的临键锁冲突

select * from tz_test_04 where field1 = 'field5' for update;
select * from tz_test_04 where id = 5 for update;
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field3', '3');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '5');

行锁锁非索引字段的情况

我们这里更新测试表结构如下

CREATE TABLE `tz_test_04` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`field1` varchar(128) DEFAULT NULL,`field2` varchar(128) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,KEY `field_1_2` (`field1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

然后执行 sql 如下 “select * from tz_test_04 where field2 = '5' for update;”

如果是根据非索引字段查询, 则会进行全表扫描, 会在所有的行上面增加行临键锁

在 row_search_mvcc 中输出各个断点位置的 rec 信息如下, 即为各个记录的信息

行排他锁阻塞的 N 种方式

假设我们这里尝试模拟各种阻塞的方式, 事务1先进行执行, 然后事务2尝试获取行排他锁, 产生阻塞

事务2 这边执行固定的 sql 语句如下

begin;
select * from t_user_02 where id = '2' for update;
-- sleep 10min
commit;

事务1获取表共享锁导致事务2 获取 MDL元数据锁阻塞

begin;
lock tables t_user_02 read;
-- sleep 10min
unlock tables;
commit;

事务1获取表排他锁导致事务2 获取 MDL元数据锁阻塞

begin;
lock tables t_user_02 write;
-- sleep 10min
unlock tables;
commit;

事务1 获取行共享锁导致事务2 获取行排他锁阻塞

begin;
select * from t_user_02 where id = '2' lock in share mode;
-- sleep 10min
commit;

事务1 获取行排他锁导致事务2 获取行排他锁阻塞

begin;
select * from t_user_02 where id = '2' for update;
-- sleep 10min
commit;

行共享锁阻塞的 N 种方式

假设我们这里尝试模拟各种阻塞的方式, 事务1先进行执行, 然后事务2尝试获取行共享锁, 产生阻塞

事务2 这边执行固定的 sql 语句如下

begin;
select * from t_user_02 where id = '2' lock in share mode;
commit;

事务1获取表排他锁导致事务2 获取 MDL元数据锁阻塞

begin;
lock tables t_user_02 write;
-- sleep 10min
unlock tables;
commit;

事务1 获取行排他锁导致事务2 获取行排他锁阻塞

begin;
select * from t_user_02 where id = '2' for update;
-- sleep 10min
commit;

where 1 = 1 for update 和 where id = 1 for update 的区别

首先是都会获取表意向排他锁

首先来看一下 “where 1 = 1 for update” 情况下的一个获取锁的处理

然后我们这里讨论的主要是行排他锁的获取的差异

是会走 “else if (!impl)” 然后里面的这个 get + set

比如这里获取的是第三条记录的行排他锁, 会设置 bitmap 的第三位, 更新之后 (lock+1) 会变成 0x04 | 0x08 = 0x0c

这个获取了所有记录的行锁逻辑意义上等价于获取了表锁, 但是实际的实现二者又是有一定的区别的

同理, 这里是获取第四条记录的行排他锁

这里从当前状态可以看到 (lock+1) 为 0x0c, 表示获取了第二条记录, 第三条记录的行排他锁

本次更新调整了之后, (lock + 1) 会变成 0x0c | 0x10 = 0x1c

获取第五条记录的行排他锁的情况如下, 这里就不在继续向下赘述了

再来看一下 “where id = 1 for update” 情况下的一个获取锁的处理

这里限定的是 id, 只会获取到一条记录, 因此这里只会走 RecLock 初始化的这部分处理

完

前言