Is this an atomic upgrade? Why is the way better
So, even though I've been tinkering with this sql server for a while, I find myself second guessing myself on this one.
Scenario: I want to sell something, but I don’t want to sell it. This is the request I have. (@Quantity is usually negative, SalesItemId is the primary key of the table). Seems like a nice neat solution
UPDATE SalesItem
SET QtyAvailable = QtyAvailable + @Quantity
WHERE SalesItemId = @Id
AND QtyAvailable + @Quantity >= 0
IF @@ROWCOUNT = 0 BEGIN
RAISERROR ('Cannot sell item, would sell out')
END
Will the update do this and where is the request in the same trasaction thus preventing the QtyAvailable from getting less than zero? I would think so, but I get examples of where I end up selling more items that I need. I always thought this was just another part of the system, but all fingers seem to point to this request?
Is it better to do a two-step process? eg. This (or reverse check before updating)
BEGIN TRAN
UPDATE SalesItem
SET QtyAvailable = QtyAvailable + @Quantity
WHERE SalesItemId = @Id
IF ( SELECT QtyAvailable > FROM SalesItem where SalesItemId = @Id) < 0
BEGIN
RAISERROR ('Cannot sell item, would sell out')
ROLLBACK TEAN
RETURN
END
COMMIT TRAN
source to share
Your only update statement is an atomic transaction. It seems efficient and elegant as it stands. If you get negative QtyAvaliables, you should check all other instances of your code that change its value.
You said @Quantity is negative. Make sure that if you have instances where you want to add a positive amount and then subtract it using the offset transaction, that they run together in a transaction.
source to share
IMHO burying business logic in the database is a mistake (leave it in the application - it's easier to test, modify, debug, write, etc.), but if you absolutely need to (for example, there is no "application" - you are using a tool that uses SQL for actions, or you have an outdated situation of many database based applications doing work), I would:
- create a trigger when inserting (and updating, deleting) an order item that updates the stock level of a product
- create a check limit for a product that requires the stick level to be other than zero.
The atomicity is then hooked up to the insert (or update or delete) statement, since the triggers / checks are fired in the same transaction as the event that triggers them.
Simply put, with this in place, it is not possible for any request to reorder-of-data cause a situation where a negative stock level exists.
source to share