Timeout Errors When Execution Long Running Procedure

When I execute a long running procedure, I get timeout errors when other users try to execute other procedures with UPDATE or INSERT statements.



I suspect that the other procedures are trying to execute DML statements on tables that are locked by the long running procedure.



I have a sharred trigger on all my tables that creates and updates records in tables AuditLogDetails and AuditLogParent for keeping a log of modifications. I suspect that tables AuditoLogDetails and AuditLogParent are locked by the long running procedure.



How can I change the LOCKING behavior of the long running procedure to fix the time out errors that I get?



The long running procedure is displayed below.



ALTER PROCEDURE [dbo].[spPostPresenceToHistory2]

@PostDate DateTime,

@Department Int,

@Division Int,

@Testing Bit = 0,

@XDoc xml OUTPUT,

@XDoc2 xml OUTPUT,

@ModifierID varchar(20),

@Comment varchar(200)

AS

BEGIN

BEGIN TRANSACTION

DECLARE @PostCount Int,@PreCount Int,@DiffCount Int

IF @Testing=1

BEGIN

PRINT 'DELETE FROM History2_Presence'

EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment

SELECT @PreCount=COUNT(*) FROM History2_Presence

IF EXISTS(SELECT E.ID FROM History2_Personel E WHERE E.PostDate=@PostDate)

BEGIN

DELETE FROM History2_Presence FROM History2_Presence H

INNER JOIN History2_Personel Ps ON H.Personel_ID=Ps.ID AND Ps.PostDate=@PostDate

WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate

AND EXISTS (SELECT P.ID FROM Presence P

WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))

END

ELSE

BEGIN

DELETE FROM History2_Presence FROM History2_Presence H

INNER JOIN Personel As Ps ON H.Personel_ID=Ps.ID

WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate

AND EXISTS (SELECT P.ID FROM Presence P

WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))

END

SELECT @PostCount=COUNT(*) FROM History2_Presence

IF @PreCount<>@PostCount

BEGIN

SET @DiffCount = @PreCount-@PostCount

SET @XDoc2.modify('

insert <Table Name="History2_Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]

')

END

END

PRINT 'INSERT INTO History2_Presence'

EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment

SELECT @PreCount=COUNT(*) FROM History2_Presence

INSERT INTO [dbo].[History2_Presence]

([ID]

,[User_ID]

,[Personel_ID]

,[Date_de_Presence]

,[Category_Motif_ID]

,[DateEntre]

,[Category_TypeDePresence_ID]

,[Travaille_de_Jour]

,[Heur_Supplementaire_Travaille]

,[prime_transport]

,[Tarif]

,[Jour_Travaille]

,[Montant_Supplementaire_Par_Heur]

,[Salair_par_Jour]

,[Salair_Minimum]

,[IsAutomaticRec])

SELECT [P].[ID]

,[P].[User_ID]

,[P].[Personel_ID]

,[P].[Date_de_Presence]

,[P].[Category_Motif_ID]

,[P].[DateEntre]

,[P].[Category_TypeDePresence_ID]

,[P].[Travaille_de_Jour]

,[P].[Heur_Supplementaire_Travaille]

,[P].[prime_transport]

,[P].[Tarif]

,[P].[Jour_Travaille]

,[P].[Montant_Supplementaire_Par_Heur]

,[P].[Salair_par_Jour]

,[P].[Salair_Minimum]

,[P].[IsAutomaticRec]

FROM [dbo].[Presence] AS P

INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID

WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division

AND NOT EXISTS

(SELECT HP.ID FROM History2_Presence HP

WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))



SELECT @PostCount=COUNT(*) FROM History2_Presence

IF @PreCount<>@PostCount

BEGIN

SET @DiffCount = @PostCount-@PreCount

SET @xdoc.modify('

insert <Table Name="History2_Presence" RecordAdded="{ sql:variable("@DiffCount") }"/> as last into /Inserted_Records[1]

')

END

IF @Testing=0

BEGIN

PRINT 'DELETE FROM Presence'

EXEC sp_SetPostingProperties 'Presence',@ModifierID,@Comment

SELECT @PreCount=COUNT(*) FROM Presence

DELETE FROM Presence FROM Presence P

INNER JOIN Personel As Ps ON P.Personel_ID=Ps.ID

WHERE P.Date_de_Presence=@PostDate AND Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division

AND EXISTS

(SELECT HP.ID FROM History2_Presence HP

WHERE (HP.Date_de_Presence=@PostDate AND HP.Personel_ID=P.Personel_ID AND HP.Travaille_de_Jour=P.Travaille_de_Jour) OR (HP.ID=P.ID))

SELECT @PostCount=COUNT(*) FROM Presence

IF @PreCount<>@PostCount

BEGIN

SET @DiffCount = @PreCount-@PostCount

SET @XDoc2.modify('

insert <Table Name="Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]

')

END

END



COMMIT TRANSACTION

END