DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 966,904 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Anonyma
Anonyma

Posted on

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)

Collapse
 
iscfernando profile image
Luis Fernando Prudencio Cruz

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)

Collapse
 
headwinds profile image
brandon flowers

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.

Collapse
 
kamo profile image
KAIDI

Maybe I will need this in the future !

Collapse
 
buu97 profile image
RATEFINANAHARY Toky Abraham

Seems to work for individual queries and not for full transaction though

🌚 Friends don't let friends browse without dark mode.

Sorry, it's true.