Page 1 of 1

Transaction and locking?

Posted: Wed Jun 20, 2007 7:23 am
by jzaiq1
Hi,

I am new to PHP and have an e-commerce project with a shopping cart functionality. I'v developed the "add","delete","update" functions of the shopping cart but have some problems with "place order".

My database is MySQL 5.0.37

Here are relative tables about order placement:
"Inventory" - store my all products (products include: A,B,C,...I,J,K)
"Orders" - store order information (order ID, buyer ID,...etc.)
"Order_detail" - store order details (product ID, unit price, quantity,...etc.)

My scenario is :
3 persons check out the shopping cart at the same time,
person 1 buy products A,B,C,D,E,F,G
person 2 buy products C,D,E,F,G,H,I
person 3 buy products E,F,G,H,I,J,K

In the scenario, should I lock the whole 3 table or simply lock the related rows when I create a new order to prevent error?

Since I have no idea how to use transaction or locking properly, the other way on my mind is to set a "time flag". When the first person click the "check out" button, set the flag to "hold on" and call a counter to countdown 1 minute to process the order to prevent the other two persons doing the same thing.

Which way is better? How to do?
I'v read the book "Beginning PHP 5 and MySQL E-Commerce from Novice to Professional" but the book mentioned few about this part. Could anyone recommend me some books mentioned about it?

Many thanks!

Posted: Wed Jun 20, 2007 7:44 am
by superdezign
No idea what you're asking. You could always have the "check out" functionality check the database for that session, add all the prices, place the orders for those items and, upon completion, clear that row of the database.

Posted: Wed Jun 20, 2007 7:52 am
by jzaiq1
superdezign wrote:No idea what you're asking. You could always have the "check out" functionality check the database for that session, add all the prices, place the orders for those items and, upon completion, clear that row of the database.
I'm sorry.

The table "Inventory" has a column "qty" which store the quantity of the product.
The buyer have to assign the value. If the buyer 1 buys all the product E, the buyer 2 will get an error when he click the "check out" button.

Posted: Wed Jun 20, 2007 8:02 am
by superdezign
Then you'll want to arrange the shopping cart as having an serialized array/object holding the items that each customer is buying and, when they make they make their purchased, you'll use the array to remove that amount of those items from the inventory table.

Posted: Wed Jun 20, 2007 9:55 am
by pickle
What you'll want to do is:
  1. When an order is placed, put a write lock on all 3 tables. This will prevent other orders from modifying the tables while you're accessing them
  2. Check to make sure the inventory is in stock
  3. Place the order data in `Orders` and `Order_details
  4. Update the inventory
  5. Release the lock
I'm not sure you can do all the steps in one transaction - especially step 2.

Also, note that you can't do transactions on tables of type "myISAM". If you want transactions, you should use "InnoDB".