SQL Server的INSERT或UPDATE解决方案

Question

假设有一个表格结构如下：MyTable(KEY, datafield1, datafield2...)。

通常我想要更新已有的记录，或在记录不存在时插入新的记录。

基本上：

IF (key exists)
  run update command
ELSE
  run insert command

最佳性能的编写方式是什么？

- chris

---

类似的问题：

• 如何在SQL Server上插入更新存储过程？
• SQL Server 2005实现MySQL REPLACE INTO的方法？

- Sören Kuklau

对于第一次看到这个问题的任何人，请确保阅读所有答案及其评论。年龄有时会导致误导性的信息... - Aaron Bertrand

考虑使用在SQL Server 2005中引入的EXCEPT运算符。 - Tarzan

Answer 1

不要忘记事务。性能虽好，但简单的（IF EXISTS...）方法非常危险。
当多个线程尝试执行插入或更新时，很容易出现主键冲突。

@Beau Crawford和@Esteban提供的解决方案展示了一般性思路，但容易出错。

为避免死锁和主键冲突，可以使用类似于这样的方法：

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

或

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

- aku

---

问题询问的是最高效的解决方案而非最安全的。尽管事务会增加过程的安全性，但也会增加开销。 - Luke Bennett

这两种方法仍可能失败。如果两个并发线程在同一行上执行相同的操作，则第一个线程将成功，但第二个插入将因为主键冲突而失败。事务并不能保证插入操作一定会成功，即使更新失败是因为记录已存在。要确保任意数量的并发事务能够成功，必须使用锁。 - Jean Vincent

为什么您在BEGIN TRAN之前使用表提示（“with (xxxx)”）而不是使用“SET TRANSACTION ISOLATION LEVEL SERIALIZABLE”？ - EBarr

@CashCow，最后一次获胜，这就是INSERT或UPDATE应该执行的操作：第一个插入记录，第二个更新记录。添加锁可以使此操作在非常短的时间内完成，防止发生错误。 - Jean Vincent

我一直认为使用锁提示是不好的，我们应该让微软内部引擎来决定锁。这是规则的明显例外吗？ - user8280126

Answer 2

看到我的类似之前问题的详细回答

@Beau Crawford在SQL 2005及以下版本中是一种不错的方法，但如果您要授予声望，则应该给予第一个在SO上发布的人。唯一的问题是对于插入仍然需要进行两个IO操作。

MS Sql2008从SQL：2003标准引入了merge：

merge tablename with(HOLDLOCK) as target
using (values ('new value', 'different value'))
    as source (field1, field2)
    on target.idfield = 7
when matched then
    update
    set field1 = source.field1,
        field2 = source.field2,
        ...
when not matched then
    insert ( idfield, field1, field2, ... )
    values ( 7,  source.field1, source.field2, ... )

