A brief understanding of the relevant locks in MySQL

This article is mainly to take you to quickly understand the knowledge related to locks in InnoDB

Basic concept analysis and detailed source code analysis of RocketMQ

http://xiazai.jb51.net/202105/yuanma/RocketMQ_jb51.rar

Why do we need to lock

First of all, why lock? I think I don't need to say more, just imagine the following scene and you will get it.

When you go to the restroom in a shopping mall, what will you do? Lock the door. If you don't lock the door and it suddenly opens while you're using the toilet, it might seem a little inappropriate.

The same is true for data. In concurrent scenarios, if the data is not locked, the consistency of the data will be directly destroyed, and if your business involves money, the consequences will be even more serious.

Lock the door expression pack

Lock classification

What are the locks in InnoDB? In fact, you should already know a lot. For example, in the interview, you will be asked about the difference between the storage engines MyISAM and InnoDB. You will say that MyIASM only has table locks, but InnoDB supports both row locks and table locks. You may also be asked what the difference is between optimistic locking and pessimistic locking.

There are many concepts and nouns for locks. If you don’t have a complete worldview about locks, it will be difficult for you to understand them. Next, we will classify these locks.

According to the granularity of the lock

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

• Table Lock
• Row Lock

We will not discuss page locks here, as page locks are a concept that only exists in the BDB (BerkeleyDB) storage engine. We will mainly discuss the InnoDB storage engine here.

According to the idea of ​​lock

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

• Pessimistic Lock
• Optimistic Locking

The pessimism and optimism here have the same meaning as the nouns you usually understand. Optimistic locking assumes that there is a high probability that conflicts will not occur and locks only when necessary. Pessimistic locking believes that there is a high probability of conflict, so the locking operation will be performed regardless of whether it is necessary.

According to compatibility

According to compatibility, locks can be divided into:

• Shared Locks
• Exclusive lock

Resources with shared locks can be shared with others, but if exclusive locks are added, others cannot perform any operations without obtaining the lock.

According to the implementation of the lock

The implementation here is the specific types of locks in InnoDB, which are:

• Intention Locks
• Record Locks
• Gap Locks
• Next-Key Locks
• Insert Intention Locks
• AUTO-INC Locks

Even if locks are divided according to this classification, you may still be a little confused when seeing so many lock names. For example, what kind of lock is added when I SELECT ... FOR UPDATE ?

We should see the essence through the phenomenon. What is the essence? The essence is what object the lock is added to, and this is easy to answer:

• Added to the table
• Added to the line

And what is the nature of the locks added to the rows? The essence is to add a lock to the index.

Intention Lock

InnoDB supports locks of different granularities, including row locks and table locks. For example, the lock tables command will hold an exclusive lock on the corresponding table. To make locks of various granularities more practical, InnoDB designed intention locks.

An intention lock is a table-level lock that indicates which type of lock will be used in the next transaction. It has the following two types:

• Shared intention lock (IS) indicates that the transaction intends to add a shared lock to the records in the table
• Exclusive intention lock (IX) is an exclusive lock

For example, select ... for share adds a shared intention lock, while SELECT .. FOR UPDATE adds an exclusive intention lock. The rules are as follows:

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

The following figure shows the mutual exclusion and compatibility of these lock combinations.

According to the table above, if they are compatible with each other, the corresponding transaction can acquire the lock, but if they are incompatible, the lock cannot be acquired until the incompatible lock is released.

You may have questions when you see this, since the intention lock does not block anything except LOCK TBALES . Then what use do I have for it?

Still using the example, suppose transaction A obtains a shared lock for the row with id = 100 in the student table, and then transaction B needs to apply for an exclusive lock for the student table. These two locks are obviously conflicting, and they are for the same row.

How does InnoDB need to know that A has acquired this lock? Traverse the entire B+ tree? No, the answer is intention lock. When transaction B applies for an exclusive lock on the write table, InnoDB finds that transaction A has already acquired an intention shared lock on the table, indicating that there are records in the student table that are already locked by a shared lock. It will be blocked at this time.

Furthermore, intention locks do not block any other operations except for operations such as LOCK TABLES . In other words, intention locks only conflict with table-level locks, not row-level locks. Because the main purpose of the intention lock is to indicate that someone is about to, or is currently locking a row.

Just like when you go to the library to look for a book, you don’t need to search every bookshelf one by one. You can just go to the service desk and search on the computer to find out whether the library has the book.

Record Lock

This is a record lock, which is a type of row lock. The lock object of the record lock is the index corresponding to that row of data. If you are not clear about the index, you can read this article.

