Hobbyist Hobbyist - 2 years ago 142
SQL Question

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:

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...

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
variable to conflict with a row inserted from another procedure that is being executed in parallel?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download