SQL Server 2005中的原子UPSERT

Question

在SQL Server 2005中执行原子“ UPSERT”（存在时为UPDATE，否则为INSERT）的正确模式是什么？我在SO上看到了很多代码（例如，请参阅检查是否存在一行，否则请插入），其代码分为以下两部分：UPDATE ...FROM ...WHERE <condition>-- race condition risk hereIF @@ROWCOUNT = 0  INSERT ...要么IF (SELECT COUNT(*) FROM ... WHERE <condition>) = 0  -- race condition risk here  INSERT ...ELSE  UPDATE ...其中<condition>是自然键的评估。上述方法似乎都不能很好地处理并发。如果我不能让两行具有相同的自然键，则上述所有情况似乎都有在竞争条件场景中插入具有相同自然键的行的风险。我一直在使用以下方法，但是我很惊讶没有在人们的回应中看到它，因此我很奇怪它出了什么问题：INSERT INTO <table>SELECT <natural keys>, <other stuff...>FROM <table>WHERE NOT EXISTS   -- race condition risk here?   ( SELECT 1 FROM <table> WHERE <natural keys> )UPDATE ...WHERE <natural keys>请注意，此处提到的竞争条件与先前代码中的竞争条件不同。在早期的代码中，问题在于幻像读取（行在其他会话之间插入到UPDATE / IF之间或SELECT / INSERT之间）。在上面的代码中，竞争条件与DELETE有关。在（WHERE NOT EXISTS）执行之后但在INSERT执行之前，是否可以将另一个行删除匹配的行？目前尚不清楚WHERE NOT EXISTS在何处与UPDATE一起锁定任何内容。这是原子的吗？我在SQL Server文档中找不到该文档的记录位置。编辑： 我意识到这可以使用事务来完成，但是我想我需要将事务级别设置为SERIALIZABLE以避免幻像读取问题？对于这样一个普遍的问题，这肯定是大材小用吗？

Answer 1

在测试行是否存在时传递updlock，rowlock，holdlock提示。Holdlock确保所有插件都已序列化；行锁允许并发更新现有行。

如果您的PK是bigint，则更新可能仍会阻止，因为内部哈希会退化为64位值。

begin tran -- default read committed isolation level is fine

if not exists (select * from <table> with (updlock, rowlock, holdlock) where <PK = ...>

    -- insert

else

    -- update

commit

Answer 2

编辑：Remus是正确的，条件插入w / where子句不能保证相关子查询和表插入之间的状态一致。

正确的表提示可能会强制保持一致状态。INSERT <table> WITH (TABLOCKX, HOLDLOCK)似乎有效，但是我不知道这是否是有条件插入的最佳锁定级别。

在像Remus所述的琐碎测试中，TABLOCKX, HOLDLOCK显示的插入量是没有表提示的插入量的5倍，并且没有PK错误或过程。

原始答案，不正确：

这是原子的吗？

是的，条件插入w / where子句是原子的，您的INSERT ... WHERE NOT EXISTS() ... UPDATE表单是执行UPSERT的正确方法。

我将IF @@ROWCOUNT = 0在INSERT和UPDATE之间添加：

INSERT INTO <table>

SELECT <natural keys>, <other stuff...>

WHERE NOT EXISTS

   -- no race condition here

   ( SELECT 1 FROM <table> WHERE <natural keys> )

IF @@ROWCOUNT = 0 BEGIN

  UPDATE ...

  WHERE <natural keys>

END

单个语句始终在事务中执行，无论是它们自己（自动提交和隐式），还是与其他语句（显式）一起执行。