mysqli :: query()在选择查询时返回true [英] mysqli::query() returns true on select queries

问题描述

我正在尝试使用MySQL进行排队(我知道，对不起我！).我的设置方法是进行更新，以在队列项目上设置接收方ID，更新发生后，我通过接收方ID选择更新的项目.

I'm trying to make a queue using MySQL (I know, shame on me!). The way I have it set up is an update is done to set a receiver ID on a queue item, after the update takes place, I select the updated item by the receiver ID.

我面临的问题是当我查询更新然后执行选择时，选择查询返回true而不是结果集.发出大量请求时，似乎会发生这种情况.

The problem I'm facing is when I query for the update and then do the select, the select query returns true instead of a result set. This seems to happen when a rapid amount requests are made.

有人知道为什么会这样吗?

Does anyone have any idea why this is happening?

谢谢.

模式:

CREATE TABLE `Queue` (
  `id` char(11) NOT NULL DEFAULT '',
  `status` varchar(20) NOT NULL DEFAULT '',
  `createdAt` datetime DEFAULT NULL,
  `receiverId` char(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

出队:

update `'.self::getTableName().'`
set
    `status` = 'queued',
    `receiverId` = '%s'
where
    `status` = 'queued'
    and `receiverId` is null
order by id
limit 1;

select
    *
from 
    `'.self::getTableName().'`
where
    `receiverId` = \'%s\'
order by id
desc limit 1

推荐答案

这听起来像某种竞争条件.您正在使用MyISAM，因此可能会推迟更新(尤其是该表上的流量很大时).

This sounds like a race condition of some kind. You're using MyISAM, so it's possible an update might be deferred (especially if there's a lot of traffic on that table).

true返回表明您的select查询已正确完成，但返回且结果集为空(无行).如果发生这种情况时，您的逻辑是等待50毫秒，然后重试，那么您可能会发现一切正常.

The true return indicates that your select query completed properly but returned and empty result set (no rows). If your logic when that happens is to wait, say, 50 milliseconds, and try again, you may find that things work correctly.

Edit :您可以尝试在执行UPDATE之前锁定表，直到完成最后一个SELECT.但这可能会破坏应用程序其他部分的性能.最好的办法是使您的应用程序在遇到竞争状况时保持强大.

Edit: You could try locking the table from before you do the UPDATE until you've done the last SELECT. But that might foul up the performance of other parts of your app. The best thing to do is make your app robust in the face of race conditions.

这篇关于mysqli :: query()在选择查询时返回true的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！