MySQL INSERT和SELECT优先顺序

本文介绍了MySQL INSERT和SELECT优先顺序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如果在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.