2014-02-13 16:26
浏览 133


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 


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

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


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.

图片转代码服务由CSDN问答提供 功能建议

对于iPad应用程序,我创建了一个Web服务,在用户启动时在我的团队表中创建一个新的表行 该应用程序。 PHP生成一个唯一的ID并填充其他一些字段。

此外,我在插入的mySQL中的teams表上创建了一个触发器,其中根据已插入的内容自动生成一个teamnumber 该特定日期,项目和组的行。

不幸的是,有时我在向表中插入新行时似乎有时间问题。 如果两个应用程序在同一秒内创建一个团队(毫秒?),mySQL触发器的结果将是两个应用程序的相同团队编号。 因此,不是拥有团队编号1和2,而是两个应用都拥有团队编号1.


  TeamID |  pProject | 小组|  teamnumber | 语言代码|  created_at 


 创建TRIGGER after_team_insert在插入团队之前
每个 ROW 
WHERE DATE(created_at)= DATE(NOW())
AND = 
AND teams.pProject = NEW.pProject; \  n 
SET NEW.teamnumber = CAST(@counter AS UNSIGNED)+ 1; 

我认为我的问题与并行线程有关 将行插入数据库并因此在触发器工作时得到错误的计数。

有没有人有这个问题的解决方案,或者我必须在PHP中使用某种类型的队列 防止团队数量增加一倍?

提前致谢, Carsten。

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dqy012345 2014-02-13 16:31

    I guess you can use a lock table !

    打赏 评论
  • duandangqin0559 2014-02-14 09:57

    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.

    打赏 评论

相关推荐 更多相似问题