一文掌握MySQL唯一索引是如何加锁的_数据库_开发者

数据准备

CREATE TABLE user (
id bigint NOT NULL AUTO_INCREMENT,
name varchar(30) COLLATE utf8mb4_unicode_ci NOT编程客栈 NULL,
age int NOT NULL,
PRIMARY KEY (id),
KEY index_age (age) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

一文掌握MySQL唯一索引是如何加锁的

唯一索引等值查询

本案例其实就是在主键索引上进行等值查询，取决于查询记录是否存在，存在退化成记录锁，否则就是在索引树中找到第一个大于该查询记录的记录后，将改记录的索引中的next-key lock退换成间隙锁。

记录存在

当执行如下 id=1的锁，会给id=1的记录jiashangX型的记录锁

BEGIN;
SELECT * from user androidwhere id = 1 for update;

一文掌握MySQL唯一索引是如何加锁的

可以发现对，id=1的记录加了记录锁。update user set age = 25 where id = 1; DELETE FROM user WHERE id = 1; 语句都会被阻塞。

图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思
通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：
如果 LOCK_MODE 为 X，说明是next-key 锁；
如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
如果 LOCK_MODE 为 X,GAP，说明是间隙锁；

这里简单聊下，为什么唯一索引下等值查询就可以将next-key lock退化成记录锁，因为对指定行的操作加锁，可以直接避免其他事务对该行的删除、插入的时候，就可以避免幻读问题。

一文掌握MySQL唯一索引是如何加锁的

记录不存在

好了上面主要说的是针对记录存在，针对记录不存在的唯一索引添加锁。

BEGIN;
SELECT * from user where id = 2 for update;

一文掌握MySQL唯一索引是如何加锁的

当LOCK_MODE是间隙锁或者Next-key LOCK时，LOCK_DATA代表的就是锁的右边界，锁的左边界就是表中id为5的上一条记录的id值，也就是1，所以本次间隙锁的范围就是(1,5)。当执行INSERT INTOtest.user (id, name, age) VALUES (3, 'zhangsan2', 21); 会获取锁失败，阻塞。

这里由一个疑问就是为什么唯一索引记录不存在的情况下，会从next-key lock退化成间隙锁。
其实这种情况仅靠间隙锁就能解决。幻读的问题。
为什么不可以针对不存在的记录加记录锁，锁是加在索引上的，记录不存在，自然没办法锁住这条不存在的记录。

唯一索引范围查询

针对唯一索引范围查询，会对扫描到的索引加next-key锁

大于或者大于等于的范围查询

情况1:针对大于等于的范围查询，存在等值条件，那么该记录索引中的next-key 退化成记录锁。

BEGIN;
SELECT * from user where id > 15  for update;

一文掌握MySQL唯一索引是如何加锁的

1.首先找到的是id=20这一行，然后对该(15,20] 添加间隙锁。

2.由于是范围查询，innodb存储引擎中，有特殊的记录标识最后一条记录。supremum pseudo-record 所以扫描第二行的时候加的是(20,+8]的next- key

当对 16 17 18 19 20 以及20以上的记录进行删除更新插入操作时，会被阻塞。

>=情况

BEGIN;
SELECT * from user where id >= 15  for update;

一文掌握MySQL唯一索引是如何加锁的

从图中可以看到加了三个锁，由于可以定位到id=15这样记录，所以针对ID=15的记录添加的就是记录锁，而接着扫http://www.devze.com描的就是20这行记录，对(15,20] 加间隙锁，(20,+8)加间隙锁。

从本案例中可以获取当大于等于时，如果有等于就会针对等于的记录加记录锁。

小于或者小于等于的范围查询

BEGIN;
SEhttp://www.devze.comLECT * from user where id < 6  for update;

一文掌握MySQL唯一索引是如何加锁的

针对「小于或者小于等于」的唯一索引范围查询，如果条件值的记录不在表中，那么不管是「小于」还是「小于等于」的范围查询，扫描到终止范围查询的记录时，该记录中索引的 next-key 锁会退化成间隙锁，其他扫描的记录，则是在这些记录的索引上加 next-key 锁。

< 情况

BEGIN;
SELECT * from user where id < 5  for update;

一文掌握MySQL唯一索引是如何加锁的

非唯一索引等值查询

对非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是二级索引。所以在加锁时，同时对这两个索引都加锁。但是对主键索引加锁的时候，只有满足查询条件的记录才会对主键索引加锁。

非唯一性二级索引、主键索引都加锁

主键索引查询只针对主键索引加锁

没有加索引的查询

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

update和delete语句如果查询条件不加索引，扫描方式也是全表扫描，对每一条记录加next-key 锁，相当于锁住的全表。

BEGIN;
SELECT * from user where name = 'qxlxi'  for update;
update user set age = age + 1 WHERE name = 'qxlxi';
DELETE FROM user WHERE name = 'qxlxi';
select * from performance_schema.data_locks;

一文掌握MySQL唯一索引是如何加锁的