PHP script with multiple queries sequence

Evernoob

Greetings fellow PHP enthusiasts.

My question relates to the way PHP and MySQL may execute certain queries within PHP files given a large volume of users running PHP scripts simultaneously.

Ok, so what I want to know is if I have a PHP file with something like the following:

<?php

$Query = "Do some stuff to a database table";
execute query;

$Query = "select record from the table based on the stuff I just did";
execute query;

$Query = "Based on my previous SELECT, do some more stuff to another table";
execute query;

Pretty technical stuff I know, but what I'm wondering is if I have a massive volume of users on my site accessing pages which select and alter data from tables, is it possible that one of those people may cause a query to be run in between the queries listed in my file?

Basically, I have something like the file I have posted above which I am not at liberty to disclose for confidentiality reasons. I need to be 100% positive that the 3 queries in this file (like the example above) will be run sequentially with absolutely no queries (even selects) performed on the database in between them.

Hope that makes sense!

Evernoob

laserlight

Pretty technical stuff I know, but what I'm wondering is if I have a massive volume of users on my site accessing pages which select and alter data from tables, is it possible that one of those people may cause a query to be run in between the queries listed in my file?

Yes.

Sxooter

Wait, is it important that nothing happen that the current running transaction see, or that all other transactions wait until the current running one finishes.?

Evernoob

Well, I'm doing a query on the data and then straight after that doing another data-affecting query based on the information from the first. If a data affecting query was run by another user in between these 2, it would screw my data irrepairably. If they did a select they'd just be shown the wrong thing.

I'd like some mechanism of ensuring that once a php file starts its transactions, no other MySQL queries can be run until it has finished.

Perhaps I could add a lock field or something to the relevant records as these are running...

Sxooter

I think that mysql with innodb table handler and serializable transaction mode will work here.

Read this by Heikki Tuuri himself:

http://archives.postgresql.org/pgsql-hackers/2003-09/msg01109.php

Note that this is one of those areas that MySQL innodb beats postgresql. You've got to jump through a few more hoops to get the same functionality out of pgsql.