t-sql-upserts-merge

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Write an upsert that survives concurrency, and refuse the dangerous MERGE

编写在并发下保持正确的 upsert,并拒绝使用危险的 MERGE

An upsert looks like a one-statement problem and is a concurrency problem. Both of the shapes most code reaches for first,
IF EXISTS ... UPDATE ELSE INSERT
and a bare
MERGE
, are races. They pass every single-session test and fail the first time two callers arrive together.
Open the concurrency and MERGE checks to rerun any result below.
upsert 看似是一个单语句问题,实际上是一个并发问题。大多数代码最先想到的两种写法
IF EXISTS ... UPDATE ELSE INSERT
和裸
MERGE
都存在竞态。它们能通过每一次单会话测试,却在两个调用方同时到达时第一次就会失败。
打开并发与 MERGE 检查以复现下文中的任何结果。

Scope

适用范围

  • Key and index design belongs to
    design-azure-sql-schema
    .
  • General T-SQL dialect belongs to
    t-sql-correctness
    .
  • 键和索引设计属于
    design-azure-sql-schema
    。
  • 通用 T-SQL 语法属于
    t-sql-correctness
    。

What the race actually costs

竞态究竟会造成什么代价

Run each pattern from several sessions at once, many increments each, on one hot key. A correct upsert ends at the arithmetic total.
PatternResultCost
UPDATE
then
INSERT
when
@@ROWCOUNT = 0
, no hint
a session can die on
Msg 2627
, and the final value falls short
every write left in that session's batch is lost; the caller sees one error, not one per lost write
MERGE
with no lock hint
sessions racing the first insert raise
Msg 2627
, and the final value falls short
the failed statements' increments are rolled back
IF EXISTS
on a table with no unique key
2 rows for one key, no error at allsilent duplicates, nothing in the log to find later
MERGE WITH (HOLDLOCK)
on a unique key
the full total, no failures, no deadlockscorrect
UPDATE WITH (SERIALIZABLE)
then
INSERT
the full total, no failurescorrect
UPDATE
, then
INSERT
inside
TRY
catching 2627
the full total, no failurescorrect
The third row is the one to fear. The others raise an error someone can see. That one returns success to both callers and leaves the table wrong.
同时从多个会话运行每种模式,在同一个热点键上分别执行多次递增。正确的 upsert 最终结果应等于递增次数的算术总和。
模式结果代价
在
@@ROWCOUNT = 0
时先执行
UPDATE
再执行
INSERT
,且无提示
会话可能因
Msg 2627
而终止,并且最终值低于预期
该会话批次中其余每次写入都会丢失;调用方只会看到一个错误,而不是每次丢失写入都对应一个错误
MERGE
无锁提示
多个会话竞争首次插入时引发
Msg 2627
,并且最终值低于预期
失败语句的递增操作会被回滚
对没有唯一键的表使用
IF EXISTS
一个键对应 2 行,且完全没有错误静默产生重复数据,日志中没有任何线索可供后续排查
对唯一键使用
MERGE WITH (HOLDLOCK)
达到完整总数,无失败,无死锁正确
先执行
UPDATE WITH (SERIALIZABLE)
,再执行
INSERT
达到完整总数,无失败正确
先执行
UPDATE
,再在
TRY
中执行
INSERT
并捕获 2627
达到完整总数,无失败正确
第三行才是最值得担心的情况。其他模式都会引发有人能够看到的错误。只有这一行会让两个调用方都收到成功响应,同时留下一张错误的表。

Why the naive shape is a race

为什么朴素写法存在竞态

sql
-- WRONG. Two sessions can both take the ELSE branch.
IF EXISTS (SELECT 1 FROM dbo.counters WHERE k = @k)
    UPDATE dbo.counters SET n = n + 1 WHERE k = @k;
ELSE
    INSERT INTO dbo.counters (k, n) VALUES (@k, 1);
