63 mysql 的 行锁

时间：2024-11-02 13:05:57

前言

我们这里来说的就是 我们在 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 的数据列表如下  

bd8be40e8b125f0914f17a5700af6362.png

 

 

表意向排他锁

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

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

 

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

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

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

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

73405b3cf1462babb67210241372ab61.png

 

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

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

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

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

500f8ef6359b36de910a0462c9fb0017.png

 

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

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

9f239e0b429ce3909fb26e2b83a2621c.png

 

 

表意向共享锁

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

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

 

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

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

9853db835ffd9a506efc49a656c56096.png

 

 

行排他锁

我们这里调试 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”, 获取 行排他锁

1aa3138371aabdf9bb760ac09f6a4ed3.png

 

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

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

03e2be72acf38a978debc3dbb0b34a1e.png

 

行锁的实际这边如下, 分为 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)” 就是获取目标记录的行锁

2b762881a9b5c745436fa2a82fb45700.png

 

我们再来看一下 slowpath

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

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

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

00fd7315fb23f1e9259469d600c1c987.png

 

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

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

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

2e9613f68793704b2f09646b8a7613b1.png

 

 

行共享锁

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

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

 

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

f834e338650362795c0ecb7ef4ab5298.png

 

 

行锁锁索引字段的情况

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

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’ 的记录上面增加的索引的 行临键锁, 然后 它还会在具体的数据记录上面增加 行排他锁, 在下一个索引记录上面增加 间隙锁

8a46afb99b01947742a59df0db812e43.png

 

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

2d6fcb2407018bfc657fcab185e33464.png

 

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

c8381254fa12fe7ab9df47194c84cf67.png

 

因此如下 三个 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;”

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

9f78054aebf05d76e451a46bbb88fd32.png

 

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

00a6e95cbf740638e11957947aae68cf.png

 

 

行排他锁阻塞的 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

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

cc2fcf347a086d7c3be9833b1f012f59.png

 

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

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

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

8a565ad5d5d7c3e7c7ebb48204df23cf.png

 

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

631fa8948226edb86ee731b920ec58e4.png

 

 

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

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

04a29159aa990bed9703ac650c4e3d5c.png

 

 

完

 

 

 

相关文章