Where to use ROWLOCK, READPAST with CTE, Subquery and Update?

Question

In trying to avoid deadlocks and synchronize requests from multiple services, I'm using ROWLOCK, READPAST. My question is where should I put it in a query that includes a CTE, a subquery and an update statement on the CTE? Is there one key spot or should all three places have it (below)? Or maybe there's a better way to write such a query so that I can select ONLY the rows that will be updated.

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

I don't have an answer off-hand, but there are ways you can learn more.

The code you wrote seems reasonable. Examining the actual query plan for the proc might help verify that SQL Server can generate a reasonable query plan, too.

If you don't have an index on NotificationJob.Destination that includes QueuedDt and CompletedDt, the not exists sub-query might acquire shared locks on the entire table. That would be scary for concurrency.

You can observe how the proc behaves when it acquires locks. One way is to turn on trace flag 1200 temporarily, call your proc, and then turn off the flag. This will generate a lot of information about what locks the proc is acquiring. The amount of info will severely affect performance, so don't use this flag in a production system.

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