I have multiple instances of an application (C# console apps) which needs to pick a set of records from a table and mark them (Update AllocatedTo with instance name), so that no other instances will pick those again. Below is my stored procedure.CREATE PROCEDURE [wm_uc1].[GetAllocatedTickets]@LoadGenName varchar(MAX)ASBEGIN    SET NOCOUNT ON;    BEGIN TRY        BEGIN TRANSACTION            UPDATE [wm_uc1].[UK_HR_TicketProcessStatusMultiple] WITH (READCOMMITTEDLOCK) SET AllocatedTo = @LoadGenName            WHERE LoadGenStatus = 'Pending' AND AllocatedTo = (SELECT MIN(AllocatedTo) FROM [UK_HR_TicketProcessStatusMultiple])        COMMIT TRANSACTION        SELECT * FROM [SE84GADB].[wm_uc1].[UK_HR_TicketProcessStatusMultiple] WHERE LoadGenStatus = 'Pending' AND AllocatedTo = @LoadGenName    END TRY    BEGIN CATCH        ROLLBACK    END CATCHENDNote: AllocatedTo initially have a numeric value denoting the instance number. Once an instance picks it, AllocatedTo of that record is updated with the InstanceName. Each instance picks a set of records based on the minimum Instance number present in AllocatedTo column.I have used lock so that no other instance picks the same record. Is this approach going to work or it needs some modification?Please let me know for any clarification.
SQL Server: Lock rows on updating
sql;sql server