I have a UPDATE and INSERT Stored procedure in table which is getting executed multiple times to update record (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.

Below is Table structure with Index and 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 @ReuestType,@ID_ORGLEVELVALUE,GETUTCDATE(),@NotificationMessage

             END

             COMMIT TRAN;
END

Below is the Deadlock XML for further understanding ths 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.