Search code examples
mysqlstored-procedurespdoconcurrency

MySQL procedure OUT parameters, shared connection and concurrent requests

Suppose we have some script that can handle hundreds of requests per second and it uses connection pool to connect to DB.

This script is calling MySQL procedure and it should get output parameters.

The common practice is to make 2 requests:

call someProcedure(@parameter1, @parameter2);

and

select @parameter1, @parameter2;

Here @parameter1 and @parameter2 - are user-defined variables that have connection scope. They are shared between processes that use the same connection.

Imagine that we could run two similar processes simultaneously, both are calling the procedure and then would get results. So we have a risk that while selecting @parameter1 and @parameter2, they could be rewrited by concurrent process that also calls someProcedure(..).

How to prevent such behavior? As an idea - try to prepare unique variable names for each request. But if we have hundreds of requests per second and shared connection scope the amount of variables could rapidly become inadequate.

Is there any other good practice?

Solution

  • Edit: You clarified in the comments that you are using PDO with persist connections, not a connection pool.

    Persistent connections don't reset between requests like a connection pool does. There is a risk that information leaks through. Read What are the disadvantages of using persistent connection in PDO

    If you're concerned about this, then disable PDO::ATTR_PERSISTENT.

    Persistent connections are not typically necessary for MySQL, because opening new database connections is quick compared to other brands of database.

    To make connections even quicker, try setting skip_name_resolve=1 on the MySQL Server configuration. See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_name_resolve

    If that's still not performant enough to handle your rate of requests, you should either be scaling out to multiple application servers, or else abandoning PHP for a compiled language that supports real connection pools.

    My original answer, pertaining to connection pools, follows:

    1. Get connection from connection pool.

    2. Call procedure.

    3. Query variables using the same connection, i.e. before returning that connection to the connection pool.

    User variables are scoped to a MySQL session, so they retain their value in that session. Multiple concurrent sessions can have variables of the same name, but they do not conflict. Just like local variables in many programming languages. One session cannot inadvertently overwrite the user-defined variable of another session.

    It's in the manual: https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

    User-defined variables are session specific. A user variable defined by one client cannot be seen or used by other clients. (Exception: A user with access to the Performance Schema user_variables_by_thread table can see all user variables for all sessions.) All variables for a given client session are automatically freed when that client exits.

    The connection pool manager "resets" all session-specific state (session variables, user-defined variables, transactions, temp tables, etc.) before giving the connection to another client. This is important so information doesn't leak through from one session to the next. It would not do if the next client request could read your private data in a temp table!

    But connection pool interfaces typically give you the opportunity to hold on to the connection and use it for several SQL queries in succession. This is the only way you could do a transaction with multiple SQL statements, for example.

    So use that given connection for the unit of work in your code before returning it to the connection pool.