[原创]个人理解，请批判接受，有误请指正。转载请注明出处: https://heyfl.gitee.io/database/innodb-record-level-locks.html

官方参考

MySQL锁

  • InnoDB锁机制是基于索引建立的
  • 如果SQL语句中匹配不到索引,那么就会升级为表锁

记录锁

1
2
3
4
-- id 列为主键列或唯一索引列
SELECT * FROM table WHERE id = 1 FOR UPDATE;
或
update table set age=2 WHERE id = 1;

通过唯一索引实现的记录锁,只会锁住当前记录(必须为=不然会退化为临键锁)

间隙锁

  • 间隙锁只有在事务隔离级别 RR(可重复读) 中才会生效.
  • 为非唯一索引组成(如class,age等)
1
select student where age>26 and age<28 lock in share mode ;

使用间隙锁的条件

  • 命中普通索引锁定；
  • 使用多列唯一索引；
  • 使用唯一索引命中多行记录

临键锁(Next-key Locks)

  • 临键锁只有在事务隔离级别 RR(可重复读) 中才会生效
  • 是记录锁与间隙锁的组合
  • 可以是唯一索引,也可以是非唯一索引,对其都以间隙锁的形式进行锁定(以唯一索引匹配,并且只匹配到一条数据除外)
临键锁(Next-key Locks) 例子:
tno(唯一索引)tnametsextbirthdayprofdepartage(非唯一索引)
858张旭11969-03-12讲师电子工程系25
857张旭女11969-03-12讲师电子工程系25
856张旭男1969-03-12讲师电子工程系25
831刘冰女1977-08-14助教电子工程系29
825王萍女1972-05-05助教计算机系28
804李诚男1958-12-02副教授计算机系26

其中有唯一索引的临键为:
(-∞,804]
(804,825]
(825,831]
(831,856]
(856,857]
(857,858]
(858,+∞]

其中有非唯一索引的临键为:
(-∞,25]
(25,26]
(26,28]
(28,29]
(29,+∞]

非唯一索引临键锁验证
1
2
3
-- session1
select * from teacher WHERE age between 26 and 28 lock in share mode ;

这时候会锁定非唯一索引的临键 (25,29]
所以我们测试更新age=25成功 插入age=27阻塞 更新age=29阻塞 插入age=30成功即可验证

1
2
3
4
5
6
7
8
9
10
11
12
13
-- session2
-- 更新age=25-->成功
update teacher set tsex='女1' WHERE age=25;

-- 插入age=27阻塞
insert into `test`.`teacher` ( `tno`, `tname`, `tsex`, `tbirthday`, `prof`, `depart`,`age`) values ( '740', '张旭1', '12', '1969-03-12 00:00:00', '讲师', '电子工程系',27);

-- 更新age=29-->阻塞
update teacher set tsex='女1' WHERE age=29;


-- 更新age=30-->成功
insert into `test`.`teacher` ( `tno`, `tname`, `tsex`, `tbirthday`, `prof`, `depart`,`age`) values ( '740', '张旭1', '12', '1969-03-12 00:00:00', '讲师', '电子工程系',30);
唯一索引临键锁验证
1
2
-- session1
select * from teacher WHERE tno between "831" and "856" lock in share mode ;

根据上面的sql,我们匹配到唯一索引临键锁为:(825,857]
所以我们测试更新tno=825-->成功 更新tno=857阻塞 更新age=858成功即可验证

1
2
3
4
5
6
-- 更新tno="825"-->成功
update teacher set tsex='女1' WHERE tno="825";
-- 更新tno="857"-->阻塞
update teacher set tsex='女1' WHERE tno="857";
-- 更新tno="858"-->成功
update teacher set tsex='女1' WHERE tno="858";