Suppose I have a game of chess stored in a SQL database with something like the following schema:
CREATE TABLE chessgames(
game_id INTEGER,
move_id INTEGER,
move char(4)
};
So if an ongoing game, with game_id 0, has the moves e4 e5 then the table would have the tuples (0, 1, "e4") and (0, 2, "e5").
Now suppose a client tries to corrupt the database by sending both the move d4 and Nf3 at the same time. Trying to get two moves processed and effectively trying to insert the tuples (0, 3, "d4") and (0, 3, "Nf3"), both with the same move_id, breaking the uniqueness of move_id.
What is the best idiomatic way to ensure uniqueness? One possibility that occurred to me would be to have my C++ code contain a list of mutexes, one mutex for each game. When a move such as d4 arrives, the C++ code locks the mutex for the corresponding game, runs the following SQL query
SELECT move_id, move FROM chessgames WHERE game_id = 0
to fetch all the moves for the game (in the example I gave this would be e4 and e5), the C++ code takes those moves and checks that no row already has move_id = 3 and then plays out the moves to construct the current position so that it can check that the move d4 is valid. If it is valid it runs
INSERT INTO chessgames VALUES (0, 3, "d4")
to store the move in the database and then it frees the mutex.
This way if the Nf3 move arrives at the same time as the d4 move processing of it will get blocked by the locked mutex and when Nf3 is finally processed it will see that a row with move_id = 3 already exists and it will be ignored.
Is there a better way of doing this? Is my database schema even reasonable for what I am trying to do?
I learned that what I need is optimistic locking much like the answer to this question.