Deadloc in the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Deadloc in the query

Author

Topic

GauravRahi
Starting Member

1 Post

Posted - 2011-01-18 : 18:06:55

I am running the following query. It is ending up in a deadlock situation where it is running endlessly. Appriciate 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
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 clode below

set QUOTED_IDENTIFIER ON
go

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

IF NULLIF(LTRIM(RTRIM(@policynumber)), #39;#39;) is not null
begin
set @count=@count+1
end

IF NULLIF(LTRIM(RTRIM(@agentnumber)), #39;#39;) is not null
begin
set @count=@count+1
end

IF NULLIF(LTRIM(RTRIM(@groupnumber)), #39;#39;) is not null
begin
set @count=@count+1
end

select @sessionIndexCount=count(*) from dbo.cl_SessionHasIndexValue with (nolock) where cl_sessionid =@Sessionid

if @SessionIndexCount >=@count
set @returnValue =1
else
set @returnValue =0

return (@returnValue)

END

Gaurav Rahi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-18 : 18:39:46

We have to see the deadlock owner in order to help. You'll need to track down the deadlock owner via Profiler's deadlock graph or trace flag 1222.

It is impossible to assist with deadlocks when only the victim query is posted. The owner is required.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources