sequelize: Deadlock on multiple transactions

I believe I have a workaround using p-queue.

const Sequelize = require('sequelize');
const {default: PQueue} = require('p-queue');
const sequelize = new Sequelize('mysql://user:password@dburl:3306/test')

const sequelize.queue = new PQueue({concurrency: (sequelize.connectionManager.pool.maxSize -1)});
const inTransaction = fn => sequelize.queue.add(
  () => sequelize.transaction((transaction) => fn(transaction)),
);

class TestUser extends Sequelize.Model {}
TestUser.init({
  username: Sequelize.STRING,
  birthday: Sequelize.DATE
}, { sequelize, modelName: 'testuser' })

for (var i = 0; i < 5; i++) {
    inTransaction(t => {
        return TestUser.findOne({
            where: { username: 'janedoe' }
        }, { transaction: t })
        .then(user => {
            return TestUser.update(
                { birthday: new Date(1980, 1, 8) },
                {
                    where: { username: 'janedoe' }
                },
                { transaction: t }
            )
        })
    })
    .then(() => console.log('done'))
    .catch(err => console.log(err))
}

Maybe this could be worked into Sequelize.transaction() so that it works out of the box.

Thanks for your reply, @papb. I’m glad to hear that you consider this what should be handled by Sequelize and looking forward to the day I can send a bunch of requests without worry about queueing.

Hi all, i’ve been having the same trouble myself but found my answer here:

https://javascript.plainenglish.io/how-to-handle-database-deadlock-in-sequelize-6bee46e9ed99

Tek Loon has amended his connection to include a retry if there’s a deadlock, which sorted out my issue, here’s the snippet:

const sequelize = require('sequelize);
const sequelize = new Sequelize(
  'DATABASE_NAME',
  'DATABASE_USER',
  'DATABASE_PASSWORD',
  {
    host: 'DB_HOST',
    port: 3306,
    dialect: 'mysql',
    // The retry config if Deadlock Happened
    retry: {
      match: [/Deadlock/i],
      max: 3, // Maximum rety 3 times
      backoffBase: 1000, // Initial backoff duration in ms. Default: 100,
      backoffExponent: 1.5, // Exponent to increase backoff each try. Default: 1.1
    },
  },
);

The above uses mysql but also worked for my mssql DB. Hope this helps!

Leaving this comment in case anyone has a similar problem. Until now I was using the following code template:

let transaction;
try {
    transaction = await sequelize.transaction();
    // const object = await Model.update({...}, { transaction })
    // const object2 = await Model2.update({...}, { transaction })
    await transaction.commit();
} catch (err) {
    console.log(err);
    if (transaction) {
        await transaction.rollback();
    }
}

but I have found that only pool.max transactions can run simultaneously, and the (pool.max + 1) transaction will cause a deadlock which completely blocks new transactions.

The way I have solved this problem is using this template instead (instead of async-await you can you Promises):

sequelize.transaction(async transaction => {
    // const object = await Model.update({...}, { transaction })
    // const object2 = await Model2.update({...}, { transaction })

})
    .then(() => {
        // todo
    })
    .catch(err => console.log(err))

Can you clarify this? Did you expect Sequelize to automatically detect that the number of transactions reached the limit and wait before starting the transaction?

@mycoboco The following code does not deadlock.

const Sequelize = require('sequelize');
const sequelize = new Sequelize('mysql://user:password@dburl:3306/test')

class TestUser extends Sequelize.Model {}
TestUser.init({
  username: Sequelize.STRING,
  birthday: Sequelize.DATE
}, { sequelize, modelName: 'testuser' })

for (var i = 0; i < 5; i++) {
    sequelize.transaction(t => {
        return TestUser.findOne({
            where: { username: 'janedoe' },
            transaction: t 
        });
    })
    .then(() => console.log('done'))
    .catch(err => console.log(err))
}

And simplified minimal case solutions:

const sequelize = new Sequelize(database, user, password, { pool: { max: 1 } } );

sequelize.transaction(t => {
    return sequelize.query("SELECT 1+1 AS result")
});

Will deadlock

const sequelize = new Sequelize(database, user, password, { pool: { max: 1 } } );

sequelize.transaction(t => {
    return sequelize.query("SELECT 1+1 AS result", { transaction : t })
});

will not

Please verify on your end as well. I recommend using CLS to make transactions sticky. Cheers.

So to speak, yes, because Sequelize has enough information and is the right place to do that, I think. I don’t think Sequelize should require every server implementation using it to have a global queue for handling requests working with transactions.

If I’m the first or the only to expect this, which would make me surprised and perplexed, you can consider this a feature request. What I’d like to know is whether I should handle such requests by myself or wait for Sequelize to do for me.