sql-server-2008 - 在哪里使用 ROWLOCK、READPAST 和 CTE、子查询和更新？

Question

为了避免死锁和同步来自多个服务的请求，我使用了 ROWLOCK、READPAST。我的问题是我应该将它放在包含 CTE、子查询和 CTE 更新语句的查询中的什么位置？有一个关键点还是三个地方都应该有(下)？或者也许有更好的方法来编写这样的查询，以便我只能选择将要更新的行。

alter proc dbo.Notification_DequeueJob
    @jobs int = null
as

    set nocount on;
    set xact_abort on;

    declare @now datetime
    set @now = getdate();

    if(@jobs is null or @jobs <= 0) set @jobs = 1

    ;with q as (
        select 
            *, 
            dense_rank() over (order by MinDate, Destination) as dr
        from 
        (
            select *, 
                min(CreatedDt) over (partition by Destination) as MinDate
            from dbo.NotificationJob with (rowlock, readpast)
        ) nj

    where (nj.QueuedDt is null or (DATEDIFF(MINUTE, nj.QueuedDt, @now) > 5 and nj.CompletedDt is null))
    and (nj.RetryDt is null or nj.RetryDt < @now)
    and not exists(
        select * from dbo.NotificationJob
        where Destination = nj.Destination
        and nj.QueuedDt is not null and DATEDIFF(MINUTE, nj.QueuedDt, @now) < 6 and nj.CompletedDt is null)
    )
    update t
        set t.QueuedDt = @now, 
            t.RetryDt = null
    output 
        inserted.NotificationJobId, 
        inserted.Categories, 
        inserted.Source, 
        inserted.Destination, 
        inserted.Subject, 
        inserted.Message
    from q as t
    where t.dr <= @jobs
go  

Answer 1

我没有现成的答案，但您可以通过多种方式了解更多信息。

你写的代码看起来很合理。检查 proc 的实际查询计划可能有助于验证 SQL Server 是否也可以生成合理的查询计划。

如果 NotificationJob.Destination 上没有包含 QueuedDt 和 CompletedDt 的索引，则 not exists子查询可能会获取整个表的共享锁。这对于并发来说会很可怕。

您可以观察 proc 在获取锁时的行为。一种方法是打开 trace flag 1200暂时，调用您的 proc，然后关闭标志。这将生成大量有关 proc 正在获取哪些锁的信息。信息量会严重影响性能，所以不要在生产系统中使用这个标志。

dbcc traceon (1200, -1) -- print detailed information for every lock request.  DO NOT DO THIS ON A PRODUCTION SYSTEM!
exec dbo.Notification_DequeueJob
dbcc traceoff (1200, -1) -- turn off the trace flag ASAP