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.

Install MySQL and DBeaver.

For learning purposes in this post, we should have MySQL installed on our computers. I recommend using a Docker container so it won’t conflict with the existing versions of MySQL on your computer. We will use MySQL 8 in this post because it has a very easy way to watch the locks in the database.

We use a named volume for the MySQL container, so when the container is restarted, our data can be persisted.

We use 3307 rather than 3306 for the port of the MySQL container to avoid potential port conflicts.

Note: If you want to follow the examples introduced in this post, you need to use the version of MySQL (8.0.26) at the time of writing because the lock mechanism has been updated for newer versions and the behavior will be slightly different. However, the basic concepts introduced in this post will be generally applicable.

You can then access the MySQL server in the Docker container on the command line directly:

$ mysql -h 127.0.0.1 -uroot -proot -P 3307

Alternatively, you can use a graphical database manager to access it. It is recommended to use DBeaver in this post. DBeaver is a universe database manager and can be used to manage different types of databases. It has a very user-friendly interface and powerful hints for writing queries.

You can download and install DBeaver from the official website. In order to access your MySQL server in DBeaver, you need to create a connection, which should be fairly straightforward with the user interface. If you can't install and set up DBeaver or you prefer not to use it, you can use MySQL workbench or just use the command line. It won’t make any difference for the queries we are going to run.

Learn different types of locks in MySQL.

Personally, I think the lock types in the official document of MySQL are not well defined. Firstly, different lock types are interleaved which makes them difficult to understand. Secondly, different isolation levels have different lock types. Let’s explore them one by one and hopefully, we can get them straightened out.

Isolation level — Repeatable Read (RR)

Isolation is a key component in the ACID property for transactions. Basically, it means that a transaction should be isolated from others and thus won’t be impacted by others. There are four isolation levels for the InnoDB engine of MySQL, namely, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level is REPEATABLE READ and will be kept as the isolation level for the examples in this post. There can be quite a lot to talk about for each isolation level. For now, you don’t need to understand all of them. You just need to know about the REPEATABLE READ isolation level. You can the isolation level with this query:

-- MySQL 5.x
SELECT @@tx_isolation;
-- REPEATABLE-READ

-- MySQL 8.x
SELECT @@transaction_isolation;
-- REPEATABLE-READ

With the REPEATABLE READ isolation level. All the SELECT queries in the same transaction will read from the same snapshot established by the first SELECT query. As all the SELECT queries read from the same snapshot, they will have the same result within the same transaction, that is what “repeatable read” means. With the REPEATABLE READ isolation level, phantom rows can be avoided by gap locks, which will be introduced later. It should be noted that simple reading from snapshots does not add any locks to the tables. We can, however, add locks to SELECT queries with the SELECT … FOR SHARE/UPDATE syntax, as will also be introduced later.

Lock Granularity — Table Lock vs Row Lock

Table-level locking

InnoDB supports both table-level locks and row-level locks. With InnoDB, we normally don’t lock a table as a whole. However, if needed, it can be done with the LOCK TABLE … READ/WRITE query to lock a table in READ or WRITE mode explicitly.

For demonstration purposes, let’s create a simple table that will be used for the queries in this post.

Before we demonstrate how to lock tables, let’s set autocommit to 0 so we can check the tables locked in table performance_schema.data_locks.

SET autocommit = 0;

Now let’s lock the table in READ mode. Locking a table in READ mode will add a shared (S) lock to the table. Multiple transactions can have an S lock on the same table. The S lock allows the transactions that hold the lock to read the table.

LOCK TABLE student_scores READ;

We can check which tables are locked by two queries:

On the other hand, a table can also be locked in the WRITE mode. Locking a table in WRITE mode will add an exclusive (X) lock to the table. As the name suggests, only one transaction can have an X lock on a table. The X lock allows the transaction that holds the lock to update or delete records in the table. If we now lock the table in WRITE mode, we can see the X lock in performance_schema.data_locks.

It is recommended to use table performance_schema.data_locks to check the locks in the databases as it gives more information about the locks. However, this table is not available in MySQL 5.x. As an alternative, you can set the global variable innodb_status_output_locks to 1 and check the lock information in SHOW ENGINE INNODB STATUS.

SET GLOBAL innodb_status_output_locks=1;
SHOW ENGINE INNODB STATUS;

You can see TABLE LOCK table `study`.`student_scores` trx id 2497 lock mode X in the logs.

Row-level locking

It’s uncommon to lock a table as a whole with the InnoDB engine. Most of the time, we only lock the records we need to read or update/delete. Similar to table-level locking, there are also shared (S) and exclusive (X) locks for row-level locking. The meaning of the S and X locks are the same as with table-level locking, just that only some rows are locked, not the whole table.

To lock one or more rows, we can use the SELECT … FOR SHARE/UPDATE query. In MySQL 5.x, the FOR SHARE option is not available, and we need to use the traditional LOCK IN SHARE MODE option.

Let’s first lock a single record in shared mode:

