Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Ressourcen für IT-Professionals >
> 
> 
SQL Server deadlock victim

Answered SQL Server deadlock victim

  • Dienstag, 5. Februar 2013 09:59
     
     
    Anmelden
    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

     

Alle Antworten

  • Dienstag, 5. Februar 2013 12:25
     
     
    Anmelden
    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
     
  • Dienstag, 5. Februar 2013 13:36
     
     
    Anmelden
    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

     
  • Dienstag, 5. Februar 2013 15:38
     
     Beantwortet
    Anmelden
    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!


    • Als Antwort markiert Mark Roworth 2 Dienstag, 5. Februar 2013 16:22
    •  
     
  • Dienstag, 5. Februar 2013 16:22
     
     
    Anmelden
    0

    Hi Maresh,

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

    Mark

    Mark Roworth

     
  • Mittwoch, 6. Februar 2013 09:04
     
     
    Anmelden
    0
    Cheers!
     
  • Donnerstag, 7. Februar 2013 22:15
     
     
    Anmelden
    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