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!
Transaction and locking?
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
I'm sorry.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.
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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
What you'll want to do is:
Also, note that you can't do transactions on tables of type "myISAM". If you want transactions, you should use "InnoDB".
- 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
- Check to make sure the inventory is in stock
- Place the order data in `Orders` and `Order_details
- Update the inventory
- Release the lock
Also, note that you can't do transactions on tables of type "myISAM". If you want transactions, you should use "InnoDB".
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.