November 13, 2020 at 3:20 pm
I am occasionally getting a "Transaction (Process ID X) was deadlocked on lock" when running the following MERGE statements in a stored prodcedure and I don't really understand why:
SET XACT_ABORT ON
BEGIN TRANSACTION
MERGE [Product] WITH (SERIALIZABLE) AS TARGET
USING
(
SELECT DISTINCT Id, Name
FROM @Products
) AS SOURCE ON (TARGET.Id = SOURCE.Id)
WHEN MATCHED
THEN UPDATE SET TARGET.Name = SOURCE.Name
WHEN NOT MATCHED BY TARGET
THEN INSERT (Id, Name) VALUES (Id, Name) ;
MERGE [Item] WITH (SERIALIZABLE) AS TARGET
USING
(
SELECT DISTINCT i.Id, i.ProductId, i.Qty
FROM @Items i
JOIN [Product] p ON i.ProductId = p.Id
) AS SOURCE ON (TARGET.ProductId = SOURCE.ProductId AND TARGET.Qty = SOURCE.Qty)
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductId, Qty) VALUES (SOURCE.ProductId, SOURCE.Qty) ;
COMMIT TRANSACTION
@Products and @Items are table-values parameters.
What is causing the deadlock? Is it the JOIN to the [Product] table? Why is the objectName then [Item] in the xml_deadlock_report?:
<deadlock>
<victim-list>
<victimProcess id="process2cc707b8ca8" />
</victim-list>
<process-list>
<process id="process2cc707b8ca8" taskpriority="0" waitresource="OBJECT: 7:1973582069:2 " waittime="3266" ownerId="21187063" transactionname="user_transaction" lasttranstarted="2020-11-13T08:55:46.503" XDES="0x2cc495e0490" lockMode="X" schedulerid="15" kpid="54872" status="suspended" spid="70" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-11-13T08:55:46.503" lastbatchcompleted="2020-11-13T08:55:46.507" lastattention="1900-01-01T00:00:00.507" hostpid="68388" isolationlevel="read committed (2)" xactid="21187063" currentdb="7" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
<executionStack>
<frame procname="spname" line="25" stmtstart="2304" stmtend="3762" sqlhandle="0x03000700fb1c2229023c910072ac000001000000000000000000000000000000000000000000000000000000">
MERGE [Item] WITH (SERIALIZABLE) AS TARGET
USING
(
SELECT DISTINCT i.Id, i.ProductId, i.Qty
FROM @Items i
JOIN [Product] p ON i.ProductId = p.Id
) AS SOURCE ON (TARGET.ProductId = SOURCE.ProductId AND TARGET.Qty = SOURCE.Qty)
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductId, Qty) VALUES (SOURCE.ProductId, SOURCE.Qty) ; </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 690101499] </inputbuf>
</process>
<process id="process2cc7002f468" taskpriority="0" logused="896" waitresource="OBJECT: 7:1973582069:0 " waittime="3266" ownerId="21187037" transactionname="user_transaction" lasttranstarted="2020-11-13T08:55:46.500" XDES="0x2cc49b30490" lockMode="X" schedulerid="3" kpid="41316" status="suspended" spid="85" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-11-13T08:55:46.500" lastbatchcompleted="2020-11-13T08:55:46.500" lastattention="1900-01-01T00:00:00.500" hostpid="68388" isolationlevel="read committed (2)" xactid="21187037" currentdb="7" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
<executionStack>
<frame procname="spname" line="25" stmtstart="2304" stmtend="3762" sqlhandle="0x03000700fb1c2229023c910072ac000001000000000000000000000000000000000000000000000000000000">
MERGE [Item] WITH (SERIALIZABLE) AS TARGET
USING
(
SELECT DISTINCT i.Id, i.ProductId, i.Qty
FROM @Items i
JOIN [Product] p ON i.ProductId = p.Id
) AS SOURCE ON (TARGET.ProductId = SOURCE.ProductId AND TARGET.Qty = SOURCE.Qty)
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductId, Qty) VALUES (SOURCE.ProductId, SOURCE.Qty) ; </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 690101499] </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="2" objid="1973582069" subresource="FULL" dbid="7" objectname="DbName.Item" id="lock2cc5c2e7680" mode="IX" associatedObjectId="1973582069">
<owner-list>
<owner id="process2cc7002f468" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process2cc707b8ca8" mode="X" requestType="wait" />
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="1973582069" subresource="FULL" dbid="7" objectname="DbName.Item" id="lock2cc474d7180" mode="X" associatedObjectId="1973582069">
<owner-list>
<owner id="process2cc707b8ca8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2cc7002f468" mode="X" requestType="wait" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
November 13, 2020 at 5:56 pm
If the MERGE throws an exception is there a rollback? Could you post the whole procedure? Is the lock still an issue or it's inconsistent? Broken MERGE statements are a super pita.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 16, 2020 at 8:17 am
The whole procecure is what you see above, except for the parameters, and the issue is intermittent.
SET XACT_ABORT ON should roll back the transaction on run-time errors.
November 16, 2020 at 9:29 am
Both process request an exclusive lock on partitions on table Item. The deadlock does not occur on the table variables. By the way Do you really need to use the serializable isolation level?
November 16, 2020 at 2:18 pm
I use the WITH (SERIALIZABLE) hint to avoid deadlocks...
November 16, 2020 at 2:44 pm
The SERIALIZABLE would cause higher levels of locks and cause them to be held longer.
Use INSERT instead of MERGE in the second statement.
Personally I'd use an UPDATE then INSERT on the first table as well, since I've found MERGE to have some quirks and performance issues, although technically a MERGE should work fine.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 16, 2020 at 3:28 pm
>>Use INSERT instead of MERGE in the second statement.
How would I then handle duplicates? I don't want to insert a duplicate record if there already is one that matches the ProductId and Qty. That's why I use the MERGE statement in the first place.
November 16, 2020 at 3:35 pm
>>Use INSERT instead of MERGE in the second statement.
How would I then handle duplicates? I don't want to insert a duplicate record if there already is one that matches the ProductId and Qty. That's why I use the MERGE statement in the first place.
INSERT INTO Item ( ProductId, Qty )
SELECT SOURCE.ProductId, SOURCE.Qty
FROM
(
SELECT DISTINCT /*i.Id,*/ i.ProductId, i.Qty
FROM @Items i
JOIN [Product] p ON i.ProductId = p.Id
) AS SOURCE
WHERE
NOT EXISTS(SELECT 1 FROM Item i WHERE i.ProductId = SOURCE.ProductId AND i.Qty = SOURCE.Qty)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 16, 2020 at 4:39 pm
Why have you got DISTINCT in the select from the table variable, can't you make a table variable with distinct values already in it?
Create a primary key or an index on the table variables Id column to get better performance.
If you use UPDATE followed by INSERT instead of a MERGE you will get better performance.
So the first statement can be written as:
UPDATE Target
SET Target.Name = p.Name
FROM [Product] Target
INNER JOIN @Products p
ON p.Id = Target.Id
AND p.Name <> Target.Name
INSERT INTO [Product]
(
Id,
Name
)
SELECT Id,
Name
FROM @Products p
WHERE NOT EXISTS(SELECT *
FROM [Product] Target
WHERE Target.Id = p.Id)
and the second statement as Scott has shown.
November 17, 2020 at 8:00 am
Thanks for the suggestions. I am still curious to know why the MERGE statement deadlocks though. Any ideas?
November 17, 2020 at 9:52 am
Thanks for the suggestions. I am still curious to know why the MERGE statement deadlocks though. Any ideas?
Here is a page with a long list of known issues with the MERGE statement.
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
One of the known issues is the deadlocks.
November 17, 2020 at 3:13 pm
Thanks for the suggestions. I am still curious to know why the MERGE statement deadlocks though. Any ideas?
The way to restrict the number of rows the MERGE is applied to in the Target table is by using a Common Table Expression. Otherwise, the MERGE applies to all rows. There's no WHERE clause and no FROM clause in MERGE statements. The 2 MERGE statements here would apply to all rows. Maybe that's not what was intended? USING and CASE WHEN are not at all equivalent to FROM and WHERE.
For these reasons and many others, having been annoyed many times with MERGE statements, I've sworn them off and refactored them away. If the MERGE approach doesn't pass the outermost boolean criteria (does it work?) then why investigate too far into it? Look at Aaron Bertrand's list it's plenty enough to steer me away. The UPDATE and INSERT statements from Scott and Jonathan are great starts at replacing the MERGE statements.
Also, as ScottPlecher pointed out "The SERIALIZABLE would cause higher levels of locks and cause them to be held longer."
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply