Sample Header Ad - 728x90

Purging SP getting deadlocked with App...Not able to completed understand Deadlock Graph

1 vote
1 answer
126 views
We have a stored procedure which purges old data. On a regular basis, it is getting deadlocked with an application stored procedure. Below is the deadlock graph: Deadlock Graph Deadlock XML: delete from DBNAME..TABLE_NAME with(rowlock) where email_id in (select email_id from ARCHIVAL_BATCH EXEC @return_value = [dbo].[PURGE_SP_NAME] @arch_mon_crit = 93, @batch_size = 3, @po_sql_code = @po_sql_code OUTPUT, @po_sql_msg = @po_sql_msg OUTPU DECLARE @return_value int, @po_sql_code int, @po_sql_msg nvarchar(2000) EXEC @return_value = [dbo].[PURGE_SP_NAME] @arch_mon_crit = 93, @batch_size = 3, @po_sql_code = @po_sql_code OUTPUT, @po_sql_msg = @po_sql_msg OUTPUT SELECT @po_sql_code as N'@po_sql_code', @po_sql_msg as N'@po_sql_msg' SELECT 'Return Value' = @return_value delete from DBNAME..TABLE_NAME with(rowlock) where email_id in (select email_id from ARCHIVAL_BATCH EXEC @return_value = [dbo].[PURGE_SP_NAME] @arch_mon_crit = 93, @batch_size = 3, @po_sql_code = @po_sql_code OUTPUT, @po_sql_msg = @po_sql_msg OUTPU DECLARE @return_value int, @po_sql_code int, @po_sql_msg nvarchar(2000) EXEC @return_value = [dbo].[PURGE_SP_NAME] @arch_mon_crit = 93, @batch_size = 3, @po_sql_code = @po_sql_code OUTPUT, @po_sql_msg = @po_sql_msg OUTPUT SELECT @po_sql_code as N'@po_sql_code', @po_sql_msg as N'@po_sql_msg' SELECT 'Return Value' = @return_value INSERT INTO TABLE_NAME_alt ( email_id ,activity_id ,text_content ) VALUES ( @p_email_id ,@p_activity_id ,@p_text_content EXEC @v_ret_value = APP_SERVER_NAME1 @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 EXEC @P0 = APP_SP_NAME @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , 'n' , @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 unknown (@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(4000),@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_NAME @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , 'n' , @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 I have just started troubleshooting to fix the deadlock. I am not able to get a few things in about the deadlock graph: 1. Our Purging SP is a single SP which runs as one transaction, then why we are we seeing two processes for it. 2. What is exchange event? From where did it come? 3. At what level are locks are being taken? Row, Page or table? 4. How to proceed further? NOTE: 1. We are deleting less records batch wise in purging SP. 2. I have removed the actual SP and table Names with Aliases. **UPDATE 1:** After implementing Eric Darling's suggestion mentioned in the comment (i.e adding OPTION (MAXDOP 1) in the problematic statement of purging SP), it is still getting deadlocked, but this time the deadlock graph is different. This time there is only one process for purging SP instead of 2 and also exchange event has disappeared. Now error message is also the traditional one. I am still puzzled about why our purging SP is getting object level lock even though we are deleting very less number of rows compared to total number of rows in the table. Any help would be appreciated.
Asked by sachin-SQLServernewbiee (473 rep)
Dec 22, 2020, 06:10 PM
Last activity: Dec 27, 2020, 06:17 PM