SQL stored procedure waiting and executing sequentially

Question

I have a stored procedure that reads the ID of a row with status x, then immediately sets that rows id to status y.

Since this stored procedure is being called by multiple client apps, somehow the same values are being returned whereas really it 2 executions should not find any in status x.

I'm not using anything other than wrapping the actions in a begin transaction / commit.

Rough example:

Begin Transaction
IF (@Param = '2') -- all
    BEGIN
        @resultID = (SELECT ... WHERE STATUS_ID = X
    END
ELSE
    BEGIN
        @resultID = (SELECT ... WHERE STATUS_ID = X
    END
IF (@ResultID > 0)
    BEGIN
        UPDATE JOB_QUEUE SET STATUS_ID = Y WHERE ID = @ResultID 
    END
COMMIT
SELECT * from JOB_QUEUE WHERE ID = @ResultID 

Somehow the query has returned the same @resultID from the table .. so I would presume I need some locking or something to prevent this.

Is there a method to ensure that executions of the stored procedure at the same time result in one executing and then the other (sequentially)?

Thanks.

Answer 1

The simple answer is to speed up the whole process - if its a slow running query, then the select can run before the update is finished.

If you need to select the values for some other report, you could effectively run the update as the first statement, and use the OUTPUT keyword to return the ID's of the updated records eg:

UPDATE JOB_QUEUE
SET STATUS_ID = Y WHERE STATUS_ID = X
OUTPUT inserted.ID