Home Need help understanding why I get dirty reads on code that has same locking during execution
Reply: 0

Need help understanding why I get dirty reads on code that has same locking during execution

user2216
1#
user2216 Published in May 28, 2018, 3:17 am

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?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305788 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO