Multiple requests to one set of data

Question

I am not entirely sure how to word this, however my example should help. I have encountered the following problem:

There are 3 bidders in an auction:

• The first bids $1, and their account is deducted of this amount.
• The second bidder bids $2, so the first's bid is refunded, while the second's balance is deducted.
• However, the script refunding the first bidder has not yet finished, and a third bidder sends a request to bid $3.
• Because the script hasn't finished yet, it still fetches the first bidder as the highest, so begins to refund them again.

After all these scripts have finished executing, bidder 1 gets refunded twice and bidder 2's bid has just disappeared.

I use mysql to store information about the bids, and php to execute the requests.

I have heard Database Transactions might help, but I am not entirely sure how.

Answer 1

Your second script consists of two interaction with the database. By default, each DB statement is executed as a single transaction. In your case, you need to do both interaction of the second script as a single transaction. This prevents the third script (that should also run as a single transaction) from starting before the second script finished. See PHP + MySQL transactions examples for an example on doing multi-statements transaction with php and mysql.