none
SQL Server deadlock victim

    Frage

  • Hi there,

    I have two jobs that run concurrently on our data warehouse. The reference the same source tables. One of them creates RTT (referral to treatment data), the other a Clinic Slots dataset. At the point that they run, the source tables aren't touched by any other process and the calculation of them is complete.

    About 1 night in 5 the Clinic Slots dataset is chosen as a deadlock victim. I've changed it so that every table reference it makes is (NOLOCK), but this is still happening. Should it be? I could also do the same to the RTT process. Anyone able to give me a quick pointer to stop this happening? Thanks,

    Mark


    Mark Roworth

    Dienstag, 5. Februar 2013 09:59

Antworten

  • Hi Mark,

    If there is update, delete, insert on your queries, even if you use (nolock) there won't be any affect.  Best approach is to analyze your deadlock graph.  

    Cheers!


    Dienstag, 5. Februar 2013 15:38

Alle Antworten

  • Hi there,

    Its worth analyzing the indexes on your table, especially the FKey column. Also, take a look at the query plan.

    Regards

    Mahesh
    Dienstag, 5. Februar 2013 12:25
  • Hi Mahesh,

    Thanks for the reply. Certainly this might speed the process up. I am more interested in how a step in a job might get deadlocked when all table references are suffixed with NOLOCK. I don't understand how this can happen. Any thoughts? Thanks,

    Mark


    Mark Roworth

    Dienstag, 5. Februar 2013 13:36
  • Hi Mark,

    If there is update, delete, insert on your queries, even if you use (nolock) there won't be any affect.  Best approach is to analyze your deadlock graph.  

    Cheers!


    Dienstag, 5. Februar 2013 15:38
  • Hi Maresh,

    Aha. Thank you. That points me in the right direction.

    Mark


    Mark Roworth

    Dienstag, 5. Februar 2013 16:22
  • Cheers!
    Mittwoch, 6. Februar 2013 09:04
  • I would look at SNAPSHOT ISOLATION on the database and remove the NOLOCK.

    I have seen many Deadlock victims that are caused by long running queiries that do not have covering indexes and the lock is on the Non-clustered Index and then the query needs to go to the Clustered Index for more columns.

    NOLOCK is going to be removed in future versions because there is a chance for a row to be read twice because of page splits.

    The suggestion about looking at the Query Plan is where I would start if I were you.

    When A Covering Index no longer covers - http://thesmilingdba.blogspot.com/2012/10/when-covering-index-no-longer-covers.html

    Thomas


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    Donnerstag, 7. Februar 2013 22:15