locked
C# Data Delete and insert from winform application throwing dead lock error RRS feed

  • Question

  • 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.

    screen shot attached

    so here is my SP code which is bit long.

    USE [RDSS_WB]
    GO
    /****** Object:  StoredProcedure [dbo].[USP_CRSSaveTickerBogey]    Script Date: 9/11/2020 11:19:53 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
                                
    --<?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 = ''                    
      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 '+@TmpCrossCalc2H,GETDATE())                    
      END    
        
      IF TRIM(@TmpCrossCalcFY) <> '' AND @CrossCalcFY = ''                    
      BEGIN                    
        INSERT INTO tblLog_Data(TickerID,EventType,ErrorText,EventDate) VALUES(@TickerID,'Error type is CrossCalFY Formula parse during insertion',                    
             'TickerID '+@TickerID+' CrossCalFY Formula could not parse. data stored in tblTicker_LiConfig table and record id '+CAST(@ConfigID AS VARCHAR(MAX))+' Formula is '+@TmpCrossCalcFY,GETDATE())                    
      END                    
                        
     END            
     END
                         
     SET @ConfigID=0                    
     SET @StandrdFormula = ''                    
     SET @BlueMatrix1stElementFormula = ''                    
     SET @CrossCalc1Q = ''                    
     SET @CrossCalc2Q = ''        
     SET @CrossCalc1H = ''    
     SET @CrossCalc3Q = ''                    
     SET @CrossCalc4Q = ''              
     SET @CrossCalc2H = ''    
     SET @CrossCalcFY = ''  
     SET @TmpCrossCalcFY = ''   
     SET @SummaryTab =''    
    
     SET @QCCheck = ''
     SET @QCType = ''
     SET @BlueMatrix1stElement = ''
     SET @DevelopmentStage = ''
     SET @AllowedDecimalPlace = ''
     SET @CurrencySign = ''
     SET @CurrencyCode = ''
     SET @AllowPercentageSign = ''
     SET @AllowComma = ''
     SET @Section = ''
     SET @LI = ''
    
      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                            
        
      END                          
                              
      CLOSE CURRECORD                                                    
      DEALLOCATE CURRECORD                              
      COMMIT TRAN                                      
                            
      SET @STATUS='SUCCESS'                             
      SET QUOTED_IDENTIFIER ON;                        
     END TRY                                      
     BEGIN CATCH                                                
      --PRINT 'err'                                             
       ROLLBACK TRAN                                                
                                                    
       DECLARE @ErrorMessage NVARCHAR(4000);                     
       DECLARE @ErrorSeverity INT;                                                  
       DECLARE @ErrorState INT;                                                  
                                                    
       SELECT @ErrorMessage = ERROR_MESSAGE() + ' occurred at Line_Number: ' + CAST(ERROR_LINE() AS VARCHAR(50)),                                                  
        @ErrorSeverity = ERROR_SEVERITY(),                                                  
        @ErrorState = ERROR_STATE();                                 
                               
        SET @STATUS='FAIL'                                              
        PRINT 'Err---> '+  @ErrorMessage                                         
        RAISERROR                           
        (                          
       @ErrorMessage, -- Message text.                                                  
       @ErrorSeverity, -- Severity.                                                  
       @ErrorState -- State.                                                  
        );                               
      END CATCH                            
     END 

    so when 80 users using my application and all click on button which call this SP then this dead lock appear.

    please tell me a good suggestion to fix this problem. what i need to change in my c# code and in sql server end SP code.

    i also use command timeout = 0 is it good approach to use command timeout = 0

    please give me right solution to get rid of this error. thanks



    • Edited by Sudip_inn Friday, September 11, 2020 5:15 PM
    Friday, September 11, 2020 5:02 PM

Answers

  • Try the initial retry logic, with minimal changes:

    bool retry;
    do
    {
       retry = false;
       try
       {
          // calling stored procedure
          . . . your code from the current body of ‘try-catch’
       }
       catch( SqlException exc )
       {
          if( !exc.Errors.Cast<SqlError>( ).Any( e => new[] { 1204, 1205, 1222 }.Contains( e.Number ) ) ) throw;
          retry = true;
          Thread.Sleep(111);
       }
    } while( retry );
    

    Put it between try and catch of your current code.

    • Marked as answer by Sudip_inn Saturday, September 12, 2020 11:53 AM
    Saturday, September 12, 2020 10:44 AM

All replies

  • Did you consider to rerun the transaction, as suggested by the message, i.e. to repeat the operation (in a loop) in C# after intercepting this kind of errors?

    Friday, September 11, 2020 5:57 PM
  • Did you consider to rerun the transaction, as suggested by the message, i.e. to repeat the operation (in a loop) in C# after intercepting this kind of errors?

    Sorry not clear what you are trying to say. what i need to return. i use BEGIN TRAN and at end i use COMMIT TRAN. i do not know how to return transaction from SP.

    please provide some code which clearly show me what i need to add in my SP code.

    also guide how to refactor my SP code. tell me do i need to change or add any line of code when calling SP from c# application ?

    in SP do i need to use any locking ? if yes which lock i should use ?

    waiting for your suggestion. i just very badly stuck. thanks

    Friday, September 11, 2020 6:54 PM
  • The following shows how to perform a retry every ten seconds up to five. I would do a max of three retries.

    https://stackoverflow.com/questions/9212255/transaction-process-id-was-deadlocked-on-lock-resources-with-another-process-a


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, September 11, 2020 10:07 PM
  • actually something missing in my SP body which create dead lock when 50 people start using my application and application calling my SP which first delete records from few tables and then insert into few tables with in loop.

    though i use BEGIN TRAN and COMMIT TRAN but still getting dead lock. probably i need to use some locking to avoid this problem but not aware how to construct my code with locking.

    Saturday, September 12, 2020 6:03 AM
  • This deadlock is not a critical error; it means that SQL Server is busy to process the requests from other 49 people. You can perform something else, or simply briefly wait, then try again. If you want to implement the retry logic in C#, which is probably easier, show the portion of your C# code that executes this stored procedure.

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

    Saturday, September 12, 2020 7:40 AM
  • private bool SaveSectionLiPeriodID(string strXML, string str10QKLiconfig)
            {
                bool status = false;
                try
                {
                    
                    // Calling store proc to insert data into db
                    string output;
                    //strXML = strXML.Replace("&amp;", "&");
                    //str10QKLiconfig = str10QKLiconfig.Replace("&amp;", "&");
    
                    string sConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RDSSDB"].ConnectionString;
                    //DataTable dt = str10QKLiconfig.ToDataTable();
    
                    using (SqlConnection con = new SqlConnection(sConnectionString))
                    {
                        //using (SqlCommand cmd = new SqlCommand("USP_InsertUniqueSectionsAndLineItems", con))
                        using (SqlCommand cmd = new SqlCommand("[USP_CRSSaveTickerBogey]", con))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandTimeout = 0; // 10 minute
                            cmd.Parameters.Add("@TickerID", SqlDbType.VarChar).Value = cmbTicker.SelectedValue.ToString().Trim();
                            cmd.Parameters.Add("@UserID", SqlDbType.VarChar).Value = GlobalVariable.strLoginID;
                            cmd.Parameters.Add("@BogyXML", SqlDbType.Xml).Value = strXML;
                            cmd.Parameters.Add("@LiConfigXML", SqlDbType.Xml).Value = str10QKLiconfig;
                            SqlParameter outPutParameter = new SqlParameter();
                            outPutParameter.ParameterName = "@STATUS";
                            outPutParameter.SqlDbType = System.Data.SqlDbType.VarChar;
                            outPutParameter.Direction = System.Data.ParameterDirection.Output;
                            outPutParameter.Size = 200;
                            cmd.Parameters.Add(outPutParameter);
    
                            con.Open();
                            cmd.ExecuteNonQuery();
                            output = outPutParameter.Value.ToString();
    
                            HideLoader();
    
                            if (output.Contains("SUCCESS"))
                            {
                                status = true;
                                MessageBox.Show("Data imported successfully");
                            }
    
                        }
                    }
                    
                }
                catch (Exception ex)
                {
                    HideLoader();
                    //showing error message
                    MessageBox.Show("Error " + ex.Message.ToString());
                }
                finally
                {
                    iSNewRecordInserted = false;
                    //hiding loader
                    HideLoader();
                }
                return status;
            }

    Sir this code i am using to call my SP and SP throw dead lock error and c# function capture the error and show in message box.

    please guide me how people develop big & robust product where million of user perform CRUD operation on same table without any dead lock and data gets inserted and updated in db very quickly.

    looking for your suggestion. thanks

    Saturday, September 12, 2020 9:09 AM
  • The sproc you are running is questionable. It may be doing too much in a multi user environment. Other situations of code using the same tables even in other applications in a concurrent usage secnario can cause tabel deadlocks, because code is not dealing with deadlocks by using Nolock or Read  Uncommitted.

    https://www.datanumen.com/blogs/4-key-tips-avoid-deadlocks-sql-server/

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15

    I would say that overall database table usage has not been factored into the overall usage of the tables to prevent deadlocks in a concurrent usage situation used by your application or other applications using like tables concurrently.



    • Edited by DA924x Saturday, September 12, 2020 9:26 AM
    Saturday, September 12, 2020 9:23 AM
  • Try the initial retry logic, with minimal changes:

    bool retry;
    do
    {
       retry = false;
       try
       {
          // calling stored procedure
          . . . your code from the current body of ‘try-catch’
       }
       catch( SqlException exc )
       {
          if( !exc.Errors.Cast<SqlError>( ).Any( e => new[] { 1204, 1205, 1222 }.Contains( e.Number ) ) ) throw;
          retry = true;
          Thread.Sleep(111);
       }
    } while( retry );
    

    Put it between try and catch of your current code.

    • Marked as answer by Sudip_inn Saturday, September 12, 2020 11:53 AM
    Saturday, September 12, 2020 10:44 AM