Post reply

DEADLOCK problem in our production DB

  • November 8, 2011 at 1:51 pm

    #259651

    Hi Folks,

    I am facing below issue in our production DB and would like to understand the reason behind this.

    Transaction (Process ID 297) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.\

    We are using SERIALIZABLE in a specific core SP and we are sure the above error comes from this SP's one of the update statement. Now, my doubt is that even though we gave serializable, why there is a deadlock victim? Would there be a chance that the Isolation level would have been changed inside the SP?

    We have given high timeout for our DB commands as well.

    Any thoughts? Please help.

    Regards,
    Suresh Arumugam

  • November 8, 2011 at 2:32 pm

    #1404855

    You might want to have a look at Bart Duncans blog for a description on how to troubleshoot deadlocks.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 8, 2011 at 2:34 pm

    #1404856

    Suresh Kumar-284278 (11/8/2011)

    I am facing below issue in our production DB and would like to understand the reason behind this.

    Transaction (Process ID 297) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    We are using SERIALIZABLE in a specific core SP and we are sure the above error comes from this SP's one of the update statement. Now, my doubt is that even though we gave serializable, why there is a deadlock victim? Would there be a chance that the Isolation level would have been changed inside the SP?

    We have given high timeout for our DB commands as well.

    Hard to tell without knowing the application in detail but, if I remember it right "serializable isolation level" is the most restrictive of the isolation levels meaning that as concurrency goes up so go up - exponentially - the chances of hitting a blocking lock situation.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • November 8, 2011 at 2:44 pm

    #1404861

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

    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 8, 2011 at 2:45 pm

    #1404864

    PaulB-TheOneAndOnly (11/8/2011)

    Hard to tell without knowing the application in detail but, if I remember it right "serializable isolation level" is the most restrictive of the isolation levels meaning that as concurrency goes up so go up - exponentially - the chances of hitting a blocking lock situation.

    Yup.

    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 8, 2011 at 3:08 pm

    #1404878

    This would be a good time to review the application to see if there is truly a need to use a serializable isolation level.

    It’s likely that that a less restrictive isolation level would do better and you might want to consider one of the row-versioning isolation levels, read committed snapshot or snapshot.

    Using Row Versioning-based Isolation Levels

    http://msdn.microsoft.com/en-us/library/ms179599(v=SQL.90).aspx

  • November 11, 2011 at 2:55 pm

    #1406607

    Thanks for all your replies.

    few days we are not getting the deadlock issue in Production. However, we are tracking the situtation now and will get back to you.

    Thanks much for the valuable inputs again.

    Regards,
    Suresh Arumugam

  • November 12, 2011 at 1:24 am

    #1406649

    Hope you have enabled the trace flag 1222 suggested by Gail. Whenever deadlock occurs, it'll be written in the SQL Server's error log.

Viewing 8 posts - 1 through 7 (of 7 total)

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