gwgeller ... I think you're using transaction incorrectly. When you said
Quote:
|
Originally Posted by gwgeller
Start Transaction
Look up existing quantity
If enough remains, decrement appropriate amount
commit transaction
|
Transaction won't do you any good. Consider this scenario:
Code:
CREATE TABLE `item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`item_name` varchar(255) NOT NULL,
`item_quantity` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO item (NULL, 'Oranges', 100);
INSERT INTO item (NULL, 'Grapes', 50);
And the following SQL statement:
Code:
START TRANSACTION;
SELECT * FROM item;
UPDATE item SET item_quantity = 25 WHERE id = 1;
COMMIT;
Now have 2 connections going to MySQL. Run the first 3 statements on one connection (all except the commit part). Then on the 2nd connection, do the select statement. You will see that Oranges still have quantity 100. When you issue a commit on the first connection, then you'll see Oranges having 25 quantity on the 2nd connection.
Similarly, when you start transaction, then look up quantity then issue an update statement. And right before you commit, another connection is looking up the quantity, the other connection will see the old data. This is obviously not what you want.
What you need in this case is locking. Either lock the table for read and write on one thread, or use advisory lock like
GET_LOCK(), or rely on warnings if you don't want to use transactions.
1. Lock table:
Code:
LOCK TABLES item WRITE;
SELECT @qty := item_quantity - 1 FROM item WHERE id = 1;
UPDATE item SET item_quantity = @qty WHERE id = 1 AND @qty >= 0;
UNLOCK TABLES;
You can detect for success or not by checking @qty variable to be greater than or equal to 0.
2. GET_LOCK()
Code:
SELECT @my_lock := GET_LOCK('my_lock', 10);
SELECT @qty := item_quantity - 1 FROM item WHERE id = 1;
UPDATE item SET item_quantity = @qty WHERE id = 1 AND @my_lock = 1;
SELECT RELEASE_LOCK('my_lock');
You can detect for success or not by checking the result of the RELEASE_LOCK statement. If it's NULL, then you were unable to update.
3. Rely on warnings
This method relies on warnings. The idea is to generate warning if item_quantity goes negative. So, alter the table on item_quantity column to have it unsigned. Then when you have 2 connections, 1 makes item_quantity becomes zero and the other one puts it to negative, you'll get a warning code 1264: Out of range value for column 'item_quantity' at row 1.
There may be better solutions than the 3 above (It's highly possible that the 3 ways above may need improvements as well). But I hope this leads you to a starting point on how to solve your issue.
dBforums
>
Transactions and Locking
09-24-09, 16:37
My test environment wasn't quite like my production environment so my last post did not fully work. To start, Mysql takes a little digging to understand transactions. Another gotcha I encountered was that some statements cause implicit COMMITs. I was using a temporary table and when I dropped it there was an implicit COMMIT which was messing up my testing. To remedy this I used DROP TEMPORARY TABLE which does not use an implicit COMMIT. Next is the bigger issue. I actually needed to use SELECT...FOR UPDATE for my select to wait on any transactions to be committed. So here is my SQL outline:
Linear Mode
