On Aug 30, 2010, at 12:09 PM, real...@areality.co.uk wrote:
> Hello,
>
> I'm working on a very simple stock tracking system on unix with MySQL 5 and
> PHP.
>
> Working under the assumption that all my updates would be executed
> sequentially in the order they appear in the PHP script I perform these
> operations:
>
> $sql="UPDATE shop_product SET
> working_stocklevel=working_stocklevel+".$_SESSION['prod'][$product_id]."
> WHERE id=$product_id";
> $result = mysql_query ($sql) or
> die("Error=".mysql_error()."<BR>".$sql."<br>");
>
> Then I record this activity into a seperate journal table using a function
> call
>
> Then
>
> $sql="UPDATE shop_product SET
> working_stocklevel=working_stocklevel-".$newamount." WHERE id=$product_id";
> $result = mysql_query ($sql) or
> die("Error=".mysql_error()."<BR>".$sql."<br>");
>
> My journal records that occasionally the second command is executed before
> the first - throwing all the figures out. Is this possible?
>
> My tables are MyISAM, indexed on ID and not particularly large (about 3000
> rows).
>
> I knopw this probably needs to be transactional, but I thought updates would
> auto-commit, and as such be sequential.
>
> Can anyone give any advice?
If the two UPDATE statements are issued in sequence within the same session
(within the same connection), they should execute in the same order you issue
them. Particularly since you are waiting for the result of the first before you
proceed to the second.
I suggest that when you observe an out of order entry in your journal, you
compare it with the MySQL server's general query log or binary log. The general
query log will show the order in which the server receives the statements. The
binary log will show the order in which they finish executing.
--
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
