Post reply

Seemingly basic transaction/blocking question

  • November 24, 2011 at 2:49 pm

    #246752

    Hello,

    I am looking at some incredibly straight forward code which is causing some deadlocks and I am a little unsure as to the best way to manage the blocking on it.

    DELETEaa

    FROMra

    INNER JOIN aa ON aa.ID = ra.ID

    WHEREra.ID= @ID

    ANDra.Date= CONVERT(DATE, @Date)

    DELETE

    FROMra

    WHEREra.ID= @ID

    ANDDate= CONVERT(DATE, @Date)

    The above code is the part of the procedure that is causing the locks - where the following type of contention is encountered:

    key lock on table aa ncidx_1, owned by spid b (lock type = X), waiting by spid a (lock type = U)

    key lock on table ra cidx_1, owned by spid a (lock type = S), waiting by spid b (lock type = X)

    The above code has a transaction begin just above what you see now; and then finishes later on after some other actions are completed.

    I was thinking of chopping up the transactions so that they are smaller - but logically thinking about what is happening the 2 delete statements need to both go together - so that if one fails they are both rolled back. So I wasnt sure that this would actually achieve anything.

    Indexing wise - there are index seeks happening for both chunks - but wonder if there is anything else I should do.

    What are peoples thoughts on how to get around this? I would have thought this would be super basic but I keep thinking about it and start doing one thing but then questioning whether it would actually work - thus going round in circles and never commiting to anything....which is where you wonderful people come in.

    Any ideas/comments/hints/thoughts - much appreciated.

    Thanks in advance.

    Troy

  • November 24, 2011 at 4:56 pm

    #1412817

    Lock type U? That means there's an update involved in this (or a select with UPDLOCK hint)

    Can you post the deadlock graph?

    Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs.

    DBCC TRACEON(1222,-1)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 24, 2011 at 6:57 pm

    #1412829

    Hello - thanks for the response. Yes it certainly is a U lock type - I will see if I can capture it...the deadlock isn't happening all the time, so it might be a hit/miss opportunity 🙂

  • November 25, 2011 at 12:08 am

    #1412853

    That's fine. Turn the tracelfag on (either with TRACEON or a startup option) and leave it. When the deadlock reoccurs, the deadlock graph will be automatically written into the error log.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 1, 2011 at 6:31 pm

    #1415666

    Oh well - this one has not raised it's ugly head since....and the other deadlocks I have managed to work out the resolution....so all's well that ends well I suppose...fingers crossed.

    Thanks anyhow, but figured I had better just update this thread to effectivly close it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply