Recovering from Deadlocks: retrying the query

I'm having some deadlocking issues in my ASP.Net 2.0 web application with SQL Server 2000. One solution I've come across involves catching the the deadlock error (at the application level) and then retrying the query again, in the hopes that whatever was causing the deadlock is no longer locked.

I've tried to implement such a solution, where I catch the deadlock exception, then try to execute the same query again. Only now I'm getting a "The transaction is in doubt" exception.

So am I going about this wrong? Am I supposed to retry the entire transaction, or is valid to just try to redo the one query that it failed on? Is there some way around the transaction being in doubt?

You should determine the cause of the deadlock and correct your SQL code rather than retry mechanism. Retry mechanism can be complicated depending on what you are doing, whether you are in a transaction and so on. Deadlock typically means that you have access paths in your code that is not optimized and different for similar operations. Check out the following links on how to troubleshoot deadlocks:

http://msdn2.microsoft.com/en-us/library/ms177433.aspx

http://msdn2.microsoft.com/en-us/library/ms188246.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8i93.asp

sql