1
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ You probably want repeatable and the select inside the transaction. @LoadGenName as a varchar has some problems. 11 sorts before 2. Not sure what you are trying to do here and you may need another design. \$\endgroup\$ – paparazzo Dec 2 '16 at 12:36
  • \$\begingroup\$ @Paparazzi '@LoadGenName' needs to hold text value. It is the combination of machine name and physical path of the executing directory. \$\endgroup\$ – Souvik Ghosh Dec 4 '16 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.