I have a table that is used by a legacy application as a substitute for
IDENTITY fields in various other tables.
Each row in the table stores the last used ID
LastID for the field named in
Occasionally the stored proc gets a deadlock - I believe I've built an appropriate error handler; however I'm interested to see if this methodology works as I think it does, or if I'm barking up the wrong tree here.
I'm fairly certain there should be a way to access this table without any deadlocks at all.
The database itself is configured with
READ_COMMITTED_SNAPSHOT = 1.
First, here is the table:
CREATE TABLE [dbo].[tblIDs]( [IDListID] [int] NOT NULL CONSTRAINT PK_tblIDs PRIMARY KEY CLUSTERED IDENTITY(1,1) , [IDName] [nvarchar](255) NULL, [LastID] [int] NULL, );
And the nonclustered index on the
CREATE NONCLUSTERED INDEX [IX_tblIDs_IDName] ON [dbo].[tblIDs] ( [IDName] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 80 ); GO
Some sample data:
INSERT INTO tblIDs (IDName, LastID) VALUES ('SomeTestID', 1); INSERT INTO tblIDs (IDName, LastID) VALUES ('SomeOtherTestID', 1); GO
The stored procedure used to update the values stored in the table, and return the next ID:
CREATE PROCEDURE [dbo].[GetNextID]( @IDName nvarchar(255) ) AS BEGIN /* Description: Increments and returns the LastID value from tblIDs for a given IDName Author: Hannah Vernon Date: 2012-07-19 */ DECLARE @Retry int; DECLARE @EN int, @ES int, @ET int; SET @Retry = 5; DECLARE @NewID int; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET NOCOUNT ON; WHILE @Retry > 0 BEGIN BEGIN TRY BEGIN TRANSACTION; SET @NewID = COALESCE((SELECT LastID FROM tblIDs WHERE IDName = @IDName),0)+1; IF (SELECT COUNT(IDName) FROM tblIDs WHERE IDName = @IDName) = 0 INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID) ELSE UPDATE tblIDs SET LastID = @NewID WHERE IDName = @IDName; COMMIT TRANSACTION; SET @Retry = -2; /* no need to retry since the operation completed */ END TRY BEGIN CATCH IF (ERROR_NUMBER() = 1205) /* DEADLOCK */ SET @Retry = @Retry - 1; ELSE BEGIN SET @Retry = -1; SET @EN = ERROR_NUMBER(); SET @ES = ERROR_SEVERITY(); SET @ET = ERROR_STATE() RAISERROR (@EN,@ES,@ET); END ROLLBACK TRANSACTION; END CATCH END IF @Retry = 0 /* must have deadlock'd 5 times. */ BEGIN SET @EN = 1205; SET @ES = 13; SET @ET = 1 RAISERROR (@EN,@ES,@ET); END ELSE SELECT @NewID AS NewID; END GO
Sample executions of the stored proc:
EXEC GetNextID 'SomeTestID'; NewID 2 EXEC GetNextID 'SomeTestID'; NewID 3 EXEC GetNextID 'SomeOtherTestID'; NewID 2
I've added a new index, since the existing index IX_tblIDs_Name is not being used by the SP; I assume the query processor is using the clustered index since it needs the value stored in LastID. Anyway, this index IS used by the actual execution plan:
CREATE NONCLUSTERED INDEX IX_tblIDs_IDName_LastID ON dbo.tblIDs ( IDName ASC ) INCLUDE ( LastID ) WITH (FILLFACTOR = 100 , ONLINE=ON , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON);
I've taken the advice that @AaronBertrand gave and modified it slightly. The general idea here is to refine the statement to eliminate unnecessary locking, and overall to make the SP more efficient.
The code below replaces the code above from
BEGIN TRANSACTION to
BEGIN TRANSACTION; SET @NewID = COALESCE((SELECT LastID FROM dbo.tblIDs WHERE IDName = @IDName), 0) + 1; IF @NewID = 1 INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID); ELSE UPDATE dbo.tblIDs SET LastID = @NewID WHERE IDName = @IDName; COMMIT TRANSACTION;
Since our code never adds a record to this table with 0 in
LastID we can make the assumption that if @NewID is 1 then the intention is append a new ID to the list, else we are updating an existing row in the list.