仅当特定条件行不存在时插入：并发与原子性问题咨询

我需要在表中仅当不存在满足特定条件的行时插入新行，这个需求十分常见。我查阅资料后关注到INSERT INTO table SELECT ... NOT EXISTS (SELECT ...)这类方案，但不想使用INSERT IGNORE等MySQL/MariaDB专属语句。不过我突然疑惑：单条SQL语句是否具备原子性？高并发场景下，INSERT语句中的SELECT子查询能不能和父INSERT原子执行？

我有两个具体问题：

1. INSERT INTO table SELECT ... NOT EXISTS (SELECT ...)方案是否正确？它具备原子性吗？高并发下安全可靠吗？
2. 除上述方案外，我能否用以下PHP代码实现需求（已知性能可能更低）：

$pdo->query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
$pdo->beginTransaction(); 
$statement = $pdo->prepare("SELECT ... FROM table WHERE conditions LIMIT 1");
$statement->execute();
if ($row = $statement->fetch()) {
  $statement2 = $pdo->prepare("INSERT INTO table ...");
  $statement2->execute();
}
$pdo->commit();

问题1解答 #

INSERT INTO ... SELECT ... NOT EXISTS(...)是正确的方案，具备原子性，高并发场景下安全可靠。

标准SQL中，单条DML语句本身就是原子操作——整个语句要么完全执行成功，要么完全失败。这里的子查询和外层INSERT是作为一个整体被数据库引擎处理的，执行过程中数据库会自动通过锁机制保证原子性，不会出现“子查询判定不存在，随后其他事务插入数据，当前事务再执行插入”的竞态问题。只要给查询条件涉及的字段添加合适的索引，避免全表扫描导致锁范围过大，这个方案在高并发下可以稳定运行。

问题2解答 #

这段PHP代码可以实现需求，但存在几个关键问题需要修正和注意：

• 逻辑错误：代码中if ($row = $statement->fetch())是查到满足条件的行时才执行INSERT，和“不存在时插入”的需求完全相反，应修改为if (!$row = $statement->fetch())。
• 性能与并发风险：使用SERIALIZABLE隔离级别确实能避免并发问题，但这是最高级别的隔离级别，会导致数据库锁粒度更大、持有时间更长，性能损耗远高于单条INSERT ... SELECT方案，高并发场景下容易引发锁等待甚至死锁，仅适合并发量极低的业务场景。
• 异常处理缺失：开启事务后，若执行过程中出现错误，必须调用$pdo->rollback()回滚事务，否则会导致事务长时间未提交，占用数据库资源。

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