使用事务还是批量查询可解决MySQL并发插入数据重复问题

结论 #

只有将完整操作包裹在搭配写锁的事务中，才能100%避免该并发问题，单纯替换为批量插入无法彻底解决冲突。

原因说明 #

为什么批量插入不行 #

就算你把1000次单条插入合并为1次批量插入，依然存在时序冲突风险：

• 进程A先执行DELETE清空Bob相关数据
• 进程A刚要执行批量INSERT的瞬间，进程B的DELETE执行完成（此时表还是空的，相当于DELETE没删任何内容）
• 进程A和进程B的批量INSERT先后执行，最终表中会出现2000条数据，依然不符合预期。

为什么事务+写锁可以彻底解决 #

你需要给DELETE语句加FOR UPDATE写锁，配合事务的原子性和隔离性，从根本上避免并发争抢：

1. 第一个执行到DELETE语句的进程会拿到匹配行的写锁，同时会加间隙锁（哪怕表中没有Bob相关数据也会生效），其他进程执行到同一条DELETE语句时会直接阻塞，直到第一个进程的事务全部提交后才会继续执行。
2. 第一个进程会在事务内完整执行完所有INSERT操作，提交事务后才会释放锁。
3. 第二个进程拿到锁之后，会先删除第一个进程插入的1000条Bob数据，再执行自己的1000条插入，最终表中永远只会保留1000条数据。

修复后代码示例 #

// 开启PDO异常模式，方便事务异常时回滚
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 开启事务
$pdo->beginTransaction();

try {
    // DELETE语句加FOR UPDATE写锁，阻塞其他同条件的读写操作
    $query = $pdo->prepare("DELETE FROM `table` WHERE name = ? FOR UPDATE");
    $query->execute(['Bob']);

    $query = $pdo->prepare("INSERT INTO `table` (name, age) VALUES (?, ?)");
    for ($i = 0; $i < 1000; $i++) {
        // 注意：?占位符需要传索引数组，原代码中的关联数组写法不匹配
        $query->execute(['Bob', 34]);
    }

    // 所有操作完成后提交事务，释放锁
    $pdo->commit();
} catch (Exception $e) {
    // 出现异常回滚事务，避免脏数据
    $pdo->rollBack();
    throw $e;
}

补充优化建议 #

你可以把循环单条插入替换为批量插入，减少和数据库的交互次数，提升执行性能，但这只是性能优化手段，并不是解决并发问题的核心方案。

内容的提问来源于stack exchange，提问作者David Marcus