背景: Java mysql

在工作中，我们在向数据库插入数据的时候，经常会遇到“判断记录是否存在，如果不存在就新增记录；如果存在则不新增记录”的逻辑。在这里描述一下最近遇到的场景，以及思考和实践的过程。

为了更清楚的描述问题，我们假设有一张user_task表，它的结构如下

CREATE TABLE `user_task` ( `key` int ( 11 ) NOT NULL AUTO_INCREMENT, `field_one` varchar( 20 ) , `field_two` varchar( 20 ) , `user_id` int ( 11 ) NOT NULL , `task_id` int ( 11 ) NOT NULL , `other_field` varchar( 20 ) NOT NULL comment '任务执行备注' , PRIMARY KEY (`key`) , KEY `idx_field_one` (`field_one`) , KEY `idx_field_two` (`field_two`) , KEY `idx_other_field` (`other_field`)

)ENGINE=InnoDB AUTO_INCREMENT= 0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

现在假设在用户端的操作或者其他服务调用，会往这张表插入记录，要求不能有user_id+task_id重复的记录，业务上不会按照userid+task_id的条件查询，另外用户可能出现重复提交，其他服务的调用频率比较高。

下面就来看一下我们的解决思路：

1.最先想到的实现逻辑

从数据库查询user_id+task_id的记录

if (记录不存在) { 插入该记录到数据库 } else { 重复插入，不采取任何措施 }

问题: 当并发请求到来，就会插入user_id+task_id重复的记录。

2.添加唯一索引user_id+task_id

通过数据库唯一索引来避免重复数据，当数据库层发现唯一索引有重复数据，就会抛出异常。

优点: 简单有效。

问题：数据库索引已经有两个了，而且不会按照user_id+task_id的条件查询。在这个场景下，为了避免重复记录，去新建索引的代价比较大，先想想其他办法，不行再使用这个办法。

PS：如果重复插入时，不想让数据库抛异常，可以使用”INSERT IGNORE INTO TABLE(…) VALUES(…)” ， 这样当有重复记录就会忽略，执行后返回数字0。

3.数据库语句

INSERT INTO USER_TASK (key, field_one, field_two,user_id, task_id, other_field)

SELECT key, field_one, field_two, user_id, task_id,other_field FROM dual WHERE NOT EXISTS ( SELECT * FROM USER_TASK WHERE user_id = ? and task_id=?) ;

问题：并发插入的时候回出现重复记录。

使用mysql工具打开两个窗口，相当于打开了两个数据库连接。每个窗口贴上一样的sql语句：

start transaction;

INSERT INTO USER_TASK ( field_one,field_two, user_id, task_id, other_field)

SELECT 1, 1, 1, 2, 1 FROM dual WHERE NOT EXISTS ( SELECT * FROM USER_TASK WHERE user_id = 1 and task_id= 2 ) ;commit;

然后两个窗口都先开启事务执行插入,但是不提交，

最后，分别单独执行commit，提交事务。

然后查看数据库表中的数据，如下：

发现Userid+task_id的记录重复了。

4.对记录加锁

start transaction;SELECT key from USER_TASK WHERE user_id=?And task_id=? For update ;

INSERT INTO USER_TASK (key, field_one, field_two,user_id, task_id, other_field)

SELECT key, field_one, field_two, user_id, task_id,other_field FROM dual WHERE NOT EXISTS ( SELECT * FROM USER_TASK WHERE user_id = ? and task_id=?) ;commit;

问题：看似可以，其实并不可以，对记录加锁的前提是必须要数据存在，当数据库不存在记录user_id+task_id的时候，并发执行，会产生重复记录。

使用mysql工具打开两个窗口，相当于打开了两个数据库连接。每个窗口贴上一样的SQL语句：

start transaction;SELECT * from USER_TASK WHERE user_id= 1 ANDtask_id= 3 FOR UPDATE ;

INSERT INTO USER_TASK ( field_one,field_two, user_id, task_id, other_field) SELECT 1, 1, 1, 3, 1 FROM dual WHERE NOT EXISTS ( SELECT * FROM USER_TASK WHERE user_id = 1 and task_id= 3 ) ;commit;

然后两个窗口分别都先开启事务，尝试锁定数据，

最后，分别单独执行commit，提交事务。

然后查看数据库表中的数据，如下：

发现Userid+task_id的记录重复了。

5.对第一种方案加个锁

Try{ lock.lock(); if(记录不存在) { 插入该记录到数据库 }else { 重复插入，不采取任何措施 }}finally{ Lock.unlock();}

问题：如果是单实例应用，虽然能解决插入重复记录的问题，但是由于任何的插入请求都会加锁，并发请求插入记录的时候性能差。

如果是多实例（分布式）应用，由于加锁是在一台机器上，多台机器同时并发执行，同样会产生多条重复记录。如果把锁换成一个大的分布式锁，高并发请求插入记录的时候性能比较差。

6.按照业务规则过滤掉一段时间内重复的插入请求

由于Redis服务处理请求是单线程的，所有每一条到达服务端的命令不会立刻执行，所有的命令都会进入一个队列中，然后逐个被执行。这里我们通过使用redis的‘INCR’命令来避免并发问题，过滤掉多余的并发请求。业务key=user_id+task_id。

思想: 当执行’INCR’的过程中，只可能有一个线程成功的设置value为1。如果期待的值等于1，执行后面的逻辑；期待的值不等于1，被过滤掉。

private void filterRedundantInsertion (Integer userId,Integer taskId) { String key = userId + "_" + taskId;

//如果key不存在, 初始化value默认为0 RedisAtomicLong counter = new RedisAtomicLong(key, redisTemplate.getConnectionFactory()); Long expectedValue= 1 L; Long value = counter.incrementAndGet();

if (expectedValue.equals(value)) { counter.expire( 5 , TimeUnit.SECONDS); } else {

throw new BusinessException ( "redundant Insertion" ) ; }}

在执行插入之前，调用“ filterRedundantInsertion ”方法，过滤掉30秒之内的key=userId+taskId重复的插入请求。这里30秒钟执行一个插入操作足够了。

执行插入使用第3种方法中的SQL语句:

INSERT INTO USER_TASK (key, field_one, field_two,user_id, task_id, other_field)

SELECT key, field_one, field_two, user_id, task_id,other_field FROM dual WHERE NOT EXISTS ( SELECT * FROM USER_TASK WHERE user_id = ? and task_id=?) ;

优点:这种方法和第五种方法相比，粒度更细，插入数据时候的并发性能更好。在保证了并发插入的高性能的同时，避免了重复记录的插入。

返回搜狐，查看更多