how to overcome deadlock when generating unique IDs
I have a table for storing the last used ID of a combination of two numbers. CREATE TABLE [HBLastID]( [LastID] [bigint] NOT NULL, [ReadingDefHBID] [bigint] NOT NULL, [FieldHBID] [bigint] NOT NULL, CONSTRAINT [PK_HBLastID] PRIMARY KEY CLUSTERED ( [ReadingDefHBID] ASC, [FieldHBID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] I have a procedure to get the next ID, update the table, and return the ID. (It also writes the record for that combination of 2 numbers if they haven't been used before.) create procedure [HBIONextHBID] @ReadingDefHBID bigint, @FieldHBID bigint, @HBID bigint output as begin set nocount on SET TRANSACTION ISOLATION LEVEL REPEATABLE READ begin transaction select @HBID = LastID+1 from HBLastID where ReadingDefHBID = @ReadingDefHBID and FieldHBID = @FieldHBID if @HBID is null begin insert into HBLastID (ReadingDefHBID, FieldHBID, LastID) values (@ReadingDefHBID, @FieldHBID, 0) set @HBID = 1 end update HBLastID set LastID = @HBID where ReadingDefHBID = @ReadingDefHBID and FieldHBID = @FieldHBID commit transaction end If I run this code in two SSMS tabs, declare @i int = 0, @HBID bigint while @i < 1000 begin exec HBIONextHBID 2, 4, @HBID output set @i = @i + 1 end 1 of them gets deadlocked. > (1 row(s) affected) > > (1 row(s) affected) > > (1 row(s) affected) > > Msg 1205, Level 13, State 51, Procedure HBIONextHBID, Line 18 [Batch Start Line 0] > Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I have tried snapshot, serializable, read committed snapshot, I still get deadlocks. How can I always generate a unique next ID for any pair of numbers, without deadlocks? Many thanks in anticipation.
Use READ COMMITTED and do the entire logic in a single atomic statement with the MERGE statement create procedure [HBIONextHBID_2] @ReadingDefHBID bigint, @FieldHBID bigint, @HBID bigint output as begin set nocount on declare @HBID_table table (HBID bigint) merge HBLastID as target using (select @ReadingDefHBID, @FieldHBID) as source(ReadingDefHBID, FieldHBID) on target.ReadingDefHBID = source.ReadingDefHBID and target.FieldHBID = source.FieldHBID when matched then update set LastID = LastID+1 when not matched then insert (ReadingDefHBID, FieldHBID, LastID) values (source.ReadingDefHBID, source.FieldHBID, 1) output inserted.LastID into @HBID_table; set @HBID = (select top 1 HBID from @HBID_table) end