InnoDB存储引擎---间隙锁

最新推荐文章于 2024-06-27 19:38:12 发布

迪迪迦

最新推荐文章于 2024-06-27 19:38:12 发布

阅读量256

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_31426247/article/details/104235401

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

之前翻阅过很多博客，关于InnoDB存储引擎在REPEATABLE READ隔离级别下如何解决幻读的情况的？大多数的答案是MVCC+间隙锁。对于MVCC上一文章中也提及过，MVCC是保存了行记录的多种快照版本，当一个事务A正在执行某条记录的update/delete语句时，此时该记录加了X锁，如果事务B需要读取行，根据行锁的S锁，X锁兼容性来看，需要等待事务A释放该行的X锁，事务B才能获取S锁，读取到行记录。

但是InnoDB存储引擎通过乐观锁的机制帮我们实现了一致性非锁定读，即可以读取行记录的历史快照的方式，无需等待锁的释放。

此时REPEATABLE READ和READ COMMITTED的隔离级别对读取快照的方式不一样。参考上文。因此认为MVCC实现的方式解决的是数据库并发读取的一个问题，提高了数据库的并发读的能力，避免了等待锁释放。

那什么才是解决幻读的方式？在《MySQL技术内幕 InnoDB存储引擎实现》一书中找到了答案。
在这里插入图片描述
InnoDB存储引擎是采用Next-Key Locking机制来避免幻读（幻象）问题的。

什么是Next-Key Locking机制？
在InnoDB存储引擎中存在三种行锁的算法。

Record Lock：单个行记录上的锁。
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
Next-Key Lock：Record Lock+Gap Lock，锁定一个范围，并锁住本身。

Record Lock总是去锁定记录行的本身。当创建表时，未指定任何索引时，InnoDB存储引擎会隐式的使用主键来进行锁定。

Gap Lock间隙锁，以为着总是锁定一个区间范围，但是不包括记录本身。

Next-Key Lock是Record Lock与Gap Lock的结合，InnoDB存储引擎在行的读取时默认使用这种行算法。
在这里插入图片描述
建立一张如上图的表，在id上建立索引，则该索引可能被Next-Key Locking的区间为：（-∞，1] (1,2] (2,5] (5,+∞)。

当索引为唯一索引时，Next-Key Lock会退化为Record Lock

唯一索引案例：等值查询。（记录存在，锁记录）

开始会话A，查询id=2的记录，并加上X锁。

#sessionA
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gap where id=2 for update;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

开启会话B，因为id是唯一索引，所以会退化为Record Lock，而不是（-∞，1] (1,2] (2,5] (5,+∞)这些区间。

#sessionB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into gap(id) value(4);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

唯一索引：范围查询。
当会话A不是等值查找时，又会是什么情况？

#sessionA
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gap where id>2 for update;
+----+
| id |
+----+
|  4 |
|  5 |
+----+
2 rows in set (0.00 sec)

#sessionB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into gap (id) value (3);
Query OK, 1 row affected (22.97 sec)

#sessionA
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from gap where id>2 for update;
+----+
| id |
+----+
|  4 |
|  5 |
+----+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

很明显，当会话A查找id>2的时候，在会话A未提交的前提下，会话B想要插入一条id=3的记录时，阻塞了，等到会话A提交的时候，会话B的操作完成（中间间隔了22秒）。说明此时对于id>2这个范围加了X锁。

结论：
对于唯一索引，当等值查询的时候，Next-Key Lock会转换成Record Lock，只对行加锁。当范围查询的时候，InnoDB存储引擎依然采用Next-Key Lock算法。阻塞了对范围内数据的修改。

辅助索引
相对于唯一索引，辅助索引（二级索引）情况会完全不一样。
创建表：

CREATE TABLE `second` (a INT,b INT ,c INT ,PRIMARY KEY(a), KEY(b));

创建了唯一索引a，普通索引b。添加测试数据。
在这里插入图片描述

案例：唯一索引的等值查询。（记录不存在，范围锁）

