Stop 2 identical queries from executing almost simultaneously?

Question

I have developed an AJAX based game where there is a bug caused (very remote, but in volume it happens at least once per hour) where for some reason two requests get sent to the processing page almost simultaneously (the last one I tracked, the requests were a difference of .0001 ms). There is a check right before the query is executed to make sure that it doesn't get executed twice, but since the difference is so small, the check hasn't finished before the next query gets executed. I'm stumped, how can I prevent this as it is causing serious problems in the game.

Just to be more clear, the query is starting a new round in the game, so when it executes twice, it starts 2 rounds at the same time which breaks the game, so I need to be able to stop the script from executing if the previous round isn't over, even if that previous round started .0001 ms ago.

Answer 1

The trick is to use an atomic update statement to do something which cannot succeed for both threads. They must not both succeed, so you can simply do a query like:

UPDATE gameinfo SET round=round+1 WHERE round=1234

Where 1234 is the current round that was in progress. Then check the number of rows affected. If the thread affects zero rows, it has failed and someone else did it before hand. I am assuming that the above will be executed in its own transaction as autocommit is on.

Answer 2

So all you really need is an application wide mutex. flock() sem_acquire() provide this - but only at the system level - if the application is spread across mutliple servers you'd need to use memcached or implement your own socket server to coordinate nodes.

Alternatively you could use a database as a common storage area - e.g. with MySQL acquire a lock on a table, check when the round was last started, if necessary update the row to say a new round is starting (and remember this - then unlock the table. Carry on....

C.