mysql connection pool, concurrent transactions and @ user variables

Question

I am trying to figure out if I have a problem with how I use a mySql db in an application. I am running nodejs, express, mysql2, and use connection pooling, with multiple statements queries. I have multiple servers, running different services, accessing the same db.

At the beginning of each mySql instructions set, I set some user variables to check user credentials, latest status etc... In following instructions, I use these variables to conditionally access some data, or also do inserts, modify data. There are different players involved here (nodejs, mysql2 driver, mysql specs, innoDb engine), and I am not sure on which has the definite answer to the following:

Are all my instructions in my multiple statements query going to be run in one shot, or do I need to worry about different instructions from different queries and/or servers interleaving?
user variables are maintained per connection. If instructions can interleave, I could end up with different transactions messing up each other's user variables. Would wrapping a query in a START TRANSACTION; .... COMMIT; fix the problem?

Right now, a high level view of my queries would be:

const mysql2 = require('mysql2');
const pool = mysql2.createPool({
    multipleStatements: 'true',
    // other options...
}).promise();

[rows, fields] = await pool.query(`
    // Clear variables, in case no result in found in following SELECT:
    SET @enable=NULL, @status=NULL, ...;
    // Check credentials, status, others:
    SELECT blah_blah INTO @enable FROM ...
    SELECT more_blah INTO @status FROM ...
    //Do stuff based on these variables
    SELECT some_stuff FROM somewhere WHERE @enable IS NOT NULL;
    INSERT INTO someplace ... // conditioned on @status and others
`);

Thanks!

Bill Karwin Bill Karwin · Accepted Answer · 2021-05-03T16:42:42

The better practice is to acquire a connection, and hold onto that connection while you run multiple statements. There's a complete example in an answer here: node.js mysql pool beginTransaction & connection

The example shows using a connection for a transaction with multiple queries, but it should also work to use that connection for multiple statements that are not part of the same transaction. Or stated another way, multiple transactions can be run on the same connection before that connection is released back to the pool.

While one thread holds a connection and uses it for successive queries and/or transactions, the pool will not share it with other threads. I'm not a node.js developer so I don't know this first-hand about the node.js implementation, but that's the only sensible implementation of a connection pool.

Also, user variables and other session state is not leaked to other threads. When a connection is returned to the pool, all session state is wiped. That's also a best practice that should be default in the connection pool implementation, for security. You wouldn't want your bank routing number to be stored in a user variable or a temp table, and then find that the next user could read it because they chanced to acquire the same connection from the pool.

P.S.: I've never found any case that required the multipleStatements option, and it was told to be by the former Engineering Director of MySQL: "there's no legitimate reason for multi-query to exist as a feature."

mysql connection pool, concurrent transactions and @ user variables

1 Answers