#sessionA
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from second where a = 11 for update;
Empty set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#sessionB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into second value(12,12,12);
Query OK, 1 row affected (6.22 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

当会话A查询一条不存在的记录a=11时，数据库会对id=（10，15]区间加上间隙锁，而会话B需要插入一条a=12的记录，因此被锁住。相反，如果记录存在，则只锁定该记录。

案例：辅助索引的唯一查询。

#SessionA
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from second where b=11 for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 10 |   11 |   12 |
+----+------+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from second where b = 11 for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 10 |   11 |   12 |
+----+------+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#SessionB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into second value(12,12,12);
Query OK, 1 row affected (7.96 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into second value(9,10,12);
Query OK, 1 row affected (4.94 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

对辅助索引b=11的记录加锁，发现当我们插入b=12，b=10的记录时。都会被阻塞住，由此可知，Next-Key Lock会对锁定对象的前一个区间，后一个区间同时加锁即（6，11）（11，16）

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into second value(12,11,12);
Query OK, 1 row affected (16.45 sec)
mysql> commit;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into second value(14,16,12);
Query OK, 1 row affected (10.67 sec)
mysql> commit;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into second value(13,17,12);
Query OK, 1 row affected (0.00 sec)
mysql> insert into second value(6,6,12);
Query OK, 1 row affected (26.75 sec)
mysql> commit;

后续实践证明，对于b=6，b=11，b=16的插入也会阻塞，所以这里可以理解为，对于辅助索引，会在[6，16]包含左右区间值的插入都会加锁。了解完此时的辅助索引，再来看看唯一索引会怎么加锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from second where b=11 for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 10 |   11 |   12 |
+----+------+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from second where a=10 lock in share mode;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 10 |   11 |   12 |
+----+------+------+
1 row in set (14.47 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

由此可知，当记录的辅助索引加了间隙锁之后，唯一索引也加了X锁。上面的例子中插入(13,17,12)记录可以说明，此时行记录对应的唯一索引只锁住当前行。

案例辅助索引的范围查询
在这里插入图片描述
还是使用这3条数据做实验。

#sessionA
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from second where b>=11 and b<16 for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 10 |   11 |   12 |
+----+------+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

#sessionB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into second value(2,5,6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into second value(1,6,6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into second value(6,10,6);
Query OK, 1 row affected (13.88 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

到目前为止，对于b<=6的数据都不阻塞。

#sessionB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into second value(9,11,6);
Query OK, 1 row affected (4.51 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

插入b=11的数据也是会被阻塞。

#sessionB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into second value(9,15,6);
Query OK, 1 row affected (4.51 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

插入b=15的数据也是会被阻塞。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into second value(9,16,6);
Query OK, 1 row affected (6.99 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

插入b=16的数据也是会被阻塞。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into second value(9,17,6);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

当插入b=17时不会被阻塞。

辅助索引总结：当范围查询时，当b>=11&&b<16时，会对(6,16]区间的数据进行阻塞。而此时对于唯一索引仍然只是锁定行记录。

间隙锁总结：
表中只存在唯一索引时：
唯一索引的等值查询（记录不为空）时，只会对该记录行进行锁定。
唯一索引的等值查询（记录为空）时，会采用间隙锁，左开右闭的规则加锁。
唯一索引的范围查询时，会对整个范围区间加锁。

表中存在辅助索引的时：
唯一索引锁定规则同上。
辅助索引等值查询时，会采用前区间+后区间的方式加间隙锁，实践证明区间左右侧都在锁定的范围。
辅助索引范围查询时，会采用间隙锁规则加锁，加锁区间可以参考如上实践。

迪迪迦

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
InnoDB存储引擎---间隙锁

之前翻阅过很多博客，关于InnoDB存储引擎在REPEATABLE READ隔离级别下如何解决幻读的情况的？大多数的答案是MVCC+间隙锁。对于MVCC上一文章中也提及过，MVCC是保存了行记录的多种快照版本，当一个事务A正在执行某条记录的update/delete语句时，此时该记录加了X锁，如果事务B需要读取行，根据行锁的S锁，X锁兼容性来看，需要等待事务A释放该行的X锁，事务B才能获取S锁，读...
复制链接

扫一扫

专栏目录