mysql锁(九)innodb下的记录锁,间隙锁,next-key锁

****你需要知道的****
之前我们介绍了排他锁,其实innodb下的记录锁(也叫行锁),间隙锁,next-key锁统统属于排他锁。

****行锁****
记录锁其实很好理解,对表中的记录加锁,叫做记录锁,简称行锁。

****生活中的间隙锁****
编程的思想源于生活,生活中的例子能帮助我们更好的理解一些编程中的思想。
生活中排队的场景,小明,小红,小花三个人依次站成一排,此时,如何让新来的小刚不能站在小红旁边,这时候只要将小红和她前面的小明之间的空隙封锁,将小红和她后面的小花之间的空隙封锁,那么小刚就不能站到小红的旁边。
这里的小红,小明,小花,小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙,而封锁他们之间距离的锁,叫做间隙锁。

****Mysql中的间隙锁****
下表中(见图一),id为主键,number字段上有非唯一索引的二级索引,有什么方式可以让该表不能再插入number=5的记录?

图一

根据上面生活中的例子,我们自然而然可以想到,只要控制几个点,number=5****之前****不能插入记录,number=5现有的记录****之间****不能再插入新的记录,number=5****之后****不能插入新的记录,那么新的number=5的记录将不能被插入进来。

那么,mysql是如何控制number=5之前,之中,之后不能有新的记录插入呢(防止幻读)?
答案是用间隙锁,在RR级别下,mysql通过间隙锁可以实现锁定number=5之前的间隙,number=5记录之间的间隙,number=5之后的间隙,从而使的新的记录无法被插入进来。

****间隙是怎么划分的?****

:为了方面理解,我们规定(id=A,number=B)代表一条字段id=A,字段number=B的记录,(C,D)代表一个区间,代表C-D这个区间范围。

图一中,根据number列,我们可以分为几个区间:(无穷小,2),(2,4),(4,5),(5,5),(5,11),(11,无穷大)。
只要这些区间对应的两个临界记录中间可以插入记录,就认为区间对应的记录之间有间隙。
例如:区间(2,4)分别对应的临界记录是(id=1,number=2),(id=3,number=4),这两条记录中间可以插入(id=2,number=3)等记录,那么就认为(id=1,number=2)与(id=3,number=4)之间存在间隙。

很多人会问,那记录(id=6,number=5)与(id=8,number=5)之间有间隙吗?
答案是有的,(id=6,number=5)与(id=8,number=5)之间可以插入记录(id=7,number=5),因此(id=6,number=5)与(id=8,number=5)之间有间隙的,

****间隙锁锁定的区域****
根据检索条件向左寻找最靠近检索条件的记录值A,作为左区间,向右寻找最靠近检索条件的记录值B作为右区间,即锁定的间隙为(A,B)。
图一中,where number=5的话,那么间隙锁的区间范围为(4,11);

****间隙锁的目的是为了防止幻读,其主要通过两个方面实现这个目的:****
(1)防止间隙内有新数据被插入
(2)防止已存在的数据,更新成间隙内的数据(例如防止numer=3的记录通过update变成number=5)

****innodb自动使用间隙锁的条件:****
(1)必须在RR级别下
(2)检索条件必须有索引(没有索引的话,mysql会全表扫描,那样会锁定整张表所有的记录,包括不存在的记录,此时其他事务不能修改不能删除不能添加)

****接下来,通过实际操作观察下间隙锁的作用范围****


图三 表结构

**** 案例一:****

````
session 1:
start  transaction ;
select  * from news where number=4 for update ;

session 2:
start  transaction ;
insert into news value(2,4);#(阻塞)
insert into news value(2,2);#(阻塞)
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(7,5);#(执行成功)
insert into news value(9,5);#(执行成功)
insert into news value(11,5);#(执行成功)
````

检索条件number=4,向左取得最靠近的值2作为左区间,向右取得最靠近的5作为右区间,因此,session 1的间隙锁的范围(2,4),(4,5),如下图所示:


间隙锁锁定的区间为(2,4),(4,5),即记录(id=1,number=2)和记录(id=3,number=4)之间间隙会被锁定,记录(id=3,number=4)和记录(id=6,number=5)之间间隙被锁定。

因此记录(id=2,number=4),(id=2,number=2),(id=4,number=4),(id=4,number=5)正好处在(id=3,number=4)和(id=6,number=5)之间,所以插入不了,需要等待锁的释放,而记录(id=7,number=5),(id=9,number=5),(id=11,number=5)不在上述锁定的范围内,因此都会插入成功。


****案例二:****

