美文网首页技术分享
MySQL中一些不可思议的加锁情况

MySQL中一些不可思议的加锁情况

作者: 三不猴子 | 来源:发表于2020-09-12 15:22 被阅读0次

    最近看了极客时间MySQL45讲,有些情况的加锁场景真的是颠覆我的认知,然后写下这篇文章分享一下,这篇文章的内容都是基于MySQL45讲的总结。首先是数据和表的初始化:

    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);
    

    我使用的MySQL版本是5.7,可重复读的隔离级别下。先看一个小问题:

    session A session B session C
    begin;<br />select * from t where c>=15 and c<=20 order by c desc for update;
    Insert into t values(11,11,11);
    Insert into t values(6,6,6);

    估计你一看会觉得这不是非常明显,我建议自己试一试,先不说答案,看完这篇文章你应该就知道为什么了。

    先说一下MySQL加锁规律,总结起来就是下面几条:

    1. 加锁的基本单位是next-key lock。next-key lock是前开后闭区间.
    2. 查找过程中访问到的对象才会加锁。
    3. 索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
    4. 索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。
    5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

    案例一:等值查询间隙锁

    session A session B session C
    begin;<br />update t set d = d+1 where id=7
    insert into t values(8,8,8);<br />(block)
    update t set d = d+1 where id=10
    (ok)

    我们按照上面总结的规律分析一下,7是在(5,10]这个区间内的,然后加锁区间是(5,10]然后是等值查询 next-key lock 退化间隙锁最终结果是(5,10)。

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

    session A session B session C
    begin;<br />select id from t where c = 5 lock in share mode;
    update t set d = d+1 where id=5
    (ok)
    insert into t values(7,7,7);<br />(block)

    看到这个的时候我当时就是一句卧槽,这尼玛MySQL出bug了吧,这不是乱加锁,这。。。。我们按前面的几个规律分析一下。

    一开始mysql会给c=5加一行读锁。加锁的单位是next-key lock,所以(0,5]。c是一个普通索引,MySQL在找到这条记录的时候并不能确定还有没其他的行,所以要继续查找,直到找到c=10,所以加锁的范围就是(5,10],类似于案例一,结果就是(0,5)间隙锁,c=5行锁,(5,10)间隙锁。分析到这里感觉很有成就感,但是为啥update t set d = d+1 where id=5执行成功了?只有查找到了的结果才会加锁,也就说这里的行锁是加了c=5,并不会锁id,这个查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁。如果我们把session A的查询换成select * from t where c = 5 lock in share mode;查询的是所有而不是id,那么session B就会被阻塞,不信自己可以试试看。注意session A lock in share mode;如果换成 for update,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。所以我们经常说的加锁是锁主键是有前提的。

    案例三:主键索引范围锁

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

    这两句sql是等价的吗?执行逻辑肯定是等价的,但是加锁逻辑是不一样的,select * from t where id=10 for update;主键等值查询 next-key lock 退化成行锁,就是加了id=10这条锁。对于 select * from t where id>=10 and id<11 for update; 首先找到id=10这一行加个行锁,11是在(10,15]间隙内的所以加锁逻辑就是(10,15],id=10的行锁即[10,15]。

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

    select * from t where c>=10 and c<11 for update;这种情况相比于案例三差不多,只是在找到c=10这行时不会退化成行锁,而是一个 next-key lock 所以结果(5,10],然后11在(10,15]区间内加上(10,15]。即(5,15]。

    案例五:唯一索引范围锁

    对应第五条规律,唯一索引上的范围查询会访问到不满足条件的第一个值为止。唯一索引讲道理找到了那一行应该就不会继续往下扫描的,事实上却不是。

    session A session B session C
    begin;<br />select id from t where id >10 and id<=15 for update;
    update t set d = d+1 where id=20
    (block)
    insert into t values(16,16,16);<br />(block)

    对于select id from t where id >10 and id<=15 for update; 首先加上(10,15] next-key lock,找到了15之后并不会停还会继续往下扫描,所有又会加上(15,20],就出现了表格中的情况。select id from t where id >=10 and id<=15 for update;这个加锁是怎么加的呢?答案是(5,10],(10,15],(15,20]。

    案例六:非唯一索引上存在"等值"的例子

    我们在刚刚的数据上加上

    insert into t values(30,10,30);
    

    这时候表里的索引c的数据就变为

    c 0 5 10 10 15 20 25
    Id 0 5 10 30 15 20 25

    此时我们执行一条 select * from t where c=10 for update;加锁情况是怎么样的呢?

    image-20200912145714893

    (c=5,id=5)和(c=15,id=15)这两行上都没有锁。select * from t where c=10 for update limit 2;如果给这个查询上sql加个limit呢?

    image-20200912150756175

    就是少了一个间隙。

    回到我们最开始那个问题,select * from t where c>=15 and c<=20 order by c desc for update;这条sql是怎么加锁的?首先找到c=15并要找到不满足条件的值也就是c=10加锁,加上一个next-key lock 也就是(5,10],20也是一样的道理找到c=20的不满足情况的一个值25加上next-key lock (20,25],最后的结果就是(5,10],(10,15],(15,20],(20,25]。

    相关文章

      网友评论

        本文标题:MySQL中一些不可思议的加锁情况

        本文链接:https://www.haomeiwen.com/subject/xuuwektx.html