The Basics of MySQL Deadlocks

Sometimes when dealing with MySQL you might come across this slightly frustrating error:

This error message covers deadlocks — this is what we will try to explore in this post.

A deadlock occurs when different transactions are unable to proceed because each of them holds a lock that the other needs. As a result, none of the transactions succeed. This picture illustrates a deadlock:

It’s worth noting that a deadlock should not be confused with a lock — a lock occurs when a resource is being accessed by multiple processes at the same time, a deadlock on the other hand occurs when two or more transactions are waiting for one another to finish.

When using the InnoDB storage engine, deadlock detection is enabled by default — when a deadlock is detected, the engine automatically rolls back a transaction to break the deadlock. Exactly how many transactions will be rolled back is hard to tell because the engine picks transactions to be rolled back by determining how many rows are affected by the INSERT, UPDATE or DELETE operations.

In order to simulate a deadlock, we will use two tables with two open sessions to the same database.

In one session we will run the following queries:

In the second session we will also run similar queries:

Then we will run the following statements simultaneously. We will run the following query on the first table:

And the following query on the second table:

When we run the queries in MySQL we should get the following error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Some people might think that deadlocks are dangerous due to the fact that deadlocks can have a negative impact on an application’s performance which can lead to user complaints: users and MySQL DBAs might blame developers for the problem, developers might blame DBAs.

But deadlocks should not be seen as dangerous — in fact, even the MySQL documentation says that deadlocks are not dangerous. When a deadlock is encountered, a transaction could simply be restarted — restarting a transaction is one of the ways that helps deal with deadlocks in MySQL.

Avoiding MySQL deadlocks isn’t just a job of the MySQL DBAs, developers might need to be involved in the process too. In general, to avoid deadlocks in MySQL we need to avoid the occurence of the Coffman’s conditions (deadlocks occur when all of those conditions are present):

In other words, we have a few options. We can:

As previously stated, both locks and deadlocks are mechanisms to keep data safe and turning off the mechanisms is only useful in very rare scenarios. Instead, deadlocks should be seen as mechanisms that help us to make our applications more effective. To prevent deadlocks developers should make sure to avoid the Coffman conditions — if at least one condition is avoided, deadlocks should not be an issue.

Related posts:

I participated in CUNY's Entrepreneurial Journalism Creators Program for 100 days. I learned a lot about media, entrepreneurship and about myself. here's a little journey through my journey.

The Orleans is a hotel and casino located on West Tropicana Avenue near the Las Vegas Strip in Paradise, Nevada. it opened in 1996 and is currently owned by Boyd Gaming. The hotel is home of the…

CSS transitions can be a little tricky in React if you need to animate a component when it leaves the DOM. I had just that problem recently with some cards that I wanted to animate when a user…