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.

Hmm, may be a duplicate of http://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition. Checking. — Terry, Jan 19 '12 at 08:15
yes, that is "table as a queue" pattern answer by me. Your use of OUTPUT is good: just add the 3 hints `(ROWLOCK, READPAST, UPDLOCK)` — gbn, Jan 19 '12 at 10:29

score 1 · Accepted Answer · answered Jan 19 '12 at 10:30

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)

Lock only rows: ROWLOCK
Skip locked rows: READPAST
Increase duration and isolation of locks: UPDLOCK

answered Jan 19 '12 at 10:30

gbn

422,506
82
585
676

So you are saying, even for simple updates, if I have something like UPDATE Table SET Field = 1 WHERE Field = 0, it is a 'SELECT' and I need to change it to UPDATE Table WITH ( UPDLOCK ) SET Field = 1 WHERE Field = 0? – Terry Jan 24 '12 at 05:14
Question on SELECTs and if I need hints. DB is Job->JobData. A JobData row is chunk of data that a thread can grab for processing. Each thread calls 'complete' on its own JobData row, but inside the SP, I need to then set the Job row to complete if every JobData row is marked as complete. 1) UPDATE JobData SET jdStatus = @status WHERE jdKey = {jobDataKey}, then 2) IF NOT EXISTS( SELECT jdKey FROM JobData WHERE jdJobKey = {jobKey} AND ( jdStatus = 0 /* New */ OR jdStatus = 1 /* Processing */ ) ) BEGIN UPDATE Job SET jStatus = ( SELECT MAX(jdStatus) FROM JobData WHERE jdJobKey = {jobKey} ) – Terry Jan 24 '12 at 05:37
@Terry: once "allocated", post processing UPDATEs etc don't need hint. Only the allocation of the keys. Or did I misunderstand? – gbn Jan 24 '12 at 06:11
Maybe misunderstood, although think your SQL expertise is much greater than mine. Should I start a new question and post entire SP? Or message you directly or how can I try to show you my concern? – Terry Jan 24 '12 at 07:10
Another general question. In multithreaded DB queue...if I want to guarantee an update to a table and I don't want deadlocks to occur (in case two threads are trying to update at same time)...do you just put UPDATE Table WITH (ROWLOCK, UPDLOCK)? And if two threads try to call that same SP (or even if two diff SPs try to update the same row) would this just guarantee mutual exclusive actions without compromising the system by introducing deadlocks? – Terry Jan 24 '12 at 07:17
@Terry: hard to say. If you mean the "allocation" UPDATE then you need the 3 hints to make a 2nd process skip the locks from process 1. If you mean the post processing update, then you can just use ROWLOCK. In any case, you should have deadlock handling in your clirnt code anywaxy – gbn Jan 24 '12 at 11:32

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

1 Answers1