Receiving several deadlocks with simple queries when ran concurrently from different nodes We have a web application with three nodes that sit behind a load balancer that handles traffic in a round robin fashion (no sticky sessions). When we receive several requests coming in at the same time we will start to see several deadlocks occurring on one of our queries that obtains a session id. We have a table called uniqueids that we populate with around 250,000 rows that contains sequential ids specific to our application. We use the stored procedure getnextsessionid to select the next unused id and then update the used column in that row to show it has been used. Is there a different locking method we can use to ensure no two people receive the same id and prevent deadlocks?

ALTER    PROCEDURE [dbo].[getnextsessionID] 

    (
    @id varchar(15) OUT
    )

AS

    SET NOCOUNT ON
    SET LOCK_TIMEOUT 8000

    BEGIN TRAN
        SELECT TOP 1 @id = RTRIM(LTRIM(sessionID))
        FROM uniqueids WITH (HOLDLOCK,ROWLOCK,INDEX = session_used)
        WHERE used = 0

        UPDATE uniqueids
        SET used = 1,dt = GetDate()
        WHERE sessionID = @id

    COMMIT TRAN

--uniqueids table

    CREATE TABLE [dbo].[uniqueids](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [sessionID] [varchar](15) NOT NULL,
        [used] [bit] NOT NULL,
        [dt] [smalldatetime] NULL,
     CONSTRAINT [PK_uniqueids_id] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY],
     CONSTRAINT [U_sessionID] UNIQUE NONCLUSTERED 
    (
        [sessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    ) ON [PRIMARY]


    CREATE NONCLUSTERED INDEX [session_used] ON [dbo].[uniqueids]
    (
        [used] ASC,
        [sessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[uniqueids] ADD  CONSTRAINT [DF_uniqueids_used1]  DEFAULT ((0)) FOR [used]
I would use an UPDATE with an OUTPUT instead of two completely separate statements. 

ALTER PROCEDURE dbo.getnextsessionID
  @id varchar(15) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @t TABLE(id varchar(15));

  ;WITH NextID AS 
  (
    SELECT TOP (1) sessionID,used
      FROM dbo.uniqueids 
      WHERE used = 0
      ORDER BY ID
  )
  UPDATE NextID SET used = 1 
    OUTPUT deleted.sessionID INTO @t;

  SELECT @id = RTRIM(LTRIM(id)) FROM @t;
END
GO


Also if the table is large (and as the set where used = 1 grows), I might suggest a filtered index instead, simply because it's much cheaper to maintain:

CREATE INDEX session_used 
  ON dbo.uniqueids(ID, sessionID, used) 
  WHERE used = 0
  WITH (DROP_EXISTING = ON);