When Delete insert data from SP then getting Dead Lock error

Sudip Bhatt 2,271 Reputation points
2020-09-11T17:06:12.22+00:00

i have store procedure in sql server where i have BEGIN TRAN and COMMIT TRAN

with in BEGIN TRAN and COMMIT TRAN i am first deleting data from few tables and after that i insert data into those tables

with in cursor loop.

basically from my c# application i am sending xml to store procedure and SP load that xml into cursor and insert data into table row by row. now very recently i notice when i am calling this store procedure then i am getting dead lock error frequently.

here is error screen shot
1618280

Here is my store procedure code which long code but still i am posting here. please see the code and tell me what i need to change in SP code to get rid of dead lock error. my db isolation is default isolation.

my db version is
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

here is my store procedure code

--<?xml version="1.0" encoding="utf-16"?>                                      
--<Root>                                      
--  <PeriodicalData>                                      
--    <Section>Consensus Model</Section>                                      
--    <LineItem>Net Sales</LineItem>                                      
--    <XFundCode>TRIN</XFundCode>                                      
--    <StandardDate>2010 FY</StandardDate>                                      
--    <StandardValue>65225</StandardValue>                                      
--  </PeriodicalData>                                      
--  <PeriodicalData>                                      
--    <Section>Consensus Model</Section>                                      
--    <LineItem>Net Sales</LineItem>                                      
--    <XFundCode>TRIN</XFundCode>                                      
--    <StandardDate>2011 FY</StandardDate>                                      
--    <StandardValue>108249</StandardValue>                                      
--  </PeriodicalData>                                      
--</Root>                                        

--<?xml version="1.0" encoding="utf-16"?>                          
--<Root>                          
--  <TenQKLiConfig>                          
--    <Ticker>TDY1</Ticker>                          
--    <Section>Consensus Model</Section>                          
--    <LI>Cost of Revenue</LI>                          
--    <StandrdFormula />                          
--    <StandrdFormulaActual />                          
--    <AllowedDecimalPlace>1</AllowedDecimalPlace>                          
--    <CurrencySign>$</CurrencySign>                          
--    <CurrencyCode>en-US</CurrencyCode>                          
--    <AllowPercentageSign>false</AllowPercentageSign>                          
--    <AllowComma>true</AllowComma>                          
--    <QCCheck>false</QCCheck>                          
--    <QCType>SUMQ</QCType>                          
--    <BlueMatrix1stElementFormula>"Consensus Model~Cost of Revenue~3330"/"Consensus Model~Net Revenue~5833"</BlueMatrix1stElementFormula>                          
--    <DevelopmentStage />                          
--    <CrossCalc1Q />                          
--    <CrossCalc2Q />                          
--    <CrossCalc3Q />                          
--    <CrossCalc4Q />                          
--    <CrossCalcFY />                          
--    <GH_FontStyle>Regular</GH_FontStyle>                          
--    <GH_Strikeout>false</GH_Strikeout>                          
--    <FontStyle>Regular</FontStyle>                          
--    <Strikeout>false</Strikeout>                          
--  </TenQKLiConfig>                                
--  </Root>                          

ALTER PROCEDURE [dbo].[USP_CRSSaveTickerBogey]                                                
 @TickerID varchar(20),                                               
 @UserID varchar(20),                                              
 @BogyXML xml,                              
 @LiConfigXML XML,                          
 @STATUS VARCHAR(200) OUTPUT                                                 
AS                                       

BEGIN                                      
 DECLARE @XMLFormat AS INT,@SectionOrder AS INT                                                                
 DECLARE @Section VARCHAR(100), @LineItem VARCHAR(100), @StandardDate VARCHAR(20), @StandardValue VARCHAR(20), @XFundCode VARCHAR(20),@ActualProvidedByCompany VARCHAR(3)                                             
 DECLARE @MasterID INT, @SectionID INT, @LineItemID INT, @PeriodID INT, @OrderID INT,@ConfigID INT                                       

