以队列格式处理表数据

Question

以下是我用来处理 FIFO 队列顺序中的表数据的 SP 代码的主要部分。SP 可以同时由 100 个进程调用。

create   TABLE #STAG(ident BIGINT)
begin try
begin tran
insert into CaseLock (id,locktime,sessionid,lockid) output inserted.id into #STAG
select top 1
i.ident
,getutcdate()
,case when @sess is null then i.sessionid else @sess end
,newid()
from QueueItem i WITH (ROWLOCK, READPAST)     where
not exists(select 1 from CaseLock lck WITH (ROWLOCK, READPAST) where  lck.id = i.ident)
and i.QUEUEIDENT    = @QUEUEIDENT       
and i.finished is null and (i.deferred is null or i.deferred < getutcdate())   

commit
end try
begin catch
rollback
end catch
Select i.encryptid, i.id, i.ident, i.keyvalue, i.data, i.status, i.attempt
from QueueItem i  WITH (ROWLOCK, READPAST) 
where EXISTS(SELECT 1 FROM #STAG WHERE [#STAG].[ident]=i.ident)

队列项表中的数据无法删除，我们使用 CaseLock 表来锁定已选择进行处理的记录。处理记录后，完成的列将更新为当前日期时间。

我正在得到

违反主键约束"PK_CaseLock"。无法插入 对象'dbo'中的重复键。箱锁'。 重复的键值为 (105(.

我的问题是SP必须始终返回数据，如果没有从SP返回任何数据，应用程序将崩溃。

如何修改代码以解决主键冲突错误，它还返回记录？

Answer 1

在两个单独的会话(SSMS 查询窗口(中执行以下命令：

declare @QUEUEIDENT  = 12345 --a queue value which has rows for processing
begin transaction
select top 1
i.ident
from QueueItem i WITH (ROWLOCK, READPAST)     where
not exists(select 1 from CaseLock lck WITH (ROWLOCK, READPAST) where  lck.id = i.ident)
and i.QUEUEIDENT    = @QUEUEIDENT       
and i.finished is null and (i.deferred is null or i.deferred < getutcdate())

您将在两个窗口中获得相同的值，这就是引发 CaseLock 上的主键冲突的原因(如果同时执行的查询，它们将尝试插入相同的主键，一个执行将成功，另一个将失败(。

问题在于 SELECT(行锁(，读取行后锁不会保留在行上。 另一个可能的问题是跳过 CaseLock 上的任何现有锁：

where not exists(select 1 from CaseLock lck WITH (ROWLOCK, READPAST)

....假设您在队列项目处理失败时从 CaseLock 中删除行(这些队列项目需要稍后重试(，您最终可能会有一个队列读取器尝试插入一个 CaseLock 正在"飞行"的项目进行删除(读取器读取过去删除行上的任何 CaseLock 锁定(。

为了解决这两个可能的问题，您可以尝试在阅读时保持对 QueueItem 的锁定，并在没有任何锁的情况下检查 CaseLock：

UPDLOCK on QueueItem keeps the lock for the duration of the transaction,
NOLOCK on CaseLock makes sure that i.ident will NEVER be inserted if it exists

''

...   from QueueItem i WITH (ROWLOCK, UPDLOCK, READPAST)
where
not exists(select 1 from CaseLock lck WITH (NOLOCK) where  lck.id = i.ident)...

如果要保留现有实现，并根据另一个表检查队列项处理状态，则所有这些操作。 更简单的方法是在 QueueItem 上有一个与流程相关的列，并使用 READPAST 更新该列(依次捕获插入的.ident 等(。