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

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

Bryan Maynard
1#
Bryan Maynard Published in 2018-02-13 17:13:06Z

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?

Ross Bush
2#
Ross Bush Reply to 2018-02-13 17:41:54Z

In largish databases times will arise when you are unable to avoid deadlocks in your code. The database will not always look ahead on all things to avoid contention. In these rare and special cases you can make use of the high performance locking used by SQL Server itself --> SP_GETAPPLOCK(). You can structure your code in such a way to give exclusive access to a critical section in your stored procedure and serialize access to that. SP_GETAPPLOCK is definitely not a bad thing when all traditional lock hints fail.

DECLARE @MyTimeoutMiliseconds INT =5000--Wait only five seconds max then timeout

BEGIN TRAN
EXEC @LockRequestResult=SP_GETAPPLOCK 'MyCriticalWork','Exclusive','Transaction',@MyTimeoutMiliseconds
IF(@LockRequestResult>=0)BEGIN

            /*
            DO YOUR CRITICAL READS AND WRITES HERE

            You may prefer try finally to release

            */

    COMMIT TRAN -- <--Releases the lock!
END ELSE
    ROLLBACK TRAN --<--Releases the lock!
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO