0

Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example:

Deadlock of the replication procedure
frame procname=Bocss2.dbo.sp_MSupd_dbotblBOrderItem line=79 stmtstart=8684 stmtend=14714 sqlhandle=0x03002a004d01df507b1245016ba3000001000000000000000000000000000000000000000000000000000000

With the following query:

SELECT 'INSERT INTO @TempTable1 (OrderNo, OrderSeqNo, OrderItemDate) 
VALUES (LTRIM(''',strBxOrderNo, '''),', sintOrderSeqNo, ', ''', 
  CONVERT(VARCHAR(20), sdtmOrderItemStatusUpdated, 113), ''')' 
FROM tblBOrderItem
WHERE sintOrderItemStatusId = 4 -- In Picking
AND sdtmOrderItemStatusUpdated BETWEEN GETDATE() -30 AND GETDATE() -1
 -- Give the warehouse a chance to deliver the order
AND decFinalPrice > 0
ORDER BY strItemNo

I don't want to enable the snapshot isolation level - where writes don't block readers - at the cost of maintaining a row version at the tempDB, but I am considering partitioning the big tables that are usually the culprits on the deadlocks.

what stats I could have a look at so that I could guess whether or not this is a good idea?

2

Partitioning will likely not help, unless the user query in the deadlock can be rewritten in order to let partition elimination kick in.

I would focus on making both operations in the deadlock graph as fast as possible instead. This will involve ad-hoc indexing or rewriting the query in a different form.

If the replication agent is often the deadlock winner, you could consider lowering its deadlock priority, in order to let the user queries run without being killed. However, I would explore this option only as a safety net in case you're not able to reduce deadlocks to a very rare event.

1
-2

I know this doesn't specifically answer your questions, but in similar situation I found that you can set the priority with regards to deadlocks - i.e. which process is chosen as the victim: https://msdn.microsoft.com/en-us/library/ms186736.aspx

This allows you to control whether the current code is likely to be chosen as victim and therefore give your chosen process a better chance at completion than the 'other' process. I'm not sure how you'd link this in to replication (assuming you want replication to 'win'), but could be useful?

We didn't end up using this approach, we used 'No Lock' where applicable on the other process to relieve the contention. In our environment - it was more important to let the 'other process' complete than worry about Replication needing to reconnect.

PS. Partitioning your tables is well worth doing in my experience, especially if you can move your most active tables onto faster media.

6
  • 1
    Once again, please leave a comment if you're going to downvote. I'd love to know why this happens - I don't mind you having your opinion or downvoting my answer if you do have that opinion, but please let me know why. From my point of view, my answer is constructive, potentially raises an avenue for a work-around and contains a link to a supporting MSDN page to support my suggested method. - the work-around could very well provide a path for the OP not to have this issue anymore, isn't this why we're on here to help each other from different levels, perspectives and planes of experience???? – MHSQLDBA May 26 '15 at 14:55
  • 2
    very few people comment on downvotes. It can get frustrating at times. Asking for comments won't likely lead who downvoted to explain their reasons. – spaghettidba May 26 '15 at 18:11
  • 1
    Thanks for replying spaghettidba, it's a real pity that there's no feedback from the votes - all I'm taking away from this is a sense of annoyance - not a way to improve my answer next time. If someone felt strongly enough to vote it would be nice to know why - maybe this would lead to better participation. At the moment I feel as if I should think twice before I answer next time in case someone doesn't like my answer- and surely that isn't in the spirit of the forum? Anyway, thanks for your time. – MHSQLDBA May 27 '15 at 7:53
  • 3
    Wasn't my down-vote, but might I offer a couple of suggestions: (1) You suggested NOLOCK and explained about two things that are important to you, but failed to warn any reader about much more important problems - hint: deadlocks & replication are not your biggest concerns (2) Being new to this site, please review this post and discard the expectation that you will always (or maybe ever) get any reasoning behind up-votes or down-votes. – Aaron Bertrand May 27 '15 at 21:48
  • 1
    @MHSQLDBA regarding "At the moment I feel as if I should think twice before I answer next time in case someone doesn't like my answer" No, don't think like that. Think like "is there something wrong and how can I improve my answer" when you get a downvote. Sometimes, it's true, you can't find anything wrong and you get downvotes. Leave the answer. If you get both (up and down votes), that's fine. It's either a controversial question or someone doesn't like you or some detail. If you have only downvotes and/or a lot of them, then you can remove the answer. – ypercubeᵀᴹ May 27 '15 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.