How to prevent multiple web requests from processing the same records?

Question

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?

Answer 1

Sounds like you need to use the singleton pattern.

http://en.wikipedia.org/wiki/Singleton%5Fpattern

Answer 2

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.

Answer 3

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.

Answer 4

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.