MySQL INSERT和SELECT优先顺序 [英] MySQL INSERT and SELECT Order of precedence

问题描述

如果在mysql表上同时完成INSERT和SELECT，哪个先执行?

if an INSERT and a SELECT are done simultaneously on a mysql table which one will go first?

示例:假设用户"表的行计数为0.

Example: Suppose "users" table row count is 0.

然后这两个查询同时运行(假设它在相同的毫秒/微秒内):

Then this two queries are run at the same time (assume it's at the same mili/micro second):

INSERT into users (id) values (1)

和

SELECT COUNT(*) from users

最后一个查询会返回0还是1?

Will the last query return 0 or 1?

推荐答案

取决于您的users表是MyISAM还是InnoDB.

Depends whether your users table is MyISAM or InnoDB.

如果是MyISAM，则其中一条语句或另一条语句将锁定表，而您除了

If it's MyISAM, one statement or the other takes a lock on the table, and there's little you can do to control that, short of locking tables yourself.

如果是InnoDB，则它是基于事务的.多版本体系结构允许并发访问表，并且SELECT将从事务开始的那一刻开始查看行数.如果同时执行INSERT，则SELECT将看到0行.实际上，如果INSERT的事务尚未提交，那么几秒钟后甚至可以看到由SELECT执行的0行.

If it's InnoDB, it's transaction-based. The multi-versioning architecture allows concurrent access to the table, and the SELECT will see the count of rows as of the instant its transaction started. If there's an INSERT going on simultaneously, the SELECT will see 0 rows. In fact you could even see 0 rows by a SELECT executed some seconds later, if the transaction for the INSERT has not committed yet.

这两个事务无法真正同时开始.保证交易有一定的顺序.

There's no way for the two transactions to start truly simultaneously. Transactions are guaranteed to have some order.

这篇关于MySQL INSERT和SELECT优先顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！