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

Recordset solution is sought. Please help.

-- First of all, thank you for your effort and your time.
--
-- I am looking for a record set solution to the following problem.
--
-- We have feature 1 2 and 3 and users define certain combinations of them.
These can be 2-way combinations where feature3 is null or 3 way combinations
-- For simplicity, let us assume we have values A B and C for these three
features.
-- Users can fill them in redundant ways as
-- ACB, CBA, BCA, CAB, ABC, BAC.
-- I would like to select a single occurrence, ABC, or one out of six,
always one distinct combination.
--
-- I have made an attempt however I remove all duplicate occurances. I just
don't see how I can resolve this. Please help.
--
-- Thanks again for your help.
--
set nocount on
declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
null, Feature2 varchar(10) null)
insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
insert @.tmp values ('C','B','A')
insert @.tmp values ('B','C','A')
insert @.tmp values ('C','A','B')
insert @.tmp values ('A','B','C')
insert @.tmp values ('B','A','C')
insert @.tmp values ('D','A','C') -- return either DAC or ACD
insert @.tmp values ('P','A','C')
insert @.tmp values ('N','B','D')
insert @.tmp values ('A','C','D')
SELECT *
FROM @.tmp t
WHERE NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
)
AND NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE -- 3 way
t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
t.feature1
OR
t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
t.feature
OR
t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
t.feature
)
-- Expected outcome:
A,B,C
D,A,C
P,A,C
N,B,DYou can add an Identity column to the temp table and check for Identity
column not equal in your sub query.
Perayu
"Farmer" wrote:

> -- First of all, thank you for your effort and your time.
> --
> -- I am looking for a record set solution to the following problem.
> --
> -- We have feature 1 2 and 3 and users define certain combinations of them
.
> These can be 2-way combinations where feature3 is null or 3 way combinatio
ns
> -- For simplicity, let us assume we have values A B and C for these three
> features.
> -- Users can fill them in redundant ways as
> -- ACB, CBA, BCA, CAB, ABC, BAC.
> -- I would like to select a single occurrence, ABC, or one out of six,
> always one distinct combination.
> --
> -- I have made an attempt however I remove all duplicate occurances. I jus
t
> don't see how I can resolve this. Please help.
> --
> -- Thanks again for your help.
> --
> set nocount on
> declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
> null, Feature2 varchar(10) null)
> insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
> insert @.tmp values ('C','B','A')
> insert @.tmp values ('B','C','A')
> insert @.tmp values ('C','A','B')
> insert @.tmp values ('A','B','C')
> insert @.tmp values ('B','A','C')
> insert @.tmp values ('D','A','C') -- return either DAC or ACD
> insert @.tmp values ('P','A','C')
> insert @.tmp values ('N','B','D')
> insert @.tmp values ('A','C','D')
>
> SELECT *
> FROM @.tmp t
> WHERE NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
> AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
> )
> AND NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE -- 3 way
> t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
> t.feature1
> OR
> t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
> t.feature
> OR
> t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
> t.feature
> )
> -- Expected outcome:
> A,B,C
> D,A,C
> P,A,C
> N,B,D
>
>|||Thank you for replying.
How do you suggest I do it? Please post what you think I can use.
"Perayu" <Perayu@.discussions.microsoft.com> wrote in message
news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
> You can add an Identity column to the temp table and check for Identity
> column not equal in your sub query.
> Perayu
> "Farmer" wrote:
>|||Farmer
Why not perfom such reports on the client side?
"Farmer" <someone@.somewhere.com> wrote in message
news:ukeQ0UzpFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Thank you for replying.
> How do you suggest I do it? Please post what you think I can use.
>
> "Perayu" <Perayu@.discussions.microsoft.com> wrote in message
> news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
>