MySQL锁实际案例分析(一)

案例来源:
ITPUB论坛,原帖地址 http://www.itpub.net/thread-2055372-1-1.html
--------------------------------------------------------------------------正文--------------------------------------------------------------------------
数据库隔离级别RR
表结构及数据如下图:


作者的疑问1:总是提示有黑客信息被拦截, 只能无奈截图了


原帖作者疑问2:



------------------------------------------------------------------------拙见&分析-----------------------------------------------------------------------

分析疑问1:
从常规考虑来说,显示的加上for update应该是会对数据加上排它锁,所以两个session应该会发生资源争用,导致有一个session超时回滚;
所以看上去似乎是挺奇怪,实际在测试环境验证的时候,也能发现确实不会有争用;
验证结果如下图
session1:


session2:

看看innodb status:

锁定的行数为1,这两个事务也确实没有发生资源争用, why?

疑问1解惑:
关键出在id=2这行数据本就不存在这一点;
事实上, 如果id=2存在的时候, 这个语句肯定会对这一行数据加上X锁, 并且会使得另外的session获取不到锁, 发生等待;
如果id=2不存在呢? 在RR隔离级别下, 会用GAP锁锁住不满足条件的第一行记录, 保证没有满足的记录插入数据; 
在这个例子中, GAP锁锁住的是第一条不满足的记录-- id=1之后的所有记录, 即GAP锁锁住了(1,正无穷)整个范围,插入大于1的值都会失败;
测试如下图:

对应的innodb status:


从innodb的status里面能很明显的看出插入意向锁在等待sessino1持有的锁;
有意思的地方来了,锁信息中出现了一个supremum;

追问1: 什么是supremum?
回答: 引用官方文档描述, supremum是索引上代表无穷大的一个"虚拟行", 所表达的意义就是比索引中的任何值都要大;
因此session1显示加持排它锁的时候, 锁的是(1,supremum)的间隙;
PS: session1中的1 rows lock(s) 锁的到底是哪个, 还不太清楚, 直观上感觉是supremum,
但是如果有X锁, session2想要给supremum加X锁又应该会产生争用, 求解惑_(:з」∠)_

id=1的列其实是可以修改的:


而GAP锁之间并不会冲突, 所以session2执行同一个语句的时候, 两个语句都能正常的执行;
所以session1和session2在对一个不存在的数据行(id=2)显式加排它锁的时候, 实际上加的是GAP锁;
GAP锁之间并不冲突, 所以不会发生预想中的争用;

疑问2:去掉了primary key之后, session1和session2 发生了争用;
回答:去掉primary key之后, 这个表相当于没有任何索引了, 所以任何显式的加锁行为都会锁住整个表的所有行;
session1和session2理所当然的会出现锁等待;
innodb status如下如所示:


------------------------------------------------------------------------------The End----------------------------------------------------------------------------------------

PS:最近明显感觉天赋树已经开始在往歪了点, 新世界的大门徐徐打开.........

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29510932/viewspace-2063796/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29510932/viewspace-2063796/