当多个用户在短时间窗口完成时，堆叠查询是否可以在彼此之间潜入？

Question

I find this kind of hard to explain, but consider the following situation:

You have a website with two insert queries that get loaded right after eachother, there might be some variable declarations and for loops in between, but no other queries besides these two:

$mysqli->doQuery("INSERT INTO `company_order`(`customer_id`, `item_id`) 
                                      VALUES ($givenid, $givenproduct")
// This table has a primary key that gets defined using auto_increment.

/* (loop that defines array with 50~ variables, few names that get defined in object variables) */

$mysqli->doQuery("INSERT INTO `customer_orderlist`(`customer_id`,`order_id`) 
                                           VALUES ($givenid, (LAST_INSERT_ID()) ")

Imagine if two users loaded the same function that executes these queries almost right after eachother. Is there a risk that one user might get the last inserted ID from the other user, or is it guaranteed that the queries will be executed in order without any other queries getting called in between them?

Answer 1

The MySQL function LAST_INSERT_ID() returns the first auto-generated value successfully inserted for an AUTO INCREMENT column as a result of the most recent successful INSERT statement executed on the current connection.

The value is stored by the server on a per-connection basis. This guarantees the value returned by LAST_INSERT_ID() on your second query is the value generated for the AUTO INCREMENT column on your first query, no matter how many other INSERT queries run between these two queries on other connections.

To answer your questions:

is it guaranteed that the queries will be executed in order without any other queries getting called in between them?

No, there is no such guarantee. The queries are executed in the order you send them to the server but your connection does not block other connections to execute their own queries (and vice-versa).

Is there a risk that one user might get the last inserted ID from the other user

No. There is no such risk as long as the two queries (the INSERT that auto generates an AUTO INCREMENT value and the query that calls LAST_INSERT_ID()) run on the same connection (and no other INSERT query runs between them on the same connection).