innodb 存储引擎。select * from test_table where id = 3 for update; 其中 id 为唯一索引，且不存在 id = 3 这一行，此时为何要使用间隙锁呢？

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 2435 days ago, the information mentioned may be changed or developed.

25 replies • 2022-03-24 17:52:53 +08:00

RedisMasterNode

Mar 5, 2020

id 是唯一索引的时候没有使用 next_key locking，应该是 record lock

jdz

Mar 6, 2020 via Android

@RedisMasterNode 你可以试一下，此时插入 2 也会失败

simonlu9

Mar 6, 2020

很简单，正常会锁，因为在可重复读的情况下，在开启事务中，select * from test_table where id = 3 for update 这一句第一次查询是没有数据，第二次查询我也要没有数据，那么怎么保证，只能开间歇锁范围，范围是多少，决定在于你 id=3 这个记录的索引范围，假如 id 没有索引，肯定会锁全表

jdz

Mar 6, 2020 via Android

@simonlu9
为什么不是只是锁 3 那一行呢，而是要锁一个范围呢？

hhyvs111

Mar 6, 2020

第三行不存在，所以要锁一定的范围。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由 MySQL Server 层进行过滤，因此也就把所有的记录都锁上了。

RedisMasterNode

Mar 6, 2020 via Android

@jdz 你的描述很模糊，id=3 这行存在？

RedisMasterNode

Mar 6, 2020 via Android

@RedisMasterNode 抱歉是我眼睛很模糊（滑跪！）如果按照这个描述的话不存在 3 这行当然会进行范围锁 orz

simonlu9

Mar 6, 2020

@jdz 3 这一行根本不存在，在保持可重复读的情况下，是不允许其他事务插入 3 这一行的操作，由于你存在 3 这一行，所以要锁范围，至于范围是多少，是采取于你的记录

simonlu9

Mar 6, 2020

@simonlu9 不存在 3 这一行

sanggao

Mar 6, 2020

怎么证明使用了间隙所

zifangsky

Mar 6, 2020

Next-Key 锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（ GAP ）”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的 Next-Key 锁。

举例来说，假如 emp 表中只有 101 条记录，其 empid 的值分别是 1、2、…、100、101，下面的 SQL：
Select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁，也会对 empid 大于 101 （这些记录并不存在）的“间隙”加锁。

同样，InnoDB 除了通过范围条件加锁时使用 Next-Key 锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用 Next-Key 锁！

——《深入浅出 MySQL 》

至于为什么要设置这种这种机制，坐等其他人回答

sujin190

Mar 6, 2020

既然锁是和记录关联的，你都说了这行不存在，那如何给这一行加锁呢，虽然我房子还没建但是你先给我上个锁的意思？？

BBCCBB

Mar 6, 2020

就是因为没有 id=3, 才会把比 3 小的第一个 id 和比 3 大的第 1 个 id 这个区间锁起来, 防止插入 id=3 的.

fancy111

Mar 6, 2020

楼上正确

gaius

Mar 6, 2020 via Android

id 是主键，因为你 for update 了，所以用的 gap，否则是行。

gaius

Mar 6, 2020 via Android

我错了..上面我瞎说的原来是不存在

jdz

Mar 6, 2020 via Android

@BBCCBB 为什么不是仅仅锁定 3，而是要锁定一个区间呢

chibupang

Mar 6, 2020 via iPhone

因为数据库引擎无法判断你当前的事务需要对 id=3 的字段做什么操作。即使这个记录不存在，但是当前事务的后续操作可能是插入一条 id=3 的记录，如果是这样，必然是介于 id=2 到 id=4 之间，如果不范围锁定的话会出现什么情况？可能另一事务也正在插入一条 id=3 的记录，这样就会出现两条不同的 id=3 记录。