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?

share|improve this question

    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 timeoutBEGIN TRANEXEC @LockRequestResult=SP_GETAPPLOCK 'MyCriticalWork','Exclusive','Transaction',@MyTimeoutMilisecondsIF(@LockRequestResult>=0)BEGIN/*DO YOUR CRITICAL READS AND WRITES HEREYou may prefer try finally to release*/COMMIT TRAN -- <--Releases the lock!END ELSEROLLBACK TRAN --<--Releases the lock!
    share|improve this answer
    • 1
      Hey Ross, How is your solution compare to using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION -- DO WORK HERE COMMIT TRANSACTION Would your solution help prevent deadlocks?– Chris SavageFeb 13 at 18:59
    • 1
      Under SERIALIZABLE tx uncommitted data can't be read. The lock above serializes access to a code block, not just the data. Setting the lock timeout gives a bit more fined tuned control to wait 3 minutes for a lock, if you so desired.– Ross BushFeb 13 at 19:06
    • One caveat is that the "code" lock is only within the context of the caller or SP in which it was obtained. Resources will still need to be protected from modifications outside of this context, however, I am betting the code above in the question is not duplicated elsewhere in the system.– Ross BushFeb 13 at 19:18

    Your Answer

     
    discard

    By posting your answer, you agree to the privacy policy and terms of service.

    Not the answer you're looking for? Browse other questions tagged or ask your own question.