Do I need UPDLOCK hint in this SP? Reading a Data Queue Row More Than Once

Question

My DB Schema is Job -> (Many) JobData -> (Many) Results. A Job is broken into multiple JobData rows so that multiple threads can process 'chunks' of the Job (each JobData row). Then the processing threads insert Result row(s) for each of the JobData rows it processes.

A single thread attempts to read results in a loop until all the results are read. Note that at the same time threads can be still processing JobData rows (until all are processed) and inserting results. So in the loop, each call to this SP passes in a JobKey...yet somehow with this code, results are read multiple times (very low frequency...i.e. < 3 rows per 8000 rows of results) but only randomly reproducible.

I've added a comment below in code next to my UPDATE TOP statement regarding UPDLOCK. Thanks in advance.

BEGIN TRANSACTION

-- Create a temp table TO store the select results
DECLARE @UnReadResults TABLE
(
    ResultKey uniqueidentifier
)

-- Wouldn't expect a UPDLOCK is needed since UPDATE statements create exclusive lock anyway?  Do I need to SELECT WITH UPDLOCK first, then do the UPDATE statement?

UPDATE TOP ( @pageSize ) Result
SET rResultRead = 1
OUTPUT INSERTED.rKey INTO @UnReadResults
FROM Result r INNER JOIN JobData AS jd ON jd.jdKey = r.rJobDataKey
WHERE jd.jdJobKey = @jobKey AND rResultRead = 0

-- Just return Job (always the same), JobData (could vary if chunk of result rows
-- spans multiple JobDatas) and Results that I successfully 'grabbed' by joining
-- to my temp table

SELECT j.jKey, j.jStatus, j.jResultsRead, 
   jd.jdKey, jd.jdJobKey, jd.jdDateStart, jd.jdDateComplete, jd.jdStatus,
   r.rKey, r.rProfileKey, r.rProfileAuthID, r.rResult, r.rReadLock, r.rReadAttempts
FROM Job j
    INNER JOIN JobData jd
        ON jKey = jdJobKey
    INNER JOIN Result r
        ON jdKey = rJobDataKey
    INNER JOIN @UnReadResults urr
        on rKey = urr.ResultKey

COMMIT TRANSACTION

EDIT: Just wanted to put what I have in place now based on @gbn answer. Excluding the entire SP, but the UPDATE statement is now formatted as so:

UPDATE TOP ( @pageSize ) Result
SET rResultRead = 1, rReadLock = @lock, rReadAttempts = rReadAttempts + 1
OUTPUT INSERTED.rKey INTO @UnReadResults
FROM Result r WITH ( ROWLOCK, READPAST, UPDLOCK ) INNER JOIN JobData AS jd ON jd.jdKey = r.rJobDataKey
WHERE jd.jdJobKey = @jobKey AND rResultRead = 0

Been running successfully now for a few days, so hopefully I nailed issue. If this doesn't look like proper use of table hints feel free to let me know.

Answer 1

Your UPDATE is in fact SELECT followed by an UPDATE because you have JOINs and WHEREs.

You need the extra hints as per the other answer you noted (which is my answer)

1. Lock only rows: ROWLOCK
2. Skip locked rows: READPAST
3. Increase duration and isolation of locks: UPDLOCK