CODEWITHSUNDEEP
mysqlstored-procedures
Hobbyist
Hobbyist

Reputation: 16192

Does MySQL handle queries within a procedure synchronously?

Perhaps the title is a little misleading, so I'll explain my question in further detail. Obviously the queries inside of the procedure are executed synchronously and in order, but are procedures themselves executed synchronously?

Lets say I have a procedure called "Register" which handles a couple of queries, for example it looks like this:

BEGIN
    DECLARE account_inserted INT(11);
    INSERT INTO accounts (...) VALUES (...);
    SET account_inserted = LAST_INSERTED_ID(); # <------
    QUERY using account_inserted...
    QUERY using account_inserted...
    QUERY using account_inserted... 
    ...
END

Now lets say that there were numerous requests to register coming in at the same time (For example purposes let us say around 200-300 requests) would it execute all of the procedures in order? Or is it possible would the LAST_INSERTED_ID() variable to conflict with a row inserted from another procedure that is being executed in parallel?

Upvotes: 1

Views: 592

Answers (2)

miken32
miken32

Reputation: 42719

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

Upvotes: 3

eggyal
eggyal

Reputation: 125865

You're muddling three things:

  1. Whether MySQL executes procedures synchronously

    This could be interpreted to mean either "does MySQL wait for each command within a procedure to complete before moving on to the next?" or "does MySQL wait for the entire procedure to complete before accepting further instructions from the connection that invoked the CALL command?". In both cases, the answer is "yes, it does".

  2. Whether invocations of MySQL procedures are executed atomically

    As with any other series of commands, commands within procedures are only atomic if performed within a transaction on tables that use a transactional storage engine. Thus a different connection may well execute another INSERT between the INSERT in your procedure and the command that follows.

  3. Whether LAST_INSERTED_ID() is guaranteed to return the value generated by the immediately preceding INSERT command in the procedure?

    Yes, it is. The most recent insertion ID is maintained on a per-connection basis, and as described above the connection waits for CALL to complete before further commands are accepted.

Upvotes: 3

Related Questions