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

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

1.场景重现

1.1 没有分区的场景

先建一个没有分区的表

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

插入一些数据

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

此时开启一个事务（称为事务1），把id=5的数据改为changed

事务1未提交，同时在另外一个窗口，再开一个事务（称为事务2），插入id为4的数据；

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

 

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

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

1.2 分区的场景

CREATE TABLE student (
    `id` INT NOT NULL,
    `name` VARCHAR (128) DEFAULT NULL,
  `country` VARCHAR(64) NOT NULL,
    PRIMARY KEY(id,country)
) 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值是一样的（后面解释）

INSERT INTO STUDENT (`id`, `name`, `country`)
VALUES
    (1, 'name1', 'CHINA'),
    (3, 'name3', 'CHINA'),
    (5, 'name5', 'CHINA'),
    (7, 'name7', 'CHINA'),
    (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区间，造成阻塞。

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