Home Need help understanding why I get dirty reads on code that has same locking during execution
 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 null drop 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 AS BEGIN declare @return int; --value to be returned and declared within sproc SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION Select @return = number from dbo.Sequence with (updlock) --get initial value plus one Update dbo.Sequence set number = @return + @AddSomeNumber --increment it COMMIT TRANSACTION Select @return + @AddSomeNumber as nextid END GO  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 AS BEGIN declare @return int; --value to be returned and declared within sproc update 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 nextid END GO  What can we do to get the faster performance of the second (non-serializable) proc, while avoiding duplicate generated IDs?