A Straightforward Guide for MySQL Locks

In this article, I aim to introduce you to the common and fundamental locks in InnoDB. If you're not familiar, InnoDB is a storage engine for MySQL, and it’s the default one when you create a database. My goal here is to break down these locks in InnoDB using simple analogies and examples. This can help you grasp the basics so you can explore more on your own if you're curious.

Before diving into each type of lock, I want to point out three things. First, I won't cover every single lock in InnoDB. Second, the details of locks involve other database concepts, such as isolation levels and indexes. I’ll intentionally ignore these concepts to keep this article simple. Lastly, I'm not a MySQL or InnoDB expert—just a regular engineer sharing what I've learned over the past few months. If you find any mistakes, please let me know in the comments. I'd really appreciate it!

Throughout the article, I’ll use following table as example

CREATE TABLE `posts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `like_count` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) 

+----+---------+------------+
| id | user_id | like_count |
+----+---------+------------+
|  1 |     100 |          5 |
|  2 |     102 |         10 |
|  3 |     104 |         15 |
+----+---------+------------+

 

Here's a quick guide to the diagram we'll use: T1 and T2 represent two separate transactions, with the posts table shown between them. The arrow represents the timeline, moving from older to newer operations.

 
 

Exclusive vs. Shared Lock

Both exclusive locks and shared locks are fundamental in InnoDB. An exclusive lock occurs when a transaction requests write access, while a shared lock occurs when a transaction requests read access.

Let’s use a hotel analogy to explain what exclusive and shared locks are and their compatibility. Imagine a fancy hotel that costs $10,000 per night, but offers tours of its rooms for just $10. Additionally, there are two types of customers: guests (who stay) and visitors (who tour). The hotel has rules to ensure privacy:

For visitors:

• One room can be visited by many people
  • This prevents people from lining up to visit a room
• A room that has been occupied by guests cannot be visited

For guests:

• One room can only be occupied by one guest (or group)
• A room with visitors cannot be occupied

I hope these policies make sense. Now, let’s relate this to shared and exclusive locks. In this analogy, a room represents a row, visitors represent the shared lock, and guests represent the exclusive lock. Let’s review the policies again and see how they seamlessly translate to shared and exclusive locks.

Shared Lock

• A row can be added multiple shared lock
• A row with an exclusive lock can’t be added a shared lock

Exclusive Lock

• A row can only be added one exclusive lock
• A room with shared lock can’t be added an exclusive lock

When a transaction attempts to acquire either a shared lock or an exclusive lock, it must follow these policies.

 

Let's look at an example in action.

Please ignore the IS and the REC_NOT_GAP. I'll explain what those locks do shortly. For now, just focus on the S and X locks. As you might guess, S represents a shared lock, and X represents an exclusive lock.

1. Transactions T1 and T2 each start their own transaction
2. T1 requests and acquires a shared lock on the row where id=2 by using SELECT … FOR SHARE statement
   • We can think of FOR SHARE as adding a shared lock.
3. T2 uses the same statement to request and acquire a shared lock on the same row
   • T2 is granted the shared lock because they are compatible with each other.
4. T1 then attempts to request an exclusive lock on the same row
   • T1 has to wait until T2 releases its shared lock because a shared lock is not compatible with an exclusive lock.
   • The red error message indicates that transaction T1 is waiting for a lock to be released but has exceeded the allowed timeout period.
   • When two locks are not compatible, the second lock has to wait until the first lock is released.

 
 

Intention Lock

An intention lock is a table-level lock. Most of the time, we don’t need to specify an intention lock because InnoDB will automatically issue it when necessary. According to the MySQL documentation, the main purpose of an intention lock is to indicate that a transaction intends to read or write rows in the future.

Recall from the previous example, there are two IS locks.
An IS lock represents an intention shared lock (with IX for intention exclusive lock). It means the transaction T1 and T2 inform InnoDB that they are going to perform a read operation on this table.

 

Let’s see one more example.

1. Transactions T1 and T2 each start their own transaction.
2. T1 attempts to update the row where id=2, so it requests and acquires the exclusive X lock and the exclusive intention lock IX .
3. T2 uses the same statement to request and acquire a shared lock on a different row, and it also requests and acquires the exclusive X lock and the exclusive intention lock IX

It’s not surprising that two exclusive locks are granted because the two transactions access different rows. However, it might be surprising that two exclusive intention locks are added to the same table. In the previous paragraph, we mentioned that exclusive locks are not compatible with each other. Why is it different here?

It's important to note that the main purpose of an intention lock is not to lock the whole table. Instead, it’s to indicate that a transaction intends to access the rows in this table.

 

Let's use real-life scenarios to illustrate the purpose and importance of intention locks. The following three scenarios show how hotel staff respond when customers want to stay or visit. Most customers want to stay on the 10th floor because the views from there are stunning.

Scenario A:
In this scenario, the staff does nothing specific regarding the floor level when a customer comes in to stay or visit.

This example can be inefficient as the staff has to check each room when Customer B asks for rooms on the entire floor.

 

Scenario B:
In this scenario, the staff treats every customer as if they were the president, blocking the entire floor whenever a customer stays or visits.

This significantly decreases the utilization of rooms when the staff blocks the whole floor for Customer A.

 

Scenario C:
In this scenario, the hotel does not block the entire floor when a customer stays or visits. Instead, it marks the floor to indicate that someone is occupying it.

This approach is the most efficient so far. The staff doesn’t need to check each room, and visitors are still allowed to visit.

In the above analogies, floors represent tables, rooms represent rows, staff represent InnoDB, and customers represent transactions.

• In Scenario A, because there’s no table-level lock, InnoDB has to check each row to see if there’s a lock on it when a transaction asks for an exclusive table-level lock, which can be less efficient.

• In Scenario B, if InnoDB locks the whole table because a transaction issues a lock on some rows, other transactions have to wait until this transaction releases the lock, significantly decreasing concurrency.

• In Scenario C, InnoDB puts a special mark on the table to indicate that there’s a lock on a row, which is the purpose of intention locks. This allows further transactions to access the same table and respond to the table-level exclusive lock without checking each row.

From these scenarios, we can see the importance of intention locks. They improve efficiency and allow concurrent access.

 
 

Record Lock

From the MySQL documentation, a record lock is a lock on an index record. Without getting into the details of what an index record means, let's simplify: a record lock is essentially a row-level lock locks a row.
(This isn't entirely accurate in the details of MySQL, but it helps us understand record locks from a high-level perspective.)

In most cases, a record lock appears when we read or update a row using a primary key or unique index. This is because both of these ensure access to only one row (or none) without touching other rows.

Let’s reveal the remaining lock we skip in the first example.
Both T1 and T2 use the primary key to update a row. Therefore, REC_NOT_GAP represents a record lock. (S, REC_NOT_GAP) means shared record lock while (X, REC_NOT_GAP) means exclusive record lock.

 
 

Gap Lock

According to the MySQL documentation, a gap lock is a lock on a gap between index records or on the gap before the first or after the last index record. To oversimplify, a gap lock is a lock on a range of rows. When a gap lock is added to a range of rows, no other transactions are allowed to insert rows within that range. One advantage of gap locks is that they help prevent phantom reads.

To understand gap locks better, let's look at the structure of rows from a different perspective. Often, we view the table structure as row by row. However, conceptually, we can think of gaps between each row, including before the first row and after the last row. We can group each gap and the records as follows:

[-infinity, (100, 1)] -> A
[(100, 1), (102, 2)]  -> B
[(102, 2), (104, 3)]  -> C
[(104, 3), infinity]  -> D

In this representation, [x, y] indicates each group, and (secondary index, primary index) indicates the index value. In our example, it’s (user_id, id). A gap lock secures the range from x up to, but not including y. For instance, when innoDB indicates that there’s a gap lock on group A, it means other transactions are not allowed to insert the user_id value before 100 (i.e., from -infinity to 99).

Keep this representation in mind; it will be helpful for understanding the examples later.

 

Gap locks typically occur when we use a non-unique secondary index to query rows. Let’s walk through an example to see gap locks in action.
(To better illustrate gap locks, the following examples sort the rows by user_id)

[-infinity, (100, 1)] -> A 🔒(X, GAP)
[(100, 1), (102, 2)]  -> B
[(102, 2), (104, 3)]  -> C
[(104, 3), infinity]  -> D

When T1 updates the row where user_id = 1, it issues a gap lock on group A. (X, GAP) represents an exclusive gap lock, and (S, GAP) represents a shared gap lock. The three insert operations by T2 show that the gap lock indeed locks the range from -infinity to 99. (Note that gap locks do not lock the row itself.)

 
As mentioned earlier, one advantage of gap locks is preventing phantom reads. Let’s walk through one example, but without a gap lock this time.

Because there’s no gap lock when T1 first accesses the rows, T2 is allowed to insert a row into the table. Later, T1 uses the same query but gets a different result set. This situation is called a phantom read: when a transaction executes the same query twice but gets different sets of rows.

 

Before moving on to the next type of lock, it’s important to note that gap locks are compatible with each other, even though they are exclusive locks. The purpose of a gap lock is not to prevent access to the gap, but to prevent other transactions from inserting into the gap.

 
 

Next-Key Lock

A next-key lock is simply a combination of a record lock and a gap lock. When a lock shows X or S, it means they are exclusive and shared next-key locks, respectively. Again, these are just combinations of exclusive (shared) record locks and exclusive (shared) gap locks.

 

Let’s see how next-key lock perform in different scenarios, as next-key locks are quite common in InnoDB.

[-infinity, (100, 1)] -> A 🔒(X)
[(100, 1), (102, 2)]  -> B 🔒(X,GAP)
[(102, 2), (104, 3)]  -> C
[(104, 3), infinity]  -> D

The first row shows the next-key lock X on 100, which is our group A. The second row shows the gap lock X,GAP on 102, which is our group B. It’s important to note that a next-key lock does lock the target row itself, while a gap lock does not.
If we combine the next-key lock and gap lock in this example, all the rows before the user_id value of 102 are locked. That’s why T2 can only insert the value 102.

 

[-infinity, (100, 1)] -> A 
[(100, 1), (102, 2)]  -> B 🔒(X)
[(102, 2), (104, 3)]  -> C 🔒(X,GAP)
[(104, 3), infinity]  -> D

The update statement by T1 acquires three locks: an intention lock IX on the table, a next-key lock X on group B, and a gap lock X,GAP on group C. The next-key lock secures the range from the user_id value of 100 to 102 (including 102), and the gap lock secures the range from 102 to 103 (excluding 104). To sum up, the range from 100 to 103 is locked by T1. Therefore, T2 is only allowed to insert values where user_id is equal to 99 and 104 in this example.

 

[-infinity, (100, 1)] -> A 
[(100, 1), (102, 2)]  -> B 
[(102, 2), (104, 3)]  -> C 🔒(X)
[(104, 3), infinity]  -> D 🔒(X,GAP)

In this example, all the rows after the user_id of 102 are locked by T1 because there’s a next-key lock on group C and a gap lock on group D. So, only the first insert statement from T2 is successful.

 
 

Summary

In this article, we explore different types of locks in InnoDB. We look at their simple definitions, how they lock data, and provide plenty of examples in action. I hope you gain a basic understanding after reading this article.

• Exclusive vs. Shared Lock
  • A shared lock allows multiple transactions to read a resource.
  • An exclusive lock allows only one transaction to modify a resource.
  • Use the hotel analogy to remember their compatibility.
• Intention Lock
  • IX or IS
  • A table-level lock.
  • Indicates a transaction intends to read or write rows in the future.
  • Improves efficiency and allows concurrent access.
  • Intention locks are compatible with each other.
• Record Lock
  • (X,REC_NOT_GAP) or (S,REC_NOT_GAP)
  • A row-level lock that locks a specific row.
• Gap Lock
  • (X,GAP) or (S,GAP)
  • A lock on a range of rows, preventing other transactions from inserting into the gap.
  • Use groupings to understand how gap locks work.
  • Helps prevent phantom reads.
  • Gap locks are compatible with each other.
• Next-Key Lock
  • X or S
  • A combination of record lock and gap lock.

 
 

Reference

• MySQL documentation: InnoDB Locking
• MySQL Blog Post
• A Comprehensive (and Animated) Guide to InnoDB Locking
• 深入了解mysql--gap locks,Next-Key Locks
• Efficient MySQL Performance
• MySQL Concurrency