[QUESTION] Connection pool returning same connection concurrently

[danigomez]

[QUESTION] Connection pool returning same connection concurrently

[danigomez]

Thanks for you answer!

n this scenario do you always expect insertId incremented by both calls? It's possible that one query finished, then you acquired the same connection (after it was released) from another user and got same insertId.

Yes, exactly, take into account that i'm running the promises using a Promise.all, and i'm not explicitly sharing the connection but always getting a new one from the pool each time.

These are the steps that i'm running

1. Build an array with multiple Promises
2. On each Promise, get free connection from pool using getConnection
3. Insert some data on auto incremented table
4. Get LAST_INSERT_ID(), here i got the same ID on more than one promise

If i'm not mistaken, making an insert before getting last_insert_id ensures that i will get a different id after each query.
So the only case that i can think of to get the same id is that both promises were using the same connection at the same time and after both finishes the step 3 both will get the same id on step 4.

This only happened to me once after having this code running for more than two years, so it was a DB glitch or a very weird edge case of the getConnection call that returned the same connection.

Following is simplified version of the code that i'm running to make it more clear:

const connection = await mysql.pool.getConnection();

await connection.beginTransaction();

/*
Should the insertion conclude on both promises prior to invoking LAST_INSERT_ID, I'd obtain identical IDs on both, provided the same connection is shared between them.
*/
await connection.query({
  sql: `
      INSERT INTO
        testable (testdata)
      VALUES 
        (?)
    `,
  values: ['thisisateststring'],
});

const [[{ id }]] = await connection.query({
  sql: `
      SELECT LAST_INSERT_ID() AS id; 
    `,
}) as any;
    
await connection.commit();
connection.release();

I'll also take a look to the threadId property, it could help to make sure that is the same connection. Also i'll try to write a test case to reproduce this scenario

[sidorares]

is it possible that the connection pool returns the sames connection reference if the getConnection is called concurrently?

Should not be possible

And after about 2 years of using this code, i found an issue where the call of LAST_INSERT_ID() returned the same inserted ID on two different Promises
In this scenario do you always expect insertId incremented by both calls? It's possible that one query finished, then you acquired the same connection (after it was released) from another user and got same insertId.

Can you also check connection.threadId to see if these are same or different connections? If two different connections query are executed in parallel, I'm not sure hoq mysql server handles concurrency in that case