Skip navigation links

MySQL Forums :: PHP :: FOR UPDATE combined in a single query


Advanced Search

FOR UPDATE combined in a single query
Posted by: Mad Max ()
Date: February 07, 2013 11:06AM

Just wondering, if the approaches below serve ACID - Isolation purposes in same manner. What I mean is, row should be locked and no one should ever be able to update table_one and table_two until the process finishes.

1. I know that the first style is correct but what about combining queries like in style 2 below?

2. Can one read total value from the row with SELECT statement or everything is forbidden in FOR UPDATE case?

Thanks


//** STYLE 1 ********************************************************

mysql_query("SET AUTOCOMMIT=0");

$query = "SELECT total FROM table_one WHERE id = '1' FOR UPDATE";

if (mysql_query($query))
{
$record = mysql_fetch_array($query);
$total = $record['total'];

$query = "UPDATE table_two SET total = '$total' WHERE id = '1'";

if (mysql_query($query))
{
mysql_query("COMMIT");
}
else
{
mysql_query("ROLLBACK");
}

}

mysql_query("SET AUTOCOMMIT=1");

//** STYLE 2 ********************************************************

mysql_query("SET AUTOCOMMIT=0");

$query = "UPDATE table_two SET total = (SELECT total FROM table_one WHERE id = '1' FOR UPDATE) WHERE id = '1'";

if (mysql_query($query))
{
mysql_query("COMMIT");
}
else
{
mysql_query("ROLLBACK");
}

mysql_query("SET AUTOCOMMIT=1");

Options: ReplyQuote


Subject Written By Posted
FOR UPDATE combined in a single query Mad Max 02/07/2013 11:06AM
Re: FOR UPDATE combined in a single query Peter Brawley 02/07/2013 12:15PM
Re: FOR UPDATE combined in a single query Mad Max 02/08/2013 02:28AM
Re: FOR UPDATE combined in a single query Rick James 02/10/2013 10:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.