Stock System: Prevent Negative Quantities or Large Numbers

Databases
1

Guys,

We have a stock system that uses a very simple concept of adjusting stock. It basically says:

UPDATE `stocktable` SET `qty` = `qty` - 1 WHERE `id` = 123456

which works fine in most cases, except when we’re trying to remove more stock than we should. As this is an unsigned BIGINT, if we remove two items from stock but the system says that there are only one actually in stock, we end up with 18446744073709551615, simulated by:

SELECT CAST(1 AS UNSIGNED) - 2

Is there a way to avoid this? The only way we’ve seen so far is something like

UPDATE `stocktable` SET `qty` = `qty` - 2 WHERE `id` = 123456 AND `qty` >= 2

.

Are there any better solutions?

Cheers

2

You could check if the stock is sufficient before you do the update.
Otherwise, I think the solution you found is the only one.

3

Yeah, the problem is that there’s a chance that another query could have adjusted the stock level between the two queries, is there not? Unlikely, but possible. I think we’ll stick with the previous solution then. Cheers

4

what two queries? there’s only one, the UPDATE

5
  1. Check
  2. Update

Putting them both together is surely the original query?