Deadlocks in Update/Insert procedure

Question

I have an UPDATE and INSERT stored procedure for a table which is getting executed multiple times to update rows (once every 5 seconds). This procedure is used in multiple procedures so the execution count is high, and I am getting deadlock issues on this procedure even though the table has proper INDEXING and table level hints are placed.

I have tried lots of tweaks as per blogs but no luck in reducing the deadlock count, need help in understanding what I am doing wrong. The logic is to fetch MAX(ID) from the table and Update the records or Insert if not present. The table contains at most 5000 records.

Here is the table structure with index and the stored procedure:

CREATE TABLE [dbo].[TBL_ABC]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [NotificationRequest] [nvarchar](250) NULL,
    [ID_OrgLevelValue] [int] NULL,
    [CreatedDate] [datetime] NULL,
    [ID_Control_area] [int] NULL,
    [NotificationMessage] [nvarchar](1000) NULL,
    CONSTRAINT [PK_TBL_ABC] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TBL_ABC] WITH CHECK 
    ADD CONSTRAINT [FK_TBL_ABC_Mast_OrgLevelValue] 
        FOREIGN KEY([ID_OrgLevelValue])
        REFERENCES [dbo].[Mast_OrgLevelValue] ([ID])
GO

ALTER TABLE [dbo].[TBL_ABC] WITH CHECK 
    ADD CONSTRAINT [FK_ID_Control_area_TBL_ABC] 
        FOREIGN KEY([ID_Control_area])
        REFERENCES [dbo].[Mast_Control_Area] ([Id])
GO

