查询以死锁结尾

问题描述

我正在运行以下查询.它最终陷入了僵局，不断运行.感谢早日回答

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 )

我猜上面的查询调用了一个函数indextosession，它实际上是在创建问题.这是下面的代码

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

set ANSI_NULLS ON
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)), '') is not null
begin
set @count=@count+1
end

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

IF NULLIF(LTRIM(RTRIM(@groupnumber)), '') is not null
begin
set @count=@count+1
end
--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
else
set @returnValue =0
--Select @count
--return (@count)
return (@returnValue)

END

推荐答案

这篇关于查询以死锁结尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！