0

I'm newbie as a SQL Server DBA , everyday at least once I've got a deadlock issue in SQL Server 2012 server which is using Merge statement. There are no clause like NOLOCK, UPDLOCK, HOLDLOCK has been used in the merge statement. It's a multi user environment where the Biztalk reads the xml and save data into SQL Server.

Per minute, Biztalk reads 300 xml messages. Since its a production server I can't implement anything just like that without doing research, but I haven't got any idea on how to resolve this issue. Recently I had an issue with two xml messages trying to update data in a table and trying to use the same index and error-ed out. Could anyone help me how to get away with this issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aprasy
  • 1
  • 2
  • 3
  • If you can get a reproducible case, run a SQL Trace while reproducing it, being sure to trace all the 'deadlock' events. This can give you information on exactly what SQL is deadlocking, and on exactly what resource. Deadlocks almost always result from one thread going after resource A then B, while another goes after B, then A. Sometimes you can add or remove indexes to remove the deadlock, but other times you need to revise code to always get resources in the same order. – pmbAustin May 15 '17 at 20:13
  • I took the deadlock trace and understood where the issue is. However the deadlock is not occurring always but often from the merge statement which is quite confusing me to take any decision. – aprasy May 15 '17 at 20:16
  • The Deadlock graph should show the exact item that is being deadlocked (an object id which will identify a specific index, for example). That's where you need to start. There will be a very large ID, and you can use this SQL to decode exactly what it's referring to: SELECT hobt_id, object_name(p.[object_id]), index_id FROM sys.partitions p WHERE hobt_id = 72057594060734464 -- replace with your specific lock ID – pmbAustin May 15 '17 at 20:22
  • Is it more appropriate for this to be on https://dba.stackexchange.com/? – Michael Baker May 15 '17 at 20:31

1 Answers1

0

The scan phase of MERGE is performed with a shared lock (S), optimized for the case of a single session running MERGE and concurrent sessions running SELECT. In the case of multiple concurrent MERGE statements, this can lead to deadlocks or failures.

The solution you should add a HOLDLOCK hint on the target table. This is a little bit inconsistent with other read-for-update patters which use UPDLOCK on SELECT.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67