Table getting locked during insert or update statement running on that table

Question

Table getting locked during insert or update statement running on that table

Sudip Bhatt 2,281

Apologized to post long code. basically from front end i am sending xml and load xml into cursor. iterate in cursor loop and insert or update data one by one.

i saw when i call this SP then TblLineItemTemplate table is getting locked. because from another session when i execute a select * from TblLineItemTemplate table then cursor was spinning and got data after long time.

all insert and update operation done under begin TRAN and commit TRAN. it is not clear to me what mistake i made in my SP for which whole table is getting locked. please see my code and tell me what to alter in code as a result no table should locked during insert or update rather rows should be locked that is ok but table should be return data when another session query that table.
please advise. thanks

--<?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 NVARCHAR(MAX), @LineItem NVARCHAR(MAX), @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 NVARCHAR(MAX),                                  
 @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 TickerID=@TickerID                    
 --Deleting data from tblTicker_LiConfig              
 DELETE FROM tblTicker_LiConfig WHERE TickerID=@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 TickerID=@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 TickerID=@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   NVARCHAR(MAX),                                                            
   LineItem  NVARCHAR(MAX),                                                            
   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 TickerID=@TickerID AND LineItem=@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 TickerID=@TickerID AND LineItem=@LineItem      
 UPDATE TblLineItemTemplate SET XFundCode=TRIM(@XFundCode), Action='U',UserID=@UserID WHERE ID=@LineItemID AND TickerID=@TickerID    
   END                                                


 IF NOT EXISTS(SELECT * FROM tblSectionTemplate WHERE TickerID=@TickerID AND Section=@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 TickerID=@TickerID AND Section=@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 Period=@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 Period=@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                                                 

  BEGIN /* Find Mismatch Section,Lineitem and set Action to D of those Li */
    CREATE Table #TmpSecLiTable 
    (
        ID int Identity,
        Section NVARCHAR(MAX),
        LineItem NVARCHAR(MAX)
    )

    INSERT INTO #TmpSecLiTable(Section,LineItem)
        SELECT DISTINCT UPPER(TRIM(tblSectionTemplate.Section)) AS Section, UPPER(TRIM(TblLineItemTemplate.LineItem)) AS LineItem
        FROM tblSectionLineItemTemplate INNER JOIN
        TblLineItemTemplate ON tblSectionLineItemTemplate.LineItemID = TblLineItemTemplate.ID INNER JOIN
        tblSectionTemplate ON tblSectionLineItemTemplate.SectionID = tblSectionTemplate.SectionID
        WHERE TblLineItemTemplate.TickerID=@TickerID AND tblSectionTemplate.TickerID=@TickerID
        AND TblLineItemTemplate.action<>'D' AND tblSectionTemplate.active='A'

        EXCEPT

        SELECT DISTINCT UPPER(TRIM(col.value('(Section/text())[1]', 'NVARCHAR(MAX)')))  AS Section,
        UPPER(TRIM(col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)')))  AS LineItem
        FROM @BogyXML.nodes('/Root/PeriodicalData') AS tab (col)

    --SELECT * FROM #TmpSecLiTable

    IF EXISTS(SELECT count(*) FROM #TmpSecLiTable)
    BEGIN
        UPDATE TblLineItemTemplate SET Action='D' 
        WHERE LineItem IN (SELECT LineItem FROM #TmpSecLiTable) AND TickerID=@TickerID
    END

    DROP TABLE #TmpSecLiTable
  END

 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     NVARCHAR(MAX),                                                            
   LI      NVARCHAR(MAX),                                                            
   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 TickerID=@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 TickerID=@TickerID AND Section=@Section                                     
 SELECT @LineItemID=ID FROM TblLineItemTemplate WHERE TickerID=@TickerID AND LineItem=@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 @Standr

Dan Guzman 9,516 Reputation points

2020-10-31T12:02:57.543+00:00

On a side note, I see you have SET QUOTED_IDENTIFIER OFF; in the proc code. It isn't clear to me why this exists but I suggest you create the proc with SET QUOTED_IDENTIFIER ON; and remove the SET QUOTED_IDENTIFIER statements. That will ensure the proc is compatible with other SQL Server features, like filtered indexes that require the ON setting.

Answer accepted by question author

0 additional answers

Your answer

Dan Guzman 9,516 Reputation points

2020-10-31T12:02:57.543+00:00

On a side note, I see you have SET QUOTED_IDENTIFIER OFF; in the proc code. It isn't clear to me why this exists but I suggest you create the proc with SET QUOTED_IDENTIFIER ON; and remove the SET QUOTED_IDENTIFIER statements. That will ensure the proc is compatible with other SQL Server features, like filtered indexes that require the ON setting.

Answer 1

Erland Sommarskog 134.6K MVP Volunteer Moderator

The entire procedure is a single transaction - it starts with BEGIN TRAN and ends with COMMIT TRAN. This means that all rows you update are locked while the procedure is running. Whether that is a mistake or not, I don't know, because it depends on the semantics of the operation. If the XML must be handled as a unit, I guess it has to be a single transaction.

Then again, I am not sure it has a to be a loop - it would most likely run faster as a set-based operation.

And blocking may have to happen. If you configure the database in READ_COMMITED_SNAPSHOT, readers and writers will not block each other. On the other hand, readers will read stale data, which may or may not be an issue.

Sudip Bhatt 2,281 Reputation points

2020-10-31T07:12:10.23+00:00

Sir thanks for your reply.

i am inserting data one by one in loop because i need to hold ID of new inserted data which i need to store in another table.

if i would insert data into table in one go then how could i store newly inserted lineitem id. would it be possible to hold each newly inserted lineitem id i use go for set based operation for insert and update.

looking for suggestion. thanks

Erland Sommarskog 134.6K MVP Volunteer Moderator

You can use the OUTPUT clause. Here is a quick demo:

 CREATE TABLE #myDemo (id int IDENTITY, data sysname)
 DECLARE @output TABLE (id int, data sysname)
 INSERT #myDemo(data)
    OUTPUT inserted.id, inserted.data INTO @output(id, data)
    SELECT name FROM sys.objects
SELECT *FROM @outputag-0045

Sudip Bhatt 2,281 Reputation points

2020-11-02T08:26:02.923+00:00

Sir Thanks for your answer. :)

Share via

Table getting locked during insert or update statement running on that table

0 additional answers

Your answer