Geek Answers Handbook

Does MySQL query the queue?

What happens if two people send the same request to the database at the same time and the other makes the other request different?

I have a store that has one item. Two or more people buy the product, and the request arrives at the same time on the MySQL server. I assume it will be just a queue, but if so, how does MySQL choose the first one to execute, and can I influence this?

+4
source share
5 answers

sending the same request at the same time

REQUESTS DO NOT ALWAYS WORK IN PARALLEL

It depends on the database engine. In MyISAM, almost every request receives a table-level lock, which means that requests are launched sequentially as a queue. With most other engines, they can work in parallel.

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

This is not entirely true. It is possible that a DBMS can work on a machine with more than one processor and with several network interfaces. It is very unlikely that 2 requests can appear simultaneously, but not impossible, therefore there is a mutex that guarantees that the transition to the session / execution is performed only as a single thread (execution is not necessarily the same light weight).

There are 2 approaches to solving coincident DML - either to use transactions (when each user effectively receives a database clone), and when the queries are completed, the DBMS tries to combine any changes - if the reconciliation fails, then the DBMS throws back one of the requests and reports that it does not work. Another approach is to use row level locking - the DBMS identifies the rows that will be updated by the request and marks them as reserved for updates (other users can read the original version of each row, but any attempt to update the data will be blocked until the row will be available again).

Your problem is that you have two mysql clients, each of which received the fact that there was one stock item left. This is further complicated by the fact that (since you mention PHP) the inventory levels may have been obtained at a different DBMS session than the subsequent inventory adjustment - you cannot have a transaction that spans more than an HTTP request. Therefore, you need to review any fact supported outside the DBMS in one transaction.

An optimistic lock can create a pseudo-transaction control mechanism - you mark a record that you intend to change using a timestamp and user ID (with a PHP session identifier, PHP is a good choice) - if, when you come in to change it, something else changed it, then your code knows that the data received earlier is invalid. However, this can lead to other complications.

+5
source

They are executed as soon as the user requests it, therefore, if 10 users request the request at the same time, then 10 requests will be executed at the same time.

nothing happens at the same time, and the processor does not do everything at once. He does things one at a time (per core and / or thread). If 10 users access the pages on which requests are executed, they will “hit” the server in a certain order and be processed in that order (although this order may be in milliseconds). However, if there are several requests on a page, you cannot be sure that all requests on one user page will be completed before requests are launched on another user page. This can lead to concurrency issues.

edit:

Run SHOW PROCESSLIST to find the ID of the connector you want to kill.

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

from here .

MySQL will work well with fast processors, because each query is executed in one thread and cannot be parallelized between processors.

+4
source

Consider a query similar to:

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

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

  • The first request blocks the row in items , where id is 100
  • The second request tries to do the same, but the row is locked, so it waits
  • The first request changes quantity from 1 to 0 and unlocks the line
  • The second request tries again and now sees that the row is unlocked
  • The second request blocks the row in items , where id is 100
  • The second request changes quantity from 0 to -1 and unlocks the line
+2
source

This is essentially a concurrency issue. There are ways to provide concurrency in MySQL using transactions. This means that in your eshop you can guarantee that race conditions like the ones you describe will not be a problem. See the 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 the isolation level, different results will be returned from two parallel queries.

+2
source

MySQL queries are processed in parallel. You can find out about the implementation here .

0
source

More articles:


All Articles