现在只是一个IO操作，但代码很糟糕 :-(

- Keith

---

@Ian Boyd - 是的，那是SQL:2003标准的语法，而不是其他大多数数据库提供商都决定支持的upsert。upsert语法是一种更好的方式来完成这个操作，所以至少微软也应该支持它——毕竟在T-SQL中它并不是唯一的非标准关键词。 - Keith

请查看此链接http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx，了解如何避免竞争条件导致错误的方法，即使在使用`MERGE`语法时也可能会出现此类错误。 - Seph

@Seph，这真是个惊喜——微软在这方面有些失败 :-S 我猜这意味着在高并发情况下进行合并操作需要使用 HOLDLOCK。 - Keith

@Keith 是的，当你只想要一个upsert时，MERGE就显得太过冗长了，而这是如此简单和常见的操作。不确定ANSI和MSSQL设计者是在想什么。也许再过三四年吧... 唉... - moodboom

这个回答需要更新以考虑Seph的评论，指出在没有HOLDLOCK的情况下它不是线程安全的。根据链接的帖子，MERGE隐式地获取了一个更新锁，但在插入行之前释放它，这可能会导致竞态条件和插入主键冲突。通过使用HOLDLOCK，锁定将保持到插入发生后。 - Triynko

Answer 3

执行UPSERT操作：

如果存在记录则更新MyTable表中的FieldA字段，根据Key字段匹配。
否则，
   如果没有匹配到任何记录，则插入一条新的记录到MyTable表中(FieldA)值为@FieldA。

http://en.wikipedia.org/wiki/Upsert

- Beau Crawford

---

如果您应用了适当的唯一索引约束，就不应该出现主键违规。 约束的整个目的是防止重复行发生。无论有多少个线程正在尝试插入，数据库都会根据需要进行串行化以强制执行约束...如果没有这样做，那么引擎就是毫无价值的。当然，在序列化事务中封装此操作会使其更加正确，并且更不容易出现死锁或插入失败的情况。 - Triynko

@Triynko，我认为@Sam Saffron的意思是如果两个或更多线程以正确的顺序交错执行，那么SQL Server将会抛出一个错误，指示主键冲突可能会发生。将其包装在可串行化事务中是防止以上语句中出现错误的正确方法。 - EBarr

即使您拥有一个自动递增的主键，您仍需要关注表上可能存在的任何唯一约束。 - Seph

数据库应该负责处理主键问题。你的意思是，如果更新失败，并且另一个进程在插入之前到达，那么你的插入将失败。在这种情况下，无论如何都存在竞态条件。锁定不会改变写入结果的事实，即尝试写入的进程之一将获得该值。 - CashCow

Answer 4

很多人会建议你使用MERGE，但我警告你不要这样做。默认情况下，它不能比多个语句更好地保护你免受并发和竞争条件的影响，并引入了其他危险：

• 在 SQL Server 的 MERGE 语句中要小心使用
• 所以，你想使用 MERGE，是吗？

即使有这种“更简单”的语法可用，出于简洁起见，我仍然更喜欢这种方法（错误处理已省略）：

BEGIN TRANSACTION;

UPDATE dbo.table WITH (UPDLOCK, SERIALIZABLE) 
  SET ... WHERE PK = @PK;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END

COMMIT TRANSACTION;

• 请不要再使用这种UPSERT反模式

很多人会建议这样做：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
BEGIN
  INSERT ...
END
COMMIT TRANSACTION;

但这只能确保您可能需要读两次表格才能找到要更新的行。在第一个示例中，您只需要定位一次行即可。(在两种情况下，如果最初读取未找到任何行，则会执行插入操作。)

其他人会建议这样做:

BEGIN TRY
  INSERT ...
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    UPDATE ...
END CATCH

然而，这样做存在问题，即让SQL Server捕获你本可以在第一时间预防的异常，这种情况代价更高，除非几乎每次插入操作都会失败。我在以下链接中已经证明:

• 在进入TRY/CATCH之前检查潜在的约束违规
• 不同错误处理技术的性能影响

- Aaron Bertrand

---

如何在插入/更新多条记录的临时表时进行插入/更新操作？ - Imran Qadir Baksh - Baloch

两年多以后，终于有了友善的回复 :) - Imran Qadir Baksh - Baloch

抱歉，我不能实时接收评论通知。 - Aaron Bertrand

@AaronBertrand 谢谢您。关于2020-09-23的编辑，您上面提到的首选方法在事务中使用了"ISOLATION LEVEL SERIALIZABLE"，但没有使用UPDLOCK表提示。 [2020-09-02发布的链接文章]（https://sqlperformance.com/2020/09/locking/upsert-anti-pattern）使用了两个表提示“UPDATE dbo.t WITH（UPDLOCK，SERIALIZABLE）”，并且没有使用“ISOLATION LEVEL SERIALIZABLE”（这是有道理的）。抱歉如果问得愚蠢，但是这两种方法是等效的，还是其中一种方法比另一种更好？我猜您最近的文章代表了您当前的首选方法。谢谢。 - iokevins

@iokevins 我想不出有什么区别。实际上，就偏好而言，我很矛盾，当我们谈论在查询中应用NOLOCK提示时，我更喜欢在查询级别上有提示，但是在这种情况下，如果要将NOLOCK提示应用于查询中的每个表，则更喜欢单个SET语句以便稍后修复。 - Aaron Bertrand

Answer 5

IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)

编辑：

唉，尽管这样做的解决方案没有选择器似乎更好，因为它们可以在少一个步骤的情况下完成任务，但即使对我自己有害，我也必须承认这一点。

- Esteban

---

我仍然更喜欢这个选项。upsert操作似乎更像是通过副作用进行编程，而我从未看过初始选择的微不足道的聚集索引查找在真实数据库中引起性能问题。 - Eric Z Beard

@EricZBeard 这不是关于性能的问题（尽管您执行的冗余操作并不总是一个查找，这取决于您检查以指示重复的内容）。真正的问题是额外操作为竞态条件和死锁打开的机会（我在此帖子中解释了原因）。 - Aaron Bertrand

如果使用此解决方案，请确保添加“WHERE ID = rowID”子句，因为它将以此方式更新所有行 ;) - IVIike

我认为这是处理少量数据的表格的最佳解决方案，如果查询不太频繁的话，因为对于SQL初学者来说，这是最易读的语法。 - AoooR

Answer 6

如果您想一次性插入多条记录，可以使用ANSI SQL:2003 DML语句MERGE。

MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

请查看在SQL Server 2005中模拟MERGE语句.

- Eric Weilnau

---

在Oracle中，发出MERGE语句会锁定表。在SQL*Server中是否也会发生同样的情况？ - Mike McAllister

如果你使用MERGE函数，它容易受到竞争条件的影响（参见http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx），除非你让它保持某些锁定。此外，通过SQL Profiler观察MERGE的性能表现...我发现它通常比替代方案慢并生成更多读取操作。 - EBarr

@EBarr - 感谢您提供的锁定链接。我已更新我的答案，包括建议的锁定提示。 - Eric Weilnau

还可以查看http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/。 - Aaron Bertrand

Answer 7

虽然现在评论有点晚了，但我想补充一个更完整的例子，使用 SQL Server 中的 MERGE 实现常被称作“Upsert”语句的插入+更新语句。这里提供了一个非常好的例子：http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx，上面还解释了锁定和并发情况。为了参考，我将引用相同的内容。

ALTER PROCEDURE dbo.Merge_Foo2
      @ID int
AS

SET NOCOUNT, XACT_ABORT ON;

MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
USING (SELECT @ID AS ID) AS new_foo
      ON f.ID = new_foo.ID
WHEN MATCHED THEN
    UPDATE
            SET f.UpdateSpid = @@SPID,
            UpdateTime = SYSDATETIME()
WHEN NOT MATCHED THEN
    INSERT
      (
            ID,
            InsertSpid,
            InsertTime
      )
    VALUES
      (
            new_foo.ID,
            @@SPID,
            SYSDATETIME()
      );

RETURN @@ERROR;

- user243131

---

使用MERGE语句时还有其他需要注意的事项：http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ - Aaron Bertrand

Answer 8

/*
CREATE TABLE ApplicationsDesSocietes (
   id                   INT IDENTITY(0,1)    NOT NULL,
   applicationId        INT                  NOT NULL,
   societeId            INT                  NOT NULL,
   suppression          BIT                  NULL,
   CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/

DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0

MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
    AS source (applicationId, societeId, suppression)
    --here goes the ON join condition
    ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
    UPDATE
    --place your list of SET here
    SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
    --insert a new line with the SOURCE table one row
    INSERT (applicationId, societeId, suppression)
    VALUES (source.applicationId, source.societeId, source.suppression);
GO

将表名和字段名更改为所需的内容。 注意 使用 ON 条件。 然后在 DECLARE 行中设置变量的适当值（和类型）。

干杯。

- Denver

Answer 9

这取决于使用模式。我们必须从大局出发看待使用情况，而不被细节所迷惑。例如，如果使用模式是99％的记录创建后更新，则“UPSERT”是最佳解决方案。

在第一次插入（命中）之后，所有语句都将进行单个更新，没有任何条件限制。插入时的“where”条件是必要的，否则会插入重复数据，并且您不希望处理锁定。

UPDATE <tableName> SET <field>=@field WHERE key=@key;

IF @@ROWCOUNT = 0
BEGIN
   INSERT INTO <tableName> (field)
   SELECT @field
   WHERE NOT EXISTS (select * from tableName where key = @key);
END

- Saleh Najar

Answer 10

您可以使用MERGE语句，该语句用于在数据不存在时插入数据或者在数据已存在时更新数据。

MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`

- Daniel Acosta

---

@RamenChef 我不明白。WHEN MATCHED子句在哪里？ - likejudo

@likejudo 我并没有写这个，我只是修改了它。请问一下发布帖子的用户。 - RamenChef