SQL Server 2005 - 如果不存在则插入

Question

互联网上有很多关于这个常见“问题”的信息。

解决方案如下：

IF NOT EXISTS() BEGIN INSERT INTO (...) END

在我看来，不是线程安全的，你可能会同意。

然而，您能否确认将exist放入单个select的where子句中是否足以解决SQL引擎中最高并发性的问题？这样就足够了吗？

insert into Table (columns)
select column1, column2, column3
where not exists (select top 1 1 from Table where something)

是否需要添加更高级别的事务处理或者可以在默认级别下执行提交操作？

这个操作在未提交级别下可行吗？

谢谢！

//后来添加

我可以假设两个sql语句都是正确的吗：

1) set transaction isolation level repeatable read

   IF NOT EXISTS() BEGIN INSERT INTO (...) END

2) 设置事务隔离级别为可重复读

insert into Table (columns)
select column1, column2, column3
where not exists (select top 1 1 from Table where something)

- Paul

---

不管是readcommitted还是uncommitted级别，都不能正常工作。你需要一些额外的锁定提示。可能是只有在不存在行时才插入行的重复问题。 - Martin Smith

你不需要“帮助”存在子句 - 它们足够聪明，在看到1行后就能完成。你只需执行 EXISTS (SELECT * FROM...，它会自动处理正确的事情。 - Damien_The_Unbeliever

它会做正确的事情，但在并发情况下，它不够线程安全，高负载下可能会出现主键冲突错误。因此，根据@Martin的链接，应添加可重复读隔离事务。 - Paul

@Paul - 我的意思是在 exists 子句中不需要做"select top 1..." - 我并没有对代码的其余部分进行评论。 - Damien_The_Unbeliever

@Damien_The_Unbeliever 对不起我误解了。我一直认为写"top 1 1"不会让服务器获取列的值。 - Paul

Answer 1

使用TRY/CATCH可以避免额外的读取。

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

• NOT EXISTS将读取表中的数据，无论在IF还是WHERE语句中
• 插入操作需要读取以检查唯一性

如果可以丢弃重复数据，则这是一种高度可扩展的技术。

链接:

• 请参见我的回答：Only inserting a row if it's not already there 和 SQL Server 2008: INSERT if not exits, maintain unique column
• 如果您还需要执行更新操作，请参见：Which is the best choice in delete-insert vs if-update else-insert?

- gbn

---

好的，但如果我要插入或更新，那么我可以这样做：如果 error_number() = 2627，则开始更新...结束吗？ - Paul

@Paul：请看我最后一个链接，了解如何处理UPDATE操作。 - gbn

在begin catch块中是否可以提高默认错误？还是我必须明确获取error_number和message以满足raiserror参数？ - Paul

@Paul：你需要阅读ERROR_NUMBER()和ERROR_MESSAGE()等内容，并构建自己的消息。示例。然而，该数字随后变为50000。下一个版本添加了重新抛出相同错误的功能：THROW。 - gbn

Answer 2

针对更新后的问题，可重复读 仍然不足以满足要求。

你需要使用 holdlock / serializable 级别。

你正在尝试防止 幻读（即在第一次读取时没有行符合条件，因此 NOT EXISTS 返回 true，但随后一个并发事务插入了符合条件的行）。

- Martin Smith

---

写SQL语句时，我似乎会开始变得偏执。 :) - Paul

@Paul - serializable 也会带来死锁的风险，但使用 UPDLOCK, HOLDLOCK 可以避免这种情况。不过我个人更倾向于使用 TRY...CATCH。 - Martin Smith