Update single table with mutually exclusive threads I have 2 threads (for example 2 different sql sessions) reading rows of the same table in parallel. They will do some processing and update a column to inform the row has been processed. 


Thread id:88 will read batch of 5  rows and write into Thread variable
Thread id:99 will read batch of 10 rows and write into Thread2 variable


(So I can see if the processing does overlap)

This is the table structure:



And the database has a READ COMMITTED SNAPSHOT isolation level with row versioning. Version 2012.

The execution will be as following:

Thread 88 will read 1 to 5 rows (that have not been processed or not being processed), lock them and update the thread column to 88 (to inform the thread 88 processed it) 

Then (or simultaneously), thread 99 will start and fetch next 10 records And it will pick 6 to 15 (as 1..5 are processed or currently being processed by thread 88).

This is the stored procedure I am using for thread 88:

DECLARE @Thread INT = 88
DECLARE @Continue INT = 1000

DECLARE @Id int
DECLARE @InUse INT

while (@Continue &gt; 0)
BEGIN
    DECLARE MY_CURSOR CURSOR 
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR 
        SELECT TOP 5 Id 
        FROM Names WITH (READPAST, UPDLOCK)
        WHERE inUse = 0
        ORDER BY 1 

    BEGIN TRAN
        OPEN MY_CURSOR

        FETCH NEXT FROM MY_CURSOR INTO @Id

        WHILE @@FETCH_STATUS = 0
        BEGIN 
            SELECT @InUse = InUse FROM Names where id = @Id

            IF @InUse = 0
            BEGIN
                UPDATE Names 
                SET InUse = 1, Thread = @Thread 
                WHERE id = @Id
            END

            FETCH NEXT FROM MY_CURSOR INTO @Id
        END
        CLOSE MY_CURSOR
        DEALLOCATE MY_CURSOR

        IF @Id IS NULL
            SET @Continue = 0

    COMMIT 

    WAITFOR DELAY '00:00:00:90';
    SET @Continue = @Continue - 1
END


And this for thread 99 (They are the same, just changing number of records to process and the column to inform the thread):

DECLARE @Thread INT = 99
DECLARE @Continue INT = 1000

DECLARE @Id int
DECLARE @InUse INT

WHILE (@Continue &gt; 0)
BEGIN
    DECLARE MY_CURSOR CURSOR 
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR 
        SELECT TOP 10 Id 
        FROM Names WITH (READPAST, UPDLOCK)
        WHERE inUse = 0
        ORDER BY 1 

    BEGIN TRAN
        OPEN MY_CURSOR

        FETCH NEXT FROM MY_CURSOR INTO @Id

        WHILE @@FETCH_STATUS = 0
        BEGIN 
            SELECT @InUse = InUse 
            FROM Names 
            WHERE id = @Id

            IF @InUse = 0
            BEGIN
                UPDATE Names 
                SET InUse = 1, Thread2 = @Thread 
                WHERE id = @Id
            END

            FETCH NEXT FROM MY_CURSOR INTO @Id
        END

        CLOSE MY_CURSOR
        DEALLOCATE MY_CURSOR

        IF @Id IS NULL
            SET @Continue = 0
    COMMIT 

    WAITFOR DELAY '00:00:00:90';
    SET @Continue = @Continue - 1
END


When running both simultaneously this is the output:



The batches are gone, and I don't have more 5 and 10 together. For example, thread 88 has a random number of continuous rows processed)

Now the questions:


Is ok to use (READPAST, UPDLOCK) where inUse = 0 to read mutually exclusive records?
If 1 is true, why it starts good and then the batches are gone...?
Anyway, It seems the threads are not overwriting each other as there is no row with Thread 1 and Tread 2 with same Thead id. Is this code good to  read mutually exclusive records?


Thanks!
Yes, it's ok to use (READPAST, UPDLOCK) where inUse = 0.


UPDLOCK hint will force SQL Server to use (U) locks instead of (S) locks and (U) locks are incompatible with each other, so the two threads will not be able to fetch the same row.

READPAST hint will force SQL Server to skip the locked rows rather than being blocked.


The batches are not "gone". They are just not always consist of records with consecutive IDs. 


To illustrate this , let's add a FetchTime column to the table and modify the relevant parts of the procedure:

      DECLARE MY_CURSOR CURSOR 
         LOCAL STATIC READ_ONLY FORWARD_ONLY
         FOR 
         SELECT top X Id,GETUTCDATE() as FetchTime 
         FROM Names WITH (READPAST, UPDLOCK)
         where inUse = 0
         order by 1


.
.
.

  OPEN MY_CURSOR
        FETCH NEXT FROM MY_CURSOR INTO @Id,@Fetchtime


.
.
.

 update Names set InUse = 1, Thread = @Thread,FetchTime=@FetchTime where id = @Id


All the rows in a batch will have the same value in the FetchTime column.

Now it is more evident that the batches are not "gone" : 




Yes. In fact, this approach is more efficient than synchronizing access to the table by implementing a critical section or using a mutex because the two threads can read the data in parallel without blocking each other.