3

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.

Ben Peters
  • 92
  • 9
  • 1
    why would any money be moved on a *bid*? it should only happen at the end. –  Aug 02 '15 at 21:18
  • Our site doesn't use money, it uses a kind of virtual currency. It's complicated, but does have a monetary value in the long run - deducting immediately prevents abuse of our systems. – Ben Peters Aug 02 '15 at 21:19
  • this is not how any auction should work –  Aug 02 '15 at 21:20

1 Answers1

3

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.

Matthias J. Sax
  • 59,682
  • 7
  • 117
  • 137
  • Do transactions prevent any other data being written to the database while one is in progress? Or is it like git, where a bunch is just commited at once? – Ben Peters Aug 02 '15 at 21:24
  • It depends on the system you are using. Each time a transaction is started, some object of the database are locked (this can be single tuples or a whole table -- depending how fine grained locking is implemented). If objects are locked for writing, no other transaction can read or write the same object. If a transaction commits or aborts, the locks are released. In the commit case, all changes are accessible to other transaction at once. I case of abort, the old/unchanged DB state is recovered. Thus, operating on different object can be done in parallel/concurrently. – Matthias J. Sax Aug 02 '15 at 21:28
  • Okay, thanks, seems like this is what I am looking for - I use mysql, let's say as per your example I begin a transaction. Until this is committed or rolled back, can any data be written to the table? Or can I specify certain row(s) to be locked and allow the rest to be modified? – Ben Peters Aug 02 '15 at 21:32
  • The database handles locking automatically, ie, figures out what object need to be locked -- you don't need to worry about this at all. As far as I know, MySql implement tuple based locking, ie, all tuples that are "touched" are locked. All untouched tuples can be read/written by other transaction. – Matthias J. Sax Aug 02 '15 at 21:36
  • Thank you, this is perfect. – Ben Peters Aug 02 '15 at 21:39