Sequence of Execution in Non-Transactional Script

Hello,

I'm working on a very simple stock tracking system on unix with MySQL =20
5 and PHP.

Working under the assumption that all my updates would be executed =20
sequentially in the order they appear in the PHP script I perform =20
these operations:

$sql=3D"UPDATE shop_product SET =20
working_stocklevel=3Dworking_stocklevel+".$_SESSION['prod'][ $product_id]." W=
HERE =20
id=3D$product_id";
$result =3D mysql_query ($sql) or =20
die("Error=3D".mysql_error()."
".$sql."
");

Then I record this activity into a seperate journal table using a =20
function call

Then

$sql=3D"UPDATE shop_product SET =20
working_stocklevel=3Dworking_stocklevel-".$newamount." WHERE =20
id=3D$product_id";
$result =3D mysql_query ($sql) or =20
die("Error=3D".mysql_error()."
".$sql."
");

My journal records that occasionally the second command is executed =20
before the first - throwing all the figures out. Is this possible?

My tables are MyISAM, indexed on ID and not particularly large (about =20
3000 rows).

I knopw this probably needs to be transactional, but I thought updates =20
would auto-commit, and as such be sequential.

Can anyone give any advice?

Lee


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Sequence of Execution in Non-Transactional Script

On Aug 30, 2010, at 12:09 PM, reality@areality.co.uk wrote:

> Hello,
>=20
> I'm working on a very simple stock tracking system on unix with MySQL =
5 and PHP.
>=20
> 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:
>=20
> $sql=3D"UPDATE shop_product SET =
working_stocklevel=3Dworking_stocklevel+".$_SESSION['prod'][ $product_id]."=
WHERE id=3D$product_id";
> $result =3D mysql_query ($sql) or =
die("Error=3D".mysql_error()."
".$sql."
");
>=20
> Then I record this activity into a seperate journal table using a =
function call
>=20
> Then
>=20
> $sql=3D"UPDATE shop_product SET =
working_stocklevel=3Dworking_stocklevel-".$newamount." WHERE =
id=3D$product_id";
> $result =3D mysql_query ($sql) or =
die("Error=3D".mysql_error()."
".$sql."
");
>=20
> My journal records that occasionally the second command is executed =
before the first - throwing all the figures out. Is this possible?
>=20
> My tables are MyISAM, indexed on ID and not particularly large (about =
3000 rows).
>=20
> I knopw this probably needs to be transactional, but I thought updates =
would auto-commit, and as such be sequential.
>=20
> 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.


--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg