deadlock with repeatable read isolation level

Trying to understand deadlock that is occurring with repeatable read isolation level when the stored proc is invoked.

This is the deadlock xml :

  
 
  
   
    
     
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     
    
    
Proc [Database Id = 8 Object Id = 1688915952]    
   
   
    
     
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     
    
    
Proc [Database Id = 8 Object Id = 1688915952]    
   
  
  
   
    
     
    
    
     
    
   
   
    
     
    
    
     
    
   
  
 

the create table with relevant indices :

CREATE TABLE [schema].[TableName](
    [Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [SomeId] [int] NOT NULL,
    [Userid] [int] NOT NULL,
    [UpdateTime] [datetime] NOT NULL,
    [RecordStatus] [tinyint] NOT NULL,
 CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--- 2 indexes that are part of deadlock 
IX_TableName_SomeId_Id = filtered Index - SomeId, Id (include status) where status = 0
IX_TableName_Id_Status_userid_UpdateTime - non clustered index - Id include(status, userid, updatetime)

The SP call is :

• isolation level repeatable read

• starts transaction

• does a select from the base table and inserts the rows that will be affected into a temp variable.

• executes another SP

• updates the base table.

• merges the data with some business logic

• if no error, commits transaction

The base table has trigger that does insert and updates to audit table for tracking change

Here's my hypothesis:

Let's call the deadlocking calls of the proc P1 and P2.

Both P1 and P2 use the filtered index to locate rows. Because this is Repeatable Read without other hints, they use S locks, and do not release them.
They happen to include a common row. We'll call its location in the filtered index Row A.

At this point both P1 and P2 have S locks on Row A.

Now, the updates begin.

P1 reads from the other index to locate rows that need to be updated. It uses U locks as it reads, and places a U lock on Row B, which actually represents the same row as A, just in a different index.

Now P1 has an S lock on Row A, and a U lock on Row B.

P1 sees that it has a row to modify, and begins the process. It must also delete the row from the filtered index, which will require an X lock.

P1 tries to convert its S lock to an X lock, but is blocked by P2's S lock.

P2 begins its update. It tries to acquire a U lock on Row B, but is blocked by P1's U lock. Deadlock.

I believe that using the UPDLOCK hint on the initial select should allow you to avoid the deadlock.

StackExchange Database Administrators Q#282786, answer score: 6