1
\$\begingroup\$

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.

\$\endgroup\$
4
  • \$\begingroup\$ Shouldn't you specify the purpose of the IsLocked field? \$\endgroup\$
    – Innat3
    Nov 17, 2016 at 11:21
  • \$\begingroup\$ @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. \$\endgroup\$ Nov 17, 2016 at 11:58
  • \$\begingroup\$ 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 \$\endgroup\$
    – Innat3
    Nov 17, 2016 at 12:03
  • \$\begingroup\$ @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. \$\endgroup\$ Nov 17, 2016 at 12:20

1 Answer 1

1
\$\begingroup\$

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)
\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.