DECLARE @TmpStandrdFormula VARCHAR(MAX)='',                     
@TmpStandrdFormulaActual VARCHAR(MAX)='',                    
@TmpBlueMatrix1stElementFormula VARCHAR(MAX)='',      
@TmpCrossCalc1Q VARCHAR(MAX)='',                    
@TmpCrossCalc2Q VARCHAR(MAX)='',                    
@TmpCrossCalc1H VARCHAR(MAX)='',    
@TmpCrossCalc3Q VARCHAR(MAX)='',                    
@TmpCrossCalc4Q VARCHAR(MAX)='',        
@TmpCrossCalc2H VARCHAR(MAX)='',    
@TmpCrossCalcFY VARCHAR(MAX)=''                    

 DECLARE @LI VARCHAR(100),                          
 @StandrdFormula VARCHAR(MAX),                          
 @StandrdFormulaActual VARCHAR(MAX),                          
 @AllowedDecimalPlace VARCHAR(5),                      
 @CurrencySign NVARCHAR(5),                          
 @CurrencyCode VARCHAR(10),                          
 @AllowPercentageSign VARCHAR(10),                          
 @AllowComma VARCHAR(5),                          
 @QCCheck VARCHAR(5),                          
 @QCType VARCHAR(10),                          
 @BlueMatrix1stElement VARCHAR(MAX),                           
 @BlueMatrix1stElementFormula VARCHAR(MAX),                          
 @DevelopmentStage VARCHAR(MAX),                          
 @CrossCalc1Q VARCHAR(MAX),                          
 @CrossCalc2Q VARCHAR(MAX),       
 @CrossCalc1H VARCHAR(MAX),     
 @CrossCalc3Q VARCHAR(MAX),                          
 @CrossCalc4Q VARCHAR(MAX),      
 @CrossCalc2H VARCHAR(MAX),     
 @CrossCalcFY VARCHAR(MAX),                      
 @SummaryTab VARCHAR(MAX)          

 SET @LI =''                          
 SET @StandrdFormula =''                          
 SET @StandrdFormulaActual =''                          
 SET @AllowedDecimalPlace =''                          
 SET @CurrencySign =''                          
 SET @CurrencyCode =''                          
 SET @AllowPercentageSign =''                          
 SET @AllowComma =''                          
 SET @QCCheck =''                          
 SET @QCType =''                          
 SET @BlueMatrix1stElement = ''                          
 SET @BlueMatrix1stElementFormula =''                          
 SET @DevelopmentStage =''                          
 SET @CrossCalc1Q =''                          
 SET @CrossCalc2Q =''                          
 SET @CrossCalc3Q =''                          
 SET @CrossCalc4Q =''                          
 SET @CrossCalcFY =''                          
 SET @CrossCalc1H = ''    
 SET @CrossCalc2H = ''    
 SET @SummaryTab = ''  

 SET @MasterID=0                                      
 SET @SectionID=0                                      
 SET @LineItemID=0                                      
 SET @PeriodID=0                                      
 SET @OrderID=0                                     
 SET @ConfigID=0                    

 SET @Section=''                                      
 SET @LineItem=''                                      
 SET @StandardDate=''                                      
 SET @StandardValue=''                                      
 SET @XFundCode=''                                      

  SET @SectionOrder=1                  

 BEGIN TRY                             
  SET QUOTED_IDENTIFIER OFF;                        
  BEGIN TRAN                                      

  BEGIN --Delete all ticker specific data first              

 --DELETE c          
 --FROM TblLineItemTemplate a INNER JOIN          
 --tblTicker_LiConfig c ON a.ID = c.LineItemID INNER JOIN          
 --tblSectionTemplate b ON c.SectionID = b.SectionID          
 --INNER JOIN tblCalenderMaster cm ON cm.ID=c.MasterID          
 --WHERE a.TickerID=@TickerID AND b.TickerID=@TickerID AND cm.TickerID=@TickerID          

