MySQL 行锁的三种算法

记录锁（Record Lock）

单个记录上的锁。记录锁始终锁定索引记录本身，即使没有定义索引的表也是如此。对于这种情况，InnoDB创建一个隐藏的聚簇索引，并将该索引用于记录锁定。

间隙锁（Gap Lock）

锁定一个范围，但不包括本身。

Next key lock（Record Lock + Gap Lock）

锁定一个范围，但是会锁定记录本身。假设有索引 1,3,5,9。那么可能被锁定的区间是(-∞,1]、(1,3]、(3,5]、(5,9]、(9,+∞]

聚簇索引与非聚簇索引（二级索引）

聚簇索引

官方解释是，通常来说，聚簇索引和主键是同一个意思。（Typically, the clustered index is synonymous with the primary key. ）

如果没有定义主键的话，MySQL 会使用第一个唯一索引作为聚簇索引。

二级索引

除了聚簇索引就是二级索引了。

常见的 SQL 加锁场景

update ... where ... 语句为搜索匹配的每一条记录都设置 next-key lock。如果搜索条件是精确匹配的则只会使用记录锁。

DELETE FROM ... WHERE ... 语句为搜索匹配的每一条记录都设置 next-key lock。如果搜索条件是精确匹配的则只会使用记录锁。

insert 语句为插入的行设置记录锁。

insert into t select ... from s where ... 为插入的每一行设置记录锁。

其他更具体的细节请查看官网：dev.mysql.com/doc/refman/…

示例

以下为默认的隔离级别（可重复读）

CREATE TABLE `info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(100) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

现有id记录为 1、2、3、5、9 的 5 条记录。

A 窗口

start transaction;

update info 
set nickname = 'r'
where id = 1;

这时，id 为 1的数据就会加上记录锁，如果其他事务去操作该记录则会被阻塞。

B 窗口

start transaction;

update info 
set nickname = 'r'
where id = 1;

B 窗口被阻塞了，直到 A 事务完成。以上两个事务完成提交（就不写了）。

现在 next-key lock 可能锁定的区间有：(-∞,1]、(1,2]、(2,3]、(3,5]、(5,9]、(9,+∞]。这时我们执行范围修改：

A 窗口

start transaction;

update info 
set nickname = 'r'
where id > 8;

这时候 A 窗口的匹配条件落在了 (5,9]、(9,+∞] 这两个区间。

当 B 窗口这样执行：

B 窗口

start transaction;

update info 
set nickname = 'r'
where id > 5 and id < 7;

B 窗口被阻塞，直到 A 事务完成。

以上情况是 where 中使用到了索引，那么我们不使用索引会怎么样呢

现在有 age 为 1-5 的 5 条数据。

A 窗口

start transaction;

update info 
set nickname = 'r'
where age > 4;

B 窗口

start transaction;

update info 
set nickname = 'r'
where age > 1 and age < 3;

结果就是导致 B 窗口还是在被阻塞中，这时的锁已经上升到表锁了。

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！