Where to use ROWLOCK, READPAST with CTE, subquery and update?

When trying to avoid deadlocks and synchronizing requests from several services, I use ROWLOCK, READPAST. My question is: where should I put it in a query that includes a CTE, a subquery, and update instructions for a CTE? Is there one key place or all three of it (below)? Or maybe there is a better way to write such a query so that I can ONLY select 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  
+5
source share
1 answer

I have no answer, but there are ways to find out more.

, , . proc , SQL Server .

NotificationJob.Destination, QueuedDt CompletedDt, not exists . concurrency.

, proc, . , 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
+1

All Articles