数据重复插入问题及解决方案

喜欢ヅ旅行 2023-09-24 22:23 247阅读 0赞

## 1.问题背景 ##

某天客户反馈说，有一条一模一样的数据在列表中展示出来，主要这个列表还是比较关键的业务数据，经过排查发现，数据库中的确存在了两条数据，而且关键字段没有加唯一索引，导致了数据入库的时候重复插入了。

## 2.引起数据重复的原因有哪些 ##

引起数据库被重复插入的原因无外乎几个原因，表象原因可能就是如网络延迟、用户连点、并发等等

实际上是我们在数据库设计、及代码逻辑的严谨性出了问题。

##### 网络延迟场景 #####

用户填写完表单，然后点击提交按钮，由于网络延迟，迟迟得不到后台的响应，这时用户会下意识的再次点击，这就导致了用户的连点，从而导致后端再第一次数据没处理完的时候，又进行了第二次提交，导致数据重复

##### 用户连点场景 #####

用户填写完了表单，然后在同一时刻连续点击提交按钮，这时需要把数据提交给后台，然而提交给后台是需要一个过程的，如果这个时候用户第一次数据还没提交完，第二次数据又过来了，就导致了重复数据的产生；

其实网络延迟场景和用户连点场景最终都是因为后台没有及时响应，才导致了第二次的数据插入

##### 高并发场景： #####

同个时间点，提交大量相同请求。

## 3.解决数据重复（幂等性）的方案 ##

幂等：在编程中一个幂等操作的特点是其任意多次执行所产生的影响均与一次执行的影响相同。

关键业务数据新增操作时一定要做好幂等，别等到上线了才发现，数据库多了条脏数据。

需要幂等的场景如:

核心业务数据新增，如订单系统中新增订单、用户信息系统中新增用户；

审批流系统多人审批同一数据时，可能导致审批多次。

定时任务触发数据生成时，定时任务有时可能重复触发，导致数据生成多次。

.......

#### 3.1 前端解决方案（不可靠，推荐） ####

前端的解决办法就是：户点击按钮后，让按钮点击失效或者禁用，待后端响应完成后，按钮可用

#### 3.2 数据库解决方案(可靠，推荐，适合高并发场景) ####

幂等主要手段就是通过表中的唯一约束实现。所以通过添加表的加唯一约束或唯一索引进行约束处理，也是最有效的防治重复提交的方法

