Does MySQL queue queries?

Question

What happens if there are two people sending the same query at the same time to the database and one makes the other query return something different?

I have a shop where there is one item left. Two or more people buy the item and the query arrives at the exact same time on the MySQL server. My guess is that it will just queue but if so, how does MySQL pick the first one to execute and can i have influence on this?

Answer 1

sending the same query at the same time

QUERIES DO NOT ALWAYS RUN IN PARALLEL

It depends on the database engine. With MyISAM, nearly every query acquires a table level lock meaning that the queries are run sequentially as a queue. With most of the other engines they may run in parallel.

echo_me says nothing happens at the exact same time and a CPU does not do everything at once

That's not exactly true. It's possible that a DBMS could run on a machine with more than one cpu, and with more than one network interface. It's very improbable that 2 queries could arrive at the same time - but not impossible, hence there is a mutex to ensure that the paring/execution transition only runs as a single thread (of execution - not necesarily the same light weight process).

There's 2 approaches to solving concurent DML - either to use transactions (where each user effectively gets a clone of the database) and when the queries have completed the DBMS tries to reconcile any changes - if the reconciliation fails, then the DBMS rolls back one of the queries and reports it as failed. The other approach is to use row-level locking - the DBMS identifies the rows which will be updated by a query and marks them as reserved for update (other users can read the original version of each row but any attempt to update the data will be blocked until the row is available again).

Your problem is that you have two mysql clients, each of which have retrieved the fact that there is one item of stock left. This is further complicated by the fact that (since you mention PHP) the stock levels may have been retrieved in a different DBMS session than the subsequent stock adjustment - you cannot have a transaction spanning more than HTTP request. Hence you need revalidate any fact maintained outside the DBMS within a single transaction.

Optimistic locking can create a pseudo - transaction control mechanism - you flag a record you are about to modify with a timestamp and the user identifier (with PHP the PHP session ID is a good choice) - if when you come to modify it, something else has changed it, then your code knows the data it retrieved previously is invalid. However this can lead to other complications.

Answer 2

They are executed as soon as the user requests it, so if there are 10 users requesting the query at the exact same time, then there will be 10 queries executed at the exact same time.

nothing happens at the exact same time and a CPU does not do everything at once. It does things one at a time (per core and/or thread). If 10 users are accessing pages which run queries they will "hit" the server in a specific order and be processed in that order (although that order may be in milliseconds). However, if there are multiple queries on a page you can't be sure that all the queries on one user's page will complete before the queries on another user's page are started. This can lead to concurrency problems.

edit:

Run SHOW PROCESSLIST to find the id of the connecton you want to kill .

SHOW PROCESSLIST will give you a list of all currently running queries.

from here.

MySQL will perform well with fast CPUs because each query runs in a single thread and can't be parallelized across CPUs.

• how mysql uses memorie

Answer 3

Consider a query similar to:

UPDATE items SET quantity = quantity - 1 WHERE id = 100

However many queries the MySQL server runs in parallel, if 2 such queries run and the row with id 100 has quantity 1, then something like this will happen by default:

1. The first query locks the row in items where id is 100
2. The second query tries to do the same, but the row is locked, so it waits
3. The first query changes the quantity from 1 to 0 and unlocks the row
4. The second query tries again and now sees the row is unlocked
5. The second query locks the row in items where id is 100
6. The second query changes the quantity from 0 to -1 and unlocks the row

Answer 4

This is essentially a concurrency question. There are ways to ensure concurrency in MySQL by using transactions. This means that in your eshop you can ensure that race conditions like the ones you describe won't be an issue. See link below about transactions in MySQL.

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html

http://zetcode.com/databases/mysqltutorial/transactions/

Depending on your isolation level different outcomes will be returned from two concurrent queries.