具有外键并解决死锁的表上的SQL合并语句 [英] SQL merge statements on tables with foreign keys and resolving deadlocks

问题描述

我有两个MERGE语句，它们是在ADO.NET代码中的事务内执行的.

I have a couple of MERGE statements that I execute inside a transaction from within ADO.NET code.

插入表时，将自动分配第一个表的ID. 第二个表确实具有外键约束，这就是为什么我在insert语句中具有此select的原因.

The first table's Id will be assigned automatic when inserting into the table. The second table does have a Foreign-key constraint, that's why I have this select in my insert statement.

匹配实际上是基于某些自然键的，因为代理键不会在应用程序外部公开.

The matching is actually based on some natural key because the surrogate keys are not exposed outside the application.

MERGE语句如下.

The MERGE statements look like these.

merge MyTable with (rowlock, updlock) as t
using #someTempTable as s
on (t.[VarcharColumn] = s.[VarcharColumn])
when not matched by target
   then insert (...)
   values (...)
when matched
   then update set ... ;

merge SecondTable with (rowlock, updlock) as t
using #otherTempTable as s
on (t.[] = s.[])
when not matched by target
   then insert ([OtherColumn],[MyTable_Id])
   values (s.[OtherColumn], 
          (select Id from MyTable where MyTable.[VarcharColumn] = s.[VarcharColumn]))
when matched
   then update set ... ;

在多个并行事务中运行这些语句时，表上发生死锁.通过添加行锁提示，我可以减少插入时的死锁，但是update语句总是会引起问题.

When running these statements in multiple parallel transactions, deadlocks are occurring on the tables. I was able to reduce some deadlocks on insert by adding the rowlock hints, but the update statements will always cause problems.

我不是数据库优化方面的专家，很难找出会发生什么情况以及如何进行改进. 是否有人在这些问题上有专业意见?

I'm not an expert in Database optimizations and have a hard time finding out what happens and how to improve it. Does anyone has some professional input on these issues?

推荐答案

将锁定提示修改为WITH (HOLDLOCK).这将导致MERGE语句在整个语句中保持对受影响的行的锁定，并应消除死锁.

Modify your lock hint to WITH (HOLDLOCK). This will cause the MERGE statement to hold the lock on the affected rows through the entire statement and should eliminate the deadlocks.

这篇关于具有外键并解决死锁的表上的SQL合并语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！