Node.js 中的 MySQL 隔离级别。连接中的每个查询是隔离的还是每个池都是隔离的？

Question

我当前的 MySQL 隔离级别是每个 session 的 tx_transaction = REPEATABLE-READ。

因此，当我在不同的终端中运行以下代码时，事务会串行执行，这意味着在提交第一个事务之前，第二个事务不会启动。

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

那么如果我在nodeJS中实现这个，下面哪一个会给出与运行两个终端相同的结果？

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();

或使用池

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();
});

我的第一个猜测是池将创建单独的连接，并且在同一连接中发送查询与在同一终端中输入查询相同。然而文档说https://github.com/mysqljs/mysql#pooling-connections在介绍部分下

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

我不太确定这意味着什么。

此外，如果我使用连接池，我可以 100% 确定并发运行的查询由不同的 session 处理吗？因此，例如，如果第一个查询中没有释放池，第二个查询是否总是由另一个 session 执行？

Answer 1

我做了一些测试，并意识到连接池达到了预期的结果。

当我仅通过连接执行以下操作时

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();
        })
    });
});

它会导致以下输出

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

这意味着第一个连接打开的事务将被忽略，第二个事务将先完成。但是，当我将连接池用于以下代码时，

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();
            })
        });
    });
});

输出如下

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

这意味着第一个事务正在阻止第二个事务的执行。

所以当文档说

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

这意味着它们是按顺序传递到数据库的，但即使在事务下，它仍然是异步和并行的。但是，连接池会导致多个连接的实例化，并且不同池连接中的事务的行为与每个事务的预期相同。