how to prevent negative stock in database

Question

Hi,

I have one stock table that have no of item avaibale in stock and same time 2 user done checkout then my application update the table stock but its gose negative. I have checked condition in application .net core but deu to few sec diffrences entiry got updated. Then i supposed its can control by using transaction but still i have to check stock at the time of update so transaction can work in this case or i have to use diffrent way to prevent negative.

Answer 1

One way to address this concurrency problem is to include additional criteria in the UPDATE statement to ensure the requested quantity is still available:

UPDATE dbo.Stock  
SET AvailableQuantity -= @OrderQuantity  
WHERE  
	StockID = @StockID  
	AND AvailableQuantity >= @OrderQuantity;  

If no rows are updated, the available quantity needed for this order was depleted by another user. The row count can be checked in the app code after the update (e.g. ExecuteNonQuery() result) or an error can be raised in T-SQL and caught in the app code to display a message to the user:

UPDATE dbo.Stock  
SET AvailableQuantity -= @OrderQuantity  
WHERE  
	StockID = @StockID  
	AND AvailableQuantity >= @OrderQuantity;  
IF @@ROWCOUNT = 0 THROW 50000, 'Insufficient quantity available', 0;  

Answer 2

Hi @Dharmesh Sharma ,

Option 1.
Directly set the database field data as unsigned integer, so that the subtracted inventory field value is less than zero will directly execute SQL statement to report an error

Option 2.
Use the case when judgment statement, for example:

update dbo.Stock set count = case when count >=1 then count-1 else count end  

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Hi,

I have implement like this

ALTER PROCEDURE [dbo].[Usp_reduceSeat]  
    @inventoryId INT,  
    @seat INT,  
	@Status INT = NULL OUTPUT  
AS  
BEGIN  
    DECLARE @availSeat INT  
             
  
  
    SET NOCOUNT ON;  
    BEGIN TRANSACTION T1;  
  
    SELECT @availSeat = Seat  
    FROM dbo.AgentFlightInventoryDetails  
    WHERE ID = @inventoryId;  
    IF @availSeat >= @seat  
    BEGIN  
        UPDATE dbo.AgentFlightInventoryDetails  
        SET Seat = Seat - @seat,  
            SeatSold = SeatSold + @seat  
        WHERE ID = @inventoryId ;  
        SET @Status = 0;  
    END;  
    ELSE  
    BEGIN  
        SET @Status = 1;  
    END;  
  
  
    COMMIT TRANSACTION T1;  
  
    SELECT 1 AS 'ID', @Status AS 'Status'  
  
  
END;  

Answer 4

Change you code to not look at the value first

ALTER PROCEDURE [dbo].[Usp_reduceSeat]  
     @inventoryId INT,  
     @seat INT,  
     @Status INT = NULL OUTPUT  
 AS  
 BEGIN  
     DECLARE @availSeat INT  
                 
     SET NOCOUNT ON;  
     BEGIN TRANSACTION T1;  
  
         UPDATE dbo.AgentFlightInventoryDetails  
         SET Seat = Seat - @seat,  
             SeatSold = SeatSold + @seat  
         WHERE ID = @inventoryId   
 AND Seat >= @seat;  
 IF @@ROWCOUNT > 0  
         SET @Status = 0;  
     ELSE  
         SET @Status = 1;  
      
      
     COMMIT TRANSACTION T1;  
      
     SELECT 1 AS 'ID', @Status AS 'Status'  
      
      
 END;  

Answer 5

Tom's solution should work. Here is another one that makes a modification your original procedure:

 SELECT @availSeat = Seat  
 FROM dbo.AgentFlightInventoryDetails WITH (UPDLOCK)  
 WHERE ID = @inventoryId;  
 IF @availSeat >= @seat  

With the UPDLOCK you take an update lock on the row. This lock does not block other readers who only want a shared lock, but it blocks other processes that also want an update lock on the row. The meaning of this lock is "I'm reading this row because I will update it later."

This lock will prevent that two concurrent processes both reads the stock and think it's good, and then both take the last item that is left.