MySQL处理来自2个不同连接的查询的顺序是什么？

Question

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

Answer 1

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