Contents
What does a deadlock on a MERGE statement mean?
As your description, you come across deadlock on merge statement. A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource, which the other tasks are trying to lock. To troubleshoot the issue, I recommend you to add a TABLOCK.
Is there a holdlock hint in SQL Server MERGE statement?
Add the HOLDLOCK hint to any MERGE statements not already protected by adequate isolation levels in order to shield them from race conditions. Review the bugs above that have been fixed and verify that you are on a build of SQL Server that contains the fix.
What does merge look like in SQL Server?
MERGE *looks* like it will take care of concurrency for you, because implicitly it seems to a single, atomic statement. However, under the covers, SQL Server really does perform the different operations independently.
How to avoid deadlock with multiple rows in SQL?
To avoid deadlocks with multiple rows, the rows need to be processed (and tables accessed) in the same order every time. The table variable in the execution plan shown in the question is a heap, so the rows have no intrinsic order (they are quite likely to be read in insertion order, though this is not guaranteed):
For a similar sort of consideration, see Shared Lock issued on IsolationLevel.ReadUncommitted. So, the current situation is unfortunate for your deadlock scenario, and it may be avoidable in principle, but that’s not necessarily the same as being a ‘bug’.
Are there deadlocks with the clustered table variable?
In tests with 5000 iterations of the MERGE statement on 128 threads, no deadlocks occurred with the clustered table variable. I should stress that this is only on the basis of observation; the clustered table variable could also ( technically) produce its rows in a variety of orders, but the chances of a consistent order are very greatly enhanced.
How to resolve SQL Server deadlocks involving concurrent inserts?
I have a transaction that inserts a row into 2 tables that is getting hit from multiple threads concurrently and causing occasional deadlocks. We have reproduced in both SQL Server 2014 and 2016. TableA has an IDENTITY (auto-incrementing int) column as its PK. TableB has a composite PK made up of TableA.ID and another table’s ID.
How to deadlock a database with multiple rows?
With multiple rows, there is a new possibility for deadlock. Suppose two concurrent processes (A & B) run with table variables containing (1, 2) and (2, 1) for the same company. Process A reads the destination, finds no row, and inserts the value ‘1’. It holds an exclusive row lock on value ‘1’.