I have a UPDATE and INSERT query 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 the UPSERT procedure even though the table has proper INDEXING and table level hints are placed.

I have tried lots but no luck in reducing the deadlock.

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
)

CREATE NONCLUSTERED INDEX [TBL_ABC] ON [dbo].[Data_LineNotification]
(
    [ID_OrgLevelValue] ASC,
    [NotificationRequest] ASC
)

DECLARE @ID_ORGLEVELVALUE INT,
        @ReuestType NVARCHAR(250),
        @NotificationMessage NVARCHAR(2000)=NULL

UPDATE TBL_ABC
SET CreatedDate =GETUTCDATE (), NotificationMessage=NULL
WHERE ID = ( SELECT MAX(ID) FROM Data_LineNotification WITH (UPDLOCK) WHERE NotificationRequest = 'HU' AND ID_OrgLevelValue=47)
    --NotificationRequest = @ReuestType 
    -- AND ID_OrgLevelValue=@ID_ORGLEVELVALUE 

IF @@ROWCOUNT = 0
--IF ISNULL(@ID,0)=0 AND @ID_ORGLEVELVALUE IS NOT NULL
BEGIN

INSERT INTO TBL_ABC (NotificationRequest ,ID_OrgLevelValue,CreatedDate,NotificationMessage ) 
SELECT 'HU',47,GETUTCDATE(),NULL

END