Let each run of a sproc process its share of rows

Question

I am maintaining a sproc where the developer has implemented his own locking mechanism but to me it seemed flawed:

CREATE PROCEDURE Sproc 1
AS

Update X
set flag = lockedforprocessing
where flag = unprocessed

-- Some processing occurs here with enough time to 
-- 1. table X gets inserted new rows with a flag of unprocessed
-- 2. start another instance of this Sproc 1 that executes the above update

Select from X
where flag = lockedforprocessing

-- Now the above statement reads rows that it hadn't put a lock on to start with.

I know that I can just wrap it sproc inside a transaction with isolation level of SERIALIZABLE but I want to avoid this.

The goal is

1. that multiple instances of this sproc can run at the same time and process their own "share" of the records to achieve maximum concurrency.
2. An execution of the sproc should not wait on a previous run that is still executing

I don't think REPEATABLE READ can help here since it won't prevent the new records with a value of "unprocessed" being read (correct me if I'm wrong please).

I just discovered the sp_getlock sproc and it would resolve the bug but serialize exaction which is not my goal.

A solution that I see is to have each run of the proc generate its own unique GUID and assign that to the flag but somehow I am thinking I am simulating something that SQL Server already can solve out of the box.

Is the only way that let each run of a sproc process it's "share" of the rows to have it in SERIALIZABLE?

Regards, Tom

Answer 1

Assuming there is an ID field in X, a temporary table of updated Xs can help:

CREATE PROCEDURE Sproc 1
AS
-- Temporary table listing all accessed Xs
   declare @flagged table (ID int primary key)
-- Lock and retrieve locked records
   Update X
      set flag = lockedforprocessing
   output Inserted.ID into @flagged
    where flag = unprocessed

-- Processing
   Select from X inner join @flagged f on x.ID = f.ID

-- Clean-up
   update X
      set flag = processed
     from x inner join @flagged f on x.ID = f.ID