第二十六节、为什么只改一行的语句,锁这么多?

这里需要明确间隙锁和next-key lock的概念及加锁规则。

加锁规则有以下两条前提说明:

1、MySQL后面的版本可能会改变加锁策略,以下规则限于5.x系列<=5.7.24,8.0系列<=8.0.13.

2、间隙锁在可重复读隔离级别下才有效。


加锁规则的两个“原则”、两个“优化”和一个“bug”

1、原则1:加锁的基本单位是next-key lock(前开后闭区间);

2、原则2:查找过程中访问到的对象才会加锁;

3、优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁;

4、优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁;

5、一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。


案例一:等值查询间隙锁

CREATE TABLE `t` (

  `id` int(11) NOT NULL,

  `c` int(11) DEFAULT NULL,

  `d` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `c` (`c`)

) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),

(10,10,10),(15,15,15),(20,20,20),(25,25,25);


1、根据原则1,加锁单位是next-key lock,session A加锁范围就是(5,10];

2、同时根据优化2,这是一个等值查询(id=7),而id=10不满足查询条件,next-key lock退化成间隙锁,因此最终加锁的范围是(5,10)。

所以,session B要往这个间隙里面插入id=8的记录会被锁住,但是session C修改id=10这行驶可以的。


案例二:非唯一索引等值锁


这里session A要给索引c上c=5的这一行加上读锁。

1、根据原则1,加锁单位是next-key lock,因此会给(0,5]加上next-key lock;

2、c是普通索引,因此仅访问c=5这一条记录是不能马上停下来的,需要向右遍历,查到c=10才放弃。根据原则2,访问到的都要加锁,因此要给(5,10】加next-key lock。

3、同时这个符合优化2:等值判断,向右遍历,最后一个值不满足c=5这个等值条件,因此退化成间隙锁(5,10).

4、根据原则2,只有访问到的对象才会加锁,这个查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁,这就是为什么session B的update语句可以执行完成。

但session C要插入一个(7,7,7)的记录,就会被session A的间隙锁(5,10)锁住。

需要注意,这个例子中,lock in share mode只锁覆盖索引,但是如果是for update 就不一样了。执行for update 时,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明,锁是加在索引上的;同时,它给我们的指导是,如果你要用lock in share mode来给行加读锁避免数据被更新的话,就必须得绕过覆盖索引的优化,在查询字段中键入索引中不存在的字段。比如,将session A的查询语句改成select d from t where c=5 lock in share mode。


案例三:主键索引范围锁

关于范围查询

mysql> select * from t where id=10 for update;

mysql> select * from t where id>=10 and id<11 for update;

在逻辑上,这两条查询语句肯定是等价的,但是加锁规则不太一样 。


1、开始执行的时候,要找到第一个id=10的行,因此本该是next-key lock(5,10]。根据优化1,主键id 上的等值条件,退化成行锁,只加了id=10这一行的行锁。

2、范围查找就往后继续找,找到id=15这一行停下来,因此 需要加next-key lock(10,15].

所以,session A 这时候锁的范围就是主键索引上,行锁id=10和next-key lock(10,15].这样,session B和session C的结果就能理解了。

这里需要注意一点,首次session A定位查找id=10的行的时候,是当做等值查询来判断的,而向右扫描到id=15的时候,用的是范围查询判断。


案例四、非唯一索引范围锁


这次session A 用字段c来判断,加锁规则和案例三唯一的不同是:在第一次用c=10定位记录的时候,索引c上加了(5,10]这个next-key lock后,由于索引c 是非唯一索引,没有优化规则,也就是不会蜕变为行锁,因此最终session A加的锁死,索引上的(5,10]和(10,15]这两个next-key lock。

所以从结果上看,session B要插入(8,8,8)的这个insert 语句时就被堵住了。

这里需要扫描到c=15才停止扫描,是合理的,因为InnoDB要扫到c=15,才知道不需要继续往后找了。


案例五:唯一索引范围锁bug



案例六、非唯一索引上 存在“等值”的例子






案例七:limit语句加锁




案例八:一个死锁的例子




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