MySQL select or update acting very strange

Question

I got a very simple select statement for instance:

SELECT     id, some_more_fields_that_do_not_matter 
FROM       account 
WHERE      status = '0' 
LIMIT      2

Keep in mind that the above returns the following id's: 1,2

The next thing I do is loop these rows in a for each and update some records:

UPDATE     account 
SET        connection = $_SESSION['account_id'], 
           status = '1' 
WHERE      id = $row_id

Now the rows in the table with id's 1,2 have the status '1' (which I do check to make sure the rows where correctly updated). If it failed to do such, I will undo everything. As soon as everything is OK I have a counter at the first place which is 2 in this case, so 2 rows should have been updated, and I check this with a simple COUNT(*). This information will also be emailed with for instance the following data (which means everything was updated correctly):

- Time of update: 2013-09-30 16:30:02
- Total rows to be updated (selected) = 2
- Total rows successfully updated after completing queries = 2
The following id's should have been updated 
(these where returned by the SELECT statement):
1,2

So far so good. Now comes the weird part. The very next query made by a other user will however sometimes return for instance the id's 1,2 (but that's impossible because these should never be returned by the SELECT statement because they do not contain the status '0' anymore. So what happens is the following: I now receive an email with for instance:

- Time of update: 2013-09-30 16:30:39
- Total rows to be updated (selected) = 10
- Total rows successfully updated after completing queries = 8
The following id's should have been updated 
(these where returned by the SELECT statement):
1,2,3,4,5,6,7,8,9,10

Now it is really strange the 1 and 2 are selected by the update. In most cases it goes good, but very rarely it just doesn't and returns some same ID's which are already updated with a status '1'.

Notice the time between these updates. It's not even the same time. I first thought it would be something like that these queries would be executed at the exact same time (which is impossible right?). Or is this possible? Or could it somehow be that the query has been cached, and should I edit some settings at my mysql.conf file?

I have never had this problem, I tried every way of updating but it seems to keep happening. Is it maybe possible at some way to combine these 2 queries in one huge update query? All the data is just the same and doesn't do anything strange. I hope someone has a clue what this could cause the problem and why this is randomly (rarely) happening.

EDIT:

I updated the script, added the microtime to check how long the SELECT, UPDATE and CHECK-(SELECT) together takes.

First member (with ID 20468) makes a call at: 2013-10-01 08:30:10

2/2 rows have been updated correctly of the following 2 ID's: 33412,33395

Queries took together 0.878005027771 seconds

---

Second member (with ID 10123) makes a call at: 2013-10-01 08:30:14

20/22 rows have been updated correctly of the following 22 ID's: 33392,33412,33395,33396,41489,13011,12555,27971,22811 and some more but not important

Queries took together 3.3440849781036 seconds

Now you see that the 33412 and 33395 are again returned by the SELECT.

---

Third member (with ID 20951) makes a call at: 2013-10-01 08:30:16

9/9 rows have been updated correctly of the following 9 ID's: 33392,33412,33395,33396,41489,13011,12555,27971,22811

Queries took together Didn't return anything which concerns me a little bit too

Since we do not know how long the last queries took, we only know that the first and second should work correctly without problems because if you look there are 4 seconds between them. and the execution time was 3.34 seconds. Besides that the first one started at 2013-10-01 08:30:17 because the time that is logged for the call (when emailing it) is at the end of the script. The check to see how long the queries took are from the start of the first query and the stop directly after the last query and this is before I send the email (of course).

Could it be something in my.cnf file that mysql is doing this weird? Still I don't understand why id didn't return any execution time for the last (third) call. A solution for this would be to Queue these actions by first saving them into a table and executing them one at a time by a cron job. But it's not really what I want, it should be instant when a member makes the call. Thanks for the help so far.

Anyway here is my my.cnf in case someone has suggestions for me (Server has 16GB RAM installed):

[client]
port                    = 3306
socket                  = /var/run/mysqld/mysqld.sock

[mysqld_safe] 
socket                  = /var/run/mysqld/mysqld.sock
nice                    = 0

[mysqld]
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
key_buffer              = 16M 
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover          = BACKUP
max_connections         = 20
query_cache_type        = 1 
query_cache_limit       = 1M 
query_cache_size        = 4M
log_error               = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M
innodb_buffer_pool_size = 333M
join_buffer_size        = 128K 
tmp_table_size          = 16M
max_heap_table_size     = 16M
table_cache             = 200

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

EDIT 2:

    $recycle_available = $this->Account->Membership->query("
    SELECT Account.id,
    (SELECT COUNT(last_clicks.id) FROM last_clicks WHERE last_clicks.account = Account.id AND last_clicks.roulette = '0' AND last_clicks.date BETWEEN '".$seven_days_back."' AND '".$tomorrow."') AS total, 
    (SELECT COUNT(last_clicks.id)/7 FROM last_clicks WHERE last_clicks.account = Account.id AND last_clicks.roulette = '0' AND last_clicks.date BETWEEN '".$seven_days_back."' AND '".$tomorrow."') AS avg 
    FROM membership AS Membership 
    INNER JOIN account AS Account ON Account.id = Membership.account
    WHERE Account.membership = '0' AND Account.referrer = '0' AND Membership.membership = '1'
    HAVING avg > 0.9
    ORDER BY total DESC");
    foreach($referrals as $key => $value)
    {
        $this->Account->query("UPDATE account SET referrer='".$account_id."', since='".$since_date."', expires='".$value['Account']['expires']."', marker='0', kind='1', auction_amount='".$value['Account']['auction_amount']."' WHERE id='".$recycle_available[$key]['Account']['id']."'");
        $new_referral_id[] = $recycle_available[$key]['Account']['id'];
        $counter++;
    }
    $total_updated = $this->Account->find('count',array('conditions'=>array('Account.id'=>$new_referral_id, 'Account.referrer'=>$account_id, 'Account.kind'=>1)));

Answer 1

You indicate in the comments you are using transactions. However, I can't see any $dataSource->begin(); nor $dataSource->commit(); in the PHP snippet you posted. Therefore, you must be doing $dataSource->begin(); prior to the snippet and $dataSource->commit(); or $dataSource->rollback(); after the snippet.

The problem is that you're updating and then trying to select prior to committing. No implicit commit is created, so you don't see updated data: http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html