The
SELECT
takes a shared lock and releases it immediately, under every isolation level below serializable, so there is a window between the test and the insert.
BEGIN TRAN
does not close it: a transaction makes the pair atomic on failure without making the read block anyone. Hold the window open with a two second
WAITFOR
and both sessions take the
INSERT
branch.
sql
-- 错误。两个会话可能都会进入 ELSE 分支。
IF EXISTS (SELECT 1 FROM dbo.counters WHERE k = @k)
    UPDATE dbo.counters SET n = n + 1 WHERE k = @k;
ELSE
    INSERT INTO dbo.counters (k, n) VALUES (@k, 1);
SELECT
会获取共享锁并立即释放;在所有低于 serializable 的隔离级别下都是如此,因此测试与插入之间存在一个窗口。
BEGIN TRAN
无法消除该窗口:事务只会让这对语句在失败时保持原子性,并不会让读取阻塞其他会话。用两秒的
WAITFOR
保持该窗口打开,两个会话就会都进入
INSERT
分支。

Three upserts that hold

三种经得起并发测试的 upsert

Each lands every increment under concurrent sessions on one hot key.
1. MERGE, when the statement genuinely needs more than one action.
sql
BEGIN TRAN;
MERGE dbo.counters WITH (HOLDLOCK) AS t
USING (SELECT @k AS k) AS s
    ON t.k = s.k
WHEN MATCHED THEN
    UPDATE SET t.n = t.n + 1
WHEN NOT MATCHED THEN
    INSERT (k, n) VALUES (s.k, 1);
COMMIT;
The hint goes on the target, before the alias. Two things make it work, and only the first is about correctness.
HOLDLOCK
is documented as a synonym for
SERIALIZABLE
, and it is what holds a range lock across the match and the insert. The unique index on the join key is what makes that range lock narrow. Without it the lock covers far more of the table: concurrent sessions against a table with no unique key deadlock with
Msg 1205
, and the victims roll back.
2. UPDATE first, insert only if nothing was updated. Fewer moving parts, and the better choice when the row usually already exists.
sql
BEGIN TRAN;
UPDATE dbo.counters WITH (SERIALIZABLE) SET n = n + 1 WHERE k = @k;
IF @@ROWCOUNT = 0
    INSERT INTO dbo.counters (k, n) VALUES (@k, 1);
COMMIT;
SERIALIZABLE
on the
UPDATE
is not decoration. Without it this is the first row of the table above, the one that loses writes.
3. Catch the duplicate key and treat it as the update signal. Best when the row usually does not exist, because the common path takes no extra lock.
sql
UPDATE dbo.counters SET n = n + 1 WHERE k = @k;
IF @@ROWCOUNT = 0
BEGIN
    BEGIN TRY
        INSERT INTO dbo.counters (k, n) VALUES (@k, 1);
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() IN (2601, 2627)
            UPDATE dbo.counters SET n = n + 1 WHERE k = @k;
        ELSE
            THROW;
    END CATCH
END
Pattern 3 depends on a unique constraint or unique index on the key: without one nothing raises 2627 and the insert simply succeeds twice. Patterns 1 and 2 stay correct without one only when the caller retries deadlock victims, and slowly. Under many sessions on a few hot keys, the hinted
UPDATE
and the
MERGE
produce no duplicate rows on a table with no unique key, and they lose no increments only if every deadlock victim (
Msg 1205
) is retried
: a victim's transaction rolls back and its increment is gone unless it is rerun. On a heap nearly every attempt by the hinted
UPDATE
ends as a deadlock victim and throughput collapses, while with a unique index the same load commits orders of magnitude more increments with no deadlocks. Add the unique key anyway: it narrows the range lock, and it turns any code path that forgets the hint into an error instead of a silent second row. Sizing that key is
design-azure-sql-schema
.
每种模式都能在多个会话并发操作同一个热点键时,完整计入每次递增。
1. 当语句确实需要多个操作时,使用
MERGE
。
sql
BEGIN TRAN;
MERGE dbo.counters WITH (HOLDLOCK) AS t
USING (SELECT @k AS k) AS s
    ON t.k = s.k
WHEN MATCHED THEN
    UPDATE SET t.n = t.n + 1
WHEN NOT MATCHED THEN
    INSERT (k, n) VALUES (s.k, 1);
COMMIT;
提示应加在目标表上,并位于别名之前。有两点使它能够正常工作,但只有第一点与正确性直接相关。文档将
HOLDLOCK
定义为
SERIALIZABLE
的同义词,它负责在匹配和插入期间持有范围锁。联接键上的唯一索引才是缩小范围锁范围的关键。 如果没有唯一索引,锁覆盖的范围会大得多:针对无唯一键表的并发会话会发生死锁并报
Msg 1205
,死锁牺牲者的事务将被回滚。
2. 先执行
UPDATE
,仅当没有更新任何行时才插入。
环节更少,当目标行通常已经存在时,这是更好的选择。
sql
BEGIN TRAN;
UPDATE dbo.counters WITH (SERIALIZABLE) SET n = n + 1 WHERE k = @k;
IF @@ROWCOUNT = 0
    INSERT INTO dbo.counters (k, n) VALUES (@k, 1);
COMMIT;
UPDATE
上的
SERIALIZABLE
并非只是装饰。缺少它时,写法就会退化为上表中的第一种模式,也就是会丢失写入的模式。
3. 捕获重复键错误,并将其视为执行更新的信号。 最适合目标行通常不存在的情况,因为常见路径不会额外加锁。
sql
UPDATE dbo.counters SET n = n + 1 WHERE k = @k;
IF @@ROWCOUNT = 0
BEGIN
    BEGIN TRY
        INSERT INTO dbo.counters (k, n) VALUES (@k, 1);
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() IN (2601, 2627)
            UPDATE dbo.counters SET n = n + 1 WHERE k = @k;
        ELSE
            THROW;
    END CATCH
END
模式 3 依赖于键上的唯一约束或唯一索引:如果没有它,就不会有任何操作抛出 2627,插入会直接成功两次。模式 1 和模式 2 在没有唯一索引时仍能保持正确,但前提是调用方会重试死锁牺牲者,而且处理速度会很慢。在少量热点键上承受许多会话并发时,带提示的
UPDATE
和
MERGE
在没有唯一键的表上不会产生重复行,而且只有当每个死锁牺牲者(
Msg 1205
)都被重试时,才不会丢失递增。死锁牺牲者的事务会被回滚,除非重新执行,否则其递增操作就会消失。在堆表上,带提示的
UPDATE
几乎每次尝试都会成为死锁牺牲者,吞吐量会崩溃;而使用唯一索引时,相同负载可以提交高出几个数量级的递增操作,并且不会发生死锁。无论如何都应添加唯一键:它会缩小范围锁的范围,还能让任何忘记添加提示的代码路径直接报错,而不是悄无声息地产生第二行。该键的字段规格设计属于
design-azure-sql-schema
。

When to refuse MERGE outright

何时应彻底拒绝使用 MERGE

Say no and write separate statements. None of these is folklore.
  • A partial batch with
    WHEN NOT MATCHED BY SOURCE THEN DELETE
    .
    That clause belongs to a full reload, never to an upsert of whichever rows this request happens to carry. A target holding
    A
    ,
    B
    ,
    C
    and a source naming only
    A
    ends with one row and no error.
  • A source that can contain the same key twice.
    Msg 8672
    , "The MERGE statement attempted to UPDATE or DELETE the same row more than once". Deduplicate the source first, with
    GROUP BY
    or a window function, rather than loosening the
    ON
    clause.
  • Any filter in the
    ON
    clause.
    Put only the columns being matched there. Adding
    AND t.qty > 100
    to
    ON
    makes an existing row look unmatched, so the
    NOT MATCHED
    branch fires and hits
    Msg 2627
    on the key that already exists. Row filters go in the
    WHEN
    clause or in a view or common table expression over the source.
  • A columnstore target. Avoid any form of columnstore index as a
    MERGE
    target, and instead stage, then delete and insert in batches.
  • High concurrency at scale. At scale
    MERGE
    may introduce complicated concurrency issues, and separate statements can perform better with less blocking.
  • Literal values in a frequently executed statement. Simple parameterization is not applied to
    MERGE
    , so literals compile a new plan every execution. Pass parameters through
    sys.sp_executesql
    . Building the statement by concatenation instead is
    prevent-sql-injection
    .
MERGE
also requires a semicolon terminator, and omitting it is
Msg 10713
by number rather than a generic syntax error.
直接拒绝使用 MERGE,并改写为多条独立语句。以下情况都有明确依据,并非无根据的禁忌。
  • 带有
    WHEN NOT MATCHED BY SOURCE THEN DELETE
    的部分批次处理。
    此子句适用于全量重载,绝不能用于仅处理当前请求所携带行的 upsert。若目标中包含
    A
    、
    B
    、
    C
    ,而源中只包含
    A
    ,最终会只剩一行且不会报错。
  • 源中可能包含重复键。 会触发
    Msg 8672
    :“MERGE 语句尝试多次更新或删除同一行”。应先用
    GROUP BY
    或窗口函数对源去重,而不是放宽
    ON
    子句。
  • ON
    子句中包含任何筛选条件。
    仅把待匹配列放在那里。添加
    AND t.qty > 100
    会让已有行看起来未匹配,从而触发
    NOT MATCHED
    分支,并在已存在的键上引发
    Msg 2627
    。行级筛选条件应放在
    WHEN
    子句中,或者放在基于源数据的视图或公用表表达式中。
  • 目标是 columnstore 索引的表。 避免将任何形式的 columnstore 索引用作
    MERGE
    目标;应改为暂存数据,然后分批删除和插入。
  • 大规模高并发。 在大规模场景下,
    MERGE
    可能引入复杂的并发问题,而独立执行多条语句可能性能更好、阻塞更少。
  • 频繁执行的语句中使用字面量。
    MERGE
    不适用简单参数化,因此每次执行字面量都会编译新计划。应通过
    sys.sp_executesql
    传递参数。如果改为通过字符串拼接来构建语句,则属于
    prevent-sql-injection
    范畴。
MERGE
还要求以分号结尾,遗漏分号会按编号报
Msg 10713
,而不是笼统的语法错误。

The indexed view question, stated honestly

关于索引视图的问题,实话实说

A long standing warning says a
MERGE
can leave an indexed view inconsistent with its base tables, and a routine consistency check will not report it. Half of that is current.
The half that holds. An integrity check performs only physical consistency checks on an indexed view. The logical checks, which compare the materialized rows against the base tables, run only under
EXTENDED_LOGICAL_CHECKS
. So a view that disagrees with its base tables is invisible to a routine check, whatever put it there. That holds for Azure SQL Database today, and it is why this defect class was ever severe.
The half that is not current. On Azure SQL Database the published minimal repro and four further shapes, including an aggregated view over a two table join, stay consistent at compatibility levels 120 through 170. So do not tell a developer their indexed views are being corrupted today. Say instead that this defect raises no error and fails no check, so an indexed view over a
MERGE
target has to be verified deliberately. The repair, if the two ever disagree, is to drop and recreate the view's clustered index.
一个长期存在的警告称,
MERGE
可能导致索引视图与其基表不一致,而例行一致性检查不会报告这种问题。其中一半目前仍然成立。
仍然成立的部分。 对索引视图执行完整性检查时,只会进行物理一致性检查。将物化行与基表进行比较的逻辑检查,仅在
EXTENDED_LOGICAL_CHECKS
下运行。因此,无论是什么原因导致视图与基表不一致,例行检查都看不到它。这一点目前对 Azure SQL Database 仍然成立,也正是该缺陷类别曾经十分严重的原因。
目前已不成立的部分。 在 Azure SQL Database 上,已公布的最小复现以及另外四种形态,包括基于两个表联接的聚合视图,在兼容级别 120 至 170 下均能保持一致。因此,不要告诉开发者他们当前的索引视图正在损坏。应当这样说明:该缺陷不会引发错误,也不会导致任何检查失败,因此必须主动验证位于
MERGE
目标之上的索引视图。如果二者真的不一致,修复方法是删除并重建该视图的聚集索引。

Check it worked

验证是否生效

One session proves nothing, so run the upsert from two shells at once and compare the final value against the arithmetic total.
sqlcmd -b
exits non-zero on an error, and
-m-1
prints every message, including the severity 10 ones that otherwise arrive with no number at all.
-m-1
is an ODBC
sqlcmd
instruction
, meaning the 18.x build from
mssql-tools18
or the Microsoft command line utilities, and
localhost,1433
is exactly where you are most likely to be holding the other one. go-sqlcmd 1.x, the build
brew install sqlcmd
and
winget install sqlcmd
install, prints no
Msg
header on a severity 10 message at any
-m
value.
Msg 2627
below is severity 14 and arrives with its number on either build, so this check still works; what go-sqlcmd will not show you is a severity 10 message from the statements around it. The ODBC build is inside the container image at
/opt/mssql-tools18/bin/sqlcmd
, one
docker exec
away.
bash
UPSERT="BEGIN TRAN;
UPDATE dbo.counters WITH (SERIALIZABLE) SET n = n + 1 WHERE k = N'hot';
IF @@ROWCOUNT = 0 INSERT INTO dbo.counters (k, n) VALUES (N'hot', 1);
COMMIT;"
S=(-S localhost,1433 -d appdb -C -b -m-1)

sqlcmd "${S[@]}" -Q "CREATE TABLE dbo.counters (k NVARCHAR(50) NOT NULL PRIMARY KEY, n INT NOT NULL);"

for i in 1 2; do
  sqlcmd "${S[@]}" -Q "DECLARE @i INT = 0; WHILE @i < 50 BEGIN $UPSERT SET @i += 1; END" &
done
wait

sqlcmd "${S[@]}" -Q "SELECT n, COUNT(*) OVER () AS rows_for_key FROM dbo.counters WHERE k = N'hot';"
Expected:
n
is 100 and
rows_for_key
is 1. Below 100 means writes were lost, more than one row means the key carries no unique index. Delete
WITH (SERIALIZABLE)
and rerun: the count falls short, or one shell exits non-zero on
Msg 2627
. Against Azure SQL Database only
-S
and the authentication flags change.
Check an indexed view over the target separately, because nothing else will tell you.
sql
-- Cheapest first: read the materialized rows, then recompute from the base table.
SELECT region, total, n FROM dbo.v_totals WITH (NOEXPAND) ORDER BY region;
SELECT region, SUM(amt), COUNT_BIG(*) FROM dbo.sales GROUP BY region ORDER BY region;

-- Or ask for the logical checks by name. Without this option they do not run.
DBCC CHECKTABLE ('dbo.v_totals') WITH EXTENDED_LOGICAL_CHECKS;
The two result sets must match row for row.
DBCC
must report 0 allocation and 0 consistency errors.
单会话测试证明不了任何问题,因此应同时在两个 shell 中运行 upsert,并将最终值与递增次数的算术总和进行比较。
sqlcmd -b
遇到错误时会返回非零退出码,而
-m-1
会打印每条消息,包括严重级别为 10、原本不带编号的消息。
-m-1
是 ODBC
sqlcmd
指令
,也就是来自
mssql-tools18
的 18.x 构建版本或 Microsoft 命令行实用工具;而
localhost,1433
很可能正是你运行另一个 shell 时使用的地址。通过
brew install sqlcmd
和
winget install sqlcmd
安装的 go-sqlcmd 1.x 构建版本,在任何
-m
值下都不会为严重级别为 10 的消息打印
Msg
标题。下面的
Msg 2627
属于严重级别 14,在任一构建版本中都会携带编号出现,因此该检查仍然有效;不过 go-sqlcmd 无法显示其周边语句产生的严重级别为 10 的消息。ODBC 构建版本位于容器镜像中的
/opt/mssql-tools18/bin/sqlcmd
,只需执行一次
docker exec
即可调用。
bash
UPSERT="BEGIN TRAN;
UPDATE dbo.counters WITH (SERIALIZABLE) SET n = n + 1 WHERE k = N'hot';
IF @@ROWCOUNT = 0 INSERT INTO dbo.counters (k, n) VALUES (N'hot', 1);
COMMIT;"
S=(-S localhost,1433 -d appdb -C -b -m-1)

