CAVEAT: This is legacy code, we know we should be using an actual SQL sequence, the goal here is to understand why one method is providing different results from another because we can't just change to a sequence on the fly at this time.
ISSUE: We are using a single valued table as a sequence object (created pre-2012) to generate and return what needs to be a guaranteed unique, incrementing number. The stored procedure in place works but is churning high CPU and causing severe blocking under high load; The remediation looks as though it should work but does not. CPU and blocking is relieved with new code but we can't seem to be able to prevent dirty reads and this is resulting in duplication.
Here's how the table looks:
if OBJECT_ID('dbo.Sequence') is not nulldrop table dbo.Sequence;create table dbo.Sequence (number int Primary Key); insert into dbo.Sequence values (1);GO
This stored proc uses serializable locking and gets accurate results, but it doesn't perform well due to the blocking:
CREATE OR ALTER PROC dbo.usp_GetSequence_Serializable @AddSomeNumber INT=1 ASBEGINdeclare @return int; --value to be returned and declared within sprocSET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTIONSelect @return=number from dbo.Sequence with (updlock) --get initial value plus oneUpdate dbo.Sequence set number=@return + @AddSomeNumber --increment itCOMMIT TRANSACTIONSelect @return + @AddSomeNumber as nextid ENDGO
Here's our faster version that avoids serializable, but it's getting duplicate values back from time to time:
CREATE OR ALTER PROC dbo.usp_GetSequence_DefaultIsolationLevel @AddSomeNumber INT=1 ASBEGINdeclare @return int; --value to be returned and declared within sprocupdate dbo.Sequence set @return=number=number + @AddSomeNumber --we tried interchanging various locking hints with no change in results (likely due to an exclusive being taken already) Select @return as nextidENDGO
What can we do to get the faster performance of the second (non-serializable) proc, while avoiding duplicate generated IDs?