2

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!

user366343
  • 21
  • 1
  • Instead of a `select for update` I think an available column, as a `tinyint(1)`, which flags the item might be a better choice. If you use a `select for update`, it is not likely a deadlock would happen, but it is likely customer #2 who clicks the purchase button would be waiting for a long time (while the lock is held by customer #1), only to be told "sorry, we're out of stock." – Nate Pinchot Jun 14 '10 at 13:38
  • I thought about the available column, but suppose two customers click on "Buy" at the same time, wouldn't the column return "Available" since both requests are read before its updated to "unavailable"? – user366343 Jun 14 '10 at 14:01
  • Sorry if I was not clear, I should have said to use `select for update` with updating the available column. This way you will only lock while checking for availability, instead of locking for the whole process. Another option would be to, after the first check on the available column, when going to update, do something like `update tbl set available = 0 where id = 1 and available = 1`. If two people run the query at the same time only 1 should succeed. You could then check how many rows were affected by the update. If 0 rows affected, you got bumped. – Nate Pinchot Jun 14 '10 at 16:11
  • Hey Nate, could you clarify what you mean by using select for update with updating the available column? If I do that first, wouldn't that give me the same problem in the first place since I have to make two transactions? And if I go with the second option, if the payment fails, I would have to do another update to set available back to 1...doesn't seem very efficient =/ – user366343 Jun 15 '10 at 14:11

1 Answers1

0

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.

Brian Hooper
  • 21,544
  • 24
  • 88
  • 139