I have 5 (or more) instances of an application running on same or different machines. Each of these application will pass a comma separated string of UserIDs and the app should pick up a unique unused record (UserID) from a DB table, matching the user IDs as passed through the SP.So if App-1 picks User-3 and marks it as 'locked' then User-3 will not be available for other apps until, App-1 remarks it as 'unlocked'.I have written a small SP to mark the first 'unlocked' user as 'locked' in the DB table and return the UserID to the calling app.SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:      Souvik Ghosh-- Create date: 12-Nov-2016-- Description: Lock a user ID and return it-- =============================================ALTER PROCEDURE LockHROSAPID@UserIDs varchar(max)ASBEGIN    SET NOCOUNT ON;    BEGIN TRY        BEGIN TRANSACTION            UPDATE [wm_uc1].[UK_HR_HROSAPUserIDs] SET IsLocked = 1            OUTPUT Inserted.HROSAPUID            WHERE ID = (SELECT TOP 1 ID FROM [wm_uc1].[UK_HR_HROSAPUserIDs]             WHERE HROSAPUID IN (@UserIDs) AND IsLocked IS NULL OR IsLocked <> 1)        COMMIT TRANSACTION    END TRY    BEGIN CATCH        ROLLBACK    END CATCH    RETURNENDGOI have tested it and it seems to work fine. But, I want to have a second opinion about this.
Read uncommited rows and lock row on update
sql;sql server
Some problems here OR IsLocked <> 1 will be process lastSELECT TOP 1 ID is non-deterministic  I hope HROSAPUID has a unique constraint   _SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:      Souvik Ghosh-- Create date: 12-Nov-2016-- Description: Lock a user ID and return it-- =============================================ALTER PROCEDURE LockHROSAPID@UserIDs varchar(max)ASBEGIN    SET NOCOUNT ON;    BEGIN TRY        BEGIN TRANSACTION            UPDATE [wm_uc1].[UK_HR_HROSAPUserIDs]             SET IsLocked = 1            OUTPUT Inserted.HROSAPUID            WHERE ID = ( SELECT TOP 1 ID                            FROM [wm_uc1].[UK_HR_HROSAPUserIDs]                           WHERE HROSAPUID IN (@UserIDs)                             AND IsLocked IS NULL                              OR IsLocked <> 1 )        COMMIT TRANSACTION    END TRY    BEGIN CATCH        ROLLBACK    END CATCH    RETURNENDGOmaybe something more like thisa single statement is a transaction    update top (1) [Table_1]   set bbb = 'no'   output inserted.pk   from [Table_1]   where ( bbb <> 'no' or bbb is null )  and pk in (13,14,15)