如果记录不存在，插入记录的最佳做法是什么? [英] What is the best practice for inserting a record if it doesn't already exist?
问题描述
如果记录不存在于表中，我知道至少三种插入记录的方法:
I know at least three ways to insert a record if it doesn't already exist in a table:
第一个使用 if not exist:
The first one is using if not exist:
IF NOT EXISTS(select 1 from table where <condition>)
INSERT...VALUES
第二个是使用合并:
The second one is using merge:
MERGE table AS target
USING (SELECT values) AS source
ON (condition)
WHEN NOT MATCHED THEN
INSERT ... VALUES ...
第三个是使用插入...选择:
The third one is using insert...select:
INSERT INTO table (<values list>)
SELECT <values list>
WHERE NOT EXISTS(select 1 from table where <condition>)
但哪个是最好的?
第一个选项似乎不是线程安全的，因为如果两个或多个用户尝试插入同一条记录，则记录可能会插入在 if 中的 select 语句和随后的 insert 语句之间.
The first option seems to be not thread-safe, as the record might be inserted between the select statement in the if and the insert statement that follows, if two or more users try to insert the same record.
至于第二个选项，合并似乎是一种矫枉过正，因为 文档 指出:
As for the second option, merge seems to be an overkill for this, as the documentation states:
性能提示:当两个表具有复杂的匹配特征混合时，为 MERGE 语句描述的条件行为最有效.例如，如果一行不存在则插入，或者如果匹配则更新该行.当简单地根据另一个表的行更新一个表时，可以使用基本的 INSERT、UPDATE 和 DELETE 语句来提高性能和可伸缩性.
Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements.
所以我认为第三个选项最适合这种情况(仅在记录不存在时插入，如果存在则无需更新)，但我想知道 SQL Server 专家的想法.
So I think the third option is the best for this scenario (only insert the record if it doesn't already exist, no need to update if it does), but I would like to know what SQL Server experts think.
请注意，插入后，我不想知道该记录是否已经存在，或者它是否是一个全新的记录，我只需要它在那里，以便我可以继续存储的其余部分程序.
Please note that after the insert, I'm not interested to know whether the record was already there or whether it's a brand new record, I just need it to be there so that I can carry on with the rest of the stored procedure.
推荐答案
遵循 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;
这篇关于如果记录不存在，插入记录的最佳做法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
