This article is mainly to take you to quickly understand InnoDB locking related knowledge

Why do we need locks

First of all, why lock? I don’t think I need to say more, just imagine the next scene and you’ll GET it.

When you go to the bathroom in a store, what action do you always do? Lock the door when you leave. If you leave the door unlocked and go to the toilet, the door will be opened with a snap, which may, may, may seem a little inappropriate.

The same goes for data. In concurrent scenarios, not locking the data directly breaks the consistency of the data, and even more so if your business involves money.

Lock the door meme

The classification of the lock

In InnoDB, what locks are there? In fact, you should already know a lot. For example, when asked the difference between MyISAM and InnoDB storage engine, you will say that MyIASM only has table locks, but InnoDB supports both row and table locks. You may also be asked what is the difference between an optimistic lock and a pessimistic lock.

There are a lot of concepts and terms for locks that can be difficult to understand if you haven’t developed a complete world view of locks. Let’s break them down into several categories.

According to the granularity of the lock

According to the granularity of lock, it can be divided into:

  • Table locks
  • Row locks

We will not discuss page lock here, page lock is only a concept in BDB storage engine, we will mainly discuss InnoDB storage engine.

Follow the idea of a lock

According to the idea of locking, it can be divided into:

  • Pessimistic locking
  • Optimistic locking

Pessimism and optimism means the same thing as what you might think of as a noun. Optimistic locking assumes that there is a high probability that there will be no conflict and locks are only used when necessary. Pessimistic locks assume that there is a high probability of conflict, so the lock operation is performed whether or not it is necessary.

In terms of compatibility

According to compatibility, locks can be divided into:

  • A Shared lock
  • Exclusive lock

A resource with a shared lock can be shared with others, whereas if an exclusive lock is attached, others cannot do anything without the lock.

Follow the implementation of the lock

The implementation here is InnoDB specific lock types, respectively are:

  • Intention Locks
  • Record Locks are used to lock records.
  • Gap Locks
  • Next-key Locks are the Locks that lock the Next Key.
  • Insert Intention Locks
  • Auto-Inc Locks

Even with locks divided according to this classification, seeing so many names for locks can still be a bit confusing. For example, I SELECT… What lock is added FOR UPDATE?

We should look through the phenomenon to see the essence, what is the essence? The essence is what the lock is applied to, and this is easy to answer:

  • It’s added to the table
  • Add to the row

And what is the nature of the lock on the added row? Essentially, the lock is placed on the index.

Intent locks

Locks of different granularity, row and table, are supported in InnoDB. For example, the lock tables command holds an exclusive lock on the corresponding table. InnoDB designs intentional locks to make a variety of different granularity locks more practical.

An intentional lock is a table-level lock that indicates what type of lock will be used in the next transaction. There are two types:

  • A shared intention lock (IS) indicates that the transaction intends to attach a shared lock to the record in the table
  • An exclusive intention lock (IX) is an exclusive lock

For example, select… For share means to add a shared intention lock, while SELECT… For UPDATE is the exclusive intention lock that is added. The rules are as follows:

  • If a transaction wants to acquire a shared lock for a row in a table, it must first acquire the shared intention lock for that table, or it must acquire an exclusive intention lock for that table.
  • Similarly, if it wants to acquire an exclusive lock, it must first acquire an exclusive intention lock

The following figure shows the mutually exclusive and compatible combinations of these locks

As shown in the table above, if the transaction is compatible, the lock will be acquired by the corresponding transaction, but if it is not compatible, the lock will not be acquired until the incompatible lock is released.

You might have a problem here, since intentionallocks block nothing but LOCK TBALES. So what do I need it for?

Again, let’s say transaction A acquires the shared lock on row id = 100 from student. Then transaction B needs to apply for the exclusive lock on student. The two locks are clearly in conflict, and on the same row.

So how does InnoDB need to know that A has acquired the lock? Go through the whole B plus tree? No, the answer is the intention lock. When transaction B asks for an exclusive lock on A table, InnoDB will find that transaction A has acquired the intended shared lock on that table, indicating that records in the student table have been locked by the shared lock. That’s when it gets blocked.

Also, intentional locks do not block any operations other than those such as Lock Tables. In other words, intentional locks conflict only with table-level locks, not row-level locks. Because the main purpose of an intention lock is to indicate that someone is about to lock, or is locking, a row.

Just like when you go to the library to find a book, you don’t need to look on every shelf next to each other. You just go to the information desk and search on the computer to see if the library has the book.

Record locks

This is a record lock, a type of row lock. The lock object of a record lock is the index corresponding to that row of data. For those who are not familiar with the index, take a look at this article.

