We have a stored procedure which purges old data. We do the purging on daily basis during off-business hours. Also we do purging in small batches. But in the 5 hour window of purging, we see around 2 deadlocks on a daily basis. Our purging SP is getting deadlocked with an application stored procedure. Below is the deadlock graph:


Deadlock XML:

        <victimProcess id="process1ee5a1cca8"/>   
        <process id="process1ee5a1cca8" taskpriority="5" logused="37852" waitresource="PAGE: 8:3:33782409 " waittime="1341" ownerId="17013937844" transactionname="batch_archive" lasttranstarted="2021-01-13T00:21:32.883" XDES="0x20e0c8d900" lockMode="U" schedulerid="16" kpid="9320" status="suspended" spid="1029" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2021-01-12T22:35:01.590" lastbatchcompleted="2021-01-12T22:35:01.590" lastattention="1900-01-01T00:00:00.590" clientapp="SQLAgent - TSQL JobStep (Job 0xAC6E654BCA73184DA4769BFB133908A1 : Step 2)" hostname="SERVER_NAME" hostpid="4024" loginname="Domain\SQL_SRVC_ACCOUNT" isolationlevel="read committed (2)" xactid="17013937844" currentdb="6" currentdbname="dba_admin" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
                <frame procname="dba_admin.dbo.PURGING_SP" line="695" stmtstart="46958" stmtend="47248" sqlhandle="0x03000600119b6e780e397800aeac000001000000000000000000000000000000000000000000000000000000">  delete from DB_NAME..TABLE_B  where activity_id in      (select activity_id from batch) OPTION(MAXDOP 1    </frame>      
                <frame procname="adhoc" line="8" stmtstart="226" stmtend="626" sqlhandle="0x01000600fa8ab015c0c41e452500000000000000000000000000000000000000000000000000000000000000">  EXEC @return_value = [dbo].[PURGING_SP]    @arch_mon_crit = 93,    @batch_size = 5,    @po_sql_code = @po_sql_code OUTPUT,    @po_sql_msg = @po_sql_msg OUTPU    </frame>     
            <inputbuf>    DECLARE @return_value int,    @po_sql_code int,    @po_sql_msg nvarchar(2000)    set deadlock_priority low;    EXEC @return_value = [dbo].[PURGING_SP]    @arch_mon_crit = 93,    @batch_size = 5,    @po_sql_code = @po_sql_code OUTPUT,    @po_sql_msg = @po_sql_msg OUTPUT    SELECT @po_sql_code as N&apos;@po_sql_code&apos;,    @po_sql_msg as N&apos;@po_sql_msg&apos;    SELECT &apos;Return Value&apos; = @return_value       </inputbuf>
        <process id="process1a77fc4ca8" taskpriority="0" logused="5176" waitresource="OBJECT: 8:1697441121:2 " waittime="7176" ownerId="17013959258" transactionname="user_transaction" lasttranstarted="2021-01-13T00:21:36.213" XDES="0x25455748b0" lockMode="IX" schedulerid="3" kpid="5484" status="suspended" spid="304" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-01-13T00:21:36.213" lastbatchcompleted="2021-01-13T00:21:35.260" lastattention="1900-01-01T00:00:00.260" clientapp="rx-process_temp_jpid[5740]" hostname="APP_SERVER" hostpid="0" loginname="DOMAIN\SRV_ACCOUNT" isolationlevel="read committed (2)" xactid="17013959258" currentdb="8" currentdbname="DB_NAME" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">     
                <frame procname="DB_NAME.dbo.APP_SP_INSIDE_MAIN_SP" line="75" stmtstart="2928" stmtend="3252" sqlhandle="0x0300080011c5170b94ab5e01b0a8000001000000000000000000000000000000000000000000000000000000">  INSERT INTO TABLE_A ( email_id ,activity_id ,header ,content ,content_type ) VALUES ( @p_email_id ,@p_activity_id ,@p_header ,@p_content ,@p_content_type    </frame>      
                <frame procname="DB_NAME.dbo.APP_SP" line="301" stmtstart="14314" stmtend="15014" sqlhandle="0x0300080012f6881b97ab5e01b0a8000001000000000000000000000000000000000000000000000000000000">  EXEC @v_ret_value = APP_SP_INSIDE_MAIN_SP @p_email_id , @p_activity_id , @p_alias_id , @p_subject , @p_email_date , @p_email_size , @p_num_attachments , @p_charset , @p_message_id , @p_from_email_address , @p_recv_email_address , @p_delete_flag , @p_header , @p_content , @p_content_type , @p_text_content , @v_sql_code OUTPUT , @v_sql_message OUTPU    </frame>      <frame procname="adhoc" line="1" stmtstart="1202" stmtend="1898" sqlhandle="0x010008009805062a00bed2062300000000000000000000000000000000000000000000000000000000000000">  EXEC @P0 = APP_SP   @P1  ,  @P2  ,  @P3  ,  @P4  ,  @P5  ,  @P6  ,  @P7  ,  @P8  ,  @P9  ,  @P10  ,  @P11  , &apos;n&apos; , @P12  ,  @P13  ,  @P14  ,  @P15  ,  @P16  ,  @P17  ,  @P18  ,  @P19  ,  @P20 OUT  ,  @P21  ,  @P22  ,  @P23  ,  @P24  ,  @P25  ,  @P26  ,  @P27  ,  @P28  ,  @P29  ,  @P30  ,  @P31  ,  @P32  ,  @P33 OUT  ,  @P34 OU    </frame>      <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">  unknown    </frame>     </executionStack>     <inputbuf>  (@P0 decimal(38,4) OUTPUT,@P1 bigint,@P2 bigint,@P3 nvarchar(4000),@P4 datetime2,@P5 int,@P6 int,@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(max),@P14 nvarchar(4000),@P15 nvarchar(max),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 decimal(38,4) OUTPUT,@P21 int,@P22 int,@P23 bigint,@P24 bigint,@P25 nvarchar(4000),@P26 bigint,@P27 datetime2,@P28 int,@P29 nvarchar(4000),@P30 nvarchar(4000),@P31 nvarchar(4000),@P32 bigint,@P33 decimal(38,4) OUTPUT,@P34 nvarchar(4000) OUTPUT)EXEC @P0 = APP_SP   @P1  ,  @P2  ,  @P3  ,  @P4  ,  @P5  ,  @P6  ,  @P7  ,  @P8  ,  @P9  ,  @P10  ,  @P11  , &apos;n&apos; , @P12  ,  @P13  ,  @P14  ,  @P15  ,  @P16  ,  @P17  ,  @P18  ,  @P19  ,  @P20 OUT  ,  @P21  ,  @P22  ,  @P23  ,  @P24  ,  @P25  ,  @P26  ,  @P27  ,  @P28  ,  @P29  ,  @P30  ,  @P31  ,  @P32  ,  @P33 OUT  ,  @P34 OUT                                                                            </inputbuf>    
        <pagelock fileid="3" pageid="33782409" dbid="8" subresource="FULL" objectname="DB_NAME.dbo.TABLE_B" id="lock1ffd963c00" mode="IX" associatedObjectId="72065991888076800">     
                <owner id="process1a77fc4ca8" mode="IX"/>     
                <waiter id="process1ee5a1cca8" mode="U" requestType="wait"/>     
        <objectlock lockPartition="2" objid="1697441121" subresource="FULL" dbid="8" objectname="DB_NAME.dbo.TABLE_A" id="lock1bdc26a600" mode="X" associatedObjectId="1697441121">     
                <owner id="process1ee5a1cca8" mode="X"/>     
                <waiter id="process1a77fc4ca8" mode="IX" requestType="wait"/>     

Few other Important Info:

  • Both the tables involved in deadlock (TABLE_A & TABLE_B) are partitioned tables.
  • Server is having 16 processors, hence lock partitioning is enabled by default.
  • Lock_escalation_desc for both the involved tables in sys.tables is TABLE.
  • Table_B is a parent table of Table_A. Since App_SP is doing INSERT whereas PURGING SP is doing DELETE, we cannot have the same sequence in both the SPs because of referential integrity.
  • Purging SP is doing DELETE from around 40 tables in a single transaction. We are using loop to delete the data in one go, but one loop will delete data for a very small batch. ONE LOOP = ONE TRANSACTION that purges data from 40 tables.

I need help on below points:

  • One year old data is getting deleted whereas a new data is getting inserted. So how can a deadlock happen between them as data is residing at two different places.
  • Why object lock is being taken on TABLE_A? We are providing very small batch size, so I don't think lock escalation can happen at entire table level.
  • If you see in resource_list of deadlock graph, one of the resource is objectlock lockPartition="2". what does LOCKPARTITION = 2 means? does it mean that partition no = 2 of table is locked? I know it is coming from lock partitioning mechanism but does it causing any negative impact and causing deadlock?

Any help would be appreciated to permanently fix the deadlock.

  • 2
    Why don't you use a partition swith to delete the data ?
    – SQLpro
    Jan 13 at 13:43
  • 1
    Hard to tell without seeing the code and the table/index structure, but it's possible (a) escalation is locking the entire table, so it doesn't matter where in the table the data resides, and/or (b) there are indexes with keys on other columns that aren't related to time of entry (e.g. an index on last name). I assume there are foreign keys between A and B? Are these indexed? Are they on columns that have any correlation to time of entry? What else is involved with the transaction that inserts new rows, and why? Theoretically that insert should win or wait. We can't see what else is happening. Jan 13 at 14:17
  • One option is have the delete not wait for locks and just fail when it can't get one. Retry a short time latter when this happens. The connection setting SET LOCK_TIMEOUT 0; will make it not wait for locks. This makes one half of your deadlock just give up rather than fight/wait it out.
    – Brian
    Jan 13 at 14:24
  • 2
    You might also consider delayed durability or soft deletes to reduce duration of locking (in addition to partitions, but partitions alone won't help if the deadlock involves non-partition-aligned index pages). But reducing locking alone won't help either if there is a logic problem (e.g. the insert is part of a transaction involving DML against the other table). Jan 13 at 14:36
  • 1
    40 tables is a lot of locks, not surprising it's escalating Jan 13 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.