none
Deadlock: How to rerun the transaction? RRS feed

  • Question

  • Hi

     

    I sometimes get the following error:

    Code Snippet
    System.Data.SqlClient.SqlException: Transaction (Process ID 76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

     

     

    What's your suggestion how to handle this error? Should I catch the exception and try again? Would you do that immediately or would you wait for a short period?

     

    I think it's best to change my code that way I can avoid this error. I'll do that but it's still possible to run into this problem nevertheless...

     

    Thank you in advance

    Tuesday, September 23, 2008 3:56 PM

Answers

  • I believe in a case of deadlock SQL Server .NET Managed provider throws exception with the error code 1205 (you could check it). What you could do is to catch this specific exception with the error code of 1205 and try to re-run SQL statement. In most cases it will work, unless your application holds some resources for long period of time. But best solution in this case would be to profile SQL Server (using SQL Profiler) and analyze cause of specific deadlock, and try to to reslove it on database level. In most cases it is related to updating multiple indexes inside of transaction(s). Try to analyze if triggers (if you have them) do not cause dead locks, since each trigger code executed in transaction context.

     

    Tuesday, October 7, 2008 8:56 PM
    Moderator

All replies

  • I'm sorry, but this forum isn't the best place to ask this question.  The ADO.Net and System.Data forum would be the best place.  Here is a link to that forum:  http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

     

    Tuesday, September 23, 2008 10:55 PM
  • Hi there

    I'm still looking for help.

    Any help is appreciated.

    Thank you in advance..

     

    Tuesday, October 7, 2008 10:04 AM
  • I believe in a case of deadlock SQL Server .NET Managed provider throws exception with the error code 1205 (you could check it). What you could do is to catch this specific exception with the error code of 1205 and try to re-run SQL statement. In most cases it will work, unless your application holds some resources for long period of time. But best solution in this case would be to profile SQL Server (using SQL Profiler) and analyze cause of specific deadlock, and try to to reslove it on database level. In most cases it is related to updating multiple indexes inside of transaction(s). Try to analyze if triggers (if you have them) do not cause dead locks, since each trigger code executed in transaction context.

     

    Tuesday, October 7, 2008 8:56 PM
    Moderator