记录一个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');