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.
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:
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.
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:
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
.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.
A hacking group related to North Korea is exploiting a zero-day in the Chromium browser…
What are crypto bubbles, how do they form, and should you worry about them? Learn…
Is the crypto-engine.pro blog legit and should you trust this resource? Learn here!
Reside in Brazil and found that your Twitter account suspended? There’s a good reason for…
This blog covers the recent Black Hat USA 2024 (DEFCON 2024) conference and digs into…
The CEO of Telegram and Telegram Web, Pavel Durov, has been released from custody and…