title: Race Conditions With Trigger After Insert In MySql
body: <p>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.</p>
  
  <p>I have simplified my code for the purposes of posting here. </p>
  
  <p>There are 3 tables:</p>
  
  <p><strong>score</strong> 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)</p>
  
  <p><strong>score_summary</strong> table: ID, UserID, GameID, SummaryValue (this table keeps a running score for each user for each game)</p>
  
  <p><strong>game_summary</strong> table: ID, GameID, SummaryValue (this table keeps a running overall score for each game)</p>
  
  <p>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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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?</p>
  
  <p>Are my concerns justified and if so is there anything I can do about this?</p>
  
  <p>Thanks in advance guys.</p>