MySQL Data concurrency

hi all,
Consider the following senario:
Salesman 1 sells three shirts
Salesman 1 retrieves current shirt count (47):
SELECT quantity FROM inventory WHERE item='shirt'
Salesman 2 sells two shirts
Salesman 2 retrieves current shirt count (47):
SELECT quantity FROM inventory WHERE item='shirt'
Salesman 1 computes new inventory level: 47 - 3 = 44 and updates shirt count:
UPDATE inventory SET quantity = 44 WHERE item = 'shirt'
Salesman 2 computes new inventory level: 47 - 2 = 45 and updates shirt count:
UPDATE inventory SET quantity = 45 WHERE item = 'shirt'
At the end of this sequence, five shirts are sold (that's good), but the inventory level says 45 rather than 42 (that's bad).
The problem arises when two or more SQL statements are dependent.

any ideas ?

Would you believe it that the scenario you described is worse compounded in a distributed computing environmnt (DCE)?

Any further ideas about solutions from these classes of problems where real solutions cost exorbitanty large doller amounts? The cost is somewhere around USD .5M.

UPDATE inventory SET quantity = quantity -3 WHERE item = 'shirt'

UPDATE inventory SET quantity = quantity -2 WHERE item = 'shirt'

Quote:

---

Originally Posted by Dasher

UPDATE inventory SET quantity = quantity -3 WHERE item = 'shirt'

UPDATE inventory SET quantity = quantity -2 WHERE item = 'shirt'

---

Your results will be corrupted when quantities are low. You will be selling shirts you do not have!

"Your results will be corrupted when quantities are low. You will be selling shirts you do not have! "

I did not show a complete solution, only a solution to the problem the OP had.

This sort of thing is really best handled with SQL transactions. If you're running SQL Server, you should be set. If you're running MySQL, I think you'll need to be using the InnoDB engine (unless MyISAM has been updated since last I used it).

When a salesman pulls up the shirt inventory, you read it as-is. But, when the salesmen enters the sale quantity, you begin a transaction:

Code:

---

start transaction;
select quantity from inventory where item='shirt';

---

Calculate new_quantity. If new_quantity >= 0:

Code:

---

update inventory set quantity={$new_quantity} where item='shirt';
commit;

---

Else if new_quantity < 0:

Quote:

---

rollback;

---

The rollback in our case doesn't really do anything, since we haven't changed anything. But, it lets SQL Server / MySQL know that it can unlock rows from table inventory were item='shirt', and it ensures that nothing is changed. (If your transaction grows in complexity, you don't want to have a neglected commit where you shouldn't!)

The thing to be aware of, and perhaps it's obvious, is that once you begin a transaction and query inventory for rows where item='shirt', no one else can touch those rows until the transaction is committed or rolled back. So, it's important that you run an isolated, non-transaction query to pull the quantity for display first. Then, when you actually update the quantity, you need to ensure the transaction isn't waiting on user input to complete.

Everything between start transaction and commit/rollback needs to happen as quickly as possible.

Assuming that quantity is actual quantity of shirts available to sell, and the new SET value is quantity minus those actually sold, it should not be possible for the value to go negative unless the quantity value was inaccurate in the first place. Quantity should = stock on hand. (or at all stores or whatever)

Another option may be to use

LOCK TABLES

UPDATE inventory SET quantity = quantity - x WHERE item = 'shirt'
or
SELECT quantity ... and
UPDATE quantity SET = x ....

UNLOCK TABLES

Quote:

---

Originally Posted by Dasher

Assuming that quantity is actual quantity of shirts available to sell, and the new SET value is quantity minus those actually sold, it should not be possible for the value to go negative unless the quantity value was inaccurate in the first place. Quantity should = stock on hand. (or at all stores or whatever)

Another option may be to use

LOCK TABLES

UPDATE inventory SET quantity = quantity - x WHERE item = 'shirt'
or
SELECT quantity ... and
UPDATE quantity SET = x ....

UNLOCK TABLES

---

Lock tables should only be used for operations that will affect all or most of a table or set of tables. No sense blocking other transactions that have nothing to do with the rows you're working with :)