Deadlocks in Databases: A Guide

Deadlocking refers to a situation where the transaction A cannot proceed due to the transaction B holding something that the transaction A needs. The transaction B cannot proceed either because it needs to wait for the transaction A to complete. Interested in how everything works? Read this blog!**

What Are Deadlocks?

In simple terms, a deadlock is a situation where two or more transactions cannot proceed due to them holding something that the other transaction needs to be completed. Picture a scene like so:

There are three guys – Jack, William and James.
William is waiting for Jack to finish eating so he can drive him home.
James is waiting for William to drive Jack home and come back, so that he can borrow his car.

See the problem? Neither of the guys can proceed to do their own thing because each of them have to wait for each other. Now translate that into the database realm and you will see why that’s a problem to begin with:

We’ll have two sessions – in one session, we need to run the following queries:

START TRANSACTION;
UPDATE `demo_table` SET `username` = “Demo” WHERE `id` = 1;

In the second session, we will also run the following queries:

START TRANSCATION;
UPDATE `demo_table_2` SET `username` = “Demo” WHERE `id` = 1;

Then run the following query on the first table:

UPDATE `demo_table` SET username = “Demo” WHERE `id` = 1;

Now run the following query on the second table:

UPDATE `demo_table_2` SET `username` = “Demo” WHERE `id` = 1;

The result of such actions should be an error that looks something like this:

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

The Problem with Deadlocks

Deadlocks aren’t a very frequent issue of concern to database administrators and is even less of a threat to developers that might not know their way around database management systems. And the problem is even made even worse by the ambiguous error messages given by database management systems. Have a look at the error message above – is it really clear what the database is doing and where did it get stuck? No, it’s not.

Database deadlocks happen because of the so-called Coffman conditions. Coffman conditions are a set of conditions that are absolutely necessary for deadlocks to exist and these are as follows:

Mutual exclusion – at least one transaction must be under a non-shareable mode.
The holding of resources – at least one transaction (process) must be holding at least one resource that is requested by another transaction.
No interruptions – for deadlocks to happen, no transactions should have the ability to be interrupted.
A circular waiting process – and last but not least, each process must wait for a resource to be released from a “lock-in” with another process, which is usually waiting for the first process to release. Thus, a deadlock takes place.

With the deadlocks now defined, we see that the error message isn’t really what it’s supposed to be – to avoid misunderstandings, the error message should look something like this:

ERROR 1213 (40001): Transaction A is holding a resource required by a transaction B to proceed; check the transaction number #XXX.

Such an error message is much more clear, don’t you think?

However, even with such an error message present, we must know what to fix for deadlocks not to be a thing anymore – even the most informational error messages don’t fix the issue: they only define it.

Fixing Deadlocks

Contrary to a popular belief, fixing the issue related to deadlocks is relatively simple – we just need to ensure that the Coffman conditions (defined above) won’t be in place once a transaction is running. If there’s no space for Coffman conditions to exist, there’s no space to breathe for deadlocks.

In order to fix the problem, there are a couple of things we can do:

We can split long-running transactions into a couple of smaller ones. Do we really need a transaction that selects all of the rows from a certain table? Chances are, we don’t. To fix such issues, make sure to employ SELECT column instead of SELECT *, instead of using nested queries, consider whether you can run them one by one, instead of counting data using multiple queries, perhaps switch the storage engine to MyISAM, then use COUNT(*)?

Avoid querying the same data multiple times – can you run one query, then make use of the results in multiple scripts?
Try to examine less data – this advice goes hand-in-hand with splitting long-running transactions. SELECT column will pretty much always be faster than SELECT *.

If you must run the same query multiple times, consider waiting a couple of seconds before trying to run it again.
Consider setting variables to disable the detection of deadlocks altogether – in InnoDB, that can be done by setting the innodb_deadlock_detect variable to OFF.

Fixing Other Issues

Deadlocks are a frequently only an issue for those who work with databases pretty extensively – think database administrators or developers fixing issues specific to database performance and queries. Others may spend more time fixing other issues related to performance, security, availability, or capacity of their database instances.

No matter what kind of a database instance you run – whether it’s SQL Server, PostgreSQL or its flavors (TimescaleDB, etc.), SQLite, MySQL, or others, the problems you will face are of a similar nature, and it’s the job of database clients to provide you with the necessary advice to help you fix those issues.

One of such database clients is DbVisualizer – the client is used by notable companies such as Tesla, Twitter, Facebook, Volkswagen, and even NASA and that’s because it supports pretty much whatever database you can imagine – from MySQL, PostgreSQL, and SQL Server to Elasticsearch, Cassandra and Db2, Azure and even Google BigQuery, everything’s there.

Interesting, isn’t it?

And best of all, DbVisualizer will also provide you with a free version of the tool for you to evaluate its capabilities before committing to anything – grab it now!

Conclusion

Deadlocks in databases are a problem partly because they’re pretty dangerous from a performance point of view, but also partly because theee database management systems that provide us error messages relating to deadlocks don’t really tell us the whole extent of the problem. That’s why we have top-rated SQL clients like DbVisualizer, though – DbVisualizer will let you visualize your queries, write them, and also make sure that your database is in a top-notch condition whatever happens. Give it a try today, read our blog to stay updated about issues surrounding the database scene, and until next time!

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.