MySQL Deadlocks 101

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

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

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

What is a Deadlock?

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.

Detecting Deadlocks

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.

Deadlock Examples

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:

START TRANSACTION;
UPDATE `table_1` SET `id` = ID WHERE `id` = 1;

In the second session we will also run similar queries:

START TRANSACTION;
UPDATE `table_2` SET `id` = ID WHERE `id` = 1;

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

UPDATE `table_1` SET `id` = ID WHERE `id` = 1;

And the following query on the second table:

UPDATE `table_2` SET `id` = ID WHERE `id` = 1;

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

For another deadlock example, see the InnoDB documentation.

Are Deadlocks Dangerous?

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.

Dealing 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):

1. Mutual exclusion: at least one resource must be held in a non-shareable mode;
2. Hold-and-wait resource holding: a process is holding at least one resource and requesting additional resources that are held by other processes;
3. No preemption (temporary interruptions): a resource can be released only voluntarily by the process holding it;
4. Circular wait: each process must wait for a resource which is being held by another process, which is waiting for the first process to release the resource.

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

• Split long transactions into smaller ones. By doing so we might allow locks to be released sooner;
• Have a look at transactions dealing with the same data – maybe we can avoid querying the same things at the same time?
• Optimize our queries to examine less data – well optimized queries might complete quicker and in turn, not cause a deadlock;
• Take a pause before retrying the transaction – transactions involved in the deadlock might finish while we wait;
• Disable deadlocks altogether – MySQL locks and deadlocks are mechanisms helping us to keep our data consistent and they should not be bypassed unless we want to compromise our data, though in some scenarios disabling them might make sense since doing so might speed our queries up (some threads waiting for the same lock might be avoided) – MySQL has an option for them to be turned off: deadlock detection can be disabled by setting the innodb_deadlock_detect variable to OFF. Deadlocks can also be disabled via the command line using the –innodb-deadlock-detect command – accepted values are ON and OFF.

Summary

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.