How to select Rows not on HOLDLOCK by another transaction

Question

I am writing a Multi-instance background service, where I would like to process the recent data first, for this I am selecting top 10 records in a batch and marking them from a instance specific id, and then reading them for processing,

This process runs in multiple instances, thus there is a chance of records to be picked by more than one instance and I could generate duplicate results.

To avoid this I would like instance to pick records which are not already put on HOLDLOCK

My current update and select statement looks like this:

ALTER PROC dbo.GetRecordsForInstance @RecordCount INT = 10, @InstanceId varchar(max)
AS
BEGIN
    update t with (ROWLOCK) set t.ProcessingStatus =@InstanceId, t.IsProcessing = 1
    from SomeTable t 
    join 
    (
        SELECT TOP (@RecordCount) Col1, Col2 
        FROM dbo. SomeTable with (ROWLOCK, HOLDLOCK)
        WHERE IsProcessing = 0 and IsCompleted = 0
        ORDER BY LastModifiedOn
    ) t1 on t.Id = t1.Id
    
    SELECT * FROM SomeTable with (ROWLOCK) where ProcessingStatus = @InstanceId
END;

Answer 1

You can use the READPAST hint to skip already-locked rows.

You cannot use it with SERIALIZABLE though, you would need to downgrade the isolation level to REPEATABLEREAD. This is not an issue in this case, as the extra guarantee is only regarding new data.

Further improvements:

• You can update the t1 derived table directly, there is no need to rejoin. Just select out all columns from that inner table.
• You can combine the UPDATE and SELECT using OUTPUT.
• To prevent deadlocks in this type of query, you must have an index on your table over the columns which you are querying by (IsProcessing, IsCompleted, LastModifiedOn) preferably with INCLUDE columns also.

CREATE OR ALTER PROC dbo.GetRecordsForInstance
  @RecordCount INT = 10,
  @InstanceId varchar(max)
AS

UPDATE t
SET
  ProcessingStatus = @InstanceId,
  IsProcessing = 1
OUTPUT inserted.*
FROM (
    SELECT TOP (@RecordCount)
      *
    FROM dbo.SomeTable t WITH (ROWLOCK, REPEATABLEREAD, READPAST)
    WHERE IsProcessing = 0 and IsCompleted = 0
    ORDER BY LastModifiedOn
) t;