mysql加锁分析实践

Posted on |

实践基于以下表结构，分析各个情况下加锁：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `tablet` (
  `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);

唯一主键加锁

先执行session1，query ok :

1
2
3
4
use souche_study;
 /*主键等值查询gap锁 */
begin;
select * from tablet where id=10 lock in share mode;

再执行session2，query ok:

1
2
3
use souche_study;
/*可以正常插入判定没有gap锁，针对唯一索引优化*/
insert into tablet values(9,8,8);

再执行session3，query blocked：

1
2
use souche_study;
update tablet set c=11 where id =10;

唯一主键等值查询不加gap间隙锁，只加对应值的行锁。

这里将session1换成：

1
2
3
4
use souche_study;
 /*主键范围查询gap锁 */
begin;
select * from tablet where id>=10 and id<15 lock in share mode;

session2，query ok：

1
2
use souche_study;
insert into tablet values(16,8,8);

session3，query blocked：

1
2
3
use souche_study;
/*gap锁*/
insert into tablet values(12,8,8);

session4，query blocked：

1
2
3
use souche_study;
/*这里范围查询会多加一个行锁，实际上会感觉比较多余,因为并没有筛选到这个值,但是从逻辑上理解在索引上扫描一个范围并不知道什么时候结束，所以会有多出来的一个行锁，可能为了不违背两阶段锁的协议*/
update tablet set c =0 where id=15;

这里如果我们将session1中id<15加上一个等号条件也就是id<=15，mysql后在id=20这行也加上锁，同时多了一个gap锁也就是多了一整个next key lock (15,20]，这是相当奇怪的 ，因为扫描到15的时候可以确定不再往后进行扫描。这种貌似可以优化的场景但是并没有进行优化。

最终上面的test发现会加id为10记录的行锁+(10,15]的next key lock。总结下，mysql在唯一主键索引查询的条件为范围索引的条件下会默认向后多加一个next key lock，等值查询直加行锁。

lock in share mode 这种方式只会在索引上加锁，在不回表的情况下不会锁主键索引记录。for update 的方式不管是否回表都会锁主键记录。

唯一普通索引加范围锁

唯一普通索引有独立的树结构，在加锁上跟主键的索引是有区别的，防止根据主键更新造成不一致读会加行锁。

将c修改为唯一索引，执行下面的session1：

1
2
3
4
5
6
7
8
9
10
use souche_study;

alter table tablet drop index c;
alter table tablet add unique index c(c);
show index from tablet;

begin;
/* 普通唯一索引加范围锁 */
select * from tablet where c >10 and c <=15 lock in share mode;
/* == select d from tablet where c >10 and c<=15 for update; */

然后执行session2，query blocked :

1
2
3
4
5
6
7
8
9
10
11
12
use souche_study;
-- 唯一普通索引c加锁范围(10,15],(15,20]，跟上面保持一致，需要多扫描一行数据并加锁（虽然感觉完全没有必要），主键索引加id=15行锁。
-- query ok c=21
insert into tablet values(16,21,8);
-- query blocked c=16 gap间隙锁 
insert into tablet values(19,16,8);
-- blocked c=15 行锁
update tablet set d=111 where c=15;
-- query ok，没有加锁，回表的时候做了优化
update tablet set d=111 where id=20;
-- blocked 加了行锁
update tablet set d=111 where id=15;

唯一索引范围锁搜索到不符合预期范围的第一个值，以该值为最终值进行加锁，上面的语句如果改成c<15那么锁范围变成(10,15]。

非唯一普通索引加范围锁

类比上面的情况多了一个next key lock，执行session1:

1
2
3
4
5
use souche_study;
begin;
-- select * from  tablet where d>10 and d<=15 for update;
/* 非唯一索引加范围锁 */
select * from tablet where d >10 and d <=15 lock in share mode;

执行session2:

1
2
3
4
5
6
7
8
9
10
11
12
13
use souche_study;
-- 同样的情况变成了非唯一索引，加锁情况一致(10,15]，(15,20]（这里并没有多加个间隙锁，作了优化），主键索引加id=15行锁
-- query ok d=8
insert into tablet values(11,8,8)
-- query ok d=23
insert  into tablet values(13,7,23);
-- blocked  d=20行锁
update tablet set c=22 where d=20;
-- query blocked  d=16间隙锁
insert into tablet values(14,16,16);
-- query ok 
update tablet set c =24 where id =20;
commit;

非唯一索引范围锁搜索到不符合预期范围的第一个值，以该值为最终值进行加锁，上面的语句如果改成c<15那么锁范围变成(10,15]。这里并不会在右边多加间隙锁。

####

非唯一普通索引加范围锁倒序

倒序排序默认会从后往前扫描索引，mysql默认多加一个间隙锁。

1
2
3
4
5
-- session1
use souche_study;
begin;
-- 锁了(5,10],(10,15],(15,20],(20,25)
select * from tablet where d>=15 and d<=20 order by d desc lock in share mode;
1
2
3
4
5
6
7
8
9
10
-- session2
use souche_study;
-- query ok
update tablet set c=14 where d =25;
-- blocked
insert into tablet values(17,17,6);
-- query ok
update tablet set c = 18 where d=5;
-- blocked 唯一索引不存在(20,25)间隙锁
insert into tablet values(16,16,21);

总结

总结一下，分析加锁情况主要需要区分出唯一索引和非唯一索引。唯一索引是左半区或者右半区可能插入，非唯一索引是左右半区都可能插入。并且有几个原则和几个优化，原则：

1.加锁的基本单位是next key lock，前开后闭区间。

2.mysql扫描到的对象才会进行加锁。

优化：

1.索引的唯一等值查询，next key lock退化为行锁。（非唯一等值查询不退化）

2.等值查询或者范围查询向右遍历到最后一个不符合期望的值，即使是非唯一索引也不会在该值的右侧加一个间隙锁。