Click here to Skip to main content
15,946,525 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am running the following query. It is ending up in a deadlock situation where it is running endlessly. Appreciate for an early answer

select distinct (TCLE.ErrorID) from #TEMPCHECKLISTERROR TCLE

 left join cl_SessionHasIndexValue SHIV1(NOLOCK)
on  NULLIF(LTRIM(RTRIM(TCLE.PolicyNumber)), '') is not null and SHIV1.IndexValue=TCLE.PolicyNumber

 left join cl_SessionHasIndexValue SHIV2 (NOLOCK)
on  NULLIF(LTRIM(RTRIM(TCLE.PolicyNumber)), '') is null and NULLIF(LTRIM(RTRIM(TCLE.AgentNumber)), '') is not null and SHIV2.IndexValue=TCLE.AgentNumber

 left join cl_SessionHasIndexValue SHIV3 (NOLOCK)
on  NULLIF(LTRIM(RTRIM(TCLE.PolicyNumber)), '') is null and NULLIF(LTRIM(RTRIM(TCLE.AgentNumber)), '') is null and NULLIF(LTRIM(RTRIM(TCLE.GroupNumber)), '') is not null and SHIV3.IndexValue=TCLE.GroupNumber

inner join cl_checklistResults CLR (NOLOCK) on
TCLE.ErrorMessage=CLR.displayText and TCLE.Source=CLR.Source

--and (SHIV.IndexValue = TCLE.PolicyNumber or SHIV.IndexValue = TCLE.AgentNumber or SHIV.IndexValue = TCLE.GroupNumber)
where CLR.currentstatuscode <>99 and dbo.indextoSession(COALESCE(SHIV1.cl_sessionid,SHIV2.cl_sessionid,SHIV3.cl_sessionid,NULL),TCLE.PolicyNumber,TCLE.AgentNumber,TCLE.GroupNumber) =1 )

The query above calls a function indextosession that is actually creating the problem, I guess. Here is the code below


ALTER FUNCTION [dbo].[indextoSession] (@Sessionid bigint,@Policynumber varchar(30), @AgentNumber varchar(30), @GroupNumber varchar(30) )
returns bit
Declare @count  int
Declare @sessionIndexCount  int
Declare @returnValue  bit
set @count=0

IF NULLIF(LTRIM(RTRIM(@policynumber)), '') is not null
set @count=@count+1

IF NULLIF(LTRIM(RTRIM(@agentnumber)), '') is not null
set @count=@count+1

IF NULLIF(LTRIM(RTRIM(@groupnumber)), '') is not null
set @count=@count+1
--Declare @sessionIndexCount as tinyint
select @sessionIndexCount=count(*) from dbo.cl_SessionHasIndexValue with (nolock) where cl_sessionid =@Sessionid
--Select @sessionIndexCount
if @SessionIndexCount >=@count
set @returnValue =1
set @returnValue =0
--Select @count
--return (@count)
return (@returnValue)

Updated 18-Jan-11 12:09pm
Sergey Alexandrovich Kryukov 18-Jan-11 17:42pm    
Technically, if it really runs endlessly, it is not called a deadlock. Deadlock a is pretty strict term.
Manfred Rudolf Bihy 18-Jan-11 18:18pm    
I do agree with you that the term is often used in the "strict way" you are referring to, but technically speaking it is really just a situation where progress is made impossible due to an unresolved issue and a non terminating function can cause a deadlock in a single process as well. But at least I'm privileged to know what you are talking about :=D
Sergey Alexandrovich Kryukov 18-Jan-11 21:47pm    
Yes, you are, Manfred. In essence, the usual problem is not terminology but not describing the apparent issue directly without mangling the simple facts.
By the way, my work with testing departments and similar parties made me understand that perhaps most people are enable to describe what they see literally, because they replace it with what they think they see. -- just a though.
Manfred Rudolf Bihy 19-Jan-11 12:18pm    
"because they replace it with what they think they see."
That made giggle like a mad man, not that my mind snapped already, but rather because I run across this kind of behavior on a more or less daily basis. :)

Are you actually getting a deadlock error message? If so, this might help: Reducing SQL Server Deadlocks[^].
Share this answer
Espen Harlinn 19-Jan-11 10:35am    
Good link - contains a few good "rules of thumb" 5+
It may just be that your query is taking a long time. If you are able to, you should allow it to run to completion, to prove if this is the situation or not.

One very good technique for improving the performance of queries is to study the Execution Plan[^]. This[^] is just one of thousands of articles on the subject.

I'm sure that you can evolve your own search term if you would like to investigate this further.

There are even analysis tools out there, some are open source if your budget is tight, I have not needed to use any of them so cannot make a recommendation but a search should get you some to investigate.
Share this answer
Espen Harlinn 19-Jan-11 10:33am    
Good advice 5+
I am getting a timeout message when run from dotnet.

When i try to run the query in the Sql server, it keeps on running. I discontinued it after 17 mins of execution. There are about 40000 records to look into.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900