Race Conditions With Trigger After Insert In MySql

Question

I have a mysql trigger that I have a concern about having noticed some "unsynced" data in my db after deploying the trigger to my production site a few days ago.

I have simplified my code for the purposes of posting here.

There are 3 tables:

score table: ID, UserID, Score, GameID (users enter a score each time they play a game, they can play the same game a number of times)

score_summary table: ID, UserID, GameID, SummaryValue (this table keeps a running score for each user for each game)

game_summary table: ID, GameID, SummaryValue (this table keeps a running overall score for each game)

When a game score is entered for a user into the score table, the trigger is there to update the user's running total score (SummaryValue) in table score_summary and also to update the game_summary table for given GameID.

CREATE TRIGGER scores_insert_trigger AFTER INSERT ON scores
FOR EACH ROW BEGIN

    UPDATE scores_summary
    SET SummaryValue=SummaryValue + NEW.Score 
    WHERE UserID=NEW.UserID
    SELECT ROW_COUNT() INTO rowCount;
    IF (rowCount=0) THEN
        INSERT INTO scores_summary
        (UserID, GameID, SummaryValue)
        VALUES
        (NEW.UserID, NEW.GameID, NEW.Score);
    END IF;

    UPDATE game_summary
    SET SummaryValue=SummaryValue + NEW.Score 
    WHERE GameID=NEW.GameID
    SELECT ROW_COUNT() INTO rowCount;
    IF (rowCount=0) THEN
        INSERT INTO game_summary
        (GameID, SummaryValue)
        VALUES
        (NEW.GameID, NEW.Score);
    END IF;
END;

My concerns are with regards to the trigger running into a race condition if a number of users enter scores at the same time - in particular for the update of the game_summary when no scores exist for the particular game - if two users attempt this at the same time, they will both get rowCount=0 and will then both do an insert?

Are my concerns justified and if so is there anything I can do about this?

Thanks in advance guys.

Answer 1

Just create UNIQUE index on game_summary.GameID or make it primary key.

UNIQUE index will prevent adding duplicate row by second user. Whole transaction will fail and will be rollbacked so your application need to handle this exception e.g. after failing try again before giving up. The disadvantage of this solution is that in very busy environment this will lead to many collisions and many rollbacks but you can be sure there won't be any duplicate rows.

You can also use GET_LOCK(str, timeout) function to gain exclusive lock for given GameID, update or insert value, and finally RELEASE_LOCK(str) so next user can update scores too. This could be much more efficient in some situations but you need to check that.