0

I'm building a game session, and each session has many states. When the game asks for the next step, the current game session is selected, and then the latest game state chosen. The user makes an action on the state, returns it, it is checked against the last state, if valid, it saves the action, creates a new state, and returns it to the user. This process repeats. At the end of the game, the game session is marked as ended, and a new one is created.

If any action is submitted twice simultaneously, they will both be able to grab the same game session, grab the latest game state and validate it against the action. The transaction only seems to be preventing the writing of any row that is modified in the transaction. Whichever one is hit second will wait till the first one is done, then write it's data and create the next game state.

I need to create a read lock on the game session row, so if any instance is doing any action on the game sessions states within some transaction, any other attempt to read the game session will be delayed until that instance/transaction has completed so the read will always get the data after the transaction.

I haven't been able to find out how I can create a row read lock. Could someone assist me? Thanks.

EDIT:

I think I need to do a SELECT game session row FOR UPDATE within the transaction, and that will lock the row for reading, but then before the transaction would I have a SELECT game session row LOCK IN SHARE MODE to make sure it waited till it could start it?

Not sure if I'm using those correctly.

Somewhere within my PHP code abstracted with PDO (specifically Eloquent):

SELECT FROM game_session WHERE id = 5 LOCK IN SHARE MODE; #wait till any lock is completed
BEGIN TRANSACTION;
    SELECT FROM game_session WHERE id = 5 FOR UPDATE; #lock the row
    /* stuff */
END TRANSACTION;

That seems a bit redundant. I know there's also a lot for transaction isolation level, though I'm unclear as to where that would tie in, is SERIALIZABLE needed? Or would that mess up the "FOR UPDATE" bit?

phazei
  • 5,323
  • 5
  • 42
  • 46
  • store a token both sides to compare against. similar as being done to prevent cross site form submission – animaacija Apr 16 '15 at 04:00
  • The idea is to prevent fraudulent actions by the user, but they have to be allowed to submit. They could have the same game session running on 2 devices that will both have valid tokens and hit submit at the same time. It needs to be locked at the db level, and preferably without extra columns for manually locking. – phazei Apr 16 '15 at 04:12
  • I'm thinking I'd need to do a SELECT FOR UPDATE within the transaction, and a SELECT LOCK IN SHARE MODE outside of it, but I'm not entirely sure how those would work. – phazei Apr 16 '15 at 04:15

0 Answers0