Interestingly, we see two locks. The second one is the S lock for the record whose id is 10. It should be noted that the row or record lock is always added to the corresponding primary index. If a table does not have a primary index, an implicit clustered index will be created automatically. Since id is the primary key for table student_scores, the S lock is added to the primary index with an id equaling 10. REC_NOT_GAP means the S lock is a record lock but not a gap lock. We will introduce the gap lock soon.

The first lock IS is called intention lock, here it is an intention S lock. An IS lock is a table-level lock. It is used to solve the conflicts between table-level and row-level locks. An IS lock means that the transaction intends to add an S lock to one or more records of a table. Similarly, an IX lock means that the current transaction intends to add an X lock to one or more records of a table. For efficiency, table-level S or X locks will be compared with IS or IX locks, rather than with row-level S or X locks.

Before a transaction can acquire row-level S or X locks on some records, it must first acquire an IS or IX intention lock on the table, that’s why we saw two locks for the query above.

For completeness, let’s lock the record in WRITE mode and see what locks are added. In order only to see the X and IX locks, we need to roll back the previous S and IS locks to remove existing locks in the current transaction:

As expected, we see the table-level IX lock and row-level X lock.

Gap locks and Next-Key locks

We need to introduce gap lock and next-key lock at the same time because they are very closely related. As the name indicates, a gap lock locks the gap between index records. Especially in MySQL, there is an infimum record that is smaller than any index records of the table and a supremum record that is greater than any index records of the table. Therefore, the gap lock can also be added before the first index record and after the last index record.

Let’s add a gap lock to our table. As before, let’s use ROLLBACK first to release any locks added in the current transaction:

This result is more complex than you think because it includes a next-key lock as well, which will be introduced soon.

First, as always, before a row-level S or X lock is acquired, a table-level IS or IX lock must first be acquired.

The third lock is an exclusive (X) gap lock, which locks the gap before record 20. It should be noted that record 20 is not locked because we use id < 20 in the query. If you change it to id <= 20, you will see the lock mode changes to X, which is a next-key lock.

The second lock is the so-called “next-key lock”. In MySQL, the next-key lock is a combination of a record lock and a gap lock on the gap before the index record. The name “next-key lock” can be very counterintuitive. If you cannot figure out why it’s called this way, you need to remember it by heart. The next-key lock is the default lock when you apply locks to a range of records and is indicated by a single S or X in the LOCK_MODE field. If you have enabled logging of locks with the innodb_status_output_locks global variable as mentioned above, you can find the lock information by SHOW ENGINE INNODB STATUS, where you can see the lock type is lock_mode X. As a comparison, for the gap lock above, the lock type in the log is lock_mode X locks gap before rec. Take some time to digest this part, as it is very important for understanding the logs of deadlock. You can change the range in the query above and observe the different locks added to the table.

Let’s now add locks using a secondary index (non-primary index). In this case, the locks can be a little more complex:

Let’s analyze the locks added to the table:

An Intention X lock (IX) was added to the table.
Next-key locks (record + gap locks) for age = 14, age =15 and supremum pseudo-record. The next-key lock is also added to the supremum record because we use a filter age > 13 here, thus with this lock, we cannot insert new records with an age greater than the maximum age, which is 15. It should be noted that in the LOCK_DATA field, for each age, the primary index record is attached as well. This is because the lock added to a secondary index will finally be added to the primary index.
Record locks are added to primary indexes id = 30 and id = 50. These two records are locked because they meet the filtering condition. These two records cannot be updated or deleted by other transactions. However, they can still be read from snapshots without the FOR SHARE/UPDATE conditions because reading from snapshots does not add any locks to the records.

Insert intention locks

Don’t be fooled by the name, insert intention locks are not intention locks but are a special type of gap lock. Insert intentions locks added to a gap indicates that multiple transactions can insert into the same gap as long as they are not inserted to the same index position. Let’s see this with an example.

In the current transaction, run the following queries:

Then open a new transaction in a new console. In DBeaver, you can just open a New SQL Script, then run the following queries:

This new transaction will block because the gap for id < 20 is locked by the first transaction. Since the second transaction is blocked, we need to check the locks in the first transaction:

It shows that the second transaction is waiting for the insert intention gap X lock before the primary index record with id equaling 20. If you have enabled the logging of locks with the innodb_status_output_locks global variable, you can find in SHOW ENGINE INNODB STATUS that the lock type is lock_mode X locks gap before rec insert intention waiting.

To prove that insert intention gap locks do not block each other if two transactions insert into two different positions in the gap. You can run the following INSERT queries in two different transactions and observe the locks added. If you run them in existing transactions, remember to run ROLLBACK first to remove existing locks.

We don’t see the insert intention locks because they have been granted and the insertions have been completed in both transactions. However, as both transactions have not been committed yet, the table-level intention locks are still not released so other transactions cannot lock the whole table in S or X mode.

The common types of locks in MySQL have been introduced by simple examples. Now we can read the deadlock information of SHOW ENGINE INNODB STATUS can start our journey to analyze and solve deadlocks in the next post. For now, you may want to take some more time to digest the locks introduced in this post and run the queries by yourself to get stronger feelings about them.