6

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?

Improve this question
1
  • 1
    "can i have influence on this?": - Yes. Use the appropriate transaction isolation level such as serializable Commented Apr 20, 2013 at 10:03

5 Answers 5

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

"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" what if you have Serializable isolation level? I think you are describing phantom reads which is not the case, necessarily .
4

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.

Improve this answer

7 Comments

Well, if there are 10 users and the server has 10 CPUs and all 10 users cause MySQL to run the query at the exact same millisecond (extremely unlikely), then theoretically all 10 queries could run at the same time if MySQL used all CPUs and didn't synchronize access to the same row.
I am not sure you are completely right. It doesn't need to happen at the exact same time for it to be a problem in databases. Also what about multiple cores or CPUs (most servers have these). Also they are not executed as soon as they are requested. Especially if there are isolation levels in place.
It's not about how many cores or CPUs there are on the webserver, what matters is what's happening on the MySQL server.
This answer riddles me, first you are telling us that 10 queries can be executed at the exact same time, then you say nothing happens at the exact same time. Yet you have 3 up-votes.
@echo_me Please take a look in transactions on SQL systems. Your answer is misleading. Of course there are concurrency issues in scenarios like the one Menno Gouw describes. Please edit it accordingly. What will happen if for example a query takes a long time and the isolation level allows dirty reads? Doesn't this mean that the outcome will be affected?
|
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
Improve this answer

1 Comment

If this is true then i do not need to worry. For my example i just need a "WHERE quantity > 0". Can somebody confirm this is true? Rid seems legit though :D. Meanwhile i read some about concurrency.
2

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.

Improve this answer

2 Comments

I am not sure your comment correlates with my answer.
+1 for mentioning transactions - OP should be using transactions for any shop software
0

Queries in MySQL are handled in parallel. You can read more about the implementation here.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.