CREATE NONCLUSTERED INDEX [IX_FK_Mast_OrgLevelValue] 
ON [dbo].[TBL_ABC] ([ID_OrgLevelValue] ASC,
                    [NotificationRequest] 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, 
          OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[usp_AddLineNotification]
    @ID_ORGLEVELVALUE INT,
    @ReuestType NVARCHAR(250),
    @NotificationMessage NVARCHAR(2000)=NULL
AS
BEGIN
    BEGIN TRANSACTION AddlineNotification ;
                   
    UPDATE TBL_ABC
    SET CreatedDate = GETUTCDATE(),       
        NotificationMessage = @NotificationMessage
    WHERE ID = (SELECT MAX(ID) 
                FROM TBL_ABC WITH (UPDLOCK) 
                WHERE NotificationRequest = @ReuestType 
                  AND ID_OrgLevelValue = @ID_ORGLEVELVALUE)
                    
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO TBL_ABC (NotificationRequest, ID_OrgLevelValue, CreatedDate, NotificationMessage) 
            SELECT  
                @RequestType, @ID_ORGLEVELVALUE, GETUTCDATE(), @NotificationMessage
    END

    COMMIT TRAN;
END

Below is the Deadlock XML for further understanding this issue.

<deadlock>
  <victim-list>
    <victimProcess id="process26f17535468" />
  </victim-list>
  <process-list>
    <process id="process26f17535468" taskpriority="0" logused="260" waitresource="KEY: 5:72057595550236672 (f8a18c219a4d)" waittime="1238" ownerId="3563795785" transactionname="AddlineNotification" lasttranstarted="2024-06-18T18:24:43.583" XDES="0x26fe9750460" lockMode="RangeS-U" schedulerid="1" kpid="62652" status="suspended" spid="57" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-06-18T18:24:41.377" lastbatchcompleted="2024-06-18T18:24:41.370" lastattention="1900-01-01T00:00:00.370" clientapp="EntityFramework" hostname="A98SW127PSMS1AP" hostpid="62948" loginname="gxoupuser" isolationlevel="read committed (2)" xactid="3563795785" currentdb="5" currentdbname="GXOUpgrade_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="GXOUpgrade_LIVE.dbo.usp_AddLineNotification" line="13" stmtstart="776" stmtend="1300" sqlhandle="0x03000500e53503310e82830071b1000001000000000000000000000000000000000000000000000000000000">
UPDATE TBL_ABC
                    SET CreatedDate =GETUTCDATE (), NotificationMessage=@NotificationMessage
                    WHERE ID = ( SELECT MAX(ID) FROM TBL_ABC WITH (UPDLOCK) WHERE NotificationRequest = @ReuestType AND ID_OrgLevelValue=@ID_ORGLEVELVALUE    </frame>
        <frame procname="GXOUpgrade_LIVE.dbo.usp_Interface_WOUpdate" line="4451" stmtstart="273404" stmtend="273514" sqlhandle="0x03000500198ac41ee39aac0078b1000001000000000000000000000000000000000000000000000000000000">
EXEC usp_AddLineNotification @LineID
                                    ,'Losses    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 516196889]   </inputbuf>
    </process>
    <process id="process26feee55468" taskpriority="0" logused="107136" waitresource="KEY: 5:72057595550236672 (20b095273284)" waittime="1070" ownerId="3563785553" transactionname="user_transaction" lasttranstarted="2024-06-18T18:24:41.127" XDES="0x26e8a208460" lockMode="RangeS-U" schedulerid="1" kpid="36164" status="suspended" spid="81" sbid="2" ecid="0" priority="0" trancount="3" lastbatchstarted="2024-06-18T18:24:41.127" lastbatchcompleted="2024-06-18T18:24:41.127" lastattention="1900-01-01T00:00:00.127" clientapp="EntityFramework" hostname="A98SW127PSMS1AP" hostpid="28224" loginname="gxoupuser" isolationlevel="read committed (2)" xactid="3563785553" currentdb="5" currentdbname="GXOUpgrade_LIVE" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="GXOUpgrade_LIVE.dbo.usp_AddLineNotification" line="13" stmtstart="776" stmtend="1300" sqlhandle="0x03000500e53503310e82830071b1000001000000000000000000000000000000000000000000000000000000">
UPDATE TBL_ABC
                    SET CreatedDate =GETUTCDATE (), NotificationMessage=@NotificationMessage
                    WHERE ID = ( SELECT MAX(ID) FROM TBL_ABC WITH (UPDLOCK) WHERE NotificationRequest = @ReuestType AND ID_OrgLevelValue=@ID_ORGLEVELVALUE    </frame>
        <frame procname="GXOUpgrade_LIVE.dbo.usp_WorkOrder_Creation" line="940" stmtstart="92022" stmtend="92104" sqlhandle="0x03000500a01ea36c2a9cac0078b1000001000000000000000000000000000000000000000000000000000000">
EXEC usp_AddLineNotification  @LineID,'WO    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 1822629536]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057595550236672" dbid="5" objectname="GXOUpgrade_LIVE.sys.query_notification_2045927056" indexname="cidx" id="lock271878c4400" mode="RangeX-X" associatedObjectId="72057595550236672">
      <owner-list>
        <owner id="process26feee55468" mode="RangeX-X" />
      </owner-list>
      <waiter-list>
        <waiter id="process26f17535468" mode="RangeS-U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057595550236672" dbid="5" objectname="GXOUpgrade_LIVE.sys.query_notification_2045927056" indexname="cidx" id="lock2717ac11180" mode="RangeS-U" associatedObjectId="72057595550236672">
      <owner-list>
        <owner id="process26f17535468" mode="RangeS-U" />
      </owner-list>
      <waiter-list>
        <waiter id="process26feee55468" mode="RangeS-U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

I have tried placing the code in TRANSACTION and HOLDLOCK on the table.

Answer 1

Your queries are deadlocking on range locks, which can be a wide lock, but in theory a range lock can be avoided completely here, by ensuring you only lock a single row.

It seems some kind of query notification is involved, possibly using SqlDependency or similar. Exactly what query was passed for the notification is not clear, but it seems to be affected by the statements in your procedure. Whether or nor improving the locking in the procedure will help, I don't know, but you should do it anyway. We really need to see that other notification query.

Firstly, your query can be improved by removing the self-join, and instead updating the subquery directly.

CREATE PROCEDURE [dbo].[usp_AddLineNotification]
    @ID_ORGLEVELVALUE INT,
    @ReuestType NVARCHAR(250),
    @NotificationMessage NVARCHAR(2000)=NULL
AS

SET NOCOUNT, XACT_ABORT ON;   -- need XACT_ABORT for proper error handling

BEGIN TRAN;
                   
UPDATE t
    SET CreatedDate = GETUTCDATE(),       
        NotificationMessage = @NotificationMessage
    FROM (
        SELECT TOP (1) *
        FROM TBL_ABC WITH (UPDLOCK) 
        WHERE NotificationRequest = @ReuestType 
          AND ID_OrgLevelValue = @ID_ORGLEVELVALUE
        ORDER BY ID DESC
    ) t;
                    
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO TBL_ABC (NotificationRequest, ID_OrgLevelValue, CreatedDate, NotificationMessage) 
    VALUES (@RequestType, @ID_ORGLEVELVALUE, GETUTCDATE(), @NotificationMessage);
END;

COMMIT TRAN;

Secondly, you need to add ID to the ordering of the NC index to support this query properly. You also need to add the two columns you are modifying as INCLUDE columns.

CREATE NONCLUSTERED INDEX [IX_FK_Mast_OrgLevelValue] 
ON [dbo].[TBL_ABC]
(ID_OrgLevelValue, NotificationRequest, ID)
INCLUDE (CreatedDate, NotificationMessage)
    WITH (DROP_EXISTING = ON);