Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Server deadlock victim

Answered SQL Server deadlock victim

  • Tuesday, February 05, 2013 9:59 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Tuesday, February 05, 2013 12:25 PM
     
     
    Sign In to Vote
    0
    Hi there,

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

    Regards

    Mahesh
     
  • Tuesday, February 05, 2013 1:36 PM
     
     
    Sign In to Vote
    0

    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

     
  • Tuesday, February 05, 2013 3:38 PM
     
     Answered
    Sign In to Vote
    0

    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!


    • Marked As Answer by Mark Roworth 2 Tuesday, February 05, 2013 4:22 PM
    •  
     
  • Tuesday, February 05, 2013 4:22 PM
     
     
    Sign In to Vote
    0

    Hi Maresh,

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

    Mark

    Mark Roworth

     
  • Wednesday, February 06, 2013 9:04 AM
     
     
    Sign In to Vote
    0
    Cheers!
     
  • Thursday, February 07, 2013 10:15 PM
     
     
    Sign In to Vote
    0

    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