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.

MERGE test.Target AS t
USING(SELECT ItemID, Score, TargetText
FROM @SrcTable
)AS S
ON t.TargetUserID = @TargetUserID
AND t.TargetUserFormPartID =@TargetUserFormPartID
AND t.ItemID = s.ItemID
WHEN NOT MATCHED THEN
INSERT(TargetUserID, TargetUserFormPartID, TenantID, ItemID, TargetText, Score, TimeSpent)
VALUES(@TargetUserID,@TargetUserFormPartID,@TenantID, s.ItemID, s.TargetText, s.Score,@TimeSpent)
WHEN MATCHED THEN UPDATE
SET t.TargetText = s.TargetText, t.Score = s.Score, t.TimeSpent =@TimeSpent

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:

SELECT sqlserver_start_time AS ServerRestartTime
FROM sys.dm_os_sys_info;

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total';

SELECT TOP 200
XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS DeadlockGraph ,
XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') AS TS1
FROM ( SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';
GO

SELECT @pSQLHandle = 0x03003600034dcc6dd08ba50046a200000100000000000000 ,
@pStartOffSet = 15480 ,
@pEndOffSet = 29192;

SELECT SUBSTRING(st.text, ( @pStartOffSet / 2 ) + 1,
( ( CASE @pEndOffSet
WHEN -1 THEN DATALENGTH(st.text)
ELSE @pEndOffSet
END - @pStartOffSet ) / 2 ) + 1) AS statement_text
FROM sys.dm_exec_sql_text(@pSQLHandle) AS st;
GO