Write SQL that reserves inventory only when enough stock remains, without a separate check-then-update race.

Two requests concurrently try to reserve 4 units when 5 remain. Only one conditional update can affect the inventory row; the other sees insufficient remaining quantity and fails.

• Require @Quantity > 0 before executing the transaction.
• The update and reservation insert succeed or fail together.
• Treat zero affected rows as unavailable or unknown product.
• Use application-generated unique reservation ids when retry safety is required.

I put the availability rule in the UPDATE predicate so checking and decrementing are one atomic statement. @@ROWCOUNT tells me whether the reservation won the race. A transaction then couples that inventory change to the reservation record, and XACT_ABORT plus TRY/CATCH ensures an insert failure does not leave stock decremented.

SET XACT_ABORT ON;

IF @Quantity IS NULL OR @Quantity <= 0
    THROW 50000, 'Quantity must be greater than zero.', 1;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Inventory
    SET AvailableQuantity = AvailableQuantity - @Quantity
    WHERE ProductId = @ProductId
      AND AvailableQuantity >= @Quantity;

    IF @@ROWCOUNT = 0
        THROW 50001, 'Insufficient inventory or unknown product.', 1;

    INSERT dbo.InventoryReservations
        (ReservationId, ProductId, Quantity, CreatedAtUtc)
    VALUES
        (@ReservationId, @ProductId, @Quantity, SYSUTCDATETIME());

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;

The update targets one primary-key row, so lock scope is small and contention is localized to the product being reserved. Hot products may still serialize heavily. A unique key on ReservationId is important when callers can retry the operation.

• Quantity is zero or negative.
• The product row does not exist.
• Available quantity exactly equals the request.
• The reservation insert fails after the update.

• Run concurrent reservations whose combined quantity exceeds stock.
• Force the reservation insert to violate a unique key and verify inventory rolls back.
• Reserve the exact remaining quantity.
• Retry with the same reservation id and define the desired idempotent response.

• Selecting quantity first and updating later without concurrency protection.
• Ignoring @@ROWCOUNT after the conditional update.
• Committing the stock change separately from the reservation insert.
• Adding broad locking hints without understanding contention and transaction scope.