t-sql-upserts-merge
Compare original and translation side by side
🇺🇸
Original
English🇨🇳
Translation
ChineseWrite 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, and a bare , are races. They
pass every single-session test and fail the first time two callers arrive together.
IF EXISTS ... UPDATE ELSE INSERTMERGEOpen the concurrency and MERGE checks to rerun any
result below.
upsert 看似是一个单语句问题,实际上是一个并发问题。大多数代码最先想到的两种写法 和裸 都存在竞态。它们能通过每一次单会话测试,却在两个调用方同时到达时第一次就会失败。
IF EXISTS ... UPDATE ELSE INSERTMERGE打开并发与 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.
| Pattern | Result | Cost |
|---|---|---|
| a session can die on | every write left in that session's batch is lost; the caller sees one error, not one per lost write |
| sessions racing the first insert raise | the failed statements' increments are rolled back |
| 2 rows for one key, no error at all | silent duplicates, nothing in the log to find later |
| the full total, no failures, no deadlocks | correct |
| the full total, no failures | correct |
| the full total, no failures | correct |
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 最终结果应等于递增次数的算术总和。
| 模式 | 结果 | 代价 |
|---|---|---|
在 | 会话可能因 | 该会话批次中其余每次写入都会丢失;调用方只会看到一个错误,而不是每次丢失写入都对应一个错误 |
| 多个会话竞争首次插入时引发 | 失败语句的递增操作会被回滚 |
对没有唯一键的表使用 | 一个键对应 2 行,且完全没有错误 | 静默产生重复数据,日志中没有任何线索可供后续排查 |
对唯一键使用 | 达到完整总数,无失败,无死锁 | 正确 |
先执行 | 达到完整总数,无失败 | 正确 |
先执行 | 达到完整总数,无失败 | 正确 |
第三行才是最值得担心的情况。其他模式都会引发有人能够看到的错误。只有这一行会让两个调用方都收到成功响应,同时留下一张错误的表。
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 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. 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 and both sessions take the branch.
SELECTBEGIN TRANWAITFORINSERTsql
-- 错误。两个会话可能都会进入 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);SELECTBEGIN TRANWAITFORINSERTThree 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.
is documented as a synonym for , 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 , and the victims roll back.
HOLDLOCKSERIALIZABLEMsg 12052. 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;SERIALIZABLEUPDATE3. 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
ENDPattern 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
and the produce no duplicate rows on a table with no unique key, and they lose no
increments only if every deadlock victim () 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
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 .
UPDATEMERGEMsg 1205UPDATEdesign-azure-sql-schema每种模式都能在多个会话并发操作同一个热点键时,完整计入每次递增。
1. 当语句确实需要多个操作时,使用 。
MERGEsql
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;提示应加在目标表上,并位于别名之前。有两点使它能够正常工作,但只有第一点与正确性直接相关。文档将 定义为 的同义词,它负责在匹配和插入期间持有范围锁。联接键上的唯一索引才是缩小范围锁范围的关键。 如果没有唯一索引,锁覆盖的范围会大得多:针对无唯一键表的并发会话会发生死锁并报 ,死锁牺牲者的事务将被回滚。
HOLDLOCKSERIALIZABLEMsg 12052. 先执行 ,仅当没有更新任何行时才插入。 环节更少,当目标行通常已经存在时,这是更好的选择。
UPDATEsql
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;UPDATESERIALIZABLE3. 捕获重复键错误,并将其视为执行更新的信号。 最适合目标行通常不存在的情况,因为常见路径不会额外加锁。
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 在没有唯一索引时仍能保持正确,但前提是调用方会重试死锁牺牲者,而且处理速度会很慢。在少量热点键上承受许多会话并发时,带提示的 和 在没有唯一键的表上不会产生重复行,而且只有当每个死锁牺牲者()都被重试时,才不会丢失递增。死锁牺牲者的事务会被回滚,除非重新执行,否则其递增操作就会消失。在堆表上,带提示的 几乎每次尝试都会成为死锁牺牲者,吞吐量会崩溃;而使用唯一索引时,相同负载可以提交高出几个数量级的递增操作,并且不会发生死锁。无论如何都应添加唯一键:它会缩小范围锁的范围,还能让任何忘记添加提示的代码路径直接报错,而不是悄无声息地产生第二行。该键的字段规格设计属于 。
UPDATEMERGEMsg 1205UPDATEdesign-azure-sql-schemaWhen to refuse MERGE outright
何时应彻底拒绝使用 MERGE
Say no and write separate statements. None of these is folklore.
- A partial batch with . That clause belongs to a full reload, never to an upsert of whichever rows this request happens to carry. A target holding
WHEN NOT MATCHED BY SOURCE THEN DELETE,A,Band a source naming onlyCends with one row and no error.A - A source that can contain the same key twice. , "The MERGE statement attempted to UPDATE or DELETE the same row more than once". Deduplicate the source first, with
Msg 8672or a window function, rather than loosening theGROUP BYclause.ON - Any filter in the clause. Put only the columns being matched there. Adding
ONtoAND t.qty > 100makes an existing row look unmatched, so theONbranch fires and hitsNOT MATCHEDon the key that already exists. Row filters go in theMsg 2627clause or in a view or common table expression over the source.WHEN - A columnstore target. Avoid any form of columnstore index as a target, and instead stage, then delete and insert in batches.
MERGE - High concurrency at scale. At scale may introduce complicated concurrency issues, and separate statements can perform better with less blocking.
MERGE - Literal values in a frequently executed statement. Simple parameterization is not applied to
, so literals compile a new plan every execution. Pass parameters through
MERGE. Building the statement by concatenation instead issys.sp_executesql.prevent-sql-injection
MERGEMsg 10713直接拒绝使用 MERGE,并改写为多条独立语句。以下情况都有明确依据,并非无根据的禁忌。
- 带有 的部分批次处理。 此子句适用于全量重载,绝不能用于仅处理当前请求所携带行的 upsert。若目标中包含
WHEN NOT MATCHED BY SOURCE THEN DELETE、A、B,而源中只包含C,最终会只剩一行且不会报错。A - 源中可能包含重复键。 会触发 :“MERGE 语句尝试多次更新或删除同一行”。应先用
Msg 8672或窗口函数对源去重,而不是放宽GROUP BY子句。ON - 子句中包含任何筛选条件。 仅把待匹配列放在那里。添加
ON会让已有行看起来未匹配,从而触发AND t.qty > 100分支,并在已存在的键上引发NOT MATCHED。行级筛选条件应放在Msg 2627子句中,或者放在基于源数据的视图或公用表表达式中。WHEN - 目标是 columnstore 索引的表。 避免将任何形式的 columnstore 索引用作 目标;应改为暂存数据,然后分批删除和插入。
MERGE - 大规模高并发。 在大规模场景下,可能引入复杂的并发问题,而独立执行多条语句可能性能更好、阻塞更少。
MERGE - 频繁执行的语句中使用字面量。 不适用简单参数化,因此每次执行字面量都会编译新计划。应通过
MERGE传递参数。如果改为通过字符串拼接来构建语句,则属于sys.sp_executesql范畴。prevent-sql-injection
MERGEMsg 10713The indexed view question, stated honestly
关于索引视图的问题,实话实说
A long standing warning says a can leave an indexed view inconsistent with its base
tables, and a routine consistency check will not report it. Half of that is current.
MERGEThe 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 . 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.
EXTENDED_LOGICAL_CHECKSThe 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 target has
to be verified deliberately. The repair, if the two ever disagree, is to drop and recreate the
view's clustered index.
MERGE一个长期存在的警告称, 可能导致索引视图与其基表不一致,而例行一致性检查不会报告这种问题。其中一半目前仍然成立。
MERGE仍然成立的部分。 对索引视图执行完整性检查时,只会进行物理一致性检查。将物化行与基表进行比较的逻辑检查,仅在 下运行。因此,无论是什么原因导致视图与基表不一致,例行检查都看不到它。这一点目前对 Azure SQL Database 仍然成立,也正是该缺陷类别曾经十分严重的原因。
EXTENDED_LOGICAL_CHECKS目前已不成立的部分。 在 Azure SQL Database 上,已公布的最小复现以及另外四种形态,包括基于两个表联接的聚合视图,在兼容级别 120 至 170 下均能保持一致。因此,不要告诉开发者他们当前的索引视图正在损坏。应当这样说明:该缺陷不会引发错误,也不会导致任何检查失败,因此必须主动验证位于 目标之上的索引视图。如果二者真的不一致,修复方法是删除并重建该视图的聚集索引。
MERGECheck it worked
验证是否生效
One session proves nothing, so run the upsert from two shells at once and compare the final value
against the arithmetic total. exits non-zero on an error, and prints
every message, including the severity 10 ones that otherwise arrive with no number at all.
sqlcmd -b-m-1-m-1sqlcmdmssql-tools18localhost,1433brew install sqlcmdwinget install sqlcmdMsg-mMsg 2627/opt/mssql-tools18/bin/sqlcmddocker execbash
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: is 100 and is 1. Below 100 means writes were lost, more than one row
means the key carries no unique index. Delete and rerun: the count falls
short, or one shell exits non-zero on . Against Azure SQL Database only and the
authentication flags change.
nrows_for_keyWITH (SERIALIZABLE)Msg 2627-SCheck 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. must report 0 allocation and 0 consistency
errors.
DBCC单会话测试证明不了任何问题,因此应同时在两个 shell 中运行 upsert,并将最终值与递增次数的算术总和进行比较。 遇到错误时会返回非零退出码,而 会打印每条消息,包括严重级别为 10、原本不带编号的消息。
sqlcmd -b-m-1-m-1sqlcmdmssql-tools18localhost,1433brew install sqlcmdwinget install sqlcmd-mMsgMsg 2627/opt/mssql-tools18/bin/sqlcmddocker execbash
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';"预期结果: 为 100, 为 1。低于 100 表示写入发生丢失;大于 1 表示该键没有唯一索引。删除 并重新运行:计数会低于预期,或者某个 shell 会因 以非零状态退出。针对 Azure SQL Database 时,只需更改 和身份验证标志。
nrows_for_keyWITH (SERIALIZABLE)Msg 2627-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;两个结果集必须逐行匹配。 必须报告 0 个分配错误和 0 个一致性错误。
DBCCDo not
禁止事项
- Do not present as an upsert. It is a race, and wrapping it in a transaction does not fix it.
IF EXISTS ... UPDATE ELSE INSERT - Do not add and stop there. Without a unique index on the join key the range lock is wide, and concurrent sessions deadlock with
HOLDLOCK.Msg 1205 - Do not translate into
ON CONFLICT DO UPDATEreflexively. Two of the three safe patterns are plain statements and both are easier to review.MERGE - Do not use on a
READPASTtarget with an insert branch. It can produce inserts that violate unique constraints.MERGE - Do not claim an indexed view is being corrupted by today without reproducing it, and do not treat a clean integrity check as evidence that one agrees with its base tables.
MERGE - 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.
- 禁止将 当作 upsert。它存在竞态,套上事务也无法修复。
IF EXISTS ... UPDATE ELSE INSERT - 不要只添加 就结束。如果联接键上没有唯一索引,范围锁会很宽,并发会话会发生死锁并报
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 shape, and the five indexed view shapes.
MERGE
- 当本文中的结果受到质疑,或需要 2627、8672、10713 或 1205 的完整消息文本时,请打开并发与 MERGE 检查。其中涵盖每种并发模式、每种会明确报错的 形态,以及五种索引视图形态。
MERGE