Managing concurrency on store checkout process

Question

I have been reading all day searching the correct way to manage concurrency within a e-commerce checkout process, and I'm still not sure how to develop it.

Scenario

User selects one product from the store, go to checkout page and pays using his credit card. The site process the payment checking stock availability and using a webservice along with the user credit card data and. The webservice finally return the result and the site display a success page (or fail).

Final checkout and payment validation flow

• The user starts the final checkout step
• I check if product has stock
• If product has stock I call the payment webservice
• If webservice result is "OK", I display the success page

It sounds very simple, but my problem is the way I get the stock of the product: I have to calculate it each time. I have two tables product and sales (example only tables), so each time a sale is completed, a sale row is added linking the product it belongs. On product table I have the maximum times that a product can be sold, like a initial stock.

So, for example, I have product X with initial stock of 99 and two users starts the checkout process for the last available unit. I check the current sold units = 98. OK, both users can buy it, I call the webservice and finally both users buy and pay the product.

Any idea of how to deal with concurrency in this case?

BTW: I'm usingy MySQL with InnodB

Answer 1

I finally implemented Optimistic Offline Lock (http://martinfowler.com/eaaCatalog/optimisticOfflineLock.html) pattern, so only one user at time can purchase an item. I had to be very careful managin dead user connections with timeouts if a user abandon the transaction.