How to retry transactions in Sequelize

You might be getting errors like "SequelizeDatabaseError: Deadlock found when trying to get lock; try restarting transaction" and wondering how you could restart the transaction as indicated.

(Yeah, you could always catch the error manually, stringify it and look for a substring, but then you would have to check for every possible error you want to retry the query for in every catch clause - and we want our code to be DRY)

This behaviour can be natively enabled by adding a retry object to the list of options provided when initializing Sequelize:

const sequelize = new Sequelize(process.env.REMOTE_DB_NAME, 
    process.env.REMOTE_DB_NAME, 
    process.env.REMOTE_DB_PW, 
    {
    host: process.env.REMOTE_DB_HOST,
    dialect: 'mysql',
    // Here:
    retry: {
        match: [Sequelize.ConnectionError]
    }
})

As you can see, we can pass native Sequelize errors to the match array.

Now you might be thinking "Ok, but the deadlock error is actually a “SequelizeDatabaseError” - so how can we specifically filter that one out?"
Well, the retry option also happens to accept regex parameters!

retry: {
        match: [/Deadlock/i],
        max: 3
    }}

As seen above, we can specify the maximum number of retries to execute (max).

The match array also accepts strings, so here's a full example showing all valid error formats:

retry: {
    match: [
        Sequelize.ConnectionError,
        Sequelize.ConnectionTimedOutError,
        Sequelize.TimeoutError,
        /Deadlock/i,
        'SQLITE_BUSY'],
    max: 3
}

(Note that neither regexes nor Sequelize errors are passed as strings)

You can find the full list of Sequelize errors here:
https://sequelize.org/v5/identifiers.html#errors
(Bonus) And here’s the full list of options you can pass to retry: https://github.com/mickhansen/retry-as-promised/blob/master/README.md

Feel free to hit me up on Twitter if you have any further questions! @Anonyma_Z

Top comments (4)

Luis Fernando Prudencio Cruz • Aug 26 '21

When I use complex transaction and add a rollback in the catch block, I get error: rollback has been called on this transaction(...), you can no longer use it. (The rejected query is attached as the 'sql' property of this error)

brandon flowers • May 31 '21

so far this is the best article I've found while searching for postgres connection errors - much thanks for writing it up!

have you experimented with that max connection limit?

github.com/sequelize/sequelize/iss... <-- older thread from 2014 mentions an "Infinity" option - just curious if dialling it up more would help with connecting?! Or do you think it 3 is still fine for production.

Also, sometimes I run into the "too many connections" error when trying to connect to ElephantSQL from Vercel (serverless) - do you have recommendations on how to config your pool object or this retry object based on a serverless architecture?

Is this retry issue best addressed on the server? It seems that is but would it also make sense to retry from the client as well? The client could wait and then timeout after say 3 seconds, and try again providing feedback to the user.

Basically, I would rather have my users wait (and understand why they are waiting) then receive a connection error.