4

I have just got this email here: enter image description here

Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication.

enter image description here

O.K. in this case in particular I noticed that this specific procedure could be tuned, I even found a missing index, as you can see below.

enter image description here

But this same situation has happened so many times, I would have had the procedure won, the replication was the victim, and I would deal with the procedure later on.

How can I put this:

SET DEADLOCK_PRIORITY LOW

everywhere, in every replication transaction?

this is to check it out:

    SELECT session_id,deadlock_priority 
FROM sys.dm_exec_sessions 
    WHERE SESSION_ID = @@SPID

enter image description here

How

4
  • 1
    You can set the Snapshot Agent's PublisherDeadlockPriority property to -1, in order to resolve deadlocks that may occur between the Snapshot Agent and user applications during snapshot generation; however, that is only one agent and only the snapshot process.
    – ooutwire
    Aug 13 '15 at 15:23
  • You want to address the underlying problem. Dont set the deadlock priority. Have you tried creating the index and test out ? That will help you.
    – Kin Shah
    Aug 13 '15 at 16:16
  • 1
    @Kin the problem is a conflict between constant updates and being able to run reports without facing deadlocks. the replication would catch up later, that is not a problem in this case. Other thing I would consider is READ_COMMITTED_SNAPSHOT isolation set ON. Still I prefer to set the replication to lose. I am dealing with the indexes too. Aug 13 '15 at 16:39
  • 1
    Using READ_COMMITTED_SNAPSHOT worked for me when I had this issue. Nov 21 '17 at 16:56
1

After having a good setup and monitoring the tempdb we opted and used the READ COMMITTED SNAPSHOT as described in the comments.

How to implement Read Committed Snapshot and how it is different to Snapshot has been nicely described (including links explaining how to set it up) here.

In addition, READ_COMMITTED_SNAPSHOT (henceforth called RCSI) will only give you some properties of optimistic concurrency. RCSI and Snapshot (SI) differ in three main ways:

  1. RCSI will be used immediately by all RC isolation queries.
  2. RCSI provides a point-in-time (snapshot) view of the database for a single statement.
  3. SI provides a point-in-time view for an entire transaction.
  4. SI can have write conflicts.

also quoted from the same source:

Other useful details relating some gotchas and caveats when using RCSI/SI can be found in these articles:

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.