mysql查询返回true,mysqli :: query（）在选择查询时返回true

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.

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?

Thanks in advance.

Schema:

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;

Dequeue:

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

解决方案

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).

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: 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.