November 26, 2007 at 2:19 pm
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?
November 26, 2007 at 2:34 pm
oops I meant to put this in the 2005 forum
November 26, 2007 at 3:10 pm
wrong forum... but here is the answer, it uses some 2005 functionality though with the rownumber and over statements
I need UPDLOCK and READPAST hints together. As explained here
So my statement should read.
WITH Requests AS
(
Select *, ROW_NUMBER() OVER(ORDER BY RequestDate ASC) AS RowNum
FROM ReportRequest_R2 WITH (UPDLOCK, READPAST)
WHERE Status = 'O'
)
UPDATE Requests
SET
Status = 'P',
StatusDate = getdate(),
StatusDesc = 'Processing',
LockGUID = @GUID,
ProcessCount = ProcessCount + 1
WHERE RowNum <= 3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply