How to interpret a t-sql deadlock trace

Question

I'm trying to sort out a deadlock by looking at the t-sql trace but I'm struggling to understand the information. I have a summary of the info here and then the full deadlock trace at the end of the post:

The first lock is on a table called dbo.RetailVoucher
The second lock is on a table called ooc.PlannedUniversalVoucher

The 1st stored procedure reads the following tables:
dbo.RetailVoucher, ooc.PlannedOrderItem and ooc.PlannedOrder.

The 2nd stored procedure reads the following tables:
ooc.PlannedBatch & ooc.PlannedUniversalVoucher

Now, for the procedures to be waiting for each other, I thought they would both have to be reading the same table at some point. I obviously don't understand how to interpret the trace. Do I need to hunt down the source of the locks on dbo.RetailerVoucher and ooc.PlannedUniversalVoucher in other procedures?

Thanks.

Here's the full trace:


 
  
   
    
     
update dbo.RetailVoucher
            set QuantityInStock -= rvsum.QuantitySum
            from dbo.RetailVoucher urv
            join (
                select oib.id, SUM(oib.Quantity) as QuantitySum
                from (
                    select coalesce(rv.RelatedVoucherId, rv.Id) id, oi.Quantity
                    from 
                        ooc.PlannedOrderItem oi 
                        join ooc.PlannedOrder po on oi.PlannedOrderId = po.Id
                        join RetailVoucher rv on oi.RetailVoucherId = rv.Id
                    where po.PlannedPacketId = @PlannedPacketId
                        and oi.PartnerId is null
                        and oi.OnDmenadServiceId is null
                        and rv.VoucherTypeId = 1
                ) oib
                group by oib.Id
            ) rvsum
            on urv.Id = rvsum.id;

         
    
    
Proc [Database Id = 8 Object Id = 2139922745]    
   
   
    
     
update ooc.PlannedBatch 
    set 
        IsCompleted = @True
    from ooc.PlannedBatch pb
        join ooc.PlannedUniversalVoucher puv on puv.PlannedBatchId = pb.Id
        left outer join @DuplicatedVouchers dv on dv.Id = puv.Id
    where dv.Id is null;     
    
    
Proc [Database Id = 8 Object Id = 1435920237]

dean · Accepted Answer

Let me try.

You have tho processes, processd0db8088 and process894748. From the execution stack you can see that the process894748 was executing PlannedPacketPreserveEVouchers, and processd0db8088 is executing OfflineOrdersImportPacket. You can see the queries in question for both processes there as well.

From resource list you can find out the resorces on which these two processes were deadlocked. First one is a particular index row in PK_RetailVoucher index on RetailVoucher table: processd0db8088 locked it exclusively and the process process894748 is waiting to gain shared lock on it. The other resource is a data page 778990 which belongs to PlannedUniversalVoucher table, held by process process894748 while processd0db8088 is waiting to gain read access to it.

The locks could have been taken anywhere from start of transaction. The queries shown here in the execution stack are the queries that were waiting on the resources, not the ones that taken the locks. Inspect the whole execution trace to find out where the locks were actually taken.

The standard advices apply: shorten transactions, access the tables in order, index appropriatelly.

How to interpret a t-sql deadlock trace

Answers (1)

Related Questions