MERGE Statement and Deadlocks

MERGE Statement and Deadlocks:

We have MERGE SQL Statement to satisfy our functional needs. Instead of writing separate Insert / Update (UPSERT) Statement we decided to use MERGE Statement because of its simplicity in syntax.

Deadlock Events noticed :

Our application calls this procedure about ~250-300 times a second. Its very high frequency stored procedure and very vital for storing critical data. We started noticing about ~1000 deadlocks in a interval of few minutes and we have never seen that many deadlocks in our production environment.

While investigating these deadlocks, we found out that concurrent threads are obtains exclusive lock on a Key which triggers deadlocks.

Proc [Database Id = 8 Object Id = 1893581784]
resource-list
keylock hobtid=72057594608746496 dbid=8 objectname=XXXXXXX indexname=ncx_TargetTable_TargetUserFormPartID_ItemID_Score id=lock6c15880 mode=U associatedObjectId=72057594608746496
owner-list
owner id=process62274c8 mode=U
waiter-list
waiter id=processf21d4c8 mode=U requestType=wait
keylock hobtid=72057594608746496 dbid=8 objectname=XXXXXXX indexname=ncx_TargetTable_TargetUserFormPartID_ItemID_Score id=lock1001d300 mode=U associatedObjectId=72057594608746496
owner-list
owner id=processf21d4c8 mode=U
waiter-list
waiter id=process62274c8 mode=U requestType=wait

We were constantly getting deadlocks on the non-clustered Index when multiple threads tries to access the same Key.

Evaluated Options:
MERGE test.Target WITH (HOLDLOCK,UPDLOCK) AS t
MERGE test.Target WITH (HOLDLOCK) AS t
MERGE test.Target WITH (TABLOCK) AS t
OPTION (ORDER GROUP)

None of them Really satisfied our concurrency needs.
We finally decided to get away with MERGE . We modified the table to include Record Created DateTime and just apply the insert.
Instead of updating the latest values, we just reconcile those while we read the data based on TimeStamp.

Solution worked for us : Just do inserts and get the latest data based on timestamp.

CASSANDRA uses similar approach for Append only operations. Their SS Tables ( similar to SQL Server Data files ) is immutable and cassandra takes care of the Old data through process called COMPACTION.
This approach seems to handle lot more throughput without any issues.

Investigation Queries:

 

Merge Deadlock

Merge Deadlock

MergeDeadLock

If you are using merge and you need high concurrency , be prepared to handle the deadlocks.
That’s the moral of this story.

 

Helpful Links on this Topic

Stack Exchange Discussion : 
http://dba.stackexchange.com/questions/23467/sql-server-2008-merge-statement-deadlocking-itself

MSDN Article on forcing Hints: 
https://msdn.microsoft.com/en-us/library/ms187373.aspx

MSSQL Tips article:
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

SQL Connect Discussion : 
https://connect.microsoft.com/SQLServer/feedback/details/723696/basic-merge-upsert-causing-deadlocks

MAXDOP Related Deadlocks: 
http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx