How to Handle Database Deadlock in Sequelize

Welcome to another new story. In this story, I will be sharing my experience in handling the SQL deadlock scenario. The story has 4 parts:

• Types of deadlock scenarios you might encounter
• Ways to resolve the deadlock
• Example code
• Some helpful resources that helped me in resolving the problem

Without further ado, let's get started.

Types of Deadlock Scenarios You Might Encounter

Firstly, it was pretty common to encounter database (DB) deadlocks in huge and complex software systems, especially if your application had frequent updates to the same DB record.

I distinguished the deadlock scenario into 2 different types based on frequency:

• Deadlock error which occurred every time at the same line of code
• Deadlock error which occurred occasionally at the same line of code

Ways to Resolve the Deadlock

There are 2 ways to resolve the deadlock.

• Identify where the deadlock happened and fix it. This is suitable for the 1st scenario as you have no other choice since the deadlock happened every single time. Normally, you could fix this at the code level.
• Retry strategy when deadlock happened. This is suitable for the 2nd scenario where you have little clue as to why it happened and have an inconsistent occurrence pattern. Last but not least, this scenario also takes a substantial amount of time and effort to debug. So implementing the retry strategy is the best workaround.

In addition, according to the MySQL documentation,

Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock. --- MySQL Docs

In layman's terms, if a deadlock happens, simply retry again. And in the next section, I will be sharing how to implement retry in Sequelize when a deadlock happens.

Example Code in Sequelize

Now, I will be setting up the retry strategy using Sequelize ORM. Actually, it is pretty easy to set up. You define the retry strategy upon initializing the Sequelize library.

Below is the complete code. The main focus is the retry object. The retry object is the configuration of the retry strategy.

• match : Consists of an array of errors. If matched, the DB transaction will retry again until the maximum attempt is reached. I am using regex /Deadlock/i to ensure the transaction only retries when a deadlock happens.
• max : The maximum number of retry attempts.
• backOffBase : Initial backoff duration.
• backOffExponent : Exponent to increase in backoff for each retry.

Additional Helpful Resources

Exponential Backoff Calculator

You can get more insight and the intervals between each insight using the Exponential Backoff Calculator below.

Exponential Backoff Calculator

An online exponential backoff calculator. Input interval (secs), max retries, and exponential rate.

retry-as-promised library

This is the library Sequelize used to implement their retry strategy. Refer to retry-as-promised for all the configurations you can set up in the retry object.

Last but not least, there is also a great post about retry transactions in Sequelize. Refer to the link below.

You might be getting errors like "SequelizeDatabaseError: Deadlock found when trying to get lock; try restarting...

Conclusion

In this story, I shared the types of deadlock scenarios and how to implement the retry strategy using Sequelize ORM.

Thanks for reading.