主键，唯一键，普通索引使用记录锁，间隙锁以及临键锁的情况

主键，唯一键，普通索引使用记录锁，间隙锁以及临键锁的情况

[toc]

环境准备

select version();
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.02 sec)

show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

CREATE TABLE `tb_test1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL COMMENT '普通索引',
  `unique_num` int(11) NOT NULL COMMENT '唯一索引',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_num` (`unique_num`) USING BTREE,
  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (2, 2, 3);
INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (5, 5, 6);
INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (7, 9, 8);
INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (9, 5, 10);

set autocommit =0;

锁的相关概念

innodb-locking

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

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 c1 from where c1 = 10 for update;语句会阻止任何对t.c1=10这条记录进行插入，更新，删除的事务。

间隙锁是对索引记录之间的间隙加锁，或者是对第一条索引记录之前或者最后一条索引记录之后的间隙加锁。比如，select c1 from t where c1 between 10 and 20 for update;语句会阻止任何将15值插入t.c1列，不管该列是否已经有此值，因为该范围内所有现有值之间的间隙都已被锁定。

临键锁是索引记录上的记录锁和索引记录之前的间隙锁的组合。

测试用例

注意，以下每句插入删除，都是在不同的session中，且begin和commit均略写了

主键测试

环境中，主键为id

主键边界
(-INF, 2]
(2, 5]
(5, 7]
(7, 9]
(9, MAX]

第一组用例

SELECT * from tb_test1 where id = 2 FOR UPDATE; #这里是记录锁
SELECT SLEEP(15); #延迟锁时间，能在其他session进行增删语句输入

DELETE from tb_test1 where id=2; #阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (1, 11, 111); #未阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (3, 33, 333); #未阻塞

第二组用例

SELECT * from tb_test1 where id = 3 FOR UPDATE; #这里是间隙锁
SELECT SLEEP(15);

DELETE from tb_test1 where id=2; #未阻塞

DELETE from tb_test1 where id=5; #未阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (4, 44, 444); #阻塞

第三组用例

SELECT * from tb_test1 where id BETWEEN 4 AND 6 FOR UPDATE; #这里是间隙锁
SELECT SLEEP(15);

DELETE from tb_test1 where id=2;  #未阻塞

DELETE from tb_test1 where id=7; #阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (3, 33, 333); #阻塞，3落到了(2, 5)

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (6, 66, 666); #阻塞，6落到了(5, 7)

小结

• 当加锁语句使用主键作为定值查询条件，当定值条件能匹配到某个具体记录，则只会用到行锁；当定值条件并不能找到对应记录，则会用到间隙锁；
• 当加锁语句使用主键作为范围查询条件，会用到间隙锁

唯一键测试

环境中，唯一键为unique_num

唯一键边界
(-INF, 3]
(3, 6]
(6, 8]
(8, 10]
(10, Max]

第一组用例

SELECT * from tb_test1 where unique_num = 3 FOR UPDATE; #这里是记录锁
SELECT SLEEP(15);

DELETE from tb_test1 where unique_num=3; #阻塞 

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (1, 11, 1); #未阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (3, 55, 5); #未阻塞

第二组用例

SELECT * from tb_test1 where unique_num = 5 FOR UPDATE; #这里是间隙锁
SELECT SLEEP(15);

DELETE from tb_test1 where unique_num=3; #未阻塞

DELETE from tb_test1 where unique_num=6; #未阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (4, 44, 4); #阻塞

第三组用例

SELECT * from tb_test1 where unique_num BETWEEN 5 AND 7 FOR UPDATE; #这里是间隙锁?
SELECT SLEEP(15);

DELETE from tb_test1 where unique_num=3; #未阻塞

DELETE from tb_test1 where unique_num=8; #阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (3, 34, 4); #阻塞，4落到了(3, 6)

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (6, 67, 7); #阻塞，7落到了(6, 8)

小结

• 当加锁语句使用唯一键作为定值查询条件，当定值条件能匹配到某个具体记录，则只会用到行锁；当定值条件并不能找到对应记录，则会用到间隙锁；
• 当加锁语句使用唯一键作为范围查询条件，会用到间隙锁

普通索引测试

环境中，普通索引为number

普通索引边界
(-INF, 2]
(2, 5]
(5, 9]
(9, max]

第一组用例

SELECT * from tb_test1 where number = 2 FOR UPDATE; #这里是临键锁（记录锁+间隙锁）
SELECT SLEEP(15); 

DELETE from tb_test1 where number =2; #阻塞

DELETE from tb_test1 where number=5; #非阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (1, 1, 111); #阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (3, 3, 333); #阻塞

第二组用例

SELECT * from tb_test1 where number = 4 FOR UPDATE; #这里是间隙锁
SELECT SLEEP(15);

DELETE from tb_test1 where number=2; #未阻塞

DELETE from tb_test1 where number=5; #未阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (3, 3, 44); #阻塞，3落到了(2, 5)

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (6, 6, 66); #未阻塞

第三组用例

SELECT * from tb_test1 where number BETWEEN 4 AND 8 FOR UPDATE; #这里是间隙锁
SELECT SLEEP(15);

DELETE from tb_test1 where number=2; #未阻塞

DELETE from tb_test1 where number=9; #阻塞

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (3, 3, 4); #阻塞，3落到了(2, 5)

INSERT INTO tb_test1 (`id`, `number`, `unique_num`) VALUES (6, 8, 7); #阻塞，6落到了(5, 9)

小结

• 当加锁语句使用普通索引作为条件，不管定值查询或范围查询，都是间隙锁；

总结

1. 加锁语句，使用主键或唯一键进行定值查询，查询对象存在时，使用的是记录锁（行锁），查询对象不存在时，使用的是间隙锁。
2. 加锁语句，使用主键或唯一键进行范围查询，使用的是间隙锁
3. 加锁语句，使用普通索引进行定值或者范围查询，均使用的是间隙锁
4. 间隙锁可以看做是左右开区间“（）”，而临键锁是记录锁+间隙锁，所以看做是左开右闭区间“（]”，且InnoDB默认加锁为临键锁
5. 间隙锁和临建锁都是为解决幻读问题

参考

MySQL的锁机制 - 记录锁、间隙锁、临键锁

innodb-locking