遵循 Vladimir Baranov 的 评论，阅读 Dan Guzman 的关于 条件插入/更新竞争条件 和 UPSERT" Race Condition With MERGE，似乎所有三个选项都具有相同的缺点多用户环境.

Following Vladimir Baranov's comment, reading Dan Guzman's blog posts about Conditional INSERT/UPDATE Race Condition and "UPSERT" Race Condition With MERGE, seems like all three options suffers from the same drawbacks in a multi-user environment.

消除合并选项是一种矫枉过正的做法，我们只剩下选项 1 和 3.

Eliminating the merge option as an overkill, we are left with options 1 and 3.

Dan 提出的解决方案是使用显式事务并向选择添加锁提示以避免竞争条件.

Dan's proposed solution is to use an explicit transaction and add lock hints to the select to avoid race condition.

这样，选项 1 变为:

This way, option 1 becomes:

BEGIN TRANSACTION
IF NOT EXISTS(select 1 from table WITH (UPDLOCK, HOLDLOCK) where <condition>)
BEGIN
    INSERT...VALUES
END
COMMIT TRANSACTION

然后选项 2 变为:

BEGIN TRANSACTION
INSERT INTO table (<values list>)
SELECT <values list>
WHERE NOT EXISTS(select 1 from table WITH (UPDLOCK, HOLDLOCK)where <condition>)
COMMIT TRANSACTION

当然，在这两个选项中都需要一些错误处理——每个事务都应该使用 try...catch 以便我们可以在发生错误时回滚事务.

Of course, in both options there need to be some error handling - every transaction should use a try...catch so that we can rollback the transaction in case of an error.

话虽如此，我认为第三个选项可能是我个人最喜欢的，但我认为应该没有区别.

That being said, I think the 3rd option is probably my personal favorite, but I don't think there should be a difference.

遵循对话我在其他一些问题的评论中与 Aaron Bertrand 有过交流 - 我并不完全相信使用 ISOLATION LEVEL 是比单个查询提示更好的解决方案，但至少这是另一个需要考虑的选择:

Following a conversation I've had with Aaron Bertrand in the comments of some other question - I'm not entirely convinced that using ISOLATION LEVEL is a better solution than individual query hints, but at least that's another option to consider:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
INSERT INTO table (<values list>)
SELECT <values list>
WHERE NOT EXISTS(select 1 from table where <condition>);
COMMIT TRANSACTION;