使用TRIGGER在mySQL INSERT上进行计时问题

For an iPad app I have created a web service which creates a new table row in my teams table when the user starts the app. The PHP generates a unique ID and fills some other fields as well.

Additionally I have created a trigger on the teams table in mySQL on insert where a teamnumber is generated automatically depending on the already inserted rows for that specific day, project and group.

Unfortunately I seem to have a timing issue when inserting new rows to my table sometimes. If two apps create a team in the same second (milisecond?) the result of the mySQL trigger will be the same teamnumber for both apps. So instead of having teamnumber 1 and 2 both apps have teamnumber 1.

My teamtable looks something like this:

TeamID | pProject | group | teamnumber | languagecode | created_at

And the trigger for mySQL:

CREATE TRIGGER after_team_insert BEFORE INSERT ON teams 

FOR EACH ROW
BEGIN

SELECT COUNT( * ) INTO @counter
FROM teams
WHERE DATE( created_at ) = DATE( NOW( ) )
AND teams.group = NEW.group
AND teams.pProject = NEW.pProject;

SET NEW.teamnumber = CAST(@counter AS UNSIGNED) + 1;

END;

I think my problem has something to do with parallel threads that insert rows into the database and therefor get a wrong count when the trigger is working.

Does anyone have a solution for this problem or do I have to use some kind of queue in PHP to prevent this doubling of teamnumbers?

Thanks in advance, Carsten.

If, for any reason, you can't lock your table you can try to handle the problem differently:

  • Add a unique constraint on the columns group / pProject / teamnumber.
  • Handle any constraint error in your code, check if the teamnumber is the problem, and then try to reevaluate it.
  • Ensure you're teamnumber has been set. If not, try again.

Not that clean, a bit tricky, but it should do the work if you don't mind team number 3 was inserted after team number 4.