Read uncommited rows and lock row on update 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Souvik Ghosh
-- Create date: 12-Nov-2016
-- Description: Lock a user ID and return it
-- =============================================
ALTER PROCEDURE LockHROSAPID
@UserIDs varchar(max)
AS
BEGIN
    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 &lt;&gt; 1)
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK
    END CATCH
    RETURN
END
GO




I have tested it and it seems to work fine. But, I want to have a second opinion about this.
Some problems here 


OR IsLocked &lt;> 1 will be process last
SELECT TOP 1 ID is non-deterministic  
I hope HROSAPUID has a unique constraint   


_

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Souvik Ghosh
-- Create date: 12-Nov-2016
-- Description: Lock a user ID and return it
-- =============================================
ALTER PROCEDURE LockHROSAPID
@UserIDs varchar(max)
AS
BEGIN
    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 &lt;&gt; 1 )
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK
    END CATCH
    RETURN
END
GO


maybe something more like this
a single statement is a transaction  

  update top (1) [Table_1] 
  set bbb = 'no' 
  output inserted.pk 
  from [Table_1] 
  where ( bbb &lt;&gt; 'no' or bbb is null )
  and pk in (13,14,15)