sql-server - 需要帮助理解为什么我在执行期间对具有相同锁定的代码进行脏读

Question

警告:这是遗留代码，我们知道我们应该使用实际的 SQL 序列，这里的目标是理解为什么一种方法提供的结果与另一种方法不同，因为我们不能在运行时即时更改为序列这次。

问题:我们使用单值表作为序列对象(2012 年之前创建)来生成并返回需要保证唯一且递增的数字。适当的存储过程可以工作，但会占用高 CPU 并在高负载下导致严重阻塞；补救措施看起来应该有效，但实际上没有。新代码减轻了 CPU 和阻塞，但我们似乎无法防止脏读，这会导致重复。

表格如下所示:

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

此存储过程使用可序列化锁定并获得准确的结果，但由于阻塞而表现不佳:

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

这是我们的更快版本，它避免了可序列化，但它会时不时地返回重复值:

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

我们可以做些什么来获得第二个(不可序列化的)proc 的更快性能，同时避免重复生成的 ID？

Answer 1

在大型数据库中，有时您无法避免代码中的死锁。数据库不会总是向前看所有事情以避免争用。在这些罕见的特殊情况下，您可以利用 SQL Server 本身使用的高性能锁定 --> SP_GETAPPLOCK().您可以以这样一种方式构建您的代码，以独占访问存储过程中的关键部分并序列化对其的访问。当所有传统锁提示都失败时，SP_GETAPPLOCK 绝对不是一件坏事。

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!