I have a couple of MERGE statements that I execute inside a transaction from within ADO.NET code.
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.
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 ... ;
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?
解决方案
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.