Briefly understand the related locks in MySQL

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

Why do we need to lock

First of all, why do we need to lock? I think I don't need to say more, imagine the next scenario you can get.

You go to the toilet in the bathroom of the mall, what is the operation you will definitely do at this time? Lock the door. If you don't lock the door, go to the toilet, and the door is opened with a slap, it may seem like a little bit inappropriate.

The same is true for data. In a concurrent scenario, if you don't lock the data, it will directly destroy the consistency of the data, and if your business involves money, the consequences will be even more serious.

lock door emoticon

Classification of locks

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 engine 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 is the difference between optimistic and pessimistic locking.

There are many concepts and terms of locks. If you have not constructed a complete world view of locks, then your understanding will be more difficult. 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 won't discuss page locks here. Page locks are a concept that only exists in the BDB (BerkeleyDB) storage engine. We mainly discuss the InnoDB storage engine here.

Follow the thought of the lock

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

• Pessimistic lock
• Optimistic lock

The pessimism and optimism here have the same meaning as the nouns you usually understand. Optimistic locking believes that there is a high probability that conflicts will not occur, and only locks when necessary. The pessimistic lock believes that there will be a high probability of conflict, so no matter whether it is necessary to lock or not, it will perform the lock operation.

Follow compatibility

According to compatibility, locks can be divided into:

• Shared lock
• Exclusive lock

The resource to which the shared lock is added can be shared with other people, and if the exclusive lock is added, other people cannot perform any operations unless they can get the lock.

According to the realization 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 the locks are divided according to this classification, it may still be a little confused to see so many lock terms. For example, what lock did SELECT ... FOR UPDATE

We should look at the essence through the phenomenon. What is the essence? The essence is what object the lock is added to, and this is a good answer:

• Added to the table
• Added to the line

What is the essence of the lock added to the row? Essentially, the lock is added to the index.

Intent lock

Different granular locks, row locks and table locks are supported in InnoDB. For example, the lock tables command will hold the exclusive lock of the corresponding table. In order to make multiple locks of different granularities more practical, InnoDB designed intention lock .

The intention lock is a table-level lock , which 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 is the added shared intent lock, and SELECT .. FOR UPDATE is the added exclusive intent lock. The rules are as follows:

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

The following figure shows the mutual exclusion and compatibility of the combination of these types of locks

Compared with the above table, in the case of mutual compatibility, the corresponding transaction can acquire the lock, but if it is incompatible, the lock cannot be acquired until the incompatible lock is released.

You may have problems seeing this, since the intent lock does not block anything LOCK TBALES What do I want it for?

Still by example, suppose that transaction A acquires the shared lock of the row id = 100 in the student table, and then transaction B needs to apply for an exclusive lock on the student table. The two locks are obviously in conflict, and they are still on the same line.

How does InnoDB need to sense that A has acquired the lock? Traverse the entire B+ tree? No, the answer is intent lock. When transaction B applies for an exclusive lock for writing the table, InnoDB will find that transaction A has acquired the intent shared lock of the table, indicating that there are records in the student table that are locked by the shared lock. It will be blocked at this time.

And, the intention lock will not block any other operations except for operations LOCK TABLES In other words, intent locks will only conflict with table-level locks, but not with row-level locks. Because the main purpose of the intention lock is to indicate that someone is about to lock a row, or is in the process of locking it.

Just like when you go to the library to find a book, you don't need to look for each shelf next to each other. You can go directly to the service desk and search on a computer to know if the library has the book.

Record lock

This is the 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. The index may look less clear this article .

When we execute the SELECT * FROM student WHERE id = 1 FOR UPDATE statement, a record lock will be added to the index with a value of 1. As for 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 will also be added to this hidden clustered index.

When we start a transaction to update the row of id = 1, if we do not commit the transaction immediately, and then start a transaction to update the row with id = 1, use show engine innodb status view at this time, we can see the words lock_mode X locks rec but not gap waiting

X means exclusive lock. It can be seen from this that record locks can also be divided into shared locks and exclusive locks. When we use FOR UPDATE is exclusive, while using LOCK IN SHARE MODE is shared.

gap that appears in the above words is another line lock realization gap lock .

Gap lock

For gap locks (Gap Locks), the locked object is also an index. In order to better understand the gap lock, let's take an example.

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

Suppose we have age . Running this statement will prevent other transactions from student table, regardless of whether the table actually has data with an age of 18-25. Because the essence of the gap lock is to lock a range on the index, and the storage of the index on the underlying B+ tree in InnoDB is ordered.

Let me give another example:

SELECT * FROM student WHERE age = 10 FOR UPDATE;

It is worth noting that the age here is not a unique index, but a simple non-clustered index. At this time, the age = 10 the data of 060f63c427cf1c, and the Gap of age < 10 If the current transaction is not committed, other transactions will be blocked age < 10

Gap lock is a compromise solution for MySQL under comprehensive consideration of performance and concurrency, and it is only available under Repeatable Read (RR) , if the current transaction isolation level is Read Committed (RC) , MySQL will disable the gap lock.

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 is 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 in Gap

The shared and exclusive gap locks are consistent with this goal, so they can exist at the same time.

Pro key lock

Next-Key Locks is the implementation of InnoDB's last row lock. The next-key lock is actually a record lock and gap lock . In other words, the adjacent key lock will add a record lock to the corresponding index, and additionally lock an interval.

But not all proximity key locks are played like this, 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, id = 23 will always be only one piece of data for 060f63c427e4d0. At this time, adding a gap lock is completely unnecessary, but it will reduce concurrency. Therefore, when the index used is unique index , the adjacent key lock will be downgraded to record lock .

Suppose we have a total of 3 index data of 10, 20, and 30. Then, corresponding to the proximity key lock, the possible lock interval will be as follows:

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

InnoDB's default transaction isolation level is Repeatable Read (RR) . In this case, InnoDB will use the phantom read .

Briefly explain the phantom reading, that is, in the transaction, you execute two queries, the first time you query 5 pieces of data, but the second time you check, you actually find 7 pieces of data, which is phantom reading .

Maybe you have learned about InnoDB's RR transaction isolation level to prevent phantom reading in many previous blogs or interview eight-part essays. The key to RR preventing phantom reading is Pro key lock .

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

SELECT * FROM student WHERE id > 100 FOR UPDATE;

gap lock to the interval (90, 110] and (110,∞) record lock the index of id=110. In this way, other transactions are It is not possible to add data to this interval, even if 100 does not exist at all.

Insert intent lock

Next is the Insert Intention Locks, which will be added before INSERT It is essentially a kind of gap lock.

As an example, suppose we now have index records 10 and 20, and transactions A and B insert data with index values of 14 and 16, respectively. At this time, transactions A and B will lock the gap between 10-20 with an insert intention lock. , After acquiring the insertion intention lock, the exclusive lock of 14, 16 will be acquired.

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

Self-increasing lock

Finally, there are AUTO-INC Locks. The essence of self-increasing locks is table lock , which is more special. When transaction A AUTO_INCREMENT column, it will hold the self-increasing lock. At this time, the other transaction B must wait to ensure that the transaction A obtains continuous self-increment, and there will be no gaps in the middle.

This article has been put in my Github github.com/sh-blog , welcome to Star. WeChat search follows [ SH's full-stack notes ], and reply [ queue ] to obtain MQ learning materials, including basic concept analysis and RocketMQ detailed source code analysis, and continue to be updated.

If you think this article is helpful to you, please a thumbs up , close a note , share , leave a .