DB存储过程并发请求时多表插入数据丢失，求存储过程级解决方案

问题场景 #

在数据库存储过程中使用常规INSERT语句向多表插入数据，当同时收到多个服务请求时，部分表的数据插入会被跳过，转而处理下一个请求，需要在存储过程层面解决该并发问题。

原存储过程代码：

INSERT INTO RepurchaseMaster_Reg (RegId, SprRegId, SalesTo, FId, BillNo, BillDate, TotQty, TotPV, Status, SesId, UqId, IpAddress, TotalDP, TotUnitPrice, CommDate, Remarks, CBPayNo, MemType, BillType, OrdType,      
  TaxType, TaxSubType, SaleType, TotGross, TotNet, TotValue, TotCGST, TotSGST, TotIGST, SplOffCode, SplOffAmt, TotalBP, TaxAmt, NetAmt, UpdatedDate, PayableAmount, TotMRP, RepType)      
    SELECT      
      @RegId,      
      @SprRegId,      
      @SalesTo,      
      @FId,      
      @BillNo,      
      @BillDate,      
      isnull(@TotQty,0),      
      @TotPV,      
      @Status,      
      @SessId,      
      @UqId,      
      @IpAddress,      
      @TotalDP,      
      @TotUnitPrice,      
      @CommDate,      
      @Remarks,      
      @CBPayNo,      
      @MemType,      
      @BillType,      
      @OrdType,      
      @TaxType,      
      @TaxSubType,      
      @SaleType,      
      @TotGross,      
      @TotNet,      
      @TotValue,      
      @TotCGST,      
      @TotSGST,      
      @TotIGST,      
      @SplOffCode,      
      @SplOffAmt,      
      0,      
      0,      
      0,      
      GETDATE(),      
      @PayableAmt,      
      @TotMRP,      
      @RepType      
  
  SET @RMid = @@identity      
  
  IF @BillType = 2      
  BEGIN      
  
    INSERT INTO ProductCredits (RegId, Sprno, InAmt, OutAmt, Dated, Descr, Remarks, TypeOfInc, billno, rmid, ExpiredDate, LockedDate)      
      SELECT      
        0 RegId,      
        @RegId Sprno,      
        @TotPV InAmt,      
        0 OutAmt,      
        GETDATE() Dated,      
        'Product Purchase thru ID : ' + @BillNo Descr,      
       'Thru Product Purchase' Remarks,      
        'Product Purchase' TypeOfInc,      
        @BillNo billno,      
        @RMid,      
        DATEADD(yyyy, 1, GETDATE()) ExpiredDate,      
        DATEADD(yyyy, 1, GETDATE()) LockedDate      
  
    UPDATE RepurchaseMaster_Reg      
    SET TotPV = 0      
    WHERE rmid = @RMid      
  END      
  
INSERT INTO RepurchaseItems_Reg (rmid, pid, qty, MRP, DP, UnitPrice, GrossValue, DisCount, NetValue, PV, GST, IGSTAmt, CGST, CGSTAmt, SGST, SGSTAmt, TotValue, BP, amount, BV      
  , TaxAmt, CGSTTax, CGSTTaxAmt, SGSTTax, SGSTTaxAmt)      
    SELECT      
      @rmid,      
      ISNULL(ic.ProdItemId, t.pid) pid,      
      qty,      
      MRP,      
      DP,      
      UnitPrice,      
      GrossValue,      
      DisCount,      
      NetValue,      
      PV,     
GST,      
      IGSTAmt,      
      CGST,      
      CGSTAmt,      
      SGST,      
      SGSTAmt,      
      TotValue,      
      ISNULL(BP, 0),      
      ISNULL(Amount, 0),      
      BV,      
      ISNULL(IGSTAmt, 0),      
      CGST,      
      CGSTAmt,      
      SGST,      
      SGSTAmt      
 FROM tmpRPProductsItems t      
    OUTER APPLY (SELECT TOP 1      
      ProdItemId      
    FROM ProductItemCodes      
    WHERE pid = t.pid) ic      
    WHERE UQID = @UqId      
    AND sessionid = @SessId      
    AND RegId = @RegId      
    AND FId = @FId      
  
  INSERT INTO RepurchasePayments_Reg (Regid, RMid, Mop, MopNo, MopDate, MopBank, Amount, Sesid, DDNo, ChequeNo, vouchercode)      
    SELECT      
      @regid,      
      @RMid,      
      MOP,      
      @MopRefNo,      
      @Billdate,      
      '',      
      Amount,      
      @sessid,      
      DDNo,      
      '',      
      ''      
    FROM TmpPayments      
    WHERE Uniqid = @UqId      
    AND SessId = @SessId      
    AND RegId = @RegId      
    AND FId = @FId      
  
  IF (@ReqToType = 'Stores')      
  BEGIN      
    INSERT INTO RepurchaseReqToAddress_Reg (Rmid, Code, Name, Addr, State, District, City, Pincode, Mobile, Email, GSTNo)      
      SELECT      
        @RMid,      
        'Stores',      
        Name,      
        Address,      
        State,      
        District,      
        City,      
        LEFT(Pincode, 6),      
        LEFT(Mobile, 15),      
        Email,      
        LEFT(GSTNo, 15)      
      FROM StoreProfileAddress(NOLOCK)      
      WHERE CountryId = @CountryId      
  
  
    INSERT INTO RepurchaseAddress_Reg (Rmid, Name, Addr, State, District, City, Mobile, Email, Pincode, GSTNo)      
      SELECT      
        @RMid,      
        @SName,      
        @Addr,      
        @StId,      
        @District,      
        @City,      
        @Mobile,      
        Email,      
        @Pin,      
        LEFT(GSTNo, 15)      
      FROM StoreProfileAddress(NOLOCK)      
      WHERE CountryId = @CountryId      
END

解决方案 #

针对并发场景下的数据丢失问题，需从以下几个方面优化存储过程：

• 添加完整事务控制：将所有相关的插入、更新操作包裹在显式事务中，确保一组操作的原子性——要么全部执行成功提交，要么出错时全部回滚，避免部分表插入成功、部分失败的不一致状态。
• 替换@@IDENTITY为SCOPE_IDENTITY()：@@IDENTITY会返回当前会话中所有作用域的最后自增ID，若存在触发器或其他跨作用域的自增操作，会导致获取错误的ID；SCOPE_IDENTITY()仅返回当前存储过程作用域内生成的自增ID，更安全可靠。
• 保障临时表的并发隔离：若tmpRPProductsItems、TmpPayments是全局临时表（以##开头），会被所有会话共享，需改为局部临时表（以#开头）；如果是永久表用作临时存储，需确保过滤条件（UQID、sessionid等）能严格区分不同请求的数据，避免交叉读取。
• 添加错误捕获与回滚机制：使用TRY/CATCH块捕获执行过程中的异常，一旦触发错误立即回滚事务，防止脏数据残留；同时可记录错误信息便于排查。
• 移除不必要的NOLOCK提示：StoreProfileAddress(NOLOCK)会导致脏读，在事务中无需该提示，反而可能获取到不一致的地址数据，影响插入结果的准确性。

修改后的存储过程代码 #

BEGIN TRANSACTION
BEGIN TRY
  INSERT INTO RepurchaseMaster_Reg (RegId, SprRegId, SalesTo, FId, BillNo, BillDate, TotQty, TotPV, Status, SesId, UqId, IpAddress, TotalDP, TotUnitPrice, CommDate, Remarks, CBPayNo, MemType, BillType, OrdType,      
    TaxType, TaxSubType, SaleType, TotGross, TotNet, TotValue, TotCGST, TotSGST, TotIGST, SplOffCode, SplOffAmt, TotalBP, TaxAmt, NetAmt, UpdatedDate, PayableAmount, TotMRP, RepType)      
      SELECT      
        @RegId,      
        @SprRegId,      
        @SalesTo,      
        @FId,      
        @BillNo,      
        @BillDate,      
        ISNULL(@TotQty,0),      
        @TotPV,      
        @Status,      
        @SessId,      
        @UqId,      
        @IpAddress,      
        @TotalDP,      
        @TotUnitPrice,      
        @CommDate,      
        @Remarks,      
        @CBPayNo,      
        @MemType,      
        @BillType,      
        @OrdType,      
        @TaxType,      
        @TaxSubType,      
        @SaleType,      
        @TotGross,      
        @TotNet,      
        @TotValue,      
        @TotCGST,      
        @TotSGST,      
        @TotIGST,      
        @SplOffCode,      
        @SplOffAmt,      
        0,      
        0,      
        0,      
        GETDATE(),      
        @PayableAmt,      
        @TotMRP,      
        @RepType      
  
  -- 使用SCOPE_IDENTITY()获取当前作用域的自增ID
  SET @RMid = SCOPE_IDENTITY()      
  
  IF @BillType = 2      
  BEGIN      
    INSERT INTO ProductCredits (RegId, Sprno, InAmt, OutAmt, Dated, Descr, Remarks, TypeOfInc, billno, rmid, ExpiredDate, LockedDate)      
      SELECT      
        0 RegId,      
        @RegId Sprno,      
        @TotPV InAmt,      
        0 OutAmt,      
        GETDATE() Dated,      
        '产品采购单号 : ' + @BillNo Descr,      
       '通过产品采购' Remarks,      
        '产品采购' TypeOfInc,      
        @BillNo billno,      
        @RMid,      
        DATEADD(yyyy, 1, GETDATE()) ExpiredDate,      
        DATEADD(yyyy, 1, GETDATE()) LockedDate      
  
    UPDATE RepurchaseMaster_Reg      
    SET TotPV = 0      
    WHERE rmid = @RMid      
  END      
  
  INSERT INTO RepurchaseItems_Reg (rmid, pid, qty, MRP, DP, UnitPrice, GrossValue, DisCount, NetValue, PV, GST, IGSTAmt, CGST, CGSTAmt, SGST, SGSTAmt, TotValue, BP, amount, BV      
    , TaxAmt, CGSTTax, CGSTTaxAmt, SGSTTax, SGSTTaxAmt)      
      SELECT      
        @rmid,      
        ISNULL(ic.ProdItemId, t.pid) pid,      
        qty,      
        MRP,      
        DP,      
        UnitPrice,      
        GrossValue,      
        DisCount,      
        NetValue,      
        PV,     
        GST,      
        IGSTAmt,      
        CGST,      
        CGSTAmt,      
        SGST,      
        SGSTAmt,      
        TotValue,      
        ISNULL(BP, 0),      
        ISNULL(Amount, 0),      
        BV,      
        ISNULL(IGSTAmt, 0),      
        CGST,      
        CGSTAmt,      
        SGST,      
        SGSTAmt      
   FROM tmpRPProductsItems t      
      OUTER APPLY (SELECT TOP 1      
        ProdItemId      
      FROM ProductItemCodes      
      WHERE pid = t.pid) ic      
      WHERE UQID = @UqId      
      AND sessionid = @SessId      
      AND RegId = @RegId      
      AND FId = @FId      
  
  INSERT INTO RepurchasePayments_Reg (Regid, RMid, Mop, MopNo, MopDate, MopBank, Amount, Sesid, DDNo, ChequeNo, vouchercode)      
    SELECT      
      @regid,      
      @RMid,      
      MOP,      
      @MopRefNo,      
      @Billdate,      
      '',      
      Amount,      
      @sessid,      
      DDNo,      
      '',      
      ''      
    FROM TmpPayments      
    WHERE Uniqid = @UqId      
    AND SessId = @SessId      
    AND RegId = @RegId      
    AND FId = @FId      
  
  IF (@ReqToType = 'Stores')      
  BEGIN      
    INSERT INTO RepurchaseReqToAddress_Reg (Rmid, Code, Name, Addr, State, District, City, Pincode, Mobile, Email, GSTNo)      
      SELECT      
        @RMid,      
        'Stores',      
        Name,      
        Address,      
        State,      
        District,      
        City,      
        LEFT(Pincode, 6),      
        LEFT(Mobile, 15),      
        Email,      
        LEFT(GSTNo, 15)      
      FROM StoreProfileAddress      
      WHERE CountryId = @CountryId      
  
    INSERT INTO RepurchaseAddress_Reg (Rmid, Name, Addr, State, District, City, Mobile, Email, Pincode, GSTNo)      
      SELECT      
        @RMid,      
        @SName,      
        @Addr,      
        @StId,      
        @District,      
        @City,      
        @Mobile,      
        Email,      
        @Pin,      
        LEFT(GSTNo, 15)      
      FROM StoreProfileAddress      
      WHERE CountryId = @CountryId      
  END

  -- 所有操作成功，提交事务
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  -- 捕获错误，回滚事务
  ROLLBACK TRANSACTION
  -- 可选：记录错误信息到日志表
  -- INSERT INTO ErrorLog (ErrorMessage, ErrorTime) VALUES (ERROR_MESSAGE(), GETDATE())
  THROW; -- 抛出错误，让调用方感知
END CATCH

内容的提问来源于stack exchange，提问作者KALAIYARASI I