--Deleting data from tblTicker_Bogey          
 DELETE c              
 FROM TblLineItemTemplate a INNER JOIN              
 tblTicker_Bogey c ON a.ID = c.LineItemID INNER JOIN              
 tblSectionTemplate b ON c.SectionID = b.SectionID              
 WHERE a.TickerID=@TickerID AND b.TickerID=@TickerID              

 SELECT @MasterID =ID  FROM tblCalenderMaster  WHERE TRIM(TickerID)=TRIM(@TickerID)              
 --Deleting data from tblTicker_LiConfig      
 DELETE FROM tblTicker_LiConfig WHERE TRIM(TickerID)=TRIM(@TickerID)         

 --Deleting data from tblSectionLineItemTemplate              
 DELETE c              
 FROM  TblLineItemTemplate a INNER JOIN              
 tblSectionLineItemTemplate c ON a.ID = c.LineItemID INNER JOIN              
 tblSectionTemplate b ON c.SectionID = b.SectionID              
 WHERE a.TickerID=@TickerID AND b.TickerID=@TickerID              

 DELETE FROM TblLineItemTemplate WHERE TickerID=@TickerID              
 DELETE FROM tblSectionTemplate WHERE TickerID=@TickerID              

 DELETE d              
 FROM tblCalenderDetail d INNER JOIN tblCalenderMaster M              
 ON D.MasterID=M.ID              
 WHERE M.TickerID=@TickerID              

 DELETE FROM tblCalenderMaster WHERE TickerID=@TickerID              
 END              




  IF NOT EXISTS(SELECT * FROM tblCalenderMaster WHERE TRIM(TickerID)=TRIM(@TickerID))                                            
  BEGIN                                            
   INSERT INTO tblCalenderMaster(TickerID,Following53Weeks,TransitionPeriod)                                                    
   VALUES(@TickerID,'N','N')                                                 

   SELECT @MasterID = SCOPE_IDENTITY()                                    
  END                                            
  ELSE                                            
  BEGIN                                            
   SELECT @MasterID = ID FROM tblCalenderMaster WHERE TRIM(TickerID)=TRIM(@TickerID)                                      
  END                                       

  --IF @MasterID > 0                                            
  --BEGIN                                            
  -- DELETE FROM tblCalenderDetail WHERE MasterID=@MasterID                                          
  -- DELETE FROM tblTicker_Bogey WHERE MasterID=@MasterID                                            
  --END                                

  --load xml data into cursor                                                    
  Exec sp_xml_preparedocument @XMLFormat OUTPUT, @BogyXML                                       

  DECLARE CURRECORD                                                    
  CURSOR LOCAL FOR                                                    
  SELECT Section,LineItem,StandardDate,StandardValue,XFundCode,ActualProvidedByCompany                                                    
  FROM OPENXML (@XMLFORMAT, '/Root/PeriodicalData', 2)                                                    
  WITH                                           
  (                                                    
   Section   VARCHAR(100),                                                    
   LineItem  VARCHAR(100),                                                    
   StandardDate VARCHAR(20),                                                    
   StandardValue VARCHAR(20),                                                    
   XFundCode  VARCHAR(20),        
   ActualProvidedByCompany VARCHAR(3)        
  )                                       

  -- open cursor                                                    
  OPEN CURRECORD                                                    
  FETCH NEXT FROM CURRECORD INTO @Section,@LineItem,@StandardDate,@StandardValue,@XFundCode,@ActualProvidedByCompany                                                    

  -- iterate in cursor to fetch value                                                    
  WHILE (@@FETCH_STATUS=0)                                                    
  BEGIN                                                    

  --REPLACE(TRIM(@LineItem),'''','''''')                          
  --CHR(39)                        

  --PRINT @LineItem                        

   IF NOT EXISTS(SELECT * FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=TRIM(@LineItem))                                 
   BEGIN         
    --IF once a xfund code inserted into TblLineItemTemplate table for a specific line item and if same xfund comes again for same line item
    --then prevent it from here to make duplicate xfund code
    --IF EXISTS(SELECT * FROM TblLineItemTemplate WHERE UPPER(TRIM(LineItem)) = UPPER(TRIM(@LineItem)) AND TickerID = @TickerID AND  XFundCode=@XFundCode)
    --BEGIN
    --  SET @XFundCode=''
    --END

    INSERT INTO TblLineItemTemplate(TickerID,LineItem,XFundCode,Action,UserID) VALUES (TRIM(@TickerID),TRIM(@LineItem),TRIM(@XFundCode),'I',@UserID)                                            
    SET @LineItemID = SCOPE_IDENTITY()                                               
   END                                            
   ELSE                                    
   BEGIN                                            
    SELECT @LineItemID=ID FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=TRIM(@LineItem)                                            
   END                                        


   IF NOT EXISTS(SELECT * FROM tblSectionTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(Section)=TRIM(@Section))                                      
   BEGIN                                            
    INSERT INTO tblSectionTemplate(TickerID,Section,Active,OrderID)                                                    
    VALUES(TRIM(@TickerID),TRIM(@Section),'A',@SectionOrder)                                

    SET @SectionOrder = @SectionOrder + 1                                 

    -- storing identity value of last inserted item                                                    
    SELECT @SectionID = SCOPE_IDENTITY()                                            
   END                                            
   ELSE                                            
 BEGIN                                            
    SELECT @SectionID =SectionID  FROM tblSectionTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(Section)=TRIM(@Section)                                      
   END                                            


   IF NOT EXISTS(SELECT * FROM tblSectionLineItemTemplate WHERE SectionID=@SectionID AND LineItemID=@LineItemID)                                            
   BEGIN                                    

  IF  EXISTS(SELECT * FROM tblSectionLineItemTemplate WHERE SectionID=@SectionID)                                            
  BEGIN                  
  SELECT @OrderID=MAX(OrderID)+1 FROM tblSectionLineItemTemplate WHERE SectionID=@SectionID                
  END                
  ELSE                
  BEGIN             
  SET @OrderID= 1                
  END                

  INSERT INTO tblSectionLineItemTemplate(SectionID,LineItemID,Active,InsertDate,UserID,OrderID)                                                    
  VALUES(@SectionID,@LineItemID,'A',GetDate(),@UserID,@OrderID)                                                    

   END                                       

   IF NOT EXISTS(SELECT * FROM tblCalenderDetail WHERE MasterID=@MasterID AND TRIM(Period)=TRIM(@StandardDate))                                            
   BEGIN                                           
    INSERT INTO tblCalenderDetail(MasterID,Period,IsDeleted)                                                    
    VALUES(@MasterID,TRIM(@StandardDate),'N')                                          

    SELECT @PeriodID = SCOPE_IDENTITY()                                            
   END                                          
   ELSE                                          
   BEGIN                                          
    SELECT @PeriodID=ID FROM tblCalenderDetail WHERE MasterID=@MasterID AND TRIM(Period)=TRIM(@StandardDate)                                         
   END                                       


   --IF NOT EXISTS(SELECT * FROM tblTicker_Bogey WHERE MasterId=@MasterID AND SectionID=@SectionID AND LineItemID=@LineItemID AND PeriodID=@PeriodID)                                      
   --BEGIN                                      
    IF IIF(TRIM(@StandardValue) <> '',TRIM(@StandardValue),'') <> ''                                       
    BEGIN                                      
     INSERT INTO tblTicker_Bogey (MasterID,SectionID,LineItemID,PeriodID,[Values],UserID,InsertedOn,ModifyOn,status,ActualProvidedByCompany)                                      
     VALUES(@MasterID,@SectionID,@LineItemID,@PeriodID,TRIM(@StandardValue),@UserID,GetDate(),NULL,'I',@ActualProvidedByCompany)     

        PRINT 'Bogey Inserted'+CAST(@MasterID AS VARCHAR)               

    END                              

    --PRINT @Section+' '+ @LineItem+' '+ @StandardDate+' '+ IIF(@StandardValue<>'',@StandardValue,'No')                                      
    --PRINT CAST(@SectionID AS VARCHAR)+' '+ CAST(@LineItemID AS VARCHAR)+' '+ CAST(@PeriodID AS VARCHAR)+' '+ CAST(@SectionID AS VARCHAR)                                      

    --END                                      

   SET @SectionID=0                                      
   SET @LineItemID=0                                      
   SET @PeriodID=0                                      
   --SET @OrderID=0                      

   SET @Section=''                                      
   SET @LineItem=''                                      
   SET @StandardDate=''                                      
   SET @StandardValue=''                                      
   SET @XFundCode=''                                      
  FETCH NEXT FROM CURRECORD INTO @Section,@LineItem,@StandardDate,@StandardValue,@XFundCode,@ActualProvidedByCompany                                         
  END                                                    


  CLOSE CURRECORD                                                    
  DEALLOCATE CURRECORD                                         

 SET @SectionID =0                          
 SET @LineItemID =0                          
 SET @MasterID=0                          
   --Insert data into tblTicker_LiConfig                          
  --load xml data into cursor                                                    
  Exec sp_xml_preparedocument @XMLFormat OUTPUT, @LiConfigXML                                       

  DECLARE CURRECORD                                                    
  CURSOR LOCAL FOR                                                    
  SELECT Section,LI,StandrdFormula,StandrdFormulaActual,AllowedDecimalPlace,CurrencySign,CurrencyCode,AllowPercentageSign,AllowComma,QCCheck,QCType,                          
  BlueMatrix1stElement,BlueMatrix1stElementFormula,DevelopmentStage,                          
  CrossCalc1Q,CrossCalc2Q,CrossCalc1H,CrossCalc3Q,CrossCalc4Q,CrossCalc2H,CrossCalcFY,SummaryTab                          

  FROM OPENXML (@XMLFORMAT, '/Root/TenQKLiConfig', 2)                                                    
  WITH                                           
  (                                     
   Section       VARCHAR(100),                                                    
   LI   VARCHAR(100),                                                    
   StandrdFormula     VARCHAR(MAX),                                                    
   StandrdFormulaActual    VARCHAR(MAX),                                                    
   AllowedDecimalPlace    VARCHAR(5),                          
   CurrencySign      NVARCHAR(5),                          
   CurrencyCode      VARCHAR(10),                          
   AllowPercentageSign    VARCHAR(10),                          
   AllowComma      VARCHAR(5),                          
   QCCheck       VARCHAR(5),                          
   QCType       VARCHAR(10),                          
   BlueMatrix1stElement    VARCHAR(MAX),          
   BlueMatrix1stElementFormula  VARCHAR(MAX),                          
   DevelopmentStage     VARCHAR(MAX),                          
   CrossCalc1Q      VARCHAR(MAX),                          
   CrossCalc2Q      VARCHAR(MAX),        
   CrossCalc1H      VARCHAR(MAX),      
   CrossCalc3Q      VARCHAR(MAX),                          
   CrossCalc4Q      VARCHAR(MAX),                   
   CrossCalc2H      VARCHAR(MAX),         
   CrossCalcFY      VARCHAR(MAX),  
   SummaryTab       VARCHAR(MAX)          

  )                            

  SELECT @MasterID =ID  FROM tblCalenderMaster  WHERE TRIM(TickerID)=TRIM(@TickerID)                           

  --DELETE FROM tblTicker_LiConfig WHERE MasterID=@MasterID                          

    -- open cursor                                                    
  OPEN CURRECORD                                      
  FETCH NEXT FROM CURRECORD INTO @Section,@LI,@StandrdFormula,@StandrdFormulaActual,@AllowedDecimalPlace,@CurrencySign,@CurrencyCode,@AllowPercentageSign,@AllowComma,@QCCheck,@QCType                          
  ,@BlueMatrix1stElement,@BlueMatrix1stElementFormula,@DevelopmentStage,@CrossCalc1Q,@CrossCalc2Q,@CrossCalc1H,@CrossCalc3Q,@CrossCalc4Q,@CrossCalc2H,@CrossCalcFY,@SummaryTab                            

  -- iterate in cursor to fetch value                                                    
  WHILE (@@FETCH_STATUS=0)                                            
  BEGIN                           

 SELECT @SectionID =SectionID  FROM tblSectionTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(Section)=TRIM(@Section)                             
 SELECT @LineItemID=ID FROM TblLineItemTemplate WHERE TRIM(TickerID)=TRIM(@TickerID) AND TRIM(LineItem)=TRIM(@LI)                                     

 SET @TmpStandrdFormula=@StandrdFormula                    
 SELECT @StandrdFormula=dbo.fn_TranslateFormulaToID(@TickerID,@StandrdFormula,'N')                      

 SELECT @StandrdFormulaActual=dbo.fn_TranslateFormulaToID(@TickerID,@StandrdFormulaActual,'N')                          

 SET @TmpBlueMatrix1stElementFormula = @BlueMatrix1stElementFormula                    
 SELECT @BlueMatrix1stElementFormula=dbo.fn_TranslateFormulaToID(@TickerID,@BlueMatrix1stElementFormula,'N')                          

 SET @TmpCrossCalc1Q = @CrossCalc1Q                     
 SET @TmpCrossCalc2Q = @CrossCalc2Q                
 SET @TmpCrossCalc1H = @CrossCalc1H                  
 SET @TmpCrossCalc3Q = @CrossCalc3Q                         
 SET @TmpCrossCalc4Q = @CrossCalc4Q        
 SET @TmpCrossCalc2H = @CrossCalc2H                  
 SET @TmpCrossCalcFY = @CrossCalcFY                         

 SELECT @CrossCalc1Q=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc1Q,'Y')                          
 SELECT @CrossCalc2Q=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc2Q,'Y')                          
 SELECT @CrossCalc1H=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc1H,'Y')                          
 SELECT @CrossCalc3Q=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc3Q,'Y')        
 SELECT @CrossCalc4Q=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc4Q,'Y')                 
 SELECT @CrossCalc2H=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalc2H,'Y')                          
 SELECT @CrossCalcFY=dbo.fn_TranslateFormulaToID(@TickerID,@CrossCalcFY,'Y')                          


 IF @SectionID > 0 AND @LineItemID > 0                          
 BEGIN                          
   IF NOT EXISTS(SELECT * FROM tblTicker_LiConfig WHERE SectionID=@SectionID AND LineItemID=@LineItemID)                 
   BEGIN
   INSERT INTO tblTicker_LiConfig                          
   (                    
    MasterID,                          
    SectionID,                          
    LineItemID,                          
    StandrdFormula,                          
    StandrdFormulaActual,                          
    AllowedDecimalPlace,                          
    CurrencySign,                          
    CurrencyCode,                          
    AllowPercentageSign,                          
    AllowComma,                          
    QCCheck,                          
    QCType,                          
    BlueMatrix1stElement,                          
    BlueMatrix1stElementFormula,                          
    DevelopmentStage,                          
    CrossCalc1Q,                          
    CrossCalc2Q,                  
    CrossCalc1H,    
    CrossCalc3Q,                          
    CrossCalc4Q,      
    CrossCalc2H,    
    CrossCalcFY,      
    SummaryTab,    
    TickerID      
   )                          
   VALUES                          
   (                          
    @MasterID,                          
    @SectionID,                          
    @LineItemID,                          
    @StandrdFormula,                          
    @StandrdFormulaActual,                          
    @AllowedDecimalPlace,                          
    @CurrencySign,                          
    @CurrencyCode,                          
    @AllowPercentageSign,                          
    @AllowComma,                          
    @QCCheck,                          
    @QCType,                          
    TRIM(@BlueMatrix1stElement),                          
    @BlueMatrix1stElementFormula,                          
    @DevelopmentStage,                          
    @CrossCalc1Q,                          
    @CrossCalc2Q,       
 ISNULL(@CrossCalc1H,''),    
    @CrossCalc3Q,                          
    @CrossCalc4Q,              
 ISNULL(@CrossCalc2H,''),    
    @CrossCalcFY,      
    @SummaryTab,    
 @TickerID      
   )                          

    SELECT @ConfigID = SCOPE_IDENTITY()                      
    PRINT 'liconfig Inserted'+CAST(@MasterID AS VARCHAR)               

 IF @TmpStandrdFormula <> '' AND @StandrdFormula = ''                    
  BEGIN                    
   INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is Standard Formula parse during insertion',                    
         'TickerID '+@TickerID+' Standard Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpStandrdFormula,GETDATE())                    
  END                    

  IF TRIM(@TmpBlueMatrix1stElementFormula) <> '' AND @BlueMatrix1stElementFormula = ''                    
  BEGIN                    
    INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is BlueMatrix Formula parse during insertion',                    
         'TickerID '+@TickerID+' BlueMatrix1stElement Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpBlueMatrix1stElementFormula,GETDATE())                    
  END                    

  IF TRIM(@TmpCrossCalc1Q) <> ''  AND @CrossCalc1Q = ''                    
  BEGIN                    
    INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal1 Formula parse during insertion',                    
     'TickerID '+@TickerID+' CrossCal1 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc1Q,GETDATE())                    
  END                    

  IF TRIM(@TmpCrossCalc2Q) <> '' AND @CrossCalc2Q = ''                    
  BEGIN                    
    INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal2 Formula parse during insertion',                    
         'TickerID '+@TickerID+' CrossCal2 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc2Q,GETDATE())                    
  END                    

  IF TRIM(@TmpCrossCalc1H) <> '' AND @CrossCalc1H = ''                    
  BEGIN                    
    INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal2 Formula parse during insertion',                    
         'TickerID '+@TickerID+' CrossCal2 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc1H,GETDATE())                    
  END    

  IF TRIM(@TmpCrossCalc3Q) <> '' AND @CrossCalc3Q = ''                    
  BEGIN                    
    INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal3 Formula parse during insertion',                    
         'TickerID '+@TickerID+' CrossCal3 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc3Q,GETDATE())                    
  END                    

  IF TRIM(@TmpCrossCalc4Q) <> '' AND @CrossCalc4Q = ''                    
  BEGIN                    
    INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCal4 Formula parse during insertion',                    
         'TickerID '+@TickerID+' CrossCal4 Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalc4Q,GETDATE())                    
  END                    

  IF TRIM(@TmpCrossCalc2H) <> '' AND @CrossCalc2H = ''
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions
0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. m 4,271 Reputation points
    2020-09-14T06:16:33.937+00:00

    Hi @Sudip Bhatt ,

    We can’t guess the cause just by reading your code.

    You need to use monitoring tools to monitor deadlocks and locate problems.

    Turn on trace flag

    DBCC TRACEON(1222,-1),

    When a deadlock occurs, the trace flag 1222 returns the captured information to the SQL Server error log. The trace flag 1222 will set the format of the deadlock information, the order is first by process, then by resource.

    Although deadlocks cannot be completely avoided, following specific coding conventions can minimize the chance of deadlocks. The following methods help to deadlock minimize:

    1.Access the objects in the same order.
    2.Avoid user interaction in transactions.
    3.Keep the transaction short and in a batch.
    4.Use a lower isolation level.
    5.Adjust the execution plan of the statement to reduce the number of lock applications.

    More information: 31280.finding-and-extracting-deadlock-information-using-extended-events , what-are-sql-server-deadlocks-and-how-to-monitor-them

    Note: the deadlock part as next:
    The deadlock part as next:

    1. first part
      deadlock-list
      deadlock victim=process689978
    2. the second part
      process-list
      process id=process6891f8 taskpriority=0 logused=868
      waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444
      transactionname=user_transaction
      lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0
      lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54
      sbid=0 ecid=0 priority=0 transcount=2
      lastbatchstarted=2005-09-05T11:22:42.733
      lastbatchcompleted=2005-09-05T11:22:42.733
      clientapp=Microsoft SQL Server Management Studio-Query
      hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
      isolationlevel=read committed (2) xactid=310444 currentdb=6
      lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
      executionStack
      frame procname=AdventureWorks.dbo.usp_p1 line=6 stmtstart=202
      sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
      UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;
      frame procname=adhoc line=3 stmtstart=44
      sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
      EXEC usp_p1
      inputbuf
      BEGIN TRANSACTION
      EXEC usp_p1
      process id=process689978 taskpriority=0 logused=380
      waitresource=KEY: 6:72057594057457664 (350007a4d329)
      waittime=5015 ownerId=310462 transactionname=user_transaction
      lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U
      schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0
      priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077
      lastbatchcompleted=2005-09-05T11:22:44.077
      clientapp=SQL Server Management Studio-Query
      hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
      isolationlevel=read committed (2) xactid=310462 currentdb=6
      lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
      executionStack
      frame procname=AdventureWorks.dbo.usp_p2 line=6 stmtstart=200
      sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
      UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;
      frame procname=adhoc line=3 stmtstart=44
      sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
      EXEC usp_p2
      inputbuf
      BEGIN TRANSACTION
      EXEC usp_p2

    3.the third part
    resource-list
    ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks.dbo.T2
    id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
    owner id=process689978 mode=X
    waiter-list
    waiter id=process6891f8 mode=U requestType=wait
    keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks.dbo.T1
    indexname=nci_T1_COL1 id=lock3136fc0 mode=X
    associatedObjectId=72057594057457664
    owner-list
    owner id=process6891f8 mode=X
    waiter-list
    waiter id=process689978 mode=U requestType=wait

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  2. Erland Sommarskog 101.4K Reputation points MVP
    2020-09-15T12:35:13.597+00:00

    The best long-term solution is to rewrite the procedure to be set-based.

    A mid-term solution is to make sure that there are indexes that support the DELETE query that figures in the deadlock. You will need to grab the execution plan to see that indexes are actually being used.

    The best short-term solution is probably to change the client so that it does not support parallel calls. The way the procedure is written it is not apt for parallel processing. (This sort of serialisation can also be done in the stored procedure, but I guess it is easier for you to implement in the client.)

    Previously, I suggested that you should add a retry on deadlocks. Given how many processes that clash in this deadlock I don't think this is a good idea, because it may take some time until the deadlock has been entirely resolved. (SQL Server errors out one process at a time.)

    1 person found this answer helpful.
    0 comments
  3. Viorel 112.5K Reputation points
    2020-09-11T18:42:43.323+00:00

    Theoretically, you can try rerunning the transaction, as suggested in the message, i.e. to repeat the operation in C# (in a loop, maybe with short delays) after intercepting this kind of errors. (The operations can be also rerun in SQL). This is a simplistic approach.

    It is probably possible to reduce the probability of such collisions (tuning SQL statements, locking), but the retry logic can be kept, I think.

    See some recommendations: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#handling-deadlocks

  4. Erland Sommarskog 101.4K Reputation points MVP
    2020-09-11T22:08:21.497+00:00

    First of all, you are using the RTM version of SQL 2017. The most recent CU is CU22. You should download and install it as soon as possible. It is unlikely that it will resolve your issue, but you should not be such an old version.

    As for the deadlock, are you running multiple instances of this procedure in parallel?

    I am not going to read all that code to analyse where the deadlock may be. And for that matter, the code may not have to be changed at all - it could just be a matter of indexing that needs to be improved.

    Do you have trace flag 1222 enabled? If not enable it by adding -T 1222 as a startup parameter in the SQL Server Configuration Manager. This will give an output of a deadlock trace in the SQL Server error. You need this output to be able to analyse the deadlock.

    I would also advice you remove SET QUOTED_IDENTIFIER OFF, because this leads you off the beaten track, and there can be nasty surprising hiding.

    If the deadlocks are not that frequent, you could also opt to sweep them under the carpet by putting the line 

    Again:

    just before BEGIN TRY, and in the CATCH handler, you add 

    IF error_number() = 1222 GOTO Again
  5. Dan Guzman 9,211 Reputation points
    2020-09-12T13:40:14.63+00:00

    Below is an example query to extract recent deadlock events from the system_health trace. This will help identify statements leading to the deadlock.

    As I mentioned in my comment to your identical question on StackOverflow (it's considered rude to multi-post the same question on different forms), I suspect the non-sargable TRIM functions contribute to the deadlocks. Applying a function to a column in a WHERE clause predicate precludes efficient index use, resulting in a full scan even when and index exists on the column and increases the likelihood of deadlocks. Scans by different sessions will block each other when they encounter uncommitted deleted rows by other sessions. Consider removing those TRIM functions. You are trimming the values during insert anyway so it seems unnecessary.

    --get xml_deadlock_report from system_health session file target
WITH
      --get full path to current system_health trace file
      CurrentSystemHealthTraceFile AS (
        SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
        FROM sys.dm_xe_session_targets
        WHERE
            target_name = 'event_file'
            AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
    )
      --get trace folder name and add base name of system_health trace file with wildcard
    , BaseSystemHealthFileName AS (
        SELECT 
            REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
        FROM CurrentSystemHealthTraceFile
        )
      --get xml_deadlock_report events from all system_health trace files
    , DeadLockReports AS (
        SELECT CAST(event_data AS xml) AS event_data
        FROM BaseSystemHealthFileName
        CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS xed
        WHERE xed.object_name like 'xml_deadlock_report'
    )
--display 10 most recent deadlocks
SELECT TOP 10
      DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS LocalTime
    , event_data AS DeadlockReport
FROM DeadLockReports
ORDER BY LocalTime ASC;