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.
