Hello I am facing a critical issue since 2 weeks, where all the tables involved in below trigger are getting locked in a some scenario and not loading the view pages in the web application in the production region. Below SQL statement is hit continuously in the same request_session_id and when lock occurs while clients are using the application then all the tables in the trigger are getting locked and the system then works after restart of the database and IIS service. Not understanding the cause of the issue.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_UpdateWorkFlowStatus]
ON [dbo].[Wf_WorkflowTaskInstanceDocuments]
FOR INSERT, UPDATE
AS
BEGIN
print ''
SET NOCOUNT ON;
DELETE FROM triggertest
DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
THEN 'U' -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I' -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D' -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete".
END)
DECLARE @WorkFlowNo BIGINT =(select top 1 WrokflowInstanceId from Wf_WorkflowTaskInstance WITH (NOLOCK) join inserted on inserted.WorkflowTaskInstanceId= Wf_WorkflowTaskInstance.WorkflowTaskInstanceId)
IF NOT EXISTS(SELECT 1 FROM Wf_WorkflowTaskInstanceDocuments WITH (NOLOCK)
WHERE WorkflowTaskInstanceId IN
(
select WorkflowTaskInstanceId
from Wf_WorkflowTaskInstance
WHERE WrokflowInstanceId=@WorkFlowNo and Status<>4
)
AND
(IsSubmitted = 0 OR IsSubmitted IS NULL)
)
BEGIN
UPDATE Wf_WorkflowInstance SET Status='Completed' where WorkflowInstanceId=@WorkFlowNo
END
ELSE IF EXISTS(SELECT 1 FROM Wf_WorkflowTaskInstanceDocuments WITH (NOLOCK)
WHERE WorkflowTaskInstanceId IN
(
select WorkflowTaskInstanceId
from Wf_WorkflowTaskInstance
WHERE WrokflowInstanceId=@WorkFlowNo
)
AND
(IsSubmitted = 1)
)
BEGIN
UPDATE Wf_WorkflowInstance SET Status='InProgress' where WorkflowInstanceId=@WorkFlowNo
END
if exists(select 1 from Wf_WorkflowTaskInstanceDocuments WITH (NOLOCK) where participants is null)
update Wf_WorkflowTaskInstanceDocuments set participants=dbo.[GetTaskDocumentAssignees](WorkflowTaskInstanceDocumentsId) where participants is null
if(@Action='u')
begin
if exists(select 1 from Wf_WorkflowTaskInstanceDocuments WITH (NOLOCK) where len(ToParticipants)<>len(dbo.UDF_DistinctList(ToParticipants,',')) and WorkflowTaskInstanceDocumentsId in(select WorkflowTaskInstanceDocumentsId from inserted))
begin
update Wf_WorkflowTaskInstanceDocuments set ToParticipants=dbo.UDF_DistinctList(ToParticipants,','),participants=dbo.[GetTaskDocumentAssignees](WorkflowTaskInstanceDocumentsId)
where WorkflowTaskInstanceDocumentsId in(select WorkflowTaskInstanceDocumentsId from inserted)
end
else
update Wf_WorkflowTaskInstanceDocuments set participants=dbo.[GetTaskDocumentAssignees](WorkflowTaskInstanceDocumentsId) where WorkflowTaskInstanceDocumentsId in(select WorkflowTaskInstanceDocumentsId from inserted)
end
if(@Action='I')
BEGIN
insert into Wf_WorkflowTaskDocumentVersionMapping
(
WorkflowTaskInstanceId,
Wf_WorkflowInstanceDocID,
DocRegisterID
)
select
WorkflowTaskInstanceId,
WorkflowTaskInstanceDocumentsId,
DocumentRegisterID
from
Wf_WorkflowTaskInstanceDocuments WHERE WorkflowTaskInstanceDocumentsId not IN(SELECT Wf_WorkflowInstanceDocID FROM Wf_WorkflowTaskDocumentVersionMapping WITH (NOLOCK))
DECLARE @DOCID AS TABLE
(
DocumentRegisterID INT,
WorkflowTaskInstanceDocumentsId INT,
WorkflowTaskInstanceId INT
)
DECLARE @WorkflowTaskInstanceId INT
DECLARE @WorkflowInstanceId INT=0
DECLARE @CurrentWorkflowInstanceId INT=0
DECLARE @DocumentRegisterID INT=0
DECLARE @WorkflowTaskInstanceDocumentsId INT=0
DECLARE @Wf_WorkflowTaskDocumentVersionMapping INT=0
DECLARE @Wf_TaskInstanceLocal INT=0
DECLARE @RowCount int=0
DECLARE @DocVersionLocal int
DECLARE @RevisionLocal varchar(25)
DECLARE @FirstTaskInstanceID int=(select top 1 wti.WorkflowTaskInstanceId from Wf_WorkflowTaskDocumentVersionMapping wtid
join Wf_WorkflowTaskInstance wti WITH (NOLOCK) on wtid.WorkflowTaskInstanceId=wti.WorkflowTaskInstanceId
where wti.WrokflowInstanceId IN(@WorkFlowNo) order by wti.WorkflowTaskInstanceId)
DELETE FROM @DOCID
INSERT INTO @DOCID SELECT DocumentRegisterID,WorkflowTaskInstanceDocumentsId,WorkflowTaskInstanceId FROM Wf_WorkflowTaskInstanceDocuments WITH (NOLOCK) WHERE WorkflowTaskInstanceId=@FirstTaskInstanceID
DECLARE @AdsdDocVersionMappingParent CURSOR
SET @AdsdDocVersionMappingParent = CURSOR FAST_FORWARD
FOR
select DocumentRegisterID
,WorkflowTaskInstanceId
from @DOCID
OPEN @AdsdDocVersionMappingParent
FETCH NEXT FROM @AdsdDocVersionMappingParent
INTO @DocumentRegisterID
,@WorkflowTaskInstanceId
WHILE @@FETCH_STATUS = 0
BEGIN
set @DocVersionLocal=(select top 1 DocVersion from DocumentRegisterLogs where source like '%auto superseded by workflow%' and DocumentRegisterID=@DocumentRegisterID order by DocumentRegisterLogsID desc)
set @RevisionLocal=(select top 1 Revision from DocumentRegisterLogs where source like '%auto superseded by workflow%' and DocumentRegisterID=@DocumentRegisterID order by DocumentRegisterLogsID desc)
DECLARE @AdsdDocVersionMapping CURSOR
SET @AdsdDocVersionMapping = CURSOR FAST_FORWARD
FOR
select wti.WorkflowTaskInstanceId,wtid.Wf_WorkflowInstanceDocID
from Wf_WorkflowTaskDocumentVersionMapping wtid WITH (NOLOCK)
join Wf_WorkflowTaskInstance wti WITH (NOLOCK) on wtid.WorkflowTaskInstanceId=wti.WorkflowTaskInstanceId
where wti.WrokflowInstanceId IN(@WorkFlowNo) and wtid.DocRegisterID is null
OPEN @AdsdDocVersionMapping
FETCH NEXT FROM @AdsdDocVersionMapping
INTO @WorkflowTaskInstanceId,@WorkflowTaskInstanceDocumentsId
WHILE @@FETCH_STATUS = 0
BEGIN
if not exists(select 1 from Wf_WorkflowTaskDocumentVersionMapping where DocRegisterID=@DocumentRegisterID and WorkflowTaskInstanceId=@WorkflowTaskInstanceId and DocRegisterID is not null)
begin
update Wf_WorkflowTaskDocumentVersionMapping set DocRegisterID=@DocumentRegisterID where Wf_WorkflowInstanceDocID=@WorkflowTaskInstanceDocumentsId
update Wf_WorkflowTaskDocumentVersionMapping set DocVersion=@DocVersionLocal,Revision=@RevisionLocal where Wf_WorkflowInstanceDocID=@WorkflowTaskInstanceDocumentsId
select @WorkflowTaskInstanceDocumentsId
end
--//////=
update a set a.DocVersion=b.DocVersion
from Wf_WorkflowTaskDocumentVersionMapping a
join DocumentRegisterLogs b on a.DocRegisterID=b.DocumentRegisterID
join Wf_WorkflowTaskInstance wti on wti.WorkflowTaskInstanceId=a.WorkflowTaskInstanceId
where b.Source like '%auto superseded by workflow%' and (a.DocVersion is null)
and wti.WrokflowInstanceId=@WorkFlowNo
and wti.WorkflowTaskInstanceId=@WorkflowTaskInstanceId
update a set a.Revision=b.Revision
from Wf_WorkflowTaskDocumentVersionMapping a
join DocumentRegisterLogs b on a.DocRegisterID=b.DocumentRegisterID
join Wf_WorkflowTaskInstance wti on wti.WorkflowTaskInstanceId=a.WorkflowTaskInstanceId
where b.Source like '%auto superseded by workflow%' and (a.Revision is null)
and wti.WrokflowInstanceId=@WorkFlowNo
and wti.WorkflowTaskInstanceId=@WorkflowTaskInstanceId
--//////
FETCH NEXT FROM @AdsdDocVersionMapping
INTO @WorkflowTaskInstanceId,@WorkflowTaskInstanceDocumentsId
END
CLOSE @AdsdDocVersionMapping
DEALLOCATE @AdsdDocVersionMapping
FETCH NEXT FROM @AdsdDocVersionMappingParent
INTO @DocumentRegisterID
,@WorkflowTaskInstanceId
END
CLOSE @AdsdDocVersionMappingParent
DEALLOCATE @AdsdDocVersionMappingParent
update a set a.DocVersion=b.DocVersion
from Wf_WorkflowTaskDocumentVersionMapping a
join DocumentRegisterLogs b on a.DocRegisterID=b.DocumentRegisterID
join Wf_WorkflowTaskInstance wti on wti.WorkflowTaskInstanceId=a.WorkflowTaskInstanceId
where b.Source like '%auto superseded by workflow%' and (a.DocVersion is null)
and wti.WrokflowInstanceId=@WorkFlowNo
update a set a.Revision=b.Revision
from Wf_WorkflowTaskDocumentVersionMapping a
join DocumentRegisterLogs b on a.DocRegisterID=b.DocumentRegisterID
join Wf_WorkflowTaskInstance wti on wti.WorkflowTaskInstanceId=a.WorkflowTaskInstanceId
where b.Source like '%auto superseded by workflow%' and (a.Revision is null)
and wti.WrokflowInstanceId=@WorkFlowNo
and b.DocumentRegisterLogsID in(select top 1000 DocumentRegisterLogsID from DocumentRegisterLogs where DocumentRegisterID=b.DocumentRegisterID order by DocumentRegisterLogsID desc)
END
if(@Action='u')
BEGIN
DECLARE @DocumentNoInst varchar(120)
DECLARE @DocumentNoVer varchar(120)
DECLARE @DocumentIDVer int
DECLARE @DocumentVer int
DECLARE @DocInID int
SELECT @DocumentNoInst=dr.DocumentNo,@DocInID=WorkflowTaskInstanceDocumentsId FROM inserted ins JOIN DocumentRegister dr on ins.DocumentRegisterID=dr.DocumentRegisterID
SELECT @DocumentNoVer=dr.DocumentNo FROM Wf_WorkflowTaskDocumentVersionMapping insver JOIN DocumentRegister dr WITH (NOLOCK) on insver.DocRegisterID=dr.DocumentRegisterID where Wf_WorkflowInstanceDocID=@DocInID
if(@DocumentNoInst<>@DocumentNoVer)
begin
select top 1 @DocumentIDVer=DocRegisterID,@DocumentVer=insver.DocVersion from Wf_WorkflowTaskDocumentVersionMapping insver JOIN DocumentRegister dr on insver.DocRegisterID=dr.DocumentRegisterID
where WorkflowTaskInstanceId in(@FirstTaskInstanceID) and dr.DocumentNo=@DocumentNoInst
update Wf_WorkflowTaskDocumentVersionMapping set DocRegisterID=@DocumentIDVer,DocVersion=@DocumentVer where Wf_WorkflowInstanceDocID=@DocInID
end
END
-----------------------------------------***********************************-------------------------------------------
Declare @AllTasksCount int=(select count(1) from Wf_WorkflowTaskInstance WITH (NOLOCK) where WrokflowInstanceId=@WorkFlowNo)
declare @SkippedTasksCount int=(select count(1) from Wf_WorkflowTaskInstance WITH (NOLOCK) where WrokflowInstanceId=@WorkFlowNo and Status=4)
if(@AllTasksCount=@SkippedTasksCount)
begin
update Wf_WorkflowInstance set Status='Skipped' where WorkflowInstanceId=@WorkFlowNo
end
END
