Select three oldest records for updating?

Question

Post reply

Select three oldest records for updating?

blast2hell

Right there with Babe

Points: 771
More actions
November 26, 2007 at 2:38 pm

#184450

Ok, how can I update the 3 oldest records in the table, and then return the id for those 3 records?
My concern is that the stored procedure that is doing this can be fired off by the calling application again even before it's finished doing the first one, and I worry about doing a select, and then updating based off that select, and then updating the same records another call of this procedure has selected.
Here is one thing I was trying
WITH Requests AS
(
Select *, ROW_NUMBER() OVER(ORDER BY RequestDate ASC) AS RowNum
FROM ReportRequest_R2
WHERE Status = 'O'
)
UPDATE Requests
SET
Status = 'P',
StatusDate = getdate(),
StatusDesc = 'Processing',
LockGUID = @GUID,
ProcessCount = ProcessCount + 1
WHERE RowNum <= 3
SELECT RequestID FROM ReportRequests_R2
WHERE LockGUID = @GUID
Now this updates the three oldest records, but I'm worried that it starts with a select statement, and if another procedure performs the same select statement on the table, before my update statement is run...then I'll have two procedures trying to access the same value...I was thinking of using a column with a GUID in it that way I'll be sure not to get duplicate returns, but I don't think this would stop two procedures from selecting the same rows
Any help? or is this presented in a confusing manner?

Viewing 5 posts - 1 through 4 (of 4 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: 398890 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