Transaction and locking?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jzaiq1
Forum Newbie
Posts: 2
Joined: Wed Jun 20, 2007 6:47 am

Transaction and locking?

Post 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!
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
jzaiq1
Forum Newbie
Posts: 2
Joined: Wed Jun 20, 2007 6:47 am

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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".
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply