时间戳

首页 > 解决方案 > 事务逻辑和错误处理问题

sql-server - 事务逻辑和错误处理问题

问题描述

我正在关注遵循 ACID 概念的交易在线教程。我有这两张表：

-- Create Product table
CREATE TABLE Product
(
  ProductId INT PRIMARY KEY,
  Name VARCHAR(50),
  Price INT,
  Quantity INT
)
GO

-- Populate the Product Table with some test data
INSERT INTO Product VALUES(101, 'Laptop', 1234, 100)
INSERT INTO Product VALUES(102, 'Desktop', 3456, 150)
INSERT INTO Product VALUES(103, 'Tablet', 5678, 200)
INSERT INTO Product VALUES(104, 'Mobile', 7890, 250)
GO

-- Create ProductSales table
CREATE TABLE ProductSales
(
  ProductSalesId INT PRIMARY KEY,
  ProductId INT,
  QuantitySold INT
) 
GO

我已经创建了一个带有交易的存储过程，我在其中提供了 productId 和要销售的数量。

这是我的存储过程：

CREATE PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN
  -- First we need to Check the stock available for the product we want to sell
  DECLARE @StockAvailable INT

  SELECT @StockAvailable = Quantity 
  FROM Product 
  WHERE ProductId = @ProductId

  -- We need to throw an error to the calling application 
  -- if the stock is less than the quantity we want to sell
  IF(@StockAvailable< @QuantityToSell)
  BEGIN
    Raiserror('Enough Stock is not available',16,1)
  END
  -- If enough stock is available
  ELSE
  BEGIN
    BEGIN TRY
      -- We need to start the transaction
      BEGIN TRANSACTION

      -- First we need to reduce the quantity available
      UPDATE    Product SET 
          Quantity = (Quantity - @QuantityToSell)
      WHERE ProductID = @ProductID

      -- Calculate MAX ProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT    @MaxProductSalesId = CASE 
          WHEN  MAX(ProductSalesId) IS NULL THEN 0 
          ELSE MAX(ProductSalesId) 
          END 
      FROM  ProductSales

      -- Increment @MaxProductSalesId by 1, so we don't get a primary key violation
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- We need to insert the quantity sold into the ProductSales table
      INSERT INTO ProductSales(ProductSalesId, ProductId, QuantitySold)
      VALUES(@MaxProductSalesId, @ProductId, @QuantityToSell)

      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION
      SELECT    ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage,
          ERROR_PROCEDURE() as ErrorProcedure,
          ERROR_STATE() as ErrorState,
          ERROR_SEVERITY() as ErrorSeverity,
          ERROR_LINE() as ErrorLine
    END CATCH
  End
END

go

spSellProduct @ProductId=103, @QuantityToSell=300

我检查数量是否少于要出售的数量，然后我会产生错误。如果没有遇到错误，那么我开始交易。

然后我从产品表中扣除数量。之后，我在 productSales 表中增加 ProductSalesId 并插入带有 ProductId 和 QuantitySold 的新行。

如果发生任何错误，我会尝试在我的 catch 批处理中处理它们并回滚事务。

因此，在我的 ProductId 103 的产品表中，它有 200 个库存数量，我按以下方式执行存储过程：

spSellProduct @ProductId=103, @QuantityToSell=300

当@QuantityToSell 大于 Quantity 时，不会生成错误并且事务不会回滚。在这种情况下，ProductId 的数量是 200，而我尝试销售的数量是 300。

我在这里做错了什么？

标签： sql-serverstored-procedurestransactions

解决方案

您应该将所有过程代码放在 try catch 中，并在其中放置事务。尝试使用以下代码。

CREATE PROCEDURE [dbo].[spSellProduct]
@ProductID INT,
@QuantityToSell INT
AS
BEGIN
  BEGIN TRY
     BEGIN TRANSACTION 
          -- First we need to Check the stock available for the product we want to sell
          DECLARE @StockAvailable INT

          SELECT @StockAvailable = Quantity 
          FROM Product 
          WHERE ProductId = @ProductId

          -- We need to throw an error to the calling application 
          -- if the stock is less than the quantity we want to sell
          IF(@StockAvailable< @QuantityToSell)
          BEGIN
            Raiserror('Enough Stock is not available',16,1)
          END
          -- If enough stock is available
          ELSE
          BEGIN
               -- First we need to reduce the quantity available
              UPDATE    Product SET 
                  Quantity = (Quantity - @QuantityToSell)
              WHERE ProductID = @ProductID

              -- Calculate MAX ProductSalesId
              DECLARE @MaxProductSalesId INT
              SELECT    @MaxProductSalesId = CASE 
                  WHEN  MAX(ProductSalesId) IS NULL THEN 0 
                  ELSE MAX(ProductSalesId) 
                  END 
              FROM  ProductSales

              -- Increment @MaxProductSalesId by 1, so we don't get a primary key violation
              Set @MaxProductSalesId = @MaxProductSalesId + 1

              -- We need to insert the quantity sold into the ProductSales table
              INSERT INTO ProductSales(ProductSalesId, ProductId, QuantitySold)
              VALUES(@MaxProductSalesId, @ProductId, @QuantityToSell)
            END
        COMMIT TRANSACTION CHECKTR
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION 
      SELECT    ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage,
          ERROR_PROCEDURE() as ErrorProcedure,
          ERROR_STATE() as ErrorState,
          ERROR_SEVERITY() as ErrorSeverity,
          ERROR_LINE() as ErrorLine
    END CATCH
END

推荐阅读