Possible to select...for update, do e-commerce transaction, and then call a MySQL stored procedure in PHP?

Question

Hello,

I am currently building a PHP e-commerce website for my client. Its been going smoothly but I've hit a roadblock and was wondering if any MySQL/PHP experts can help me. Basically, the e-commerce site sells a product only once (meaning they only have one quantity in stock for each item), which means that once a customer checks out with that item it cannot be bought anymore.

So on checkout, I have to check to see if the product is still in stock. Assuming this will be a site with heavy traffic, there could be instances where two or more customers would try to checkout at the same time, thus both would be able to buy the product. So to prevent that, my plan is as follows:

1. Select...FOR UPDATE on the product, to lock the table row.
2. Do e-commerce transaction (Paypal, authorize.net, etc.)
3. If e-commerce transaction is successful, call a MySQL stored procedure (which also has a SQL transaction) to store order information, etc. OR ROLLBACK if failed

Is it possible to realize this plan? To perform the SELECT...FOR UPDATE, I would have to start a SQL transaction, but then I am also starting another one in Step 3 within the MySQL stored procedure. I am not sure what would happen. Also, would this plan lead to a deadlock scenario?

Sorry for the long question, but any help is appreciated!

Answer 1

I'd suggest your stored procedure ought not to have transaction control inside it. That seems to be the cause of your difficulty.

If other callers require the stored procedure like that, consider factoring out the 'works' to a new stored procedure.