接上篇
Record lock
Gap lock
Next-Key lock
record lock 按照索引记录加锁,如果没有则采用隐式的主键来锁定。
next-key 结合了Gap lock 和 record lock ,例如索引值:10,11,13,20。则按next-key 锁定的区间为(-∞,10],(10,11]
,(11,13],(13,20],(20,+∞)
注意此处指非唯一索引,如果是唯一索引,会降级为Record lock ,仅仅锁住索引本身,而非范围。同时这种降级只发生在查询所有索引列的情况下,如果存在联合索引且只是查询联合索引的某一列,即属于range类型查询,仍是采用Next-key锁定,不会降级。
举个存在唯一索引和辅助索引的例子做说明:
create table test ( a int , b int , primary key (a), key(b));
insert into test select 1, 1;
insert into test select 3, 1;
insert into test select 5, 3;
insert into test select 7, 6;
insert into test select 10,8;
执行 select * from test where b = 3 for update
存在两个索引,分别加锁,唯一主键列a加record lock , 辅助索引列b加next-key lock (1,3) 以及给下一个值的区间(3,6)加gap锁;
因此在另一个事务里执行以下语句都会阻塞,具体分析:
select * from test where a = 5 lock in share mode;
insert into test select 4,2;
insert into test select 6,5;
第一个阻塞因为加了唯一索引的record lock a = 5;
第二个主键插入4,符合条件,但是根据辅助索引b 的范围, b = 2 在(1,3)中,同样阻塞;
第三个a =6 不在主键a锁定范围,b = 5 也不在辅助索引b 的范围(1,3)中,但在另一个gap锁范围(3,6)中,因此也阻塞;
这种锁定情形下,可以执行的包括类似语句:
insert into test select 8,6;
insert into test select 2,0;
gap lock 可以通过设置隔离级别为读已提交或者将innodb_locks_unsafe_for_binlog = 1取消,但是从隔离性,性能,以及主从数据一致等方面都不建议这样做。
insert的特殊情况
对于insert 会检查下一条记录是否被锁定,如上述例子有select * from test where b = 3 for update插入insert into test select 2,2会检测到b = 3 已经被锁定,而insert into test select 2,0可以执行;
[1]:《MySQL技术内幕:InnoDB存储引擎》-第六章:锁