Deadlock in MS SQL Server on update of same table

Question

I am getting too many issues with deadlock in my environment. Deadlock mainly happen when trying the upadate. I have installed SentryOne to know what happens with deadlock.. We are having very complex system and getting lot of deadlocks on same table.

My transaction level is repeatable Read and I am getting cycle Deadlocks on the same table. What are the steps i can take to avoid cyclic deadlocks. How do we make sure cyclic deadlock doesn't occur in code.

Below is showing key lock on Reservations indexname="IX_Reservations_ReservationId_OrganizationId. I do have 18k records in the resut set and do indexing seemed to be applied. But showing index scan on ReservationOrgananization index. Do you think case statement is the cause of index scan. I do many selects with case condition inside the transaction that uses insert, update delete

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[RowVersion] AS [RowVersion], 
[Extent1].[AdjustmentAmount] AS [AdjustmentAmount], 
[Extent1].[Comment] AS [Comment], 
[Extent1].[ReservationAdjustment_Reservation] AS [ReservationAdjustment_Reservation], 
[Extent1].[ReservationAdjustment_Promotion] AS [ReservationAdjustment_Promotion], 
[Extent1].[ReservationAdjustment_AdjustmentReason] AS [ReservationAdjustment_AdjustmentReason], 
[Extent1].[CreatedBy] AS [CreatedBy], 
[Extent1].[CreatedOn] AS [CreatedOn], 
[Extent1].[ReservationProductId] AS [ReservationProductId]
FROM  [dbo].[ReservationAdjustments] AS [Extent1]
INNER JOIN [dbo].[Reservations] AS [Extent2] ON [Extent1].[ReservationAdjustment_Reservation] = [Extent2].[Id]
WHERE 123 = (CASE WHEN ([Extent2].[OrganizationId] = @p__linq__0) THEN [Extent2].[Id] END)

<deadlock>
<victim-list>
    <victimProcess id="process23b4b08c8" />
</victim-list>
<process-list>
    <process id="process23b4b08c8" taskpriority="0" logused="0" waitresource="OBJECT: 6:30675207:0 " waittime="3311" ownerId="55794405" transactionname="user_transaction" lasttranstarted="2019-02-20T16:41:15.963" XDES="0x2a4125770" lockMode="IX" schedulerid="1" kpid="102820" status="suspended" spid="94" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-02-20T16:41:16.403" lastbatchcompleted="2019-02-20T16:41:16.390" lastattention="1900-01-01T00:00:00.390" clientapp=".Net SqlClient Data Provider" hostname="''"" ''" loginname="''"" isolationlevel="repeatable read (3)" xactid="55794405" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
        <frame procname="adhoc" line="1" stmtstart="234" stmtend="670" sqlhandle="0x0200000066d3ee34a3aa9027d9cf2157cf5cca17470f03dd0000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
    </executionStack>
    <inputbuf>
(@0 decimal(18,2),@1 decimal(18,2),@2 decimal(18,2),@3 bit,@4 nvarchar(255),@5 datetimeoffset(7),@6 int,@7 binary(8))update [dbo].[P]
set [TotalPrice] = @0, [PassengerTaxAndFees] = @1, [AgentCommission] = @2, [SupplierChangeExists] = @3, [ModifiedBy] = @4, [Modified] = @5
where (([Id] = @6) and ([RowVersion] = @7))
select [RowVersion]
from [dbo].[P]
where @@ROWCOUNT &gt; 0 and [Id] = @6   </inputbuf>
    </process>
    <process id="process294aedc28" taskpriority="0" logused="12548" waitresource="OBJECT: 6:30675207:0 " waittime="6588" ownerId="55792892" transactionname="user_transaction" lasttranstarted="2019-02-20T16:41:12.020" XDES="0x2c5e09770" lockMode="IX" schedulerid="2" kpid="49456" status="suspended" spid="80" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-02-20T16:41:13.127" lastbatchcompleted="2019-02-20T16:41:13.123" lastattention="1900-01-01T00:00:00.123" clientapp=".Net SqlClient Data Provider" hostname="''"" ''" loginname="''"" isolationlevel="repeatable read (3)" xactid="55792892" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
        <frame procname="adhoc" line="1" stmtstart="1062" stmtend="5572" sqlhandle="0x02000000328db70a915f43baef23378214e51d7e0cacc8c50000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
    </executionStack>
    <inputbuf>
