MySQL INSERT and SELECT Order of precedence

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

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

Then thi开发者_运维百科s two queries are run at the same time (assume it's at the same mili/micro second):

INSERT into users (id) values (1)

and

SELECT COUNT(*) from users

Will the last query return 0 or 1?

Depends whether your users table is MyISAM or InnoDB.

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.

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.

It depends on which statement will be executed first. If first then the second will return 1, if the second one executes first, then it will return 0. Even you are executing them on the computer with multiple physical cores and due to the lock mechanism, they will never ever execute at the exactly same time stamp.