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) AS BEGIN 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 CATCH END
Note: 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.