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.

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Nov 4, 2015 at 10:33

SeanR · Accepted Answer · 2015-11-04 10:32:26Z

0

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

answered Nov 4, 2015 at 10:32

SeanR

1961 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr Me Over a year ago

i need to retrieve an id based on paramers (changes the where clause) and then update that record id, and return the entire row.

Mr Me Over a year ago

i think you are right though, the select seems to be executed before the update is.. which is why i'm looking to hold all other hits on the stored proc until one single execution is complete.

Collectives™ on Stack Overflow

SQL stored procedure waiting and executing sequentially

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related