Deadlock between INSERT and SELECT SPs

Question

I am having trouble with an application where I get a deadlock that I thought could never happen. I have two stored procedures. One of them just reads parts of the table and one of them adds a row to the table or updates an existing row if there is a primary key violation.

I have read about stored procedures can get a shared read lock and that if both stored procedures will elevate to an exclusive lock there will be a deadlock. I cannot see that happen here since the GetList stored procedure just reads and never writes. Is there any other possible cause of these stored procedures to deadlock?

The following report on the deadlock:

<?xml version="1.0" encoding="UTF-8"?>
<deadlock-list>
   <deadlock victim="process3e37f62c8">
      <process-list>
         <process id="process3e37f62c8" taskpriority="0" logused="0" waitresource="KEY: 7:72057594152681472 (de2613e7782e)" waittime="2535" ownerId="7732055781" transactionname="SELECT" lasttranstarted="2014-03-17T17:48:31.437" XDES="0x1298bc1c0" lockMode="S" schedulerid="11" kpid="21372" status="suspended" spid="121" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-03-17T17:48:31.437" lastbatchcompleted="2014-03-17T17:48:31.437" clientapp=".Net SqlClient Data Provider" hostname="STO2AP07" hostpid="4908" loginname="(hidden)" isolationlevel="read committed (2)" xactid="7732055781" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
               <frame procname="(hidden).dbo.GetList" line="18" stmtstart="1060" stmtend="1942" sqlhandle="0x03000700072cfd1b7b8be800cfa200000100000000000000">
                   SELECT 
                        TOP (@NumRows)
                        [T1].[Id] , 
                        [T1].[A] , 
                        [T1].[B] , 
                        [T1].[C] , 
                        [T1].[D] , 
                        [T1].[E] , 
                        [T1].[F] , 
                        [T1].[G] , 
                        [T1].[H] , 
                        [T1].[I] , 
                        [T1].[J] 
                    FROM [Item] AS [T1] where [J] > @LastUpdatedDateTime ORDER BY [T1].[LastUpdatedField] ASC
                </frame>
            </executionStack>
            <inputbuf>Proc [Database Id = 7 Object Id = 469576711]</inputbuf>
         </process>
         <process id="process5a514c8" taskpriority="0" logused="244" waitresource="KEY: 7:72057594152615936 (16f70bd264f5)" waittime="2535" ownerId="7732055725" transactionname="user_transaction" lasttranstarted="2014-03-17T17:48:31.437" XDES="0x6602dfa00" lockMode="X" schedulerid="13" kpid="21196" status="suspended" spid="267" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-03-17T17:48:31.437" lastbatchcompleted="2014-03-17T17:48:31.437" clientapp=".Net SqlClient Data Provider" hostname="STO2AP07" hostpid="4908" loginname="(hidden)" isolationlevel="read committed (2)" xactid="7732055725" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
               <frame procname="(hidden).dbo.AddToList" line="52" stmtstart="2484" stmtend="3294" sqlhandle="0x030007005cbf2019598be800cfa200000100000000000000">
                    UPDATE [Item] 
                    SET 
                        [A] = @A , 
                        [B] = @B , 
                        [C] = @C , 
                        [D] = @D , 
                        [E] = @E , 
                        [F] = @F , 
                        [G] = @G , 
                        [H] = @H , 
                        [I] = @I ,
                        [J] = @J 
                    WHERE [Id] = @Id
                </frame>
            </executionStack>
            <inputbuf>Proc [Database Id = 7 Object Id = 421576540]</inputbuf>
         </process>
      </process-list>
      <resource-list>
         <keylock hobtid="72057594152681472" dbid="7" objectname="(hidden).dbo.Item" indexname="PK_Item_1" id="lock47c381b80" mode="X" associatedObjectId="72057594152681472">
            <owner-list>
               <owner id="process5a514c8" mode="X" />
            </owner-list>
            <waiter-list>
               <waiter id="process3e37f62c8" mode="S" requestType="wait" />
            </waiter-list>
         </keylock>
         <keylock hobtid="72057594152615936" dbid="7" objectname="(hidden).dbo.Item" indexname="IX_LastUpdatedField" id="lock402a95800" mode="S" associatedObjectId="72057594152615936">
            <owner-list>
               <owner id="process3e37f62c8" mode="S" />
            </owner-list>
            <waiter-list>
               <waiter id="process5a514c8" mode="X" requestType="wait" />
            </waiter-list>
         </keylock>
      </resource-list>
   </deadlock>
</deadlock-list>

The following is the stored procedure named GetList:

ALTER PROCEDURE [dbo].[GetList]
    @LastUpdatedDateTime datetime,
    @NumRows int
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@NumRows)
           [T1].[Id]
         , [T1].[A]
         , [T1].[B]
         , [T1].[C]
         , [T1].[D]
         , [T1].[E]
         , [T1].[F]
         , [T1].[G]
         , [T1].[H]
         , [T1].[I]
         , [T1].[J]
      FROM [Item] AS [T1] where J >= @LastUpdatedDateTime
  ORDER BY [T1].[J] ASC
END

The following is the stored procedure named AddToList:

ALTER PROCEDURE [dbo].[AddToList]
    @Id int,
    @A varchar(30),
    @B decimal(18,2),
    @V varchar(30),
    @D varchar(512),
    @E datetime,
    @F datetime,
    @G bit,
    @H int,
    @I int
    @J datetime,
AS
IF NOT EXISTS(SELECT Id FROM Item WHERE [Id] = @Id)
    BEGIN
        SET NOCOUNT ON;
        INSERT INTO [Item] 
                  ( [Id] 
                  , [A]
                  , [B]
                  , [C]
                  , [D]
                  , [E]
                  , [F]
                  , [G]
                  , [H]
                  , [I]
                  , [J]
                  )
             VALUES
                  ( @Id
                  , @A
                  , @B
                  , @C
                  , @D
                  , @E
                  , @F
                  , @G
                  , @H
                  , @I
                  , @J
                  )
    END
ELSE
    BEGIN
        UPDATE [Item] SET
                   [A] = @A
                  , [B] = @B
                  , [C] = @C
                  , [D] = @D
                  , [E] = @E
                  , [F] = @F
                  , [G] = @G
                  , [H] = @H
                  , [I] = @I
                  , [J] = @J
        WHERE [Id] = @Id
    END

Answer 1

The select does this:

1. Read from LastUpdatedDateTime index (S-lock)
2. Look up the correspnding CI row (S-lock)

The update does this:

1. Write to CI (X-lock)
2. Write to LastUpdatedDateTime index (X-lock)

They access two resources incompatibly in reverse order. Deadlock.

Is the reader running under an isolation level higher than READ COMMITTED? READ COMMITTED should release locks immediately after reading the locked row.

If you can make the reading transaction use a snapshot isolation model the deadlock will go away in a simple, reliable and permanent manner.

If that is not possible use READ COMMITTED isolation level.

If that is not possible you'll either have to mess with locking hints (hard to maintain) or implement deadlock retry logic.