Hi all,
I am currently trying to improve the template code in our stored proc
generator.
In the insert stored proc, there seems to be a lot of code with references to
deadlock detection,
and attempts to re-run the insert if it fails due to a deadlock.
But the insert statement itself has a ROWLOCK hint on it. (See example below)
So I can't see how it could possibly get a deadlock if the row hasn't
previously existed.
Although that said, if it was escalated to a table lock in the context of a
much larger transaction
perhaps it could happen.
What do people do with respect to dead lock detection in their stored procs?
Regards,
Tony
ALTER PROCEDURE [Common].[ApplicationParameterInsert]
@Id int output,
@Name nvarchar(50),
@Description nvarchar(255),
@Value nvarchar(255),
@DataType nvarchar(50),
@UserId uniqueIdentifier,
@RowVersion rowversion output
AS
BEGIN
-- SET DEFAULT BEHAVIOR
SET NOCOUNT ON -- Performance: stops rows affected messages
SET DEADLOCK_PRIORITY LOW -- This SP to be the Deadlock victim
-- Initialise Lock-Timeout and Deadlock vars for Insert
DECLARE @iLockTimeoutRetries as int
DECLARE @IdeadLockRetries as int
DECLARE @dtLockTimeoutSleepInterval as datetime
DECLARE @dtDeadlockSleepInterval as datetime
DECLARE @iErrorNumber as int
SET @iLockTimeoutRetries = 0
SET @IdeadLockRetries = 0
SET @dtLockTimeoutSleepInterval = Common.fnLockTimeoutSleepInterval()
SET @dtDeadlockSleepInterval= Common.fnDeadlockSleepInterval()
SET @iErrorNumber = 0
RETRY:
BEGIN TRY
-- Insert Record
Insert Into [Common].[ApplicationParameter] WITH (ROWLOCK) (
[ModifiedById],
[ModifiedDate] ,
[Name],
[Description],
[Value],
[DataType])
Values (
@UserId,
GETDATE(),
@Name,
@Description,
@Value,
@DataType
)
-- Insert was successful so return the Inserted rowversion value
Select @Id = [Common].[ApplicationParameter].[Id],
@RowVersion =
[Common].[ApplicationParameter].[RowVersion]
From [Common].[ApplicationParameter]
Where[Common].[ApplicationParameter].[Id] = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
-- Get the ErrorNumber
Set @iErrorNumber = ERROR_NUMBER()
--Handle Deadlock situation (Deletes, Inserts & Inserts)
IF @iErrorNumber = 1205
BEGIN
-- If we have not made enough attempts to break the lock
IF @IdeadLockRetries < Common.fnMaxDeadlockRetries()
BEGIN
-- Increment the Attempt count
SET @IdeadLockRetries = @IdeadLockRetries + 1
-- Pause to allow the deadlock contention to
clear
WAITFOR DELAY @dtDeadlockSleepInterval
GOTO RETRY
END
END
-- Handle Lock Timeout situation (Deletes, Inserts & Inserts)
IF @iErrorNumber = 1222
BEGIN
-- If we have not made enough attempts to break the
Deadlock
IF @iLockTimeoutRetries <
Common.fnMaxLockTimeoutRetries()
BEGIN
-- Increment the Attempt count
SET @iLockTimeoutRetries = @iLockTimeoutRetries
+ 1
-- Pause to allow the lock contention to clear
WAITFOR DELAY @dtLockTimeoutSleepInterval
GOTO RETRY
END
END
--Let the Error bubble Up
exec Common.RethrowError
END CATCH
END
