InnoDB Next-Key Lock

InnoDB有三种行锁的算法：

1，Record Lock：单个行记录上的锁

2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身

3，Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身

一、在RR隔离级别测试

SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

create table t(a int,key idx_a(a))engine =innodb;

insert into t values(1),(3),(5),(8),(11);

上面索引值有1，3，5，8，11，其记录的GAP的区间如下：（-∞,1]，(1,3]，(3,5]，(5,8]，(8,11]，(11,+∞）

--锁定存在的值
select * from t where a = 8 for update;

--锁定不存在的值
select * from t where a = 9 for update;

 插入5，6，7，8，9，10 会被锁住, 其他正常。

二、关闭Gap Lock的方式

1. RC隔离级别

mysql> set global transaction isolation level read committed;

2. innodb_locks_unsafe_for_binlog=ON

SHOW VARIABLES LIKE '%innodb_locks_unsafe_for_binlog%';

三、总结

InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围。

当查询的条件含有唯一索引时，Next-Key Lock 会进行优化，将其降级为Record Lock。

 启用innodb_locks_unsafe_for_binlog或read-committed后，可以使得InnoDB gap锁最小化，但是在两种场景(外键约束和唯一索引)中，仍然不可避免的存在gap锁。

 

出处：

InnoDB 行锁

参考：

14.5.3 Locks Set by Different SQL Statements in InnoDB

next-key lock 引发的死锁