(@0 datetime2(7),@1 datetime2(7),@2 int,@3 decimal(18,2),@4 bit,@5 decimal(18,2),@6 bit,@7 bit,@8 nvarchar(255),@9 int,@10 decimal(18,2),@11 nvarchar(255),@12 nvarchar(25),@13 nvarchar(max) ,@14 nvarchar(255),@15 decimal(18,2),@16 nvarchar(25),@17 datetime2(7),@18 nvarchar(max) ,@19 decimal(18,2),@20 bit,@21 int,@22 int,@23 bit,@24 bit,@25 bit,@26 bit,@27 bit,@28 bit,@29 nvarchar(25),@30 bit,@31 bit,@32 nvarchar(255),@33 datetimeoffset(7),@34 nvarchar(255),@35 datetimeoffset(7),@36 int,@37 int,@38 int,@39 int,@40 int,@41 int)insert [dbo].[P]([EndDate], [StartDate], [Quantity], [PriceEach], [TotalPrice], [Comments], [NetRate], [NetRateAmountDue], [NetAmountPaid], [NetAmountPaidDate], [NetRatePaidInFull], [CommissionPaidInFull], [Owner], [Organization], [Source], [SourceConfirmationNumber], [PassengerTaxAndFees], [PassengerTaxesAndFeesDescription], [Destination], [ProductType], [Description], [RateDe   </inputbuf>
    </process>
</process-list>
<resource-list>
    <objectlock lockPartition="0" objid="30675207" subresource="FULL" dbid="6" objectname="''.dbo.P" id="lock2ab07a480" mode="S" associatedObjectId="30675207">
    <owner-list>
        <owner id="process294aedc28" mode="S" />
        <owner id="process294aedc28" mode="IX" requestType="convert" />
    </owner-list>
    <waiter-list>
        <waiter id="process23b4b08c8" mode="IX" requestType="convert" />
    </waiter-list>
    </objectlock>
    <objectlock lockPartition="0" objid="30675207" subresource="FULL" dbid="6" objectname=".dbo.''"" id="lock2ab07a480" mode="S" associatedObjectId="30675207">
    <owner-list>
        <owner id="process23b4b08c8" mode="S" />
        <owner id="process23b4b08c8" mode="IX" requestType="convert" />
    </owner-list>
    <waiter-list>
        <waiter id="process294aedc28" mode="IX" requestType="convert" />
    </waiter-list>
    </objectlock>
</resource-list>
</deadlock>

Answer 1

Caveat: I'm answering this on my phone. So no diagrams.

This is a synchronization issue.

A deadlock generally occurs when two or more processes want access to the same record, page, or table (depending on lock granularity and lock escalation) but in a different order, especially if explicit transactions are involved, and especially if the system is under heavy load.

Say process A, in a transaction, updates record 1, then tries to update record 2.

Meanwhile, process B, in a transaction, updates record 2, then tries to read or update record 1. A has changed the record, so B can't even read it until A's transaction commits or rolls back. Likewise, A can't read the record B has locked, and boom, deadlock.

"I knew that", you're saying to yourself.

Well, yeah, but what I'm getting at is the order of operations issue. You mentioned the system is highly complex, and that is a bit of a giveaway. It is a synchronization issue. Do you know if different code paths in the system are running the same or similar queries in a different order?

In a healthy system, if process A updates record 1, then updates record 2, and process B runs those same operations in the same order, the requests will queue up and run consecutively. B will wait for A to finish, presuming the timeout isn't exceeded.

This works because B will never obtain a lock on record 2 while A is still holding a lock on record 1, because B wants to start with record 1 just like A, and cannot get it until A releases it.

You may be dealing with table locks instead of record locks if you are updating large ranges of records or doing a lot of reads inside your transactions with your isolation level set to serializable, etc. The same general principles apply.

Answer 2

You have 2 problems here:

1. Your table has no indexes at least those that can be used in some select that was executed in the same transaction but was not catched by the deadlock graph.

2. Your transactions use repeatable read isolation level.

When you use repeatable read, the shared locks acquired are held until the end of the transaction.

Before your sessions try to make update (session 1) and insert (session 2), they make some select that locked the whole table, it's not catched in inputbuffer but maybe you know what code was executed prior data modification.

Both sessions hold S-lock on entire table and want to update/'insert', so they need to convert S-lock into IX as some rows in this table will be updated/inserted and the intent lock should be placed on the table.

IX is incompatible with S so the first session waits for second session when it releases its S-lock on the table, but the second session cannot commit because it also cannot insert as it needs IX on the same table and it cannot be granted since the first session holds S-lock.

In order to fix this you should find the prior select and create indexes that will permit to lock only some rows, not entire table, or you should get rid of repeatable read.

You can reproduce this by yourself this way:

open 2 SSMS query windows and first make a select in repeatable read that will lock the whole table, I do it using tablock:

set transaction isolation level repeatable read
begin tran 

select count(*)
from dbo.t with (tablock);

At this point both sessions hold S-lock on a table. Now return to the first window and try to make an update:

update dbo.t
set col = 'bbb'
where id = 10;

This query will be blocked as it needs IX that cannot be granted, you can see lock situation using this code (maybe you should filter for your session ids, I make my test on dedicated server with no activity):

select resource_type,
       request_session_id,
       resource_associated_entity_id,
       request_mode,
       request_type,
       request_status
from sys.dm_tran_locks
where resource_type not in ('DATABASE', 'METADATA');

Now as soon as the second session tries the insert, deadlock occurs:

insert into dbo.t (id, col)
values(3, 'aaa');