学习小记 -- Mysql中的锁（间隙锁、Next-Key Lock）

InnoDB存储引擎有3种行锁算法：

• Record Lock：单个行记录上的锁。
• Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。
• Next-Key Lock：Gap Lock+Record Lock，锁定一个返回，并且锁定记录本身。

如果InnoDB在表建立的时候没有设置任何一个索引，那么这是InnoDB存储引擎会使用隐式的主键来进行锁定。

在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种算法，例如一个索引有3，10，15，26四个值，那么该索引可能被Next-Key Lock的区间为：(-∞，3],(3，10],(10，15],(15，26],(26，+∞)。还有一种技术previous-key locking，同样如上述索引，用previous-key locking可分区为(-∞，3),[3，10),[10，15),[15，26),[26，+∞)

Next-Key Locking技术设计的目的是为了解决幻读（phantom problem），这个稍后详解。

锁降级

这里新建1个测试表：

create table t (a int primary key);
insert into t select 1;
insert into t select 2;
insert into t select 5;

接着执行下表中的SQL语句；

时间	会话A	会话B
1	BEGIN；
2	select * from t where a=5 for update;
3		BEGIN；
4		insert into t select 4;
5		commit;成功
6	commit

表t中一共有1，2，5三个值，在上面的会话中，A首先对a=5进行X锁定，由于a是主键，又是唯一索引，因为仅锁定5这个值，而不是（2，5）这个范围，所以在B会话中插入4而不会发生阻塞，可以立即插入并返回。即锁定由Next-Key Lock算法降级为了Record Lock，从而提高了并发性。

若是辅助索引，则情况完全不一样，新建一个辅助表：

create table z (a int,b int, primary key (a),key(b));
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

若会话A执行：select * from z where b=3 for update;这个表有两个缩影，所以要分别进行锁定。

由于a列为主键索引，索引锁降级为Record Lock，所以锁定范围为a=5这个值，因为b列为辅助索引，所以使用Next-Key Lock加锁，锁定的范围为(1,3]，需要特别注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上间隙锁（Gap lock），即还锁定一个范围(3,6)。所以这里的辅助索引上共有(1,3]，(3,6)两个锁，当会话B执行下面SQL 语句的话都会阻塞：

1. select * from z where a = 5 lock in share mode;
2. insert into z select 4,2;
3. insert into z select 6,5;

第一个语句不能执行，因为Record Lock已经a=5上加了一个X锁，会被阻塞

第二，三个语句不能执行，因为辅助索引锁定了(1,3]，(3,6)。

幻读（phantom problem）

间隙锁的作用是为了阻止多个事务讲记录插入到同一个范围内，从而解决了幻读的问题。试想，若A会话中已经锁定了b=3，a=5的记录，然后没有锁定(3,6)，这是会话B执行insert into z select 9,3会执行成功，所以会话A再查询的话，就查出来了（5,3）,(9,3)两条记录，这就是幻读。

InnoDB存储引擎在默认的隔离级别下（RR）采用Next-Key Lock解决幻读问题，在RC模式下则只用了Record Lock，所以避免不了幻读问题。

参考-----《MySql技术内幕-InnoDB存储引擎》