Sql-server – deadlock with repeatable read isolation level

deadlocksql serversql server 2014

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

This is the deadlock xml :

    <TextData>
  <deadlock-list>
 <deadlock victim="process6fac029848">
  <process-list>
   <process id="process6fac029848" taskpriority="0" logused="0" waitresource="KEY: 8:72078600230076416 (d3c590b351cd)" waittime="1171" 
            ownerId="26116633497" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.317" XDES="0x12553cbe3b0" 
            lockMode="U" schedulerid="3" kpid="19776" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="2" 
            lastbatchstarted="2021-01-07T10:35:38.310" lastbatchcompleted="2021-01-07T10:35:38.310" lastattention="1900-01-01T00:00:00.310" 
            clientapp=".Net SqlClient Data Provider" hostname="web01" hostpid="34852" loginname="someuser" 
            isolationlevel="repeatable read (3)" xactid="26116633497" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" 
            clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" 
            sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
   <process id="process6fab017088" taskpriority="0" logused="284" waitresource="KEY: 8:72064801531101184 (4b819525e255)" waittime="1283" 
   ownerId="26116632474" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.290" XDES="0x44037ec3b0" 
   lockMode="X" schedulerid="25" kpid="25152" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" 
   lastbatchstarted="2021-01-07T10:35:38.287" lastbatchcompleted="2021-01-07T10:35:38.283" lastattention="1900-01-01T00:00:00.283" 
   clientapp=".Net SqlClient Data Provider" hostname="web02" hostpid="57552" loginname="someuser" 
   isolationlevel="repeatable read (3)" xactid="26116632474" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
        SET
            Status = 1 /* STATUS_DELETED */,
            userid = @id,
            UpdateTime = GETUTCDATE()
        FROM @oldatt oa
        WHERE [TableName].[Id] = oa.Id     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72078600230076416" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_Id_Status_userid_UpdateTime" id="lock287c93a100" mode="U" associatedObjectId="72078600230076416">
    <owner-list>
     <owner id="process6fab017088" mode="U" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fac029848" mode="U" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72064801531101184" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_SomeId_Id" id="lock55fc471880" mode="S" associatedObjectId="72064801531101184">
    <owner-list>
     <owner id="process6fac029848" mode="S" />
    </owner-list>
    <waiter-list>
     <waiter id="process6fab017088" mode="X" requestType="convert" />
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
</TextData>

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. <<— this is where the deadlock happens
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 changes.

I am just trying to wrap my head around why I am getting too many deadlocks.

Update :

declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid

Stored procedure flow control :

variables 
    @id 
    @someid 
    
begin 
    set nocount on
    set transaction isolation level repeatable read
    set xact_abort on

    begin tran delete
    begin try

    declare @oldtable(
        Id bigint,
        someId int
        PRIMARY key clustered (id)
    )
    insert into @oldtable (
        id,
        someid
    )
    select id, someid
    from [schema].[TableName]
    where id = @id and status = 0 and someid = @someid 

    some SP runs

    -- update statememt that causes deadlock 
    -- it basically sets status = 1 -- deleted (soft delete)

    -- merge data into tables using CTE and MERGE 
    END TRY

    begin catch 
        -- error handling 
        -- roll back tran 
    end catch

    if @@trancount > 0
    begin
        commit tran delete
    END

END

Best Answer

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.

Related Solutions

Sql-server – repeatable read and nolock

The table hint in the query takes precedence, as I would have expected.

Here's my test script:

CREATE TABLE t1 (a int);

INSERT INTO t1(a) SELECT TOP 1000 message_id FROM sys.messages;


SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

    SELECT * FROM t1 WITH(NOLOCK); /* Try it with and without the hint */

    EXEC sp_lock;

ROLLBACK;

With the NOLOCK hint included, no locks are taken by the SELECT statement.

In other words, setting the isolation level on the transaction makes that isolation level the default within the transaction, but that default can still be overridden by table hints, as I demonstrated.

Sql-server – Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

It is terrible, that you learned it that way (sorry!).

READ UNCOMMITTED let's you read every row, yes. Even those who are currently used in an INSERT, UPDATE, DELETE operation. This is very useful if you need to take a quick look at some Data or in mission critical SELECT-Statements where a block would be very harmful.

In fact, you risk your integrity. It may occur that you read a row, which is currently used to be deleted or in a change. It can also appear that you read a wrong value. This may be really uncommon, but it can happen. What do I mean with that? Well, think of a row which is very broad (it has many columns with many long nvarchar-columns). An update occur on this row and sets new values. In rare cases it can happen to you, that you read only a half row. Another thing can happen for example, if a user changes his login values. He changes his mail + password. The mail is already set, but the password isn't. This way you have a inconsistent state.

I would suggest to forget about READ UNCOMMITTED. Just use it where it's really needed.

Another alternative for you can be to enable the READ_COMMITTED_SNAPSHOT database option - therefor you can use READ COMMITTED SNAPSHOT due to the enabled row versioning in your tempdb. This way you just read another (older) version of a row. This won't block your Queries. But it may occur that you read an old value too, but an consistent old value.

Another idea can be WITH(READPAST) instead of WITH(NOLOCK). You will read the old state of the table (a bit like in the SNAPSHOT ISOLATION), but you'll skip all currently locked rows instead.

Best Answer

Related Solutions

Sql-server – repeatable read and nolock

Sql-server – Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Related Question