1

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!

Will59
  • 1,430
  • 1
  • 16
  • 37

1 Answers1

1

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the answer. Actually I do use multipleStatements in many places: I want a whole bunch of changes to go through together, or not at all, so I use `START TRANSACTION; .... COMMIT;` ... Relying on the last commit to roll back everything if a single call failed. Can this be done with isolated transactions? Thanks! – Will59 May 03 '21 at 17:09
  • You can commit or rollback a set of changes without using multipleStatements. Did you look at the answer I linked to? – Bill Karwin May 03 '21 at 18:00
  • Right, I did not read it properly, thanks. – Will59 May 03 '21 at 22:50
  • Follow up question... You mention that when a connection is returned to the pool, session states are wiped. Does this also reset the impact of the REPEATABLE-READ mode? Meaning, another client using this connection will see the db data as it is when it gets the connection, or will it still see the data as it was when the connection was first opened (I don't see how this could be of any use but...). Do you know if this is documented anywhere (I'm using mysql2)? Thanks again! – Will59 May 04 '21 at 09:54
  • Every new transaction starts a new repeatable-read snapshot. This happens the same way whether a thread starts a new transaction on the connection that it has already used for a prior transaction since acquiring the connection, or if it returns the connection to the pool and the connection is given to some other thread. It doesn't matter which thread is using the connection or whether the connection has been "recycled" in the meantime or not. – Bill Karwin May 04 '21 at 14:51