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/ito 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.
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
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...
In this story, I shared the types of deadlock scenarios and how to implement the retry strategy using Sequelize ORM.
Thanks for reading.