0

Lets say you have a table with some winning numbers in it. Any of these numbers is meant to be only "won" by one person.

How could I prevent 2 simultaneous web requests that submit the same numbers from both checking and seeing that the numbers is still available and then giving the prize to both of them before the number is marked as no longer being available.

The winning solution in this question feels like what I was thinking of doing, as it can also be applied in most database platforms.

Is there any other common pattern that can be applied to this?

Community
  • 1
  • 1
feihtthief
  • 6,403
  • 6
  • 30
  • 29

3 Answers3

6

These numbers are randomly generated or something?

I would rely on the transactional semantics in the database itself: Create a table with two columns, number and claimed, and use a single update:

UPDATE winners SET claimed=1 WHERE claimed=0 AND number=@num;

Then check the number of affected rows.

Dark Falcon
  • 43,592
  • 5
  • 83
  • 98
  • you need a transaction as ironfroggy said. Otherwise you could have another request get the number inbetween the select and the update. – Byron Whitlock Oct 07 '09 at 19:24
  • 1
    There is no select and update--just a single UPDATE, which is an implicit transaction. – Dark Falcon Oct 07 '09 at 19:32
  • 1
    Just curious if you know the answer: If using a SE without transaction support (eg. MyIsam), would this still be atomic? – troelskn Oct 07 '09 at 21:29
  • "The other nontransactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called “atomic operations.” In transactional terms, MyISAM tables effectively always operate in autocommit = 1 mode. Atomic operations often offer comparable integrity with higher performance." – Dark Falcon Oct 08 '09 at 10:49
1

Use transactions. You should never have multiple threads or processes changing the same data without transactional locks and any decent database supports transactions today. Start the transaction, "grab" the winning number, and then commit. Another thread would be locked until the commit, and would only get its chance after the records are updated, when it could see its already there.

ironfroggy
  • 7,991
  • 7
  • 33
  • 44
0

A non-database solution could be to have the client make the request async and then push the request on a FIFO queue to control the requests so that only one request at a time is getting evaluated. Then respond back to the client when the evaluation is complete. The advantage here would be that under high load, the UI would not be frozen where it would be with transactional locking on the database level.

DancesWithBamboo
  • 4,176
  • 1
  • 19
  • 20