Can I update the current row of my table ?

[jeddi]

I am stepping through a table and I want to update one
records of the current row if there is a condition met.

Is it allowable to updtae the current row?
Or is there an automatic record lock that would prevent it ?


$sql = "SELECT * FROM cb_update ORDER BY id";
$result = mysql_query($sql)
or die("could not OPEN CB_update.". mysql_error());

while($row = mysql_fetch_assoc($result)){
extract($row);

if( $mgrav != $grav) {
$new_data = 'y';
$grav_chg =$grav-$mgrav;
$sql_spc = "UPDATE cb_update SET grav_chg = '$grav_chg' WHERE cb_id = '$cb_id' ";
$result = mysql_query($sql) or die("could not UPDATE cb_update .". mysql_error());
}
}


If I am not able to do it this way, then I will have to save the row
id numbers and go back to make the changes when the while loop has ended.

Does anyone know what I should do ?

Thanks.

[Fumigator]

Did you try it?

[jeddi]

No because
what I was thinking is that although it
might work a few times when I test it, when I use
it on a live system with many users, it might break.

So I was asking the question from a "correct methods" type
of viewpoint.

I suspect that mysql does record locking just at the instant of writing
any data - but that is a guess.

I was hoping for someone more experienced than I am to give me
the "this is how it works" type of answer.

[Fumigator]

MySQL locks tables/pages/rows differently depending on the engine you are using. InnoDB locks at the record level, MyIsam locks at a table level. What I'm not 100% about is the timing between C functions (mysql_array, mysql_fetch) and the locks placed on the table/row.

My common sense tells me when you call mysql_fetch_assoc(), MySQL locks the table, performs fetches the row, then unlocks the table. If it kept the lock on the table even after the fetch was done, requests would bottleneck like crazy-- who knows what kinds of PHP coding could be written between each fetch? Just doesn't make any sense. So I would think your code is fine.

Though, I have not spent any time testing and verifying MySQL's lock algorithms, so someone with more experience might have a better answer.