Let's say I have two files file1.php and file2.php.
file1.php has the following queries:
-query 1
-query 2
-query 3
file2.php has the following queries:
-query 4
-query 5
-query 6
Let's say one visitor runs the first script and another visitor runs the second one exactly at the same time.
My question is: does MySQL receive one connection and keep the second connection in queue while executing all queries of the first script, and then moves on to the second connection?
Will the order of queries processed by MySQL be 1,2,3,4,5,6 (or 4,5,6,1,2,3) or can it be in any order?
What can I do to make sure MySQL executes all queries of one connection before moving on to another connection?
I'm concerned with data integrity. For example, account balance by two users who share the same account. They might see the same value, but if they both send queries at the same time, this could lead to some unexpected outcome
The database can accept queries from multiple connections in parallel. It can execute the queries in arbitrary order, even at the same time. The isolation level defines how much the parallel execution may affect the results:
If you don't use transactions, the queries can be executed in parallel, and the strongest isolation level still guarantees only that the queries will return the same result as if they were not executed in parallel, but can still be run in any order (as long as they're sorted within each connection)
If you use transactions, the database can guarantee more:
The strongest isolation level is serializable, which means the results will be as if no two transactions ran in parallel, but the performance will suffer.
The weakest isolation level is the same as not using transactions at all; anything could happen.
If you want to ensure data consistency, use transactions:
START TRANSACTION;
...
COMMIT;
The default isolation level is read-commited, which is roughly equivalent to serializable with ordinary SELECTs happening out-of-transactions. If you use SELECT FOR UPDATE for every SELECT within the transaction, you get serializable
See: http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read
In general, you cannot predict or control order of execution - it can be 1,2,3,4,5,6 or 4,5,6,1,2,3 or 1,4,2,5,3,6 or any combination of those.
MySQL executes queries from multiple connections in parallel, and server performance is shared across all clients (2 in your case). I don't think you have a reason to worry or change this - MySQL was created with this in mind, to be able to serve multiple connections.
If you have performance problems, they typically can be solved by adding indexes or changing your database schema - like normalizing or denormalizing your tables.
You may limit max_connections to 1 but then it will give you too many connections error for other connections. Limiting concurrent execution makes no sense.