记录一个MySql 分区表+Gap锁引起插入超时的案例

最近有同事在项目上遇到一个场景,定时任务在往MySql插入一条数据超时了,而排查其他SQL,没有锁表的动作。排查到最后,发现是分区表导致id不唯一,加上Gap锁导致的。下面简单分析一下。

1.场景重现

1.1 没有分区的场景

先建一个没有分区的表

1 CREATE TABLE student (
2     `id` INT NOT NULL PRIMARY KEY,
3     `name` VARCHAR (128) DEFAULT NULL,
4     `country` VARCHAR(64) 
5 ) ENGINE = INNODB DEFAULT charset = utf8;

插入一些数据

1 INSERT INTO STUDENT (`id`, `name`, `country`)
2 VALUES
3     (1, 'name1', 'CHINA'),
4     (3, 'name3', 'JAPAN'),
5     (5, 'name5', 'USA'),
6     (7, 'name7', 'JAPAN'),
7     (9, 'name9', 'CHINA');

此时开启一个事务(称为事务1),把id=5的数据改为changed

事务1未提交,同时在另外一个窗口,再开一个事务(称为事务2),插入id为4的数据;

1 insert into student(`id`,`name`,`country`) values (4,'name4','USA');

 

  成功插入,两个事务分别提交,可以看到数据已经写入。

这很好理解,两条语句操作的id不一样,不会互相影响。可是加了分区,就不一样了。

1.2 分区的场景

1 CREATE TABLE student (
2     `id` INT NOT NULL,
3     `name` VARCHAR (128) DEFAULT NULL,
4   `country` VARCHAR(64) NOT NULL,
5     PRIMARY KEY(id,country)
6 ) ENGINE = INNODB DEFAULT charset = utf8 partition BY KEY (country) PARTITIONS 4;

 

注意,主键已经不只是id了,而是id+country。如果不把country加入到主键中,会报错。

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

而这导致了通过id并不能唯一确认一条数据(虽然从程序的逻辑上是唯一的),进而导致更新时会锁住不止一行,影响了其他事务的操作。

 

插入数据,此时country值是一样的(后面解释)

1 INSERT INTO STUDENT (`id`, `name`, `country`)
2 VALUES
3     (1, 'name1', 'CHINA'),
4     (3, 'name3', 'CHINA'),
5     (5, 'name5', 'CHINA'),
6     (7, 'name7', 'CHINA'),
7     (9, 'name9', 'CHINA');

和上面一样,开一个事务1,update id=5的数据,暂不提交。

在事务2,先后插入id=8和id=4的数据,可以看到,第一条顺利插入,第二条被阻塞,直到事务超时。

 

 

2. 原因简析

参考美团技术团队关于MySql锁的分析:https://tech.meituan.com/2014/08/20/innodb-lock.html

简单来说,就是MySql的默认事务级别是RR,在这个级别是解决了幻读问题的。不可重复读和幻读的关注点不一样,前者是update/delete,后者是insert.

为了防止幻读,MySql用Gap锁把id=5的数据前后都锁住了,即(3,5] 和(5,7],所以插入id=4或6的数据会被阻塞,而插入8不会。

那么为什么明明有ID,还要分区呢?这是因为有时候数据量大,利用分区可以访问快一点,例如以记录的create time作为分区依据。但这样就要求分区字段作为主键的一部分,破坏了原主键的唯一性。

 

上面的例子,是基于country=CHINA的分析,因为通过实验,这个锁对于同样分区的才生效。如果把其他数据的country,改为USA或JAPAN,结果又不一样了。

比如把7改为JAPAN, 这时(5,'CHINA') 和 (7,'JAPAN') 并不在同个分区,那么Gap锁锁住的是(3,5]和(5,9],插入id=8, country=CHINA同样阻塞(当然插入id=8, country=JAPAN不会阻塞,不在同个分区!)。

所以其实是 分区->ID+分区字段才能组成主键->误以为ID是主键,但实际并不唯一,导致了Gap锁->多锁了几行

 

还有其他的场景,参考这个例子:https://www.zhihu.com/question/51390849/answer/294352412

事务2如果是update,并且改变了索引字段的值,也就改变了索引的位置,那么更新后的数据可能落入gap区间,造成阻塞。

如果不改变索引字段的值,不会有这样的问题。

posted @ 2020-07-19 22:50  kingsleylam  阅读(79)  评论(0编辑  收藏