Search code examples
mysqlstored-proceduresconcurrencyinnodbtemp-tables

Thread-safety of a simple & complex stored procedure in MySQL

For the purpose of this question, I am defining a complex stored procedure as 'one involving (at least) one cursor and (at least) one loop to insert data into a temporary table and then return the records from the said temporary table'.

When working on such a complex stored procedure, I was told that when two different users logged into the application perform operations which invoke the same procedure, as the procedure, being complex, can take time up to few seconds (~10 seconds) to finish execution, then the results may not faithful on a per-user basis. That is, the results may get mixed up and one user may see the results intended for the other user, as they try to access the same temporary table.

The recommendation was to use a unique system-generated identifier for each user in order to distinguish the result sets for each user.

Now, I'd like to know the following:-

  1. Can this concurrency problem be avoided by using any table or database engine configuration settings?

  2. Is this a violation of one or more ACID properties? How does using a full ACID compliant database engine (such as InnoDB, the one I am using) impact this question?

  3. In the case of a simple stored procedure, one which involves only a single SELECT statement over a join of multiple tables, but no temporary tables, when the execution time is almost always under a second, is concurrency still a problem?

Solution

  • OK, "cursor" is irrelevant. You just want a long-running Stored Procedure running simultaneously in multiple connections by the same user.

    A connection (alias a session) is the unit of "independence". An entity (such as a table) is either specific to the connection, or global to the world (aside from permission problems). There is nothing "specific to a user".

    A CREATE TEMPORARY TABLE is unique to the connection creating it. Ditto for last_insert_id(). These are "thread-safe" in that the "connection" is the "thread".

    If you want to have multiple connections (same user or not) access the same "temporary" table, then it is up to you to create a non-TEMPORARY table and somehow know the name of that table.