SELECT * FROM student WHERE id = 1 FOR UPDATE SELECT * FROM student WHERE id = 1 FOR UPDATE What if there is no index in a table? This problem was also explained in the previous article. When a table does not have a primary key defined, InnoDB creates a hidden ROWID and uses that ROWID to create cluster indexes. Subsequent record locks are also added to the hidden cluster index.

When we start a transaction to update the row with id = 1, if we don’t commit the transaction immediately and then start another transaction to update the row with id = 1, then use Show Engine InnoDB Status to see. We can see LOCK_MODE X LOCKS REC BUT NOT GAP WAITING.

X stands for exclusive lock. It can be seen from this that record lock can also be divided into shared lock and exclusive lock modes. When we use FOR UPDATE, we are exclusive, while when we use LOCK IN SHARE MODE, we are shared.

The gap in the above words is another implementation of the gap lock row lock.

Clearance lock

In the case of Gap Locks, the object it Locks is also an index. To better understand gap locks, let’s take an example.

SELECT name FROM student WHERE age BETWEEN 18 AND 25 FOR UPDATE

Assuming we have a non-clustered index for age, running this statement prevents other transactions from adding data with age 18-25 to the student table, regardless of whether there is any data with age 18-25 in the table. Because the nature of a gap lock is to lock a range on an index, whereas in InnoDB the index is stored in the underlying B+ tree in order.

Here’s another example:

SELECT * FROM student WHERE age = 10 FOR UPDATE;

It is worth noting that the AGE here is either a unique index or a simple non-clustered index. A record lock is placed on the data where age = 10 and the Gap where age < 10 is locked. If the current transaction is not committed, other transactions that attempt to insert an age < 10 data will be blocked.

The gap lock is a compromise solution for MySQL’s performance and concurrency concerns, and is only available for Readable Reads (RR). MySQL will disable the gap lock if the isolation level of the current transaction is Read Commit (RC).

As mentioned earlier, record locks can be shared and exclusive, and gap locks are the same. But unlike record locks, shared gap locks and exclusive gap locks are not mutually exclusive. What’s going on?

We still need to look beyond the phenomenon to see the essence. What is the purpose of gap locking?

To prevent other transactions from inserting data into the GAP

The shared and exclusive gap locks are consistent with this goal, so they can exist simultaneously.

Key in the lock

Next-key Locks are InnoDB’s last implementation of row Locks. Pro-key Locks are actually a combination of record Locks and gap Locks. In other words, a pro key lock adds a record lock to the corresponding index, plus a lock interval.

But not all pro locks work this way, for the following SQL:

SELECT * FROM student WHERE id = 23;

In this case, id is the primary key, the unique index, and no matter how much data is inserted by other transactions, there will always be only one entry for id = 23. At this point, adding a gap lock is completely unnecessary and reduces concurrency. Therefore, when the index used is unique, the pro key lock is degraded to a record lock.

So let’s say we have 10,20,30, three indexes. For a pro key lock, the range of possible locks would be as follows:

  • (up to 10]
  • (10, 20]
  • (20, 30]
  • (30, up)

InnoDB’s default transaction isolation level is Reiterable Read (RR). In this case, InnoDB uses Pro-key Locks to prevent phantom reads.

In a transaction, you perform two queries. The first query returns five items, but the second query returns seven items
Phantom read.

You may have read about InnoDB’s RR transaction isolation level in many previous blogs or interview articles. The key to RR transaction isolation is critical locking.

For example, suppose there are only two rows in the student table with IDs of 90 and 110.

SELECT * FROM student WHERE id > 100 FOR UPDATE;

After executing the SQL statement, InnoDB applies a gap lock to the interval (90, 110) and (110,∞), as well as a record lock to the index (id=110). In this way, no other transaction can add data to this interval, even if 100 does not exist.

Insert intention lock

Next up is Insert Intention Locks, which we apply when we execute an INSERT statement. It is essentially a type of gap lock.

For example, if we have index records 10 and 20, transaction A and transaction B insert data with index values 14 and 16 respectively, then transaction A and transaction B will lock the Gap between 10 and 20 with the insert intention lock, and after obtaining the insert intention lock, they will obtain exclusive locks of 14 and 16.

Transaction A and B will not block each other at this point because they are inserting different rows.

On the lock

Finally, there are auto-inc Locks, which are table Locks in nature. A self-increment lock is held when transaction A adds data to A table that contains an AUTO_INCREMENT column. At this point, the other transaction B must wait to ensure that transaction A has A continuous increment without A break in the middle.

This article has been posted on my GitHub github.com/sh-blog. Welcome to STAR. WeChat search focuses on [SH full stack notes], replies to [queue] for MQ learning materials, including basic concept analysis and RocketMQ detailed source code analysis, ongoing update.

If you find this article helpful, please like it, leave a comment, share it, or leave a comment.