PHP + mySQL and concurrent database access issues

Question

I'm seeking advice about how to solve a concurrent access problem in a web application constructed with PHP and mySQL.

Application users customize something I'm calling a “collection”. When customization is complete, they can click a PayPal “Buy Now” button and purchase the collection.

A collection is made up of two separate groups of things: a group of widgets and a group of gadgets. “Customization” involves choosing which widgets to include in the widget group, and which gadgets to include in the gadget group.

This is a kind of simplified pseudo entity relationship diagram:

A wide range of widgets and gadgets are available from which to choose, but each widget and each gadget is unique and can only be used in one group at a time.

The problem comes with purchasing a collection. I need to make sure that when a user clicks the “Buy Now” button, the widgets they have selected for their widget group, and the gadgets they have selected for their gadget group are not already in use in another paid-for collection.

Imagine two people using the system simultaneously. Each creates a collection* containing some of the same widgets and/or gadgets, and proceeds to the checkout page, where they both hit the Buy Now button at exactly the same moment. Running a standard query to test whether selected widgets/gadgets were already in use would be unreliable in this scenario because, for both users, the system would reply “these are available”, and each would be allowed to purchase their collection with “double-booked resources” (as it were).

So I've been doing a lot of reading about transactions and locking today. I think this will have to be involved in my solution, but I'm having a lot of trouble visualising how this might work.

At the moment all I can conceive is something like this: at the point when the Buy Now button is clicked I start a transaction, and then run a query to lock (to prevent reading and writing by other sessions) all rows in the widget table, and all rows in the gadget table for every item listed in the collection. With the transaction started and those locks in place, I could run queries to test whether any selected items were already in use. If the queries indicated that the items were unused, I'd allow the purchase to proceed, and once completed I would release the locks.

If the queries indicated that items were already used, I'd return an error and release the locks.

If another user attempted to purchase a collection containing some of the same widgets/gadgets at the same time, they'd either receive a lock time out, and I'd return an error; or they'd have to wait until the other session had removed its locks, and then - when their session applied the same locks and ran the same queries - they'd see the “already in use” error.

So I have two questions. Does this sound like the best way of dealing with my situation. And second, if it does, is it do-able in the way I've described?

Thank you very much in advance

*which might be saved for later. I allow users to create collections containing any widgets/gadgets that they like - even ones that are already in use - reasoning that if they postpone their purchase now, then by the time they follow through with it later, those resources may have become available again

Answer 1

yes and yes. Yes, it's the best approach because it's the most obvious and most commonly used. And yes, that approach works, I've used it myself and have worked on code written by others that used it.

Try not to do analysis while holding open the transaction; pre-test, start transaction, set optimistic locks, end the transaction, check which ones succeeded, and if some were already locked, release the just set locks. Your database will thank you.