乐趣区

关于mysql:MySql-InnoDB行锁三剑客

jiezi

前言

行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有应用索引，那么该锁就会进化为表锁。

一、行锁三剑客是什么？

行锁三剑客指的是：InnoDB 引擎下的记录锁（Record Locks），间隙锁（Gap Locks），临键锁（Next-Key Locks）。

二、记录锁（Record Locks）

记录锁存在于包含主键索引在内的惟一索引中，锁定单条索引记录

— id 列为主键列或惟一索引列

SELECT * FROM table WHERE id = 1 FOR UPDATE;

id 为 1 的记录行会被锁住。
须要留神的是：

**id 列必须为惟一索引列或主键列，否则上述语句加的锁就会变成临键锁。
同时查问语句必须为精准匹配（=），不能为 >、<、like 等，否则也会进化成临键锁 **

其余实现
在通过 主键索引 与 惟一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

— id 列为主键列或惟一索引列

UPDATE SET age = 50 WHERE id = 1;

三、间隙锁（Gap Locks）

间隙锁基于非惟一索引，它锁定一段范畴内的索引记录。间隙锁基于上面将会提到的 Next-Key Locking 算法，请务必牢记：应用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

间隙锁测试：
test 表中，主键 id,age

在进行测试之前，咱们先来看看 test 表中存在的暗藏间隙：

  1. (-infinity, 1）
  2. (1, 5）
  3. (5, 7）
  4. (7, 11）
  5. (11, +infinity）

场景一：只应用记录锁，不会产生间隙锁
开启事务一，并执行主键为 id= 5 的语句。

事务 2 开启执行 insert, 失常执行不受影响。

场景二：产生间隙锁 (主键索引)
事务一依然是开启事务，然而执行的是范畴内的查问。

事务二开启，而后执行插入语句，插入 id=6.age =8.

从下面咱们能够看到，(5, 7)、(7, 11) 这两个区间，都不可插入数据，其它区间，都能够失常插入数据。所以咱们能够得出结论：当咱们给 (5, 7)这个区间加锁的时候，会锁住 (5, 7)、(7, 11] 这两个区间。

场景三：产生间隙锁（主键索引 + 锁不存在的数据）

Id= 3 并不存在这样的数据，发现锁住了区间 (1,5]

论断

  1. 对于指定查问某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;
  2. 对于查找某一范畴内的查问语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;

场景四：产生间隙锁（非主键索引）
在这张表上，咱们有 id age 这两个字段，id 是咱们的主键，咱们在 age 上，建也立了一个一般索引，为了不便咱们前面的测试
在进行测试之前，咱们先来看看 test1 表中 number 索引存在的暗藏间隙：

  1. (-infinity, 1)
  2. (1, 3)
  3. (3, 8)
  4. (8, 12)
  5. (12, +infinity)
    案例阐明
    咱们执行以下的事务（事务 1 最初提交），别离执行上面的语句：
/* 开启事务 1 */
BEGIN;
/* 查问 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 提早 30 秒执行，避免锁开释 */
SELECT SLEEP(30);

# 留神：以下的语句不是放在一个事务中执行，而是离开屡次执行，每次事务中只有一条增加语句

/* 事务 2 插入一条 number = 0 的数据 */
INSERT INTO `test1` (`number`) VALUES (0); # 失常执行

/* 事务 3 插入一条 number = 1 的数据 */
INSERT INTO `test1` (`number`) VALUES (1); # 被阻塞

/* 事务 4 插入一条 number = 2 的数据 */
INSERT INTO `test1` (`number`) VALUES (2); # 被阻塞

/* 事务 5 插入一条 number = 4 的数据 */
INSERT INTO `test1` (`number`) VALUES (4); # 被阻塞

/* 事务 6 插入一条 number = 8 的数据 */
INSERT INTO `test1` (`number`) VALUES (8); # 失常执行

/* 事务 7 插入一条 number = 9 的数据 */
INSERT INTO `test1` (`number`) VALUES (9); # 失常执行

/* 事务 8 插入一条 number = 10 的数据 */
INSERT INTO `test1` (`number`) VALUES (10); # 失常执行

/* 提交事务 1 */
COMMIT;

咱们会发现有些语句能够失常执行，有些语句被阻塞了。咱们再来看看咱们表中的数据：

这里能够看到，number (1 – 8) 的间隙中，插入语句都被阻塞了，而不在这个范畴内的语句，失常执行，这就是因为有间隙锁的起因
这是为什么呢？咱们来看看下边的图，大家就明确了。

论断

  1. 在一般索引列上，不论是何种查问，只有加锁，都会产生间隙锁，这跟惟一索引不一样；
  2. 在一般索引跟惟一索引中，数据间隙的剖析，数据行是优先依据一般索引排序，再依据惟一索引排序。

四、临键锁（Next-Key Locks）

** 临键锁，是记录锁与间隙锁的组合，它的封闭范畴，既蕴含索引记录，又蕴含索引区间。
注：临键锁的次要目标，也是为了防止幻读(Phantom Read)。如果把事务的隔离级别降级为 RC，临键锁则也会生效。**

Next-key Lock A next-key lock is a combination of a record lock on the
index record and a gap lock on the gap before the index record.

当咱们应用索引进行范畴查问，命中了记录的状况下，就是应用了临键锁，他相当于记录锁 + 间隙锁。

两种进化的状况：

唯一性索引，等值查问匹配到一条记录的时候，进化成记录锁。

没有匹配到任何记录的时候，进化成间隙锁。

左开右闭区间，目标是为了解决幻读的问题。

select * from xx where id > 5 and id < 9;

下面的 sql 命中了 id = 7 的记录，也蕴含了记录不存在的区间，所以他锁住（4，7]和（7，10]区间，在这区间，别的事务插入不了数据，所以解决了幻读问题。

总结

以上就是这次对 mysql 行锁三剑客的了解和总结，次要参照 mysql 官网文档资料并退出本人的实际和了解，贴一下 mysql 官网文档地址，心愿大家多多反对和关注。

退出移动版