MySQL是否将查询排队? [英] Does MySQL queue queries?

问题描述

如果有两个人同时向数据库发送相同的查询，而一个人使另一个查询返回的内容有所不同，该怎么办?

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?

我有一家商店，这里只剩一件东西.两个或两个以上的人购买该物品，并且查询恰好同时到达MySQL服务器.我的猜测是它只会排队，但是如果是这样，MySQL如何选择要执行的第一个，我对此有影响吗?

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?

推荐答案

同时发送相同的查询

sending the same query at the same time

查询不能总是并行运行

这取决于数据库引擎.使用MyISAM，几乎每个查询都获得表级锁定，这意味着查询将作为队列顺序运行.在大多数其他引擎中，它们可以并行运行.

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说nothing happens at the exact same time and a CPU does not do everything at once

那不是完全正确. DBMS可能可以在具有一个以上cpu和一个以上网络接口的计算机上运行. 非常不太可能同时到达两个查询-但并非不可能，因此存在一个互斥量以确保解析/执行转换仅作为一个线程运行(执行时-不一定是相同的轻量级过程).

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).

有两种方法可以解决一致性DML:要么使用事务(每个用户有效地获取数据库的克隆)，当查询完成时，DBMS尝试协调所有更改-如果协调失败，则DBMS滚动返回其中一个查询并将其报告为失败.另一种方法是使用行级锁定-DBMS标识将由查询更新的行，并将其标记为保留以供更新(其他用户可以读取每行的原始版本，但是任何尝试更新数据的尝试都可以直到该行再次可用为止.

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).

您的问题是您有两个mysql客户端，每个mysql客户端都检索到库存剩余一件的事实.由于(因为提到了PHP)库存水平可能是在与后续库存调整不同的DBMS会话中检索的，这使情况变得更加复杂-您的事务跨度不能超过HTTP请求.因此，您需要在单个事务中重新验证DBMS外部维护的所有事实.

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.

乐观锁定可以创建伪事务控制机制-使用时间戳和用户标识符标记要修改的记录(使用PHP时，PHP会话ID是一个不错的选择)-如果您要修改它，其他原因对其进行了更改，则您的代码知道以前检索到的数据无效.但是，这可能会导致其他并发症.

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.

这篇关于MySQL是否将查询排队?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！