How would I lock a stored procedure that is executing and alter the table that the stored procedure returns?

Question

I have a table as follows:

id    status
--    ------
1     pass
1     fail
1     pass
1     na
1     na

Also, I have a stored procedure that returns a table with top 100 records having status as 'na'. The stored procedure can be called by multiple nodes in an environment and I don't want them to fetch duplicate data. So, I want to lock the stored procedure while it is executing and set the status of the records obtained from the stored procedure to 'In Progress' and return that table and then release the lock, so that different nodes don't fetch the same data. How would I accomplish this?

Answer 1

You can use select with WITH (SERIALIZABLE) isolation level to make sure that the records are locked till the status gets updated like following.

SELECT TOP 100 id 
INTO   #temp 
FROM   [your_table] WITH (serializable) 
WHERE  status = 'na' 

UPDATE ut 
SET    status = 'In Progress' 
FROM   [your_table] ut WITH (serializable) 
       INNER JOIN #temp T 
               ON T.id = ut.id 

SERIALIZABLE Isolation Level Specifies the following:

• Statements cannot read data that has been modified but not yet committed by other transactions.
• No other transactions can modify data that has been read by the current transaction until the current transaction completes.
• Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Source (MSDN)