Page 1 of 1

Is it possible to retrieve values set by an Update statement

Posted: Thu Aug 20, 2009 1:33 pm
by TipPro
Basically all I am trying to do is add 1 to a field and then retrieve what the new value is... but multiple programs (not written by me) are using the same database so I am worried about race conditions.

- - - - - - - -

Is it possible to retrieve values set by an Update statement?

For example...

Code: Select all

$query = "UPDATE inventory_main SET inventoryNumber = inventoryNumber + 1 WHERE inventoryId = 7";
$dbResults = $db->query($query);
Is it possible to know what inventoryNumber is without running a Select statement after the Update statement? I am worried about race conditions since several users may update inventoryNumber in between the time I run the Update statement and the Select statements.

- - - - - - - -

I did write some code that I believe will work and prevent race conditions, but there probably is a much simpler way to go about this.

$success = false;

Code: Select all

while(!$success){
 
    $query = "SELECT inventoryNumber FROM inventory_main WHERE inventoryId = 7";
    $dbResults = $db->query($query);
    $row = $dbResults->fetch_assoc();
 
    echo "Inventory Number: ".$row['inventoryNumber']."<br />";
    $newFieldValue = $row['inventoryNumber'] + 1;
 
    echo "Attempt: ";
    $query = "UPDATE inventory_main SET inventoryNumber = IF (inventoryNumber = ".$row['inventoryNumber'].", inventoryNumber + 1, inventoryNumber) WHERE inventoryId = 7 LIMIT 1";
    $dbResults = $db->query($query);
    if($db->affected_rows == 1) $success = true;
}

Re: Is it possible to retrieve values set by an Update statement

Posted: Thu Aug 20, 2009 1:36 pm
by Eran
Strictly speaking, no, you can't have an update statement return a value. An UPDATE updates, A SELECT selects (I know, it's crazy!)
There are two ways you can deal with your requirements -
1. Use a SELECT ... FOR UPDATE query before the update (this will lock the row until after the update statement has completed). http://dev.mysql.com/doc/refman/5.0/en/ ... reads.html
2. Run both queries as part of a transaction http://dev.mysql.com/doc/refman/5.0/en/commit.html

Re: Is it possible to retrieve values set by an Update statement

Posted: Thu Aug 20, 2009 2:44 pm
by TipPro
Thanks for the reply pytrin. The SELECT... FOR UPDATE reference page was helpful. I am able to do what I need with this one statement.

Code: Select all

 
$query = "UPDATE inventory_main SET inventoryNumber = LAST_INSERT_ID(inventoryNumber + 1) WHERE inventoryId = 7";
$dbResults = $db->query($query);
$newValue = $db->insert_id;
 

Re: Is it possible to retrieve values set by an Update statement

Posted: Fri Aug 21, 2009 8:18 am
by TipPro
I have a couple quick questions about my code in my last post. I want to make sure I have no threat of a race condition.

Does $db->insert_id query the database again?

Does it matter how much time is between the time I called my first statement to update a record and the $db->insert_id?

Re: Is it possible to retrieve values set by an Update statement

Posted: Fri Aug 21, 2009 8:26 am
by Eran
Well, you're using some abstraction library that you didn't disclose so it's hard to tell without knowing what $db is.
Instinctively I would say that insert_id is available from the moment you execute your query. You should take a look at the source of the library you are using and verify if it's critical.

Re: Is it possible to retrieve values set by an Update statement

Posted: Fri Aug 21, 2009 9:27 am
by TipPro
Sorry I should have included more detail. I am using the MySQLi class.

Code: Select all

$db = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
The reference page, http://us.php.net/manual/en/mysqli.insert-id.php for ->insert_id makes me believe that this value is preset from the last query that was performed.

Re: Is it possible to retrieve values set by an Update statement

Posted: Fri Aug 21, 2009 9:35 am
by Eran
In that case, yes :)

Re: Is it possible to retrieve values set by an Update statement

Posted: Fri Aug 21, 2009 9:49 am
by TipPro
Thank you so much for your help. I really appreciate the time you took to answer my questions.