MySQL isolation levels in nodejs. Is each query in connection isolated or each pool isolated?

Question

My current isolation level for MySQL is tx_transaction = REPEATABLE-READ for each session.

So when I run the below code in different terminals the transactions are serially executed, meaning before the commit of the first transaction, the second would not start.

START TRANSACTION;
SELECT *
FROM test
WHERE id = 4 FOR UPDATE;
UPDATE test
SET parent = 98
WHERE id = 4;

So if I implement this in nodeJS, which of the following would give same result as running two terminals?

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

connection.connect();

let query = 
    START TRANSACTION;
    SELECT *
    FROM test
    WHERE id = 4 FOR UPDATE;
    UPDATE test
    SET parent = 98
    WHERE id = 4;

connection.query(query, function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.query(query, function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

or using pools

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

let query = 
    START TRANSACTION;
    SELECT *
    FROM test
    WHERE id = 4 FOR UPDATE;
    UPDATE test
    SET parent = 98
    WHERE id = 4;

pool.query(query, function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
  connection.release();
});

pool.query(query, function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
  connection.release();
});

My first guess was that the pools would create separate connections and sending queries in same connection would be same as typing in queries in the same terminal. However the documentation says https://github.com/mysqljs/mysql#pooling-connections under introduction section that

Every method you invoke on a connection is queued and executed in sequence.

and I am not exactly sure what that means.

Also, if I use connection pooling, can I be 100% sure that the concurrently running queries are handled by different sessions? So for example if the pool is not released in the first query, would the second query ALWAYS be executed by another session?

score 0 · Accepted Answer · answered Mar 15 '18 at 06:17

I have done a few tests and realized that Connection Pooling results to the expected outcome.

when I do the following with just connection

let pool = mysql.createConnection({
    connectionLimit:10,
    host: 'localhost',
    user: 'root',
    password: 'thflqkek12!',
    database: 'donationether'
});

connection.beginTransaction(function (err) {
    console.log('first transaction has started');

    if (err) {
        console.log(err);
        return;
    }

    connection.query(`INSERT INTO users VALUES (null, 0, 'username', 'token')`, function (err, results, fields) {
        if (err) {
            console.log(err);
            return;
        }

        setTimeout(function () {
            connection.commit(function (err) {
                if (err) {
                    console.log(err);
                    return;
                }

                console.log('first query done');
                connection.release();
            })
        }, 2000)
    });
});

connection.beginTransaction(function (err) {

    console.log('second transaction has started');

    if(err) {
        console.log(err);
        return;
    }

    connection.query(`UPDATE users SET username = 'c_username' WHERE username = 'username'`,function (err, results, fields) {
        if(err) {
            console.log(err);
            return;
        }

        connection.commit(function (err) {
            if(err) {
                console.log(err);
                return;
            }

            console.log('second query done');
            connection.release();
        })
    });
});

It leads to following output

first transaction has started
second transaction has started
second query done
first query done

Meaning that the transaction opened by the first connection is ignored and the second transaction finishes before. However, when I use connection pooling for following code,

let pool = mysql.createPool({
    connectionLimit:10,
    host: 'localhost',
    user: 'root',
    password: 'thflqkek12!',
    database: 'donationether'
});

pool.getConnection(function (err, connection) {
    connection.beginTransaction(function (err) {
        console.log('first transaction has started');

        if (err) {
            console.log(err);
            return;
        }

        connection.query(`INSERT INTO users VALUES (null, 0, 'username', 'token')`, function (err, results, fields) {
            console.log('first query has started');
            if (err) {
                console.log(err);
                return;
            }

            setTimeout(function () {
                connection.commit(function (err) {
                    if (err) {
                        console.log(err);
                        return;
                    }

                    console.log('first query done');
                    connection.release();
                });
            }, 2000)
        });
    });
});

pool.getConnection(function (err, connection) {
    connection.beginTransaction(function (err) {

        console.log('second transaction has started');

        if(err) {
            console.log(err);
            return;
        }

        connection.query(`UPDATE users SET username = 'c_username' WHERE username = 'username'`,function (err, results, fields) {
            console.log('second query has started');
            if(err) {
                console.log(err);
                return;
            }

            connection.commit(function (err) {
                if(err) {
                    console.log(err);
                    return;
                }

                console.log('second query done');
                connection.release();
            })
        });
    });
});

The output is as following

first transaction has started
second transaction has started
first query has started
//2seconds delay
second query has started
first query done
second query done

meaning that the first transaction is blocking the second transaction from executing.

So when the documentation said

Every method you invoke on a connection is queued and executed in sequence

It meant that they are delivered to the database in sequence but it will still be asynchronous and parallel even under transaction. However, connection pooling leads to instantiation of multiple connections and transaction within different pool connection behaves as expected for each transaction.

MySQL isolation levels in nodejs. Is each query in connection isolated or each pool isolated?

1 Answers1