Connection pool hangs on a deadlock in MySQL

[komanton]

Previously, in our solution, we closed a connection on every SQL statement or SQL error and worked without connection pool. For now, we are trying to migrate to the connection pool. And, we have a couple of thousands of integration tests. After switching to connection pool, our integration tests start hang randomly especially when we worked with almost empty DB. Below, I made small test for reproducing the deadlock situation.

Steps:

1. Create table:

 CREATE TABLE Users2 (
    `id` VARCHAR(255) NOT NULL,
    `username` varchar(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `token`  VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE username_unique (username),
    UNIQUE email_unique (email),
    UNIQUE id_unique(`id`),
    UNIQUE token_unique(`token`),
    FULLTEXT (id,username,email)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Insert one record:

INSERT INTO Users2 (id, username, email, `token`) VALUES ('user.fab347250007470389d3f73d00f200ed', 'camila', '[email protected]','111');

3. Run the test code with npm test (also, add mocha --exit in test command) and read comments:

const mysql = require('mysql2/promise');

const delayAsync = async x => {
    return new Promise(resolve => {
        setTimeout(resolve, x);
    });
};

describe('test deadlock connection pool', function() {
    this.timeout(100 * 1000);

    const pool = mysql.createPool({
        host: 'localhost',
        port: 3307,
        user: 'dev',
        password: 'dev',
        database: 'dev',
        multipleStatements: true,
        connectionLimit: 5,
        maxIdle: 0,
        idleTimeout: 60000,
        enableKeepAlive: true,
    });

    it('test stub', async () => {

        try {
            // To avoid randomness, only for test, we DO NOT release this connection, because the second insert may randomly use 'free' connection and it will not allow to reproduce the deadlock because locks from the first insert will be reused for the second insert. That's why we need a new connection for the second insert.
            const connection = await pool.getConnection();
            const result = await connection.query(`
        START TRANSACTION;
          INSERT INTO Users2 (id, username, email, token) VALUES ('user.fab347250007470389d3f73d00f200e2', 'camila2', '[email protected]', '222');
        COMMIT;
      `);
        } catch (error) {
            console.error('user with camila already exists', error.message);
        }
        console.log('try insert another user with exists email:')

        await delayAsync(20 * 1000);

        // Deadlock is here!
        // run this query to see "WAIT" lock: SELECT * from performance_schema.data_locks;
        await pool.query(`
      START TRANSACTION;
        INSERT INTO Users2 (id, username, email, token) VALUES ('user.fab347250007470389d3f73d00f200e2', 'camila3', '[email protected]', '333');
      COMMIT;
    `);

    });

    this.afterAll(function() {
        console.log('Clean resources after all tests :');

        pool?.end();
    });
});

Actual behavior: test doesn't pass but hang after the second insert. Expected behaviour: test should fail (duplicate primary key?) but NOT hang due to deadlock in DB.

Locks: (SELECT * from performance_schema.data_locks;)

WORKAROUND:

The problem here with some specific behaviour of MySQL in case of transactions and combinations of SQL error, for instance, duplicate entries. For more details about which errors are affect transaction behaviour, you can read here: https://dev.mysql.com/doc/refman/8.0/en/innodb-error-handling.html The main idea, behind this documentation, that MySQL does not release locks automatically for specific errors (list of errors in the link above). So, with help of this idea, we can implement the workaround which will rollback transactions explicitly:

const sql = `
    START TRANSACTION;
      INSERT INTO Users2 (id, username, email, token) VALUES ('user.fab347250007470389d3f73d00f200e2', 'camila2', '[email protected]', '222');
    COMMIT;
    `;
// TODO add also BEGIN https://dev.mysql.com/doc/refman/8.0/en/commit.html
const transactionRegExp = /START TRANSACTION/i;
let connection;
try {
    connection = await pool.getConnection();

    const result = await connection.query(sql);
} catch (error) {
    if (connection && sql.search(transactionRegExp) >= 0) {
        await connection.query('ROLLBACK;');
    }
} finally {
    connection?.release()
}

NOTE 1: I am not sure what should be fixed in this case because we have the workaround. Feel free to close it. But maybe this need to be documented some how.

[steveetm]

This is quite normal imo and not even a deadlock, simply locking issue, caused by the multiple statements, here is why:

Mysql stops executing the query on the first error it encounters, so it will never commit. But you keep your connection open, so locks held. You can confirm this behaviour yourself on cli, just run this:

mysql [...] -e 'START TRANSACTION; SELECT 1; INSERT INTO Users2 (id, username, email, token) VALUES ('user.fab347250007470389d3f73d00f200e2', 'camila2', '[email protected]', '222'); SELECT 2; COMMIT;'

result:

+---+
| 1 |
+---+
| 1 |
+---+
ERROR 1062 (23000) at line 1: Duplicate entry

Or enable debug logging of packets and you can see it yourself it never sends result of commit. As it will never run, so transaction left open. When you catched the error commit, rollback or close the connection explicitly, then the other insert can grab its lock.