When we execute SELECT * FROM student WHERE id = 1 FOR UPDATE statement, a record lock will be added to the index with a value of 1. What if there is no index in a table? This problem has also been explained in the article mentioned above. When a table does not have a primary key defined, InnoDB will create a hidden RowID and use this RowID to create a clustered index. Subsequent record locks are also placed on this hidden clustered index.

When we start a transaction to update the row with id = 1, if we do not commit the transaction immediately and then start another transaction to update the row with id = 1, we can see the message lock_mode X locks rec but not gap waiting when we use show engine innodb status .

X stands for exclusive lock. From this we can see that record locks can actually be divided into shared lock and exclusive lock modes. When we use FOR UPDATE , it is exclusive, and when we use LOCK IN SHARE MODE , it is shared.

gap that appears in the above text is another implementation of row lock, the gap lock.

Gap Lock

For Gap Locks, the locked object is also an index. To better understand the gap lock, let's take an example.

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

Assuming that we have created a non-clustered index for age , running this statement will prevent other transactions from adding data with age of 18-25 to student table, regardless of whether there is actually data with age of 18-25 in the table. This is because the essence of gap locking is to lock a range on the index, and the storage of indexes in the underlying B+ tree in InnoDB is ordered.

Here is another example:

SELECT * FROM student WHERE age = 10 FOR UPDATE;

It is worth noting that age here is not a unique index, but a simple non-clustered index. At this time, a record lock will be added to the data with age = 10 , and the gap age < 10 will be locked. If the current transaction is not committed, other transactions will be blocked if they want to insert a piece of data with age < 10 .

Gap locks are a compromise solution for MySQL based on performance and concurrency considerations, and are only available under Repeatable Read (RR). If the isolation level of the current transaction is Read Committed (RC), MySQL will disable gap locks.

As I just said, record locks are divided into shared and exclusive, and gap locks are actually 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 see the essence through the phenomenon. What is the purpose of the gap lock?

To prevent other transactions from inserting data into the Gap

Shared and exclusive gap locks are consistent in this goal, so they can exist at the same time.

Pro Key Lock

Next-Key Locks are the last type of row lock implementation in InnoDB. Next-Key Locks are actually a combination of record locks and gap locks. In other words, the adjacent key lock will add a record lock to the corresponding index and additionally lock an interval.

But not all temporary key locks work this way. For the following SQL:

SELECT * FROM student WHERE id = 23;

In this case, id is the primary key and unique index. No matter how much data is inserted by other transactions, there will always be only one piece of data with id = 23 . At this time, adding a gap lock is completely unnecessary and will reduce concurrency. Therefore, when the index used is a unique index, the immediate key lock will be downgraded to a record lock.

Suppose we have 3 index data items: 10, 20, and 30. Then for the temporary key lock, the possible locked range will be as follows:

• (∞, 10]
• (10, 20]
• (20, 30]
• (30, ∞)

The default transaction isolation level of InnoDB is repeatable read (RR). In this case, InnoDB will use temporary key locks to prevent phantom reads.

To briefly explain phantom read, within a transaction, you execute two queries. The first query returns 5 data items, but the second query returns 7 data items. This is a phantom read.

You may have learned in many previous blogs or interview essays that InnoDB's RR transaction isolation level can prevent phantom reads. The key to preventing phantom reads in RR is the temporary key lock.

For example, suppose there are two rows in the student table, with ids 90 and 110 respectively.

SELECT * FROM student WHERE id > 100 FOR UPDATE;

After executing this SQL statement, InnoDB will add gap locks to the intervals (90, 110] and (110,∞), and add a record lock to the index with id=110. In this way, other transactions cannot add new data to this interval, even if 100 does not exist at all.

Insert intention lock

Next is the Insert Intention Locks, which are added before we execute INSERT statement. Essentially a type of gap lock.

Let's take an example again. Suppose we now have index records 10 and 20. Transactions A and B insert data with index values ​​14 and 16 respectively. At this time, transactions A and B will use the insert intention lock to lock the gap between 10 and 20. After obtaining the insert intention lock, they will obtain exclusive locks for 14 and 16.

At this time, transactions A and B will not block each other because they insert different rows.

Auto-increment lock

Finally, there are AUTO-INC Locks. The essence of AUTO-INC Locks is table locks, which are quite special. When transaction A adds data to a table containing AUTO_INCREMENT column, it holds an auto-increment lock. At this time, other transactions B must wait to ensure that transaction A obtains continuous self-increment without any gaps in the middle.

Well, get the MQ learning materials through the link below, including basic concept analysis and RocketMQ detailed source code analysis. It is continuously updated, so don’t miss this learning material.

http://xiazai.jb51.net/202105/yuanma/RocketMQ_jb51.rar (Must collect)

The above is a brief introduction to the details of the relevant locks in MySQL. For more information about MySQL locks, please pay attention to other related articles on 123WORDPRESS.COM!