Select three oldest records for updating?

Question

Post reply

Select three oldest records for updating?

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

you should add UPDLOCK hint to the "select" statement on the batch...

Cheers,

* Noel

blast2hell Right there with Babe Points: 771 More actions · Answer 2

thanks, that should help prevent deadlocking which was one concern, but I don't think it will stop the second call of the same stored procedure from reading the same rows.

I do thank you for telling me about the UPDLOCK hint...I'm still kind of new, and I see how valuable this is.

blast2hell Right there with Babe Points: 771 More actions · Answer 3

Ok, I got it, I need UPDLOCK and READPAST hints together. As explained here

Tutorial

So my statement should read.

WITH Requests AS

(

Select Top(3) *

FROM ReportRequest_R2 WITH (UPDLOCK, READPAST)

WHERE Status = 'O'

ORDER BY RequestDate ASC

)

UPDATE Requests

SET

Status = 'P',

StatusDate = getdate(),

StatusDesc = 'Processing',

LockGUID = @GUID,

ProcessCount = ProcessCount + 1

WHERE RowNum <= 3

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 4

And wrap it all in a transaction.

Also, make sure you include the old data values in the update statement. That way, if the second call manages to get the same three rows, but has to wait for the first transaction to commit, when it goes to update, if the data doesn't match, no rows will be affected.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning