CODEWITHSUNDEEP
phpmysql
iamafish
iamafish

Reputation: 817

How do I handle mysql insert if have multiple process in same time?

I'm trying to make a simple shopping cart. Normally if product quantity is 0 buy now button will disappear and showing out of stock text.

Let's say current product have 1 quantity left and at the same time have two buyer buy the product. Both of buyers already in checkout page which script is not calculate available quantity any more.

In this case, how do I validate this part? I want the process basis on first-served, who is first insert into MySQL will get the product if not, script will return back to product page.

Let me know..

Upvotes: 2

Views: 742

Answers (4)

Vitamin
Vitamin

Reputation: 1526

You probably want to reserve the qty's already when you create the quote (which you do when the product is added to the cart).

Upvotes: 0

jeff musk
jeff musk

Reputation: 1022

You can do the locking but can lead to couple of problems--first, scaling challenges; second, you will lockout other users.

Research shows that people search and add things to cart many many more times than they actual purchase. So usually it is not recommended to place locks on products once placed in cart.

@phpmeh has given a good answer.

Upvotes: 1

James
James

Reputation: 3805

You're just going to have to check to make sure it is still in stock before you process it. Maybe put a lock on the table at the beginning of a transaction until you are done running all scripts associated with that transaction so that only one purchase can be made at a time.

Upvotes: 1

phpmeh
phpmeh

Reputation: 1792

The simples, and thus probably best solution - Use the same script to check if the item is out of stock on the insert page. If the item is out of stock, display the error message. If it it's not out of stock, let them buy it.

You just need to add that same select again.

Alternatively, depending on your schema, when you update the quantity after the sale, add a where clause to the update query: WHERE the qty >= the quantity purchased. Then, see how many rows were affected. If the number of rows affected is 0, then you know the sale didn't go through. Show the out of stock error message.

Alternatively, probably an undesirable soluation - lock out the page. When the first user goes to the page, you insert a row into a table that keeps track of who is on what pages, and prevents other uses from going to that page. Then, when the second user goes to the page, they get an error message. Then, when the first user completes the transaction, you make sure that the same session id as who locked the page is who is completing the transaction now.

Upvotes: 3

Related Questions