Understand the basics of locks and deadlocks in MySQL (Part I)

Let’s not get scared of MySQL deadlock anymore

Deadlocks are difficult to be totally avoided in transactional databases like InnoDB in MySQL. When you first see a deadlock in your application, you may get scared by its error message and get overwhelmed by the logs shown by SHOW ENGINE INNODB STATUS. In this series of posts, the deadlocks in MySQL will be demystified, and you will learn to read the deadlock message and also solve deadlock problems in your application.

Photo by marcos mayer (padlocks in steel cable) from Unsplash.

What is deadlock?

A deadlock is a situation when two or more transactions mutually hold and request a lock that the other needs. As a result, a cycle of dependencies is created and the transactions cannot proceed. By default, InnoDB automatically detects deadlocks and rolls back one transaction (the victim) to break the cycle. Normally, the transaction that infects a smaller number of rows will be picked.

What is a transaction?

In MySQL, a transaction is a group of statements that are performed as an atomic group, which means either all the statements run successfully, or none of them do.

Besides the atomicity property mentioned above, a transaction also has consistency, isolation, and durability, which as a whole are called the ACID property.

By default, the global property autocommit is ON in MySQL, which means every query is wrapped as an independent transaction and is committed automatically. We can use the BEGIN or START TRANSACTION queries to manually start a transaction and manually commit or roll back the transaction in the end. Alternatively, we can set autocommit to 0 so a transaction will not be committed automatically. In this post, we will change autocommit to 0 in order to observe the locks added by the queries more easily.

Before we can read the deadlock information, we must have a basic understanding of the different types of locks in MySQL. The concept of locks can be very elusive for beginners and can be very difficult to understand with just text descriptions. In this post, all the common types of locks in MySQL will be demonstrated with simple examples so you can easily understand and remember them.