sqlcmd "${S[@]}" -Q "CREATE TABLE dbo.counters (k NVARCHAR(50) NOT NULL PRIMARY KEY, n INT NOT NULL);"

for i in 1 2; do
  sqlcmd "${S[@]}" -Q "DECLARE @i INT = 0; WHILE @i < 50 BEGIN $UPSERT SET @i += 1; END" &
done
wait

sqlcmd "${S[@]}" -Q "SELECT n, COUNT(*) OVER () AS rows_for_key FROM dbo.counters WHERE k = N'hot';"
预期结果:
n
为 100,
rows_for_key
为 1。低于 100 表示写入发生丢失;大于 1 表示该键没有唯一索引。删除
WITH (SERIALIZABLE)
并重新运行:计数会低于预期,或者某个 shell 会因
Msg 2627
以非零状态退出。针对 Azure SQL Database 时,只需更改
-S
和身份验证标志。
应单独检查目标之上的索引视图,因为其他任何检查都不会告诉你它是否正确。
sql
-- 先用成本最低的方法:读取物化行,然后从基表重新计算。
SELECT region, total, n FROM dbo.v_totals WITH (NOEXPAND) ORDER BY region;
SELECT region, SUM(amt), COUNT_BIG(*) FROM dbo.sales GROUP BY region ORDER BY region;

-- 或按名称请求逻辑检查。不指定此选项,它们就不会运行。
DBCC CHECKTABLE ('dbo.v_totals') WITH EXTENDED_LOGICAL_CHECKS;
两个结果集必须逐行匹配。
DBCC
必须报告 0 个分配错误和 0 个一致性错误。

Do not

禁止事项

  • Do not present
    IF EXISTS ... UPDATE ELSE INSERT
    as an upsert. It is a race, and wrapping it in a transaction does not fix it.
  • Do not add
    HOLDLOCK
    and stop there. Without a unique index on the join key the range lock is wide, and concurrent sessions deadlock with
    Msg 1205
    .
  • Do not translate
    ON CONFLICT DO UPDATE
    into
    MERGE
    reflexively. Two of the three safe patterns are plain statements and both are easier to review.
  • Do not use
    READPAST
    on a
    MERGE
    target with an insert branch. It can produce inserts that violate unique constraints.
  • Do not claim an indexed view is being corrupted by
    MERGE
    today without reproducing it, and do not treat a clean integrity check as evidence that one agrees with its base tables.
  • Do not design the retry loop here. A deadlock victim needs a retry rather than a louder message, and which errors deserve one is separate work.
  • 禁止将
    IF EXISTS ... UPDATE ELSE INSERT
    当作 upsert。它存在竞态,套上事务也无法修复。
  • 不要只添加
    HOLDLOCK
    就结束。如果联接键上没有唯一索引,范围锁会很宽,并发会话会发生死锁并报
    Msg 1205
    。
  • 不要条件反射地把
    ON CONFLICT DO UPDATE
    翻译成
    MERGE
    。三种安全模式中有两种只使用普通语句,而且更易审查。
  • 不要在带插入分支的
    MERGE
    目标上使用
    READPAST
    。它可能产生违反唯一约束的插入。
  • 不要在没有实际复现的情况下声称
    MERGE
    当前会破坏索引视图,也不要把干净的完整性检查当作索引视图与基表一致的证据。
  • 不要在这里设计重试循环。死锁牺牲者需要重试,而不是更响亮的报错;哪些错误应重试属于另一项工作。

References

参考资料

  • Open the concurrency and MERGE checks when a result here is challenged, or when you need the full message text for 2627, 8672, 10713 or 1205. It covers each concurrency pattern, each loud
    MERGE
    shape, and the five indexed view shapes.
  • 当本文中的结果受到质疑,或需要 2627、8672、10713 或 1205 的完整消息文本时,请打开并发与 MERGE 检查。其中涵盖每种并发模式、每种会明确报错的
    MERGE
    形态,以及五种索引视图形态。