title: SQL statements locking recurrently
body: <p>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. </p>
  
  <pre><code>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&lt;&gt;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)&lt;&gt;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&lt;&gt;@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
  </code></pre>