insert on duplicate key update命令

背景介绍

有一批消息队列任务要执行，需要记录任务相关的数据，包括执行总数、成功数、失败数。这里采用mysql统计表的方式记录，每一批任务生成一条统计数据，但由于消息队列是无序的，所以生成统计记录的时候无法知道哪一个请求是第一次，也就不知道是 insert 还是 update。

简单列一下统计表字段

CREATE TABLE `t_statistics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `task_id` bigint(20) unsigned NOT NULL COMMENT '任务ID',
  `total_count` bigint(20) unsigned DEFAULT '0' COMMENT '总数',
  `success_count` bigint(20) unsigned DEFAULT '0' COMMENT '成功数',
  `fail_count` bigint(20) unsigned DEFAULT '0' COMMENT '失败数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_task_id` (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='任务统计表'

注意：

• primary key: id 自增主键
• unique key: task_id 任务ID

实现方式

先判断再操作

既然不知道哪次请求是第一次，那就先判断任务记录是否存在，不存在则insert，存在则update

if ( (select * from t_statistics where task_id = %d) != null ) {
	update t_statistics set total_count = total_count + 1, ... ;
} else {
	insert into t_statistics values(xxx...);
}

存在的问题：
1.每一次处理都需有两次数据库操作，暂用数据库连接且增加了RT
2.操作非原子性，会有并发问题

• 两次不同任务的请求发现DB里没有数据，决定都执行insert操作，第一个请求成功insert，第二个请求发送唯一键冲突将抛出 duplicate entry 异常
• 两次不同任务的请求发现DB里有数据，决定都执行update操作，假设这时候DB里的total_count=1，第一次请求 +1后 total_count=2，第二次请求 +1 后 total_count=2，按理说数据库最后 total_count应该为3，但是由于数据发生了脏写最后为2

insert on duplicate

这种场景下可以使用 insert on duplicate 命令，简单的说下该命令的原理：执行 insert 命令时发生主键冲突或者唯一键冲突，就执行后面的 update 命令，如果没发生冲突就执行 insert 命令。

insert into t_statistics values(xxx...) on duplicate key update total_count = total_count + 1, ... ;

可以看到命令更为简洁，操作也保持了原子性不会发生并发问题。

扩展

返回值

使用 insertOnDuplicate 的返回值是什么？

<insert id="insertOnDuplicate" parameterType="xxx.StatisticsDO">
insert into t_statistics values(xxx...) on duplicate key update total_count = total_count + 1, ... ;
</insert>

• insert -> 1
• update -> 2
• nothing -> 0

分库分表返回主键

尝试过多种方法后发现不能返回历史数据的主键，所以可以通过返回值来判断是否是insert还是update，再判断怎么获取主键，伪代码如下：

	return pk;
} else {
	return selectId(xxx);
}

执行步骤

1.尝试把新行插入到表中
2.当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时，则对现有的行加上S（共享）锁，然后返回该行数据给server层
3.server在内存对该行执行update操作
4.对该行记录加上X（排他）锁
5.将update后的结果写入该行

death lock