A stored procedure designed to generate sequential identifiers encountered deadlocks under concurrent load.

CREATE PROCEDURE [dbo].[GetNextSequence]
    @sequenceKey varchar(50),
    @result int OUTPUT
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION
            SELECT @result = CurrentVal 
            FROM sequence_table WITH (XLOCK, ROWLOCK) 
            WHERE SequenceKey = @sequenceKey;
            
            UPDATE sequence_table 
            SET CurrentVal = (CurrentVal + 1) 
            WHERE SequenceKey = @sequenceKey;
        COMMIT
    END TRY
    BEGIN CATCH
        ROLLBACK
    END CATCH
END

The table definition:

CREATE TABLE [dbo].[sequence_table](
    [RecordId] [INT] IDENTITY(1,1) NOT NULL,
    [SequenceKey] [NVARCHAR](255) NOT NULL,
    [CurrentVal] [INT] NOT NULL,
    CONSTRAINT [PK_sequence_table] PRIMARY KEY CLUSTERED 
    (
        [RecordId] ASC
    )
) ON [PRIMARY]

The SequenceKey column lacks any index. Under high concurrency, the UPDATE statement cannot efficiently locate rows by SequenceKey and must traverse the clustered endex on RecordId instead. This forces SQL Server to lock the entire index page, triggering deadlock scenarios.

Deadlock graph from production:

<deadlock-list>
 <deadlock victim="process20ec00bc8">
  <process-list>
   <process id="process20ec00bc8" lockMode="U" ...>
    <executionStack>
     <frame procname="database.dbo.GetNextSequence" line="11">
update sequence_table set CurrentVal=(CurrentVal+1) WHERE SequenceKey=@sequenceKey;
     </frame>
   </process>
   <process id="process9412988" lockMode="X" ...>
    <executionStack>
     <frame procname="database.dbo.GetNextSequence" line="8">
select @result=CurrentVal from sequence_table with(xlock,rowlock)  where SequenceKey=@sequenceKey;
     </frame>
   </process>
  </process-list>
  <resource-list>
   <keylock id="lock10fc9ea00" mode="X" associatedObjectId="72057594111787008">
    <owner-list>
     <owner id="process9412988" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process20ec00bc8" mode="U"/>
    </waiter-list>
   </keylock>
   <keylock id="lock12e75a280" mode="X" associatedObjectId="72057594111787008">
    <owner-list>
     <owner id="process20ec00bc8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process9412988" mode="X"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

The victim process holds an exclusive lock on one key while waiting for an update lock held by another proces that simultaneously waits for an exclusive lock held by the first—creating a cyclic dependency.

Resolution: Replace the clustered index on RecordId with a clustered index on SequenceKey. This allows the UPDATE statement to locate target rows directly without traversing the primary key index, eliminating the lock escalation that caused the deadlock.

CREATE TABLE [dbo].[sequence_table](
    [RecordId] [INT] IDENTITY(1,1) NOT NULL,
    [SequenceKey] [NVARCHAR](255) NOT NULL,
    [CurrentVal] [INT] NOT NULL,
    CONSTRAINT [PK_sequence_table] PRIMARY KEY CLUSTERED 
    (
        [SequenceKey] ASC
    )
) ON [PRIMARY]

After this change, the lock contention resolved completely in the production environment.