PHP/MySQL: Preventing duplicate actions quickly in succession

Question

For a browser game, I have a database table fights:

• fightID
• fromID
• toID
• simulated (1=true, 0=false)

Whenever a user requests a page, I select all fights which still have to be simulated:

SELECT fightID, fromID, toID FROM fights WHERE simulated = 0

These outstanding fights are then simulated in the PHP script and, finally, the fight is marked as simulated and the winner gets his points:

UPDATE fights SET simulated = 1 WHERE fightID = X
UPDATE users SET points = points+1 WHERE userID = WINNER

The problem:

Imagine two users coming to the page one after another in only a few milliseconds. On both users's page load the same outstanding fights are selected. Then they are simulated and - since both requested the page almost at the same time - the winner gets his points twice. The fights are then marked as simulated. But this is too late.

How can I avoid this problem? Thank you very much!

Answer 1

If you are sure that once you read the fights from the DB, they will be simulated (i.e. it is not possible that the user quits and that some fights are left unsimulated), you an use a transaction to read and update the fights in one, atomic operation.

START TRANSACTION;
SELECT fightID, fromID, toID FROM fights WHERE simulated = 0
UPDATE fights SET simulated = 1 WHERE fightID = X
COMMIT;

and then later update the scores.

Answer 2

Instead of keepeng the points in the users table you can create a 'winners' table

userId
fightId
...

and (userId,fightId) would be the primary key. That would prevent any user geting more than 1 (or whatever) points per fight. The points would be calculated by select count(*) from winners where userId=?

Answer 3

Hmm, what if you use 3 states of simulation? 0 = not simulated, 1= simulating NOW, 2= simulated. Then you can flag simulation immediately after user loads the fight.