测试数据：
CREATE TABLE `lock_test` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `idx_b` (`b`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `lock_test` (`a`, `b`, `c`, `d`) VALUES (10, 1, 1, 1);
INSERT INTO `lock_test` (`a`, `b`, `c`, `d`) VALUES (20, 3, 4, 5);
INSERT INTO `lock_test` (`a`, `b`, `c`, `d`) VALUES (30, 6, 8, 10);
INSERT INTO `lock_test` (`a`, `b`, `c`, `d`) VALUES (40, 9, 12, 15);主键索引-等值查询-键存在
T1 | T2 |
begin; | |
select * from lock_test where a=10 for update; | |
begin; | |
select * from lock_test where a=10 for update; – 阻塞 | |
insert into lock_test values(19, 19, 1, 1); – 成功 | |
update lock_test set b=1 where a=10; – 阻塞 | |
rollback; |
结论：T1对a=10的数据加了记录锁，记录锁与记录锁互斥。
主键索引-等值查询-键不存在
T1 | T2 |
begin; | |
select * from lock_test where a=15 for update; | |
begin; | |
select * from lock_test where a=15 for update; – 成功 | |
insert into lock_test values(19, 19, 1, 1); # 阻塞 | |
update lock_test set b=1 where a=10; # 成功 | |
rollback; |
结论：T1对a为(10,20)的区间加了GAP锁，GAP锁与GAP锁之间不互斥。
唯一索引-等值查询-键存在
T1 | T2 |
begin; | |
select * from lock_test where b=3 for update; | |
begin; | |
insert into lock_test values(19, 4, 1, 1); – 成功 | |
update lock_test set c=4 where b=3; – 阻塞 | |
rollback; |
结论：T1对b=3的数据加了记录锁。
唯一索引-等值查询-键不存在
T1 | T2 |
begin; | |
select * from lock_test where b=5 for update; | |
begin; | |
insert into lock_test values(19, 4, 1, 1); – 阻塞 | |
update lock_test set c=4 where b=3; – 成功 | |
rollback; |
结论：T1对b为(3,6)的区间加了GAP锁。
普通索引-等值查询-键存在
T1 | T2 |
begin; | |
select * from lock_test where c=4 for update; | |
begin; | |
insert into lock_test values(19, 42, 5, 1); – 阻塞 | |
update lock_test set b=3 where c=4; – 阻塞 | |
rollback; |
结论：T1对c为(1,8)的区间数据加了GAP锁。
普通索引-等值查询-键不存在
T1 | T2 |
begin; | |
select * from lock_test where c=6 for update; | |
begin; | |
insert into lock_test values(19, 42, 5, 1); – 阻塞 | |
update lock_test set b=3 where c=4; – 成功 | |
rollback; |
结论：T1对c为(4,8)的区间数据加了GAP锁。
唯一索引-范围查询
T1 | T2 |
begin; | |
select * from lock_test where a>=10 and a<15 for update; | |
begin; | |
insert into lock_test values(19, 42, 5, 1); – 阻塞 | |
update lock_test set b=3 where a=20; – 阻塞 | |
rollback; |
结论：T1对a为[10,20]的区间数据加了next-key锁。
普通索引-范围查询
T1 | T2 |
begin; | |
select * from lock_test where c>=8 and c<10 for update; | |
begin; | |
insert into lock_test values(19, 42, 7, 1); – 阻塞 | |
insert into lock_test values(19, 42, 11, 1); – 阻塞 | |
rollback; |
结论：T1对c为(4,12]的区间数据加了next-key锁。
普通索引-limit
T1 | T2 |
begin; | |
select * from lock_test where c=8 limit 1 for update; | |
begin; | |
insert into lock_test values(19, 42, 7, 1); – 阻塞 | |
insert into lock_test values(19, 42, 11, 1); – 成功 | |
rollback; |
结论：T1对c为(4,8]的区间数据加了next-key锁。
无索引
T1 | T2 |
begin; | |
select * from lock_test where d=10 for update; | |
begin; | |
insert into lock_test values(19, 42, 7, 1); – 阻塞 | |
insert into lock_test values(19, 42, 11, 100); – 阻塞 | |
rollback; |
结论：T1对整个表都加了锁。
总结
- 等值查询：针对唯一索引，如果该记录不存在，会产生间隙锁，如果记录存在，则只会产生记录锁；而普通索引会产生间隙锁
- 对于查找某一范围内的查询语句，不管什么索引都会产生next-key锁，普通索引可以使用limit减少锁区间范围。
- 如果列上无索引会导致锁表。