````
session 1:
start  transaction ;
select  * from news where number=13 for update ;

session 2:
start  transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
update news set id=14 where number=11;#(阻塞)
update news set id=11 where number=11;#(执行成功)
````
检索条件number=13,向左取得最靠近的值11作为左区间,向右由于没有记录因此取得无穷大作为右区间,因此,session 1的间隙锁的范围(11,无穷大),如下图所示:

此表中没有number=13的记录的,innodb依然会为该记录左右两侧加间隙锁,间隙锁的范围(11,无穷大)。

有人会问,为啥update news set id=14 where number=11会阻塞,但是update news set id=11 where number=11却执行成功呢?

间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入,此图间隙锁锁定的区域是(11,无穷大),也就是记录(id=13,number=11)之后不能再插入记录,update news set id=14 where number=11这条语句如果执行的话,将会被插入到(id=13,number=11)的后面,也就是在区间(11,无穷大)之间,由于该区间被间隙锁锁定,所以只能阻塞等待,而update news set id=11 where number=11执行后是会被插入到(id=13,number=11)的记录前面,也就不在(11,无穷大)的范围内,所以无需等待,执行成功。


****案例三:****

````
session 1:
start  transaction ;
select  * from news where number=5 for update;

session 2:
start  transaction ;
insert into news value(4,4);#(阻塞)
insert into news value(4,5);#(阻塞)
insert into news value(5,5);#(阻塞)
insert into news value(7,11);#(阻塞)
insert into news value(9,12);#(执行成功)
insert into news value(12,11);#(阻塞)
update news set number=5 where id=1;#(阻塞)
update news set id=11 where number=11;#(阻塞)
update news set id=2 where number=4 ;#(执行成功)
update news set id=4 where number=4 ;#(阻塞)
````

检索条件number=5,向左取得最靠近的值4作为左区间,向右取得11为右区间,因此,session 1的间隙锁的范围(4,5),(5,11),如下图所示:


有人会问,为啥insert into news value(9,12)会执行成功?间隙锁采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入,(id=9,number=12)很明显在记录(13,11)的后面,因此不再锁定的间隙范围内。

为啥update news set number=5 where id=1会阻塞?
number=5的记录的前面,后面包括中间都被封锁了,你这个update news set number=5 where id=1根本没法执行,因为innodb已经把你可以存放的位置都锁定了,因为只能等待。

同理,update news set id=11 where number=11由于记录(id=10,number=5)与记录(id=13,number=11)中间的间隙被封锁了,你这句sql也没法执行,必须等待,因为存放的位置被封锁了。


****案例四:****

session 1:
start  transaction;
select * from news where number>4 for update;

session 2:
start  transaction;
update news set id=2 where number=4 ;#(执行成功)
update news set id=4 where number=4 ;#(阻塞)
update news set id=5 where number=5 ;#(阻塞)
insert into news value(2,3);#(执行成功)
insert into news value(null,13);#(阻塞)

检索条件number>4,向左取得最靠近的值4作为左区间,向右取无穷大,因此,session 1的间隙锁的范围(4,无穷大),如下图所示:


session2中之所以有些阻塞,有些执行成功,其实就是因为插入的区域被锁定,从而阻塞。


****next-key锁****
next-key锁其实包含了记录锁和间隙锁,即锁定一个范围,并且锁定记录本身,InnoDB默认加锁方式是next-key 锁。
上面的案例一session 1中的sql是:select * from news where number=4 for update ;
next-key锁锁定的范围为间隙锁+记录锁,即区间(2,4),(4,5)加间隙锁,同时number=4的记录加记录锁。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 141,558评论 1 298
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 60,739评论 1 254
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 93,327评论 0 211
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 40,752评论 0 174
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 48,452评论 1 252
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 38,617评论 1 171
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 30,286评论 2 267
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,083评论 0 165
  • 想象着我的养父在大火中拼命挣扎,窒息,最后皮肤化为焦炭。我心中就已经是抑制不住地欢快,这就叫做以其人之道,还治其人...
    爱写小说的胖达阅读 28,839评论 6 227
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 32,413评论 0 213
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,186评论 2 213
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 30,506评论 1 223
  • 白月光回国,霸总把我这个替身辞退。还一脸阴沉的警告我。[不要出现在思思面前, 不然我有一百种方法让你生不如死。]我...
    爱写小说的胖达阅读 24,171评论 0 31
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,049评论 2 213
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 31,417评论 3 202
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,588评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 25,942评论 0 163
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 33,392评论 2 228
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 33,499评论 2 229

推荐阅读更多精彩内容