初步理解MySQL的gap锁

合集 - MySQL(24)

1.SQL（含索引）2018-06-112.SQL优化（SQL + 索引）2018-06-113.BTree和B+Tree和Hash索引详解2018-06-114.InnoDB和MyISAM是否支持hash索引2018-06-115.MySQL常见的三种存储引擎2018-06-116.Explain执行计划2018-06-117.explain执行计划包含的信息2022-05-318.MySQL 慢查询日志如何查看及配置2022-05-309.如何保障mysql和redis之间的数据一致性？2022-05-3010.MySQL中有哪些锁？2022-05-2611.mysql⾼并发插⼊慢优化【mysql层面优化】2022-05-2512.MySql隔离级别：RU / RC / RR / S + 脏读 / 不可重复读 / 幻读 / 可重复读2020-08-1113.SQL查询 —— 特殊查询2018-06-2214.MySQL主从复制（Master-Slave）实践2018-06-1215.Innodb引擎中B+树一般有几层？能容纳多少数据量？2023-08-2816.MYSQL的JOIN是如何使用索引的2023-07-2517.MySQL如何保证事务内一直读到最新数据？2023-07-2518.MySQL 回表 和 覆盖索引 详解2023-07-2519.Mysql联合索引是如何找到数据的2023-07-2520.MySQL 通过Docker搭建主从同步2022-06-02

21.初步理解MySQL的gap锁2022-06-02

22.mysql_14_count（*）为什么这么慢2022-06-0123.MYSQL索引使用注意事项2022-06-0124.二叉树、B树、B+树、红黑树 的 本质区别以及各个应用场景2022-06-01

收起

初识MySQL的gap，觉得这个设计比较独特，和其他数据库的做法不太一样，所以整理一个简单的memo（虽然关于gap锁，相关资料已经很多了）

1. 什么是gap

说白了gap就是索引树中插入新记录的空隙。相应的gap lock就是加在gap上的锁，还有一个next-key锁，是记录+记录前面的gap的组合的锁。

2. gap锁或next-key锁的作用

简单讲就是防止幻读。通过锁阻止特定条件的新记录的插入，因为插入时也要获取gap锁(Insert Intention Locks)。

3. 什么时候会取得gap lock或nextkey lock

这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。

3.1 REPEATABLE READ

locking reads，UPDATE和DELETE时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。

下面对非唯一索引做个测试。

表定义如下:

mysql> show create table tb2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| tb2   | CREATE TABLE `tb2` (
  `id` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  KEY `tb2_idx1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意id只是索引，不是主键

表中有3条记录： 10,20,30。

mysql> select * from tb2;
+------+------+
| id   | c1   |
+------+------+
|   10 |    0 |
|   20 |    0 |
|   30 |    0 |
+------+------+
3 rows in set (0.01 sec)

在REPEATABLE READ下，更新一条记录不提交，然后看看能阻塞另外的会话哪些操作。

SESSION 1:

SESSION 1中更新id=20的记录

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

mysql> update tb2 set c1=2 where id=20;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SESSION 2:

SESSION 2中，执行插入操作，发现[10,30)范围不能插入数据。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb2 values(9,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb2 values(10,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(19,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(20,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(21,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(29,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into tb2 values(30,4);
Query OK, 1 row affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tb2 set c1=4 where id=10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update tb2 set c1=4 where id=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update tb2 set c1=4 where id=30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

如果SESSION 1的表扫描没有用到索引，那么gap或next-key锁住的范围是整个表，即任何值都不能插入。

3.2 READ COMMITTED

只会锁住已有记录，不会加gap锁。

3.3 SERIALIZABLE

和REPEATABLE READ的主要区别在于把普通的SELECT变成SELECT ... LOCK IN SHARE MODE，即对普通的select都会获取gap锁或next-key锁。

4. REPEATABLE READ和幻读

在“consistent-read”时，REPEATABLE READ下看到是事务开始时的快照，即使其它事务插入了新行通常也是看不到的，所以在常见的场合可以避免幻读。 但是，"locking read"或更新，删除时是会看到已提交的修改的，包括新插入的行。

下面看一个例子

SESSION 1:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)

SESSION 2:

mysql> update tb1 set c1=101 where id =1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0 

SESSION 1:

mysql> select id,c1 from tb1 where id=1 LOCK IN SHARE MODE;
+----+------+
| id | c1   |
+----+------+
|  1 |  101 |
+----+------+
1 row in set (0.00 sec)

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)

mysql> update tb1 set c1=c1+1000 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 | 1101 |
+----+------+
1 row in set (0.00 sec)

上面update的行为违反了REPEATABLE READ的承诺，看到了事务开始后其它事务的并发更新。这对应用开发需要特别注意，这种情况下其它数据库通常都是报错的。

5. 其它

RR和RC相比还有一个重要的区别，RC下，扫描过但不匹配的记录不会加锁，或者是先加锁再释放，即semi-consistent read。但RR下扫描过记录都要加锁。这个差别对有全表扫描的更新的场景影响极大。详细参考http://hedengcheng.com/?p=771，关于MySQL的加锁处理，这篇文章讲得很透彻！