Mysql行级锁

微风至夏

已于 2022-11-10 11:03:09 修改

阅读量1k

点赞数

文章标签： mysql 行级锁记录锁间隙锁插入意向锁

于 2022-11-08 15:58:10 首次发布

本文链接：https://blog.csdn.net/qq_38974073/article/details/127751187

版权

sql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

概念

行级锁由存储引擎实现，这里主要讲的是InnoDB的行级锁，事务的隔离级别是可重复读。

InnoDB通过给索引项加锁实现行锁，所以在进行条件检索时，如果条件列没有索引，则无法使用行级锁，转而使用表级锁。

行级锁从锁定的范围划分：

记录锁
间隙锁
临键锁
插入意向锁

行级锁从锁的功能划分：

读锁（共享）：防止其它事务更新此行。不阻止其它事务加读锁。
写锁（排他）：具有排他性，阻止其它事务查询、更新此行数据。

加锁命令

对于插入、删除、更新语句，InnoDB会自动加写锁。

对于普通查询，不加锁。

我们可以手动加锁：

-- id是主键或辅助索引
SELECT * FROM t1_simple WHERE id=4 LOCK IN SHARE MODE;
-- id是主键或辅助索引
SELECT * FROM t1_simple WHERE id=4 FOR UPDATE;

记录锁（Record Lock）

记录锁仅锁住索引记录的一行，锁的是索引记录，非数据Data。

下面是聚簇索引的B+树结构示例图，演示在使用主键（ID列）进行精确匹配到数据时加记录锁的情形：（主键+精确匹配+命中记录）

聚簇索引加记录锁：update t set col1 = 100 where id = 14;

InnoDB每个表都有聚簇索引，默认情况下主键索引指的就是聚簇索引。

下面这些都是用到记录锁的语句（id是主键）：

-- 加读锁。
select * from t1_simple where id = 1 lock in share mode;
-- 加写锁。
select * from t1_simple where id = 1 for update;
-- 新增，修改，删除加写锁。（InnoDB自动加写锁）
insert into t1_simple values (2, 22);  -- 插入意向锁 + 写锁（写锁会锁定新插入的记录，即称记录锁）
update t1_simple set pubtime=33 where id =2; 
delete from t1_simple where id =2

间隙锁（Gap Locks）

锁住第一个索引记录前面的间隙或锁住最后一个索引记录后面的间隙或锁住索引记录之间的间隙。

加锁的位置是索引记录之间或索引前后，非索引记录本身。

可用于防止幻读，保证间隙之间不会被插入数据。

读已提交事务隔离级别：

不使用间隙锁，所以对于事务的快照读，无法处理幻读问题。
对于当前读，无法解决幻读问题；

可重复读事务隔离级别：

使用了间隙锁，所以在进行快照读的时候，可以处理幻读问题。
对于当前读，需要使用临键锁来处理幻读问题。

下面展示了使用聚簇索引列（ID）进行范围查询未命中记录的情况：（主键 + 范围查询 + 未命中记录）

锁住索引记录之间的间隙：update t set col1 = 100 where id >7 and id < 13;

临键锁（Next-Key Locks）

相当于间隙锁+记录锁的组合。左开右闭区间(间隙锁, 记录锁]

InnoDB默认使用临键锁来锁定记录，不同场景下会退化。查询的索引具有唯一性的话就优化为记录锁。

可重复读隔离级别下，快照读已通过间隙锁避免了幻读，但是对于当前读，我们就需要临键锁来避免幻读。

示例A：聚簇索引

如下图，使用聚簇索引列（ID）进行范围查询时的情况：（主键 + 范围查询 + 存在命中的记录）

示例B：辅助索引

非聚簇索引的索引称辅助索引、普通索引

事务A

begin;
-- 当前读(MVCC分为当前读的快照读，加了for update的普通查询是当前读，读取最新数据)
select * from t1_simple where pubtime = 20 for update;
-- 普通索引命中记录之后还需要进行范围查询，因为可能不止一条记录pubtime=20，所以，20及之后的区间需要加上临建锁。加了锁防止其它事务修改而读取到旧数据。
-- 临键锁区间(10,20],(20,100]

事务B

insert into t1_simple values (16, 19); -- 阻塞
select * from t1_simple where pubtime = 20 for update; -- 阻塞
insert into t1_simple values (16, 50); -- 阻塞
insert into t1_simple values (16, 101); -- 成功，因为不在临键锁的区间内

分析

pubtime 列使用了辅助索引，所以，临键锁是加载辅助索引上。

当我们加写锁的时候，由于是辅助索引（所指不含唯一索引），不具有唯一性，所以找到pubtime = 20的记录之后，还需继续往后找（因为可能有多条记录等于20），同时为了保证当前读，往后的记录要加上临键锁，防止其它事务进行修改，所以从20开始至后面100的记录 (10,20] (20, 100]，都要加上临键锁。

当我们插入pubtime=101的记录时，不在临键锁的区间内，所以不用被阻塞。