Would any configuration or INNODB principle cause the following MYSQL update to not necessarilly perform all updates?

Question

<?php
    $query = 'SELECT id FROM transaction_table';
    $result = db_query($query);  
    while($row = db_fetch_array($result)) {    
        //do some processing
        db_query('UPDATE transaction_table SET updated = "1" WHERE id = "%s"',$row['id']);
    }
?>

Every time this script run, it only updates a few random rows (8-25 on average). It should be updating all the rows.

Answer 1

echo out the query on each loop. My guess is that there are a few that error out. Use this code:

<?php
    $query = 'SELECT id FROM transaction_table';
    $result = db_query($query);  
    while($row = db_fetch_array($result)) {    
        //do some processing
        echo 'UPDATE transaction_table SET updated = "1" WHERE id = "'.$row['id'].'"<br>';
        db_query('UPDATE transaction_table SET updated = "1" WHERE id = "%s"',$row['id']);
    }
?>

Try running each query manually in MySQL directly and verify each will run. I can't see anything in the code you gave us that would cause any issues.

Answer 2

The only reason that I can think of is that this will fail to update rows that are inserted after the SELECT.

I hope that I am misreading this, but the Drupal db_fetch_array reference describes this function as:

Fetch one result row from the previous query as an array.

This doesn't really mean the previous query does it? I would hope that you can have more than one query active at a time. Note that I have never used Drupal so I'm not sure how literal the documentation is. If the documentation can be read literally, then I would be worried that the db_query('UPDATE...') is going to cause the next db_fetch_array call to go awry somehow.