php mysql有条件地将行插入到不同的表中

I am inserting a row in two different tables depending upon a condition.

我根据条件在两个不同的表中插入一行。

Let say, we have table named stories with 2 fields under consideration:
(story_id and comments_count)

比方说,我们有一个名为story的表,其中包含2个字段:(story_id和comments_count)

and 2 other tables:

和另外2个表:

regcomments (comment_id, story_id, comment_text)

注释(comment_id,story_id,comment_text)

unregcomments (comment_id, story_id, comment_text)

unregcomments(comment_id,story_id,comment_text)

Now, before inserting any new comment for any story_id, I just want to check if comments_count is less than 100. If so, the row should be inserted into regcomments, otherwise, the row should be inserted into unregcomments.

现在,在为任何story_id插入任何新评论之前,我只想检查comments_count是否小于100.如果是,则应将该行插入到regcomments中,否则,该行应插入到unregcomments中。

Here is the pseudo-code of how I've done that:

这是我如何做到的伪代码:

select comments_count from stories where story_id = x
if comment_count<100
    insert into regcomments
else
    insert into unregcomments

Now all what I am concerned about is if multiple users are accessing this script, Is there any chance this will fail? For example a story has 99 comments. 2 users call this function exactly at the same time. The first select query executes at the same time for both, and they both get 99. so both inserts will be made to regcomments, making count of regcomments 101, which is against my business rule. Any one has any idea, how the thing go. Or any ideas about how should I handle this scenario. (Don't ask me why I am using 2 tables :P anyway)

现在我所关心的是,如果有多个用户正在访问此脚本,那么这有可能会失败吗?例如,一个故事有99条评论。 2个用户同时完全调用此功能。第一个选择查询同时针对两者执行,并且它们都获得99.因此两个插入都将进行注册,从而计算注册表101,这违反了我的业务规则。任何人都有任何想法,事情如何。或者关于我应该如何处理这种情况的任何想法。 (不要问我为什么要使用2张桌子:P无论如何)

2 个解决方案

#1

Yes, it can/will fail because 10 request may all receive 99 comments_count from the SQL query, and each time new row will be inserted to regcomments, and then you'll have 109 rows in the table.

是的,它可能/将会失败,因为10个请求可能都会从SQL查询中收到99个comments_count,并且每次将新行插入到regcomments中,然后表中将有109行。

One possible solution would be to move the checking logic into the SQL (use stored procedure with locking/mutex).

一种可能的解决方案是将检查逻辑移动到SQL中(使用带锁定/互斥锁的存储过程)。

2 个解决方案

#1

更多相关文章

随机推荐