CREATE TABLE `user` (
      `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `username` char(30) NOT NULL COMMENT '用户名',
      `mobile` char(11) DEFAULT NULL COMMENT '手机号',
      `email` varchar(40) NOT NULL COMMENT '邮箱',
      `password` char(32) NOT NULL COMMENT '密码',
      `salt` char(8) NOT NULL COMMENT '密码干扰串',
      `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '账号状态 1:正常',
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL
      PRIMARY KEY (`uid`),
      UNIQUE KEY `mobile` (`mobile`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

简单的幂等写法伪代码如下：

class User {
        public function addUser(array $userParam) {
            // 幂等处理
           $userId = 0;
            try {
                $userId = UserModel->insert($userParam);//插入用户，返回uid
            } catch (Exception $e) {//捕获sql唯一约束的异常，Duplicate entry 'xxxxx' for key 'mobile'
                $userId = UserModel->getUser($userParam);//捕获唯一约束异常，返回uid
            }
     
            return $userId;
        }
    }

PHP

Copy

**注意：不推荐先查询再插入的方案，主要因为性能不好以及在高并发场景下并不能满足需求**

MySql避免"重复插入记录"的方法(INSERT ignore into,Replace into,ON DUPLICATE KEY UPDATE)\\

使用ignore关键字 如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用：

INSERT IGNORE into user VALUES('1','joshua317','13299999999');

这样当有重复记录就会忽略,执行后返回数字0 还有个应用就是复制表,避免重复记录：

使用Replace

REPLACE into user VALUES('1','joshua317','13299999999');

REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值，则在新记录被插入之前，旧记录被删除，即： 尝试把新行插入到表中 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时： 从表中删除含有重复关键字值的冲突行 再次尝试把新行插入到表中 旧记录与新记录有相同的值的判断标准就是： 表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。 返回值： REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）

**ON DUPLICATE KEY UPDATE**

也可以在INSERT INTO…..后面加上 ON DUPLICATE KEY UPDATE方法来实现。如果指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。 例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1; 
    UPDATE `table` SET `c`=`c`+1 WHERE `a`=1;

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。 注释：如果列b也是唯一列，则INSERT与此UPDATE语句相当：

UPDATE `table` SET `c`=`c`+1 WHERE `a`=1 OR `b`=2 LIMIT 1;

如果a=1 OR b=2与多个行向匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。 您可以在UPDATE子句中使用VALUES(col\_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说，如果没有发生重复关键字冲突，则UPDATE子句中的VALUES(col\_name)可以引用被插入的col\_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义，其它时候会返回NULL。

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`);

本语句与以下两个语句作用相同：

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=3; 
    INSERT INTO `table` (`a`, `b`, `c`) VALUES (4, 5, 6) ON DUPLICATE KEY UPDATE c=9;

注释：当您使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。 示例： 这个例子是我在实际项目中用到的：是将一个表的数据导入到另外一个表中，数据的重复性就得考虑(如下)，唯一索引为：email：

INSERT INTO `table_name1` (`title`, `first_name`, `last_name`, `email`, `phone`, `user_id`, `role_id`, `status`, `campaign_id`) 
        SELECT '', '', '', `table_name2`.`email`, `table_name2`.`phone`, NULL, NULL, 'pending', 29 FROM `table_name2` 
        WHERE `table_name2`.`status` = 1 
    ON DUPLICATE KEY UPDATE `table_name1`.`status`='pending'

再贴一个例子：

INSERT INTO `class` SELECT * FROM `class1` ON DUPLICATE KEY UPDATE `class`.`course`=`class1`.`course`

其它关键：DELAYED 做为快速插入，并不是很关心失效性，提高插入性能。 **IGNORE 只关注主键对应记录是不存在，无则添加，有则忽略。**

特别说明：在MYSQL中UNIQUE索引将会对null字段失效，也就是说(a字段上建立唯一索引)：

INSERT INTO `test` (`a`) VALUES (NULL);

是可以重复插入的（联合唯一索引也一样）。

3.3 悲观锁解决方案(可靠，推荐，适合高并发场景)

悲观锁是基于一种悲观的态度类来防止一切数据冲突，它是以一种预防的姿态在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，然后才可以对数据进行操作，一般数据库本身锁的机制都是基于悲观锁的机制实现的;

特点：可以完全保证数据的独占性和正确性，因为每次请求都会先对数据进行加锁， 然后进行数据操作，最后再解锁，而加锁释放锁的过程会造成消耗，所以性能不高;

加解锁伪代码如下：

/***
     1、 客户端A请求服务器设置key的值，如果设置成功就表示加锁成功
     2、 客户端B也去请求服务器设置key的值，如果返回失败，那么就代表加锁失败
     3、 客户端A执行代码完成，删除锁
     4、 客户端B在等待一段时间后在去请求设置key的值，设置成功
     5、 客户端B执行代码完成，删除锁
    **/
    $lockKey = 'lock_key';
    $lockVaule = 'lock_vaule';
    $isLock = $redisHandle->set($lockKey, $lockVaule, ['nx', 'ex' => $ttl]);//nx代表当key不存在时设置 ex代表设置过期时间
    if($isLock){// 1.获取锁成功
     
        // 2.处理业务
     
        // 3.解锁
        $redisHandle->del($lockKey);
    } else {
        // 资源被其他请求占用，提示服务繁忙，请稍后再试
    }

**意外情况发生**

假设锁提前过期后，客户端A还没执行完，然后客户端B获取到了锁，这时候客户端A执行完了，会不会在删锁的时候把B的锁给删掉？

$lockKey = 'lock_key';
    $lockVaule = 'lock_key_'.uniqid();  //分配一个随机值
    $isLock = $redisHandle->set($lockKey, $lockVaule, ['nx', 'ex' => $ttl]);//nx代表当key不存在时设置 ex代表设置过期时间
    if ($isLock) {// 1.获取锁成功
        if ($redisHandle->get($lockKey) == $value) {  //防止提前过期，误删其它请求创建的锁
            // 2.处理业务
     
            // 3.解锁
            $redisHandle->del($lockKey);
        } else {
            // 资源被其他请求占用，提示服务繁忙，请稍后再试
        }
    } else {
        // 资源被其他请求占用，提示服务繁忙，请稍后再试
    }

2.假设客户端业务处理中断，解锁失败导致锁没有释放且过期时间未到，然后客户端B却无法获取锁进行处理呢？

$lockKey = 'lock_key';
    $lockVaule = 'lock_key_'.uniqid();  //分配一个随机值
    try {
    $isLock = $redisHandle->set($lockKey, $lockVaule, ['nx', 'ex' => $ttl]);//nx代表当key不存在时设置 ex代表设置过期时间
        if ($isLock) {// 1.获取锁成功
            if ($redisHandle->get($lockKey) == $value) {  //防止提前过期，误删其它请求创建的锁
                // 2.处理业务
     
                // 3.解锁
                $redisHandle->del($lockKey);
            } else {
                // 资源被其他请求占用，提示服务繁忙，请稍后再试
            }
        } else {
            // 资源被其他请求占用，提示服务繁忙，请稍后再试
        }
    } catch (Exception $e) {
        // 3.解锁
        $redisHandle->del($lockKey);
    }

其他情况不多赘述。。。

**注意：不管是什么方案，一定要结合自己的业务场景，应用场景进行处理，上面的只是提供一种解决思路，并不是万能钥匙，切记不要生搬硬套**