sql - SQL Server 2016 上插入或更新的安全解决方案

Question

假设表结构为 MyTable(MyTableId NVARCHAR(MAX) PRIMARY KEY, NumberOfInserts INTEGER)。

我经常需要更新，即增加现有记录的计数器，或者插入一条新记录(如果该记录不存在且 NumberOfInserts 的值为 0)。

本质上:

IF (MyTableId exists)
    run UPDATE command
ELSE
    run INSERT command

我担心由于竞争条件等原因丢失数据。

最安全的编写方式是什么？

如果可能的话，我需要它 100% 准确，并愿意在必要时牺牲速度。

Answer 1

MERGE语句可以执行UPDATE和INSERT(如果需要的话还可以执行DELETE)。

即使它是单个原子语句，使用 HOLDLOCK 查询提示来防止竞争条件也很重要。有一篇博文“UPSERT” Race Condition With MERGE作者:Dan Guzman，他详细解释了它的工作原理，并提供了一个测试脚本来验证它。

查询本身很简单:

DECLARE @NewKey NVARCHAR(MAX) = ...;

MERGE INTO dbo.MyTable WITH (HOLDLOCK) AS Dst
USING 
(
    SELECT @NewKey AS NewKey
) AS Src
ON Src.NewKey = Dst.[Key]
WHEN MATCHED THEN
UPDATE
SET NumberOfInserts = NumberOfInserts + 1
WHEN NOT MATCHED THEN
INSERT
(
    [Key]
    ,NumberOfInserts
)
VALUES
(
    @NewKey
    ,0
);

<小时/>

当然，您还可以使用显式两步方法，单独检查行是否存在以及单独的 UPDATE 和 INSERT 语句。只需确保将它们全部包装在具有适当表锁定提示的事务中即可。

参见Conditional INSERT/UPDATE Race Condition丹·古兹曼(Dan Guzman)了解详细信息。