转载

关于InnoDB的Next-Key lock

最近一段时间在准备新员工培训的材料,本来打算介绍介绍概念就OK的,但是既然写了事务的章节,就特别想介绍一下锁,介绍了锁,就忍不住想介绍一下Next-Key Lock。

大家知道,标准的事务隔离级别有READ UNCOMMITTED,READ COMMITTED,REPEATED READ和SERIALIZABLE。其中InnoDB默认实现了REPEATED READ级别,这个级别可以解决非一致性读的问题,但是不能解决幻读的问题,不过InnoDB采用了Next Key Lock算法,在该级别实现了幻读保护。

至于什么是幻读,这个概念可以在官方文档上找到答案,这里就不再赘述了。

先看一个例子,按照下面的规则建表:

 CREATE TABLE `test` (   `id` int(11) DEFAULT NULL,   KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  insert into test values (1), (3), (5), (8), (11); 

注意,这里id上是有索引的,因为该算法总是会去锁住索引记录。

现在,该索引可能被锁住的范围如下:

(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)

此时,按照下面表格中的顺序开启两个Session,在Session B执行到第六步之前,都是可以成功的,第六步开始阻塞,第八步又可以正常执行。这样,就能知道Session A中的SQL实际上锁住了一个范围,除了锁住了8所在的(5, 8]区间,还同时锁住了下一个区间:(8, 11],所以插入12就不在锁定范围内了。

这里有个问题我还没有想清楚,就是为什么插入5还会被阻塞,如果谁知道请留言告知,谢谢,我自己也会找资料研究。

order Session A Session B
1 begin;
2 select * from test where id = 8 for update;
3 begin;
4 insert into test select 1;
5 insert into test select 4;
6 insert into test select 5;
7 insert into test select 9;
8 insert into test select 12;

上面这个情况是辅助索引且不唯一的情况的锁。如果是唯一索引呢?

如果将id列修改为主键,上面这个表格中,Session B第四和六步主键冲突就不说了,其他的步骤都可以执行成功, (5, 8], (8, 11]这两个区间内的所有值(主键不冲突)都可以成功插入表中。这种现象的原因是,索引唯一,InnoDB会把锁降级成Record Lock,只会锁住一个记录而已,这样能很好的提升并发性。

利用Next Key Lock,InnoDB可以在REPEATABLE READ级别下,实现幻读保护。

参考:http://www.cnblogs.com/zhoujinyi/p/3435982.html

原文  http://www.cnblogs.com/wingsless/p/5203025.html
正文到此结束
Loading...