处理多个MySQL事务的最佳实践是什么？

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

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