事务逻辑和错误处理问题

Question

我正在关注一个遵循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

我创建了一个包含事务的存储过程，在其中我提供了产品 ID 和要销售的数量。

这是我的存储过程：

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

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

然后我从产品表中扣除数量。 之后，我在产品销售表中递增产品销售ID，并插入带有产品ID和销售数量的新行。

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

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

spSellProduct @ProductId=103, @QuantityToSell=300

不会生成错误，并且当@QuantityToSell大于数量时，事务不会回滚。在本例中，ProductId 的数量为 200，我尝试销售的数量为 300。

我在这里做错了什么？

Answer 1

你应该把所有的过程代码放在一个 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