PERFORMANCE

Deadlocks in Databases: A Guide

intro

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!

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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:

  1. We’ll have two sessions – in one session, we need to run the following queries:
Copy
        
1 START TRANSACTION; 2 UPDATE `demo_table` SET `username` = “Demo” WHERE `id` = 1;
  1. In the second session, we will also run the following queries:
Copy
        
1 START TRANSCATION; 2 UPDATE `demo_table_2` SET `username` = “Demo” WHERE `id` = 1;
  1. Then run the following query on the first table:
Copy
        
1 UPDATE `demo_table` SET username = “Demo” WHERE `id` = 1;
  1. Now run the following query on the second table:
Copy
        
1 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.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

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.

The features of DbVisualizer have been built thoughtfully to solve real-world problems – DbVisualizer has everything from a SQL client letting you work within your own personal workspace to data encryption via SSH, the ability to set a local master password, simple data monitoring, and even a visual query builder as well. Did you ever imagine that the visualization of your tables could look like this?

Visualizing the tables in DbVisualizer
Visualizing the tables in DbVisualizer

The visualization of your databases, on the other hand, could look like this:

Visualizing the table data in DbVisualizer
Visualizing the table data in DbVisualizer

Interesting, isn’t it?

Not only will DbVisualizer let you visualize the structure of your databases and the tables within them, it comes with a whole bunch of additional features such as the one letting you encrypt all of your sensitive data related to the database (database passwords, SSH passphrases, etc.) with a local master password only known to you:

Setting a master password for your data in DbVisualizer
Setting a master password for your data in DbVisualizer

The powerful features of DbVisualizer will even let you control the way your SQL code is formatted and do a bunch of other interesting things:

Figuring out the best SQL formatting to use with DbVisualizer.
Figuring out the best SQL formatting to use with DbVisualizer

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 todayread our blog to stay updated about issues surrounding the database scene, and until next time!

About the author
LukasVileikisPhoto
Lukas Vileikis
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.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

How to work with SQL query optimization

author Antonello Zanini tags PERFORMANCE 10 MINS 2023-11-09
title

Why Are Your Databases Slow with Large Tables? An Overview

author Lukas Vileikis tags PERFORMANCE 9 MINS 2023-04-27
title

A Guide to Multithreading in SQL

author Ochuko Onojakpor tags PERFORMANCE 8 MINS 2023-04-14
title

10x Query Performance with a Database Index

author Lukas Vileikis tags INDEXES PERFORMANCE 6 MINS 2023-04-04
title

SQL Server for Mac: The Ultimate Guide to the Best SQL Server Client

author TheTable tags SQL SERVER 7 min 2024-03-01
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Outer Join in SQL: A Comprehensive Guide

author Ochuko Onojakpor tags JOIN 11 min 2024-02-19
title

SQL DDL: The Definitive Guide on Data Definition Language

author Antonello Zanini tags DDL SQL 7 min 2024-02-15

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