Read uncommited rows and lock row on update

Question

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 <> 1)
 COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
 ROLLBACK
 END CATCH
 RETURN
END
GO

Table snapshot

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

• Innat3: Shouldn't you specify the purpose of the IsLocked field?
• Souvik Ghosh: @Innat3 IsLocked filed gets updated by the calling app while trying to get an unlocked UserID so that the next calling app will not use it.
• Innat3: yes, that I can see, but it is unclear WHAT the PURPOSE of that field is. WHY do you want to UPDATE it? I am trying to understand the reason why you set things up like this
• Souvik Ghosh: @Innat3 I need some kind of marking (flag value) to denote the record is being locked (in use) and avoid it being reused by another app. IsLocked will be set to 1 when that particular User ID is in use by the calling app and will be updated back to 0 when the calling app is done with it, so that it can be used again when required. There is no order or sequence of updating this. Any random available UserID can be picked and released anytime by any of the calling apps.

Answer 1

Some problems here

• OR IsLocked <> 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 <> 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 <> 'no' or bbb is null )
 and pk in (13,14,15)