MySQL锁

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

官方参考

MySQL锁

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

记录锁

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

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

间隙锁

• 间隙锁只有在事务隔离级别 RR(可重复读) 中才会生效.
• 为非唯一索引组成(如class,age等)

select student where age>26 and age<28 lock in share mode ;

使用间隙锁的条件

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

临键锁(Next-key Locks)

• 临键锁只有在事务隔离级别 RR(可重复读) 中才会生效
• 是记录锁与间隙锁的组合
• 可以是唯一索引,也可以是非唯一索引,对其都以间隙锁的形式进行锁定(以唯一索引匹配,并且只匹配到一条数据除外)

临键锁(Next-key Locks) 例子:

tno(唯一索引)	tname	tsex	tbirthday	prof	depart	age(非唯一索引)
858	张旭	1	1969-03-12	讲师	电子工程系	25
857	张旭	女1	1969-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,+∞]

非唯一索引临键锁验证

-- session1
select * from teacher WHERE age between 26 and 28 lock in share mode ;

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

-- 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);

唯一索引临键锁验证

-- session1
select * from teacher WHERE tno between "831" and "856" lock in share mode ;

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

-- 更新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";

---