Dead-lock even after adding index

Question

I have a stored procedure with following statements in it

INSERT INTO dbo.[ResultItems] (PGM,GRP,PGMGRPSEQ,ITMID,ITMDESC,BRAND,PackSize,IsSelected,UserId)
    SELECT  SRIS.PGM,SRIS.GRP,SRIS.PGMGRPSEQ,SRIS.ITMID,SRIS.ITMDESC,SRIS.BRAND,SRIS.PackSize,SRIS.IsSelected,SRIS.UserId
    FROM @ItemResult SRIS
    LEFT OUTER JOIN [dbo].[ResultItems] SRI (NOLOCK)
        ON SRI.ITMID = SRIS.ITMID
        AND SRI.PGM = SRIS.PGM
        AND SRI.GRP = SRIS.GRP
        AND SRI.PGMGRPSEQ = SRIS.PGMGRPSEQ
        AND SRI.UserId=SRIS.UserId 
    WHERE SRI.ITMID IS NULL  ----logic to avoid duplicate
    GROUP BY SRIS.PGM,SRIS.GRP,SRIS.PGMGRPSEQ,SRIS.ITMID,SRIS.ITMDESC,SRIS.BRAND,SRIS.PackSize,SRIS.IsSelected,SRIS.UserId


UPDATE SRI
SET SRI.IsSelected = 1
FROM @ItemResult IST 
INNER JOIN [dbo].ResultItems SRI (NOLOCK)
ON SRI.PGM = IST.[PGM]
    AND SRI.GRP = IST.GRP
    AND SRI.PGMGRPSEQ = IST.PGMGRPSEQ
    AND SRI.ITMID = IST.ITMID
    AND SRI.UserId=IST.UserId
WHERE SRI.UserId=@UserId

I have following index on the ResultItems table

IF NOT EXISTS(select 1 from sys.sysindexes where name = 'IX_RESULTITEMS_USERID')
BEGIN
    CREATE NONCLUSTERED INDEX IX_RESULTITEMS_USERID
    ON [dbo].[ResultItems] ([UserId])
END
GO

Five concurrent users are calling this SP. The update statement has a filter condition on UserId. Each user will execute the sp with their own userID only. That is why I have created an index on UserId column.

The expectation was the index will help to avoid table scan and there will be no dead-lock (since each user is looking for their own records)… But 1 out of 10 tests is causing a dead-lock.

I believe this is because the escalation to a table scan when there is huge data (more than 20000 records by each user).

What is the best way to avoid deadlock here?

TABLE and INDEXES

CREATE TABLE [dbo].[ResultItems](
    [SRIID] [int] IDENTITY(1,1) NOT NULL,
    [PGM] [nvarchar](50) NULL,
    [GRP] [nvarchar](50) NULL,
    [PGMGRPSEQ] [nvarchar](50) NULL,
    [ITMID] [nvarchar](18) NULL,
    [ITMDESC] [nvarchar](255) NULL,
    [BRAND] [nchar](40) NULL,
    [PackSize] [nvarchar](max) NULL,
    [IsSelected] [bit] NULL,
    [UserId] [nvarchar](50) NULL,
 CONSTRAINT [PK_SEARCH_RESULT_ITEMS] PRIMARY KEY CLUSTERED 
(
    [SRIID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_PGM_GRP_PGMGRPSEQ_ITMID_UserId] ON [dbo].[ResultItems]
(
    [PGM] ASC,
    [GRP] ASC,
    [PGMGRPSEQ] ASC,
    [ITMID] ASC,
    [UserId] 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) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_RESULTITEMS_USERID] ON [dbo].[ResultItems]
(
    [UserId] 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) ON [PRIMARY]
GO

REFERENCES

What is the isolation level? Default? Have you considered adding an index that will actually allow a seek (e.g. one that covers all of the columns in the JOIN clause)? You could also add an additional filter `AND SRI.IsSelected <> 1` because there is no reason matching rows that have already been updated. — Aaron Bertrand, Dec 05 '13 at 16:04
Eek, why do your update/insert statements have NOLOCK specified on the affected table? — Aaron Bertrand, Dec 05 '13 at 16:17
@AaronBertrand I have resolved the issue and referred your blog in my answer for `Merge` — LCJ, Dec 09 '13 at 06:30

score 1 · Answer 1 · answered Dec 05 '13 at 21:58

Instead of INSERT then UPDATE, use MERGE. Also, get rid of the NOLOCK

MERGE INTO dbo.[ResultItems] SRI
USING @ItemResult SRIS ON (
      SRI.ITMID = SRIS.ITMID
  AND SRI.PGM = SRIS.PGM
  AND SRI.GRP = SRIS.GRP
  AND SRI.PGMGRPSEQ = SRIS.PGMGRPSEQ
  AND SRI.UserId=SRIS.UserId 
)
WHEN MATCHED THEN UPDATE
  SET SRI.IsSelected = 1
WHEN NOT MATCHED THEN
  INSERT (     PGM,     GRP,     PGMGRPSEQ,     ITMID,     ITMDESC,     BRAND,     PackSize,     IsSelected,     UserId)
  VALUES (SRIS.PGM,SRIS.GRP,SRIS.PGMGRPSEQ,SRIS.ITMID,SRIS.ITMDESC,SRIS.BRAND,SRIS.PackSize,SRIS.IsSelected,SRIS.UserId);

score 1 · Accepted Answer · edited May 23 '17 at 12:09

I resolved the issue by re-writing the stored procedure logic to avoid the update the statement. The update happens on a temporary table before the insert.

Other thoughts are using Dead-lock graph for analysis and using MERGE for query.

OTHER REFERENCES

MERGE BUGS

Dead-lock even after adding index

2 Answers2