MySQL下并发重复插入的唯一性问题

1. 背景

在高并发场景中，经常会遇到并发重复数据插入，没有唯一索引（比如有逻辑删除）却要保证唯一性的问题。比如分布式告警计算中，多个计算实例同时计算出同一个告警并同时插入相同的告警记录到数据库中，这时应该只插入一条记录。本文分析了在MySQL的InnoDB引擎中解决这类的问题的4种思路和相关方案。

2. 问题提出

InnoDB下，两个事务同时插入同一条数据，如何保证只有一条数据被插入？

如下表（本文使用的是MySQL 5.7.23）：

mysql> create table aa(id int not null auto_increment, type int, value int, primary key(id)) engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.03 sec)



mysql> insert into aa values(1, 1, 1), (2, 1, 1), (3, 1, 0), (4,2,1);

Query OK, 4 rows affected (0.02 sec)

Records: 4  Duplicates: 0  Warnings: 0



mysql> select * from aa;

+----+------+-------+

| id | type | value |

+----+------+-------+

|  1 |    1 |     1 |

|  2 |    1 |     1 |

|  3 |    1 |     0 |

|  4 |    2 |     1 |

+----+------+-------+

4 rows in set (0.00 sec)

规定表aa的每一类type其值(value)为0的只能有一条，比如为type为1，其value为0的只有(3,1,0)。假设现在两个事务同时插入type为2，value为0的数据，如何确保只有一个事务能插入成功？

3. 解决思路一：加唯一索引

因为相同type但value为1的记录可能有多条，所以不能通过给(type, value)加唯一性索引来解决。

mysql> alter table aa add unique type_val(type, value);

ERROR 1062 (23000): Duplicate entry '1-1' for key 'type_val'

方案1：新建表

可以另外建一个表bb，存储所有value为0的type，并给type列加上唯一索引。在aa插入前先在bb插入一条，如果成功再插入：

mysql> create table bb(type int, primary key(type)) engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.04 sec)



mysql> insert bb values(1);

Query OK, 1 row affected (0.01 sec)

Session 1	Session 2
为缺省隔离级别（repeatable read）	缺省隔离级别（repeatable read）
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)   mysql> insert into bb values(2); Query OK, 1 row affected (0.00 sec)     mysql> insert into aa(type, value) values(2, 0); Query OK, 1 row affected (0.00 sec)   mysql> commit; Query OK, 0 rows affected (0.00 sec)       mysql> select * from aa; +----+------+-------+ | id | type | value | +----+------+-------+ |  1 |    1 |     1 | |  2 |    1 |     1 | |  3 |    1 |     0 | |  4 |    2 |     1 | |  5 |    2 |     0 | +----+------+-------+ 5 rows in set (0.00 sec)	mysql> start transaction; Query OK, 0 rows affected (0.00 sec)       mysql> insert into bb values(2); (等待……) (等待……) (等待……) (等待……) (等待……) (等待……) ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'   (插入失败提示已有数据，事务结束) rollback;     mysql> select * from bb; +------+ | type | +------+ |    1 | |    2 | +------+ 2 rows in set (0.00 sec)

这个方案的优点是1.加锁少，并发性高；2.易理解。缺点是1.多建了一个表，2.在将value改成1的时候需要把表bb对应的记录删掉。

方案2 ：改造表

建唯一索引失败的原因是value为1的有多条，但我们关心的是value为0的记录，如果把value为1的记录的value值改成非0的唯一值，那么就能够建立(type,value)的唯一索引：

mysql>  create table aa2(id int not null auto_increment, type int, value int unsigned, primary key(id), unique type_val(type,value)) engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.04 sec)



mysql>  insert into aa2(type, value) values (1, unix_timestamp());

Query OK, 1 row affected (0.02 sec)



mysql>  insert into aa2(type, value) values (1, unix_timestamp());

Query OK, 1 row affected (0.01 sec)



mysql>  insert into aa2(type, value) values (1, 0);

Query OK, 1 row affected (0.00 sec)



mysql>  insert into aa2(type, value) values (2, unix_timestamp());

Query OK, 1 row affected (0.00 sec)



mysql> select * from aa2;

+----+------+------------+

| id | type | value      |

+----+------+------------+

|  3 |    1 |          0 |

|  1 |    1 | 1566654199 |

|  2 |    1 | 1566654203 |

|  4 |    2 | 1566654218 |

+----+------+------------+

4 rows in set (0.00 sec)

Session 1	Session 2
为缺省隔离级别（repeatable read）	缺省隔离级别（repeatable read）
mysql> insert into aa2(type, value) values(2, 0); Query OK, 1 row affected (0.00 sec)       mysql> select * from aa2 order by id; +----+------+------------+ | id | type | value      | +----+------+------------+ |  1 |    1 | 1566654199 | |  2 |    1 | 1566654203 | |  3 |    1 |          0 | |  4 |    2 | 1566654218 | |  5 |    2 |          0 | +----+------+------------+ 5 rows in set (0.00 sec)	mysql> insert into aa2(type, value) values(2, 0); ERROR 1062 (23000): Duplicate entry '2-0' for key 'type_val'

这个方案不需要启动事务，也不需要显式加锁，便于理解，是一种比较优美的解决方案。但是要注意检查插入失败的原因。

4. 解决思路二：锁表

MySQL提供了显式锁表和解锁表的语句LOCK TABLES和UNLOCK TABLES。要注意在InnoDB下正确使用LOCK TABLES和UNLOCK TABLES的方式：开始时是先set autocommit=0（而不是start transaction），然后是lock table，但是结束时是先commit，再unlock tables。如下所示：

Session 1	Session 2
为缺省隔离级别（repeatable read）	缺省隔离级别（repeatable read）