What's the best practice to handle multiple MySQL transaction?

Question

I'm developing an order system with PHP + mySQL. There's a high chance that multiple users will create new orders with same product at the same time.

This is the scenario: in MySQL DB, there's a table named "inventory", in which all products' item numbers and available quantity are listed.

Now,

Product A's inventory is 500 pcs.

User1 is working on a new order with 300 pcs of product A, but he hasn't submitted yet. However, there's an AJAX script to send a query to table "inventory" and since the inventory qty is 500 pcs, so it returns 300 pcs as available for this order.

User2 is working on another new order now, while User1 still hasn't submitted his order. User2 wants 400 pcs of product A, and since 300 pcs from User1 has't been submitted, AJAX function returns 400 pcs are available for User2's order.

Now here's the problem. Either User1 or User2 submits his order first, the another user will not have enough quantity to fulfill his order as the result returned by AJAX.

I want to know what is the best (or normally) practice to handle this type of task.

I have thought about PDO transaction handling (not sure if it will work, have never used it, I'm quite new), or maybe should I just forget about AJAX but do the inquiry when user submit the form?

Also I had thought about putting a "check" button on the page then after the user finished inputting the order, they hit "check" to send an inquiry to DB then get the most 'updated' quantity, maybe it's soon enough before the user hits submit.

Thanks.

Answer 1

Thwo things you have to understand first:

1. There is no such thing like "PDO transaction"
2. Transactions are inapplicable here anyway.

What you actually need is simple reservation system. Or a notification one.

In case of reservation, you may mark 300 items as "reserved". Then all others may either content with remained 200 or wait till reservation gets released or stock refunded.

While an example of notification system you may see right here on this very site: in case there is a user who is working on the answer, and question happened to be closed, an ajax notification is shown to the user, saying he has not enough items to reserve anymore.

Answer 2

This isn't a matter of what you're using to make your query (though using PDO is still a good idea.) When the user finally submits their order, your php script should perform an extra check that the requested amount is in stock. If not, the user should presented with an error that the amount of product is no longer avaiable.

Use ajax to check the stock periodically so the user gets a javascript notification when something in their order is no longer available.

Answer 3

there are 2 ways:

1) you reserve the stock as user1 puts it in their basket (and deal with timeouts / abandoned orders and moving stock back to inventory and a way to notify the users when items come back in stock) this means that user2 can't add them if user1 has them. (you could physically remove the number from the inventory, or you could add another column with 'reserved' in it - e.g. 200 in stock and 300 reserved)

2) you do the stock check + reserve when the user has begun the checkout process and notify the users if the items aren't in stock and you don't have to deal with abandoned orders as they haven't reserved the stock yet.

I have developed e-commerce systems based on both, and #1 is, imho the best - as a user I want to know if the things are in stock as I add them to my basket