一个mysql 范围锁的问题，请高手解惑！

digdeep126

liushuiwuqing4 发表于 2014-4-30 11:18
你的解释也就是我的问题，为什么 6.6在gap之内，而8.6在gap之外呢？

这个问题，我觉得我想清楚了：
where b=3 for update, 为了解决幻读，必须不能插入 b=3的记录，那么如何才能不插入b=3的记录来，使用gap锁，那么应该如何使用gap锁呢？锁住（3,5）到（6,7）之间的gap，以及（1,3）到（3,5）之间的gap.

那么：锁住（3,5）到（6,7）之间的gap ，这个应该如何来实现或者说表示呢？
锁住(3,5) 到 (3,7)，还是锁住(3,5)到(6,5),还是锁住(3,5)到(6,7)呢？
实际上锁住(3,5) 到 (3,7)也没有用，因为他可能插入（3,8），那么就剩下：
锁住(3,5)到(6,5)或者锁住(3,5)到(6,7)了，
但是(6,5)记录不存在，所以只能锁住(3,5)到(6,7)了。
所以(6,6)在范围内，而(6,8)不在范围呢。

如果允许(6,6)的插入的，那么gap锁就要改成：锁住(3,5)到(6,6)了，但是在插入(6,6)之前，记录(6,6)还不存在，无法锁住(3,5)到(6,6),
所以过程必须是这样的：释放(3,5)到(6,7)的gap锁，插入(6,6)，修改gap锁为：锁住(3,5)到(6,6)，
但是：在释放(3,5)到(6,7)的gap锁  到  插入(6,6)，修改gap锁为3,5)到(6,6) 之间，有可能会有类似 (3,6)的记录insert进来，而这导致幻读，所以允许插入(6,6)是无法实现的。
不知道这样理解对不对。

liushuiwuqing4

digdeep126 发表于 2014-5-6 13:16
这个问题，我觉得我想清楚了：
where b=3 for update, 为了解决幻读，必须不能插入 b=3的记录，那么如何 ...

专家就是专家，您解释的很到位了，我按照您说的和他们给我的链接，终于把这个问题理解了，关键一点就是要理解 MySQL 索引真正是如何存储的。

1）1个session1执行  begin ； 然后 select * from z where b=3 for update;  
2）另一个session2 （也是autocommit=0）执行 begin； 然后  insert into z select 8,6; 是可以的，回滚后，我执行 mysql> insert into z select 6,6;  却不行，就会被 阻塞住。

经过  wuliu050688 童鞋的提议，我看了下 登哥的索引博客（http://hedengcheng.com/?p=771 ） 瞬间恍然大悟了！

解释：
1）表z 的 secondary index  结构如下图所示：

b(key)	1	1	3	6	8
a	1	3	5	7	10

2）表z 的cluster index 如下表所示：

a(pk)	1	3	5	7	10
b	1	1	3	6	8

3）当 session 1 执行  select * from z where b=3 for update;  的时候，为了保证 b=3 的结果在 session1 的begin transaction 之后一直不变，MySQL 选择了
首先让 a=5 & b=3 的 secondary index 和cluster index 的索引条目（index entry） 被锁定 （用紫色标出的部分）。
其次，MySQL 会限制不让其他的 会话 insert 数据到  secondary index 里面的 b=1&a=3  和 b=6&a=7 之间的范围数据（也就是我用红色标识的数据区间）。

为什么呢？

因为索引默认是按照关键字的顺序递增来排序的（也可以设置按照 key 的递减顺序 index_col_name : col_name[(length)] [ASC | DESC] index_type:）。你按照这个插入规则来插入任何一条数据的时候，只要是插入数据时对应的 secondary index 条目 在红色数据区间之间，那么 MySQl 就不允许你插入。

（a,b)=(8,6) 对应的 secondary index 条目 在红色区间 （3,1）~（7,6）的右边， 但是 （a,b)=(6,6) 对应的 secondary index 条目 在红色区间 （3,1）~（7,6）之间。

按照这个方法，我测试了 （a,b）=(2,1)  和 （9,1） ， 结果符合我的预期，原来是这回事。
如果还是不理解，请先看看 MySQL index 的 B+tree 结构，再看看登神的那篇博客，肯定会有所收获的，祝好运， enjoy！

lixunix

gap lock 锁住index lock,对于z表来说，在b字段建立索引，那么索引的记录应该是索引字段和主键一起组成，（b,a）的形式，这才是一个索引记录，
三个索引（1,3），（3,5），（6,7）记录的GAP会被锁住，其实（6,8）也是可以插入的，而z表上只会在a=5的这行记录上加上X锁，为什么要这么干，你可以想想，假如有第三个SESSION:select * from z where a=6 for update这时候不会阻塞也不会锁住任何行，加入这时候第二个SESSIOn能插入（6,6)的话，那么第三那个session 将可以查出（6,6）这条记录，就会出现幻像读了。

lujinke

liushuiwuqing4 发表于 2014-5-12 13:44
专家就是专家，您解释的很到位了，我按照您说的和他们给我的链接，终于把这个问题理解了，关键一点就是要 ...

但是这个理论解释不通这里的问题
http://www.itpub.net/thread-1847072-1-2.html

skzhuga

liushuiwuqing4 发表于 2014-4-30 11:18
你的解释也就是我的问题，为什么 6.6在gap之内，而8.6在gap之外呢？

与索引数据的排序有关