On Thursday 07 February 2002 11:07, Tibor Radvanyi wrote: > Hi Everyone, > > I'm newbie with MySQL with some programming experience. > I'm about to implement an Internet shop. I would like to serve my > customers at a high level with, amongst other features, making sure that > every successful order will really be shipped due to a stock availability > check at order taking. > The database related steps I am planning to use are: > 1. checking stock of item X (select) > 2. recording order (insert) > 3. modifying stock if item X (update) > > With not using transaction or lock tables the available stock can be > changed by others between Step 1 and 2 or Step 2 and 3. > Which solution supports better such a scenario in MySQL? Which is used by > you? > > Thank you for your help.
This is a classic case where you need a transaction. Generally a lot of people will be buying things (hopefully). Using LOCK TABLE will of course work, but you have to lock the stock table, which will totally serialize checkouts from your shopping cart. A transaction would only serialize orders for the same item (because the initial read will only lock the one row). I would advise something like this: 1. check stock of item X 2. If its out of stock you're done 3. start transaction 4. check stock of item X again (to make sure its STILL in stock) rollback if not. 5. record order 6. decrement stock 7. commit That way you don't create transactions where there is no hope of them going through, and an extra select on a unique key (stock number) is not going to be very expensive, whereas transactions can create a lot of locks, plus you always risk the possibility of a malfunction leaving you with a hanging transaction (shouldn't happen, but in my experience its the number one cause of bad problems). > > Regards, > Tibor > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
