I'm trying to avoid deadlock in with my MERGE
query, which may be called by different threads and possibly overlap in execution with the same parameters. My experience with this query is very similar to the scenario described in this question, and I've listed the query below for reference.
CREATE PROCEDURE MergeIt
@dataToMerge MyTableType READONLY
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
BEGIN TRANSACTION
MERGE INTO TargetTable WITH(HOLDLOCK) AS [target]
USING @dataToMerge AS [source]
ON [source].KeyPart_1 = [target].KeyPart_1 AND
[source].KeyPart_2 = [target].KeyPart_2
WHEN NOT MATCHED THEN
INSERT(Data, KeyPart_1, KeyPart_2)
VALUES([source].Data, [source].KeyPart_1, [source].KeyPart_2)
WHEN MATCHED THEN
UPDATE SET [target].Data = [source].Data,
[target].KeyPart_1 = [source].KeyPart_1,
[target].KeyPart_2 = [source].KeyPart_2;
COMMIT TRANSACTION
RETURN 0
TargetTable
has an identity column which serves as the primary key, and furthermore has a uniqueness constraint on the [KeyPart_1, KeyPart_2]
column-tuple. MyTableType
has a schema similar to TargetTable
and also defines a primary key on [KeyPart_1, KeyPart_2]
column-tuple.
I am trying to ensure that only one process be allowed to run this MERGE
query at any given time, and I thought that the SERIALIZABLE
isolation level enforces this. However, this doesn't appear to be the case. I've captured these XML log events that show which resources and locks are in play during the deadlock. One query has an exclusive lock (X), and the other an update lock (U). As I'm typing this, I see that it is not necessary to update the [KeyPart_1, KeyPart_2]
column-tuple in the UPDATE
clause, which might very well be contributing to the deadlock since that tuple will trigger an index update.
Are there any other suggestions as to how I can resolve this? I suppose I can blindly try to use TABLOCKX
as the table hint, but I'd like to understand how the SERIALIZABLE
isolation level failed here.
Thanks!
KeyPart_1
andKeyPart_2
?