In-depth analysis of MySQL self-increasing lock

The article introduced all the locks in InnoDB, including intention locks, record locks... self-increasing locks Barabara. But when I looked back later, I found that the introduction to self-increasing locks was only a short paragraph.

In fact, self-locking increase (AUTO-INC Locks) this is still a lot of details worthy of discussion, for example, in concurrent scenario, InnoDB is how to ensure that the correct value of self-growth, this chapter will be dedicated simple discuss InnoDB The self-increasing lock in.

What is self-increasing lock

As we mentioned before, the self-increasing lock is a special kind of table-level lock . And when the transaction AUTO_INCREMENT column, it will hold the auto-increment lock. Assuming that transaction A is doing this operation, if another transaction B tries to execute the INSERT statement, transaction B will be blocked until Transaction A releases the self-increasing lock.

How can I say that, he is right, but he is not completely right.

Behavior and restrictions

In fact, the blocking situation mentioned above is only one of which can be understood as self-increasing lock is an interface, and there are many specific implementations. The specific configuration item is innodb_autoinc_lock_mode . Through this configuration item, we can change some details of the operation in the self-increasing lock.

Moreover, there is another limitation of self-increasing locks, that is AUTO_INCREMENT must be an index, or the column must be part of the index (joint index), but this limitation has no effect on most development scenarios.

After all, isn't our basic exercise to set the id to AUTO_INCREMENT ?

Lock mode

In fact, in InnoDB, calling the lock behavior lock mode may be more accurate. What are the specific lock modes, as follows:

• Traditional mode (Traditional)
• Continuous mode (Consecutive)
• Interleaved

Corresponding to the values 0, 1, and 2 of the innodb_autoinc_lock_mode

Seeing this, you already know why the above statement is not accurate, because InnoDB handles concurrency differently in the three modes, and the specific lock mode you choose depends on the MySQL version you are currently using.

Before MySQL 8.0, the InnoDB lock mode defaulted to continuous mode with a value of 1. After MySQL 8.0, the default mode became cross mode . As for why the default mode will be changed, I will talk about it later.

Traditional model

Traditional mode (Traditional), to put it , is that InnoDB's self-increasing lock operation mode does not have the concept of 161136e786a5b5 lock mode Only later version update, InnoDB introduced the lock mode , and then InnoDB gave this previous default mode a name, called-traditional mode.

How does the traditional model work?

We know that when we AUTO_INCREMENT , we will hold such a special table lock—auto-increasing lock (AUTO-INC), and it will be released after the statement is executed. In this way, it can be ensured that the self-increasing value generated in a single sentence is continuous.

In this way, the shortcomings of the traditional model are naturally exposed. If multiple transactions execute the INSERT operation AUTO-INC will slightly decrease the performance of MySQL, because only one INSERT statement can be executed at the same time.

Continuous mode

Continuous mode (Consecutive) is the default mode before MySQL 8.0. The reason why this mode is proposed is because the traditional mode has drawbacks that affect performance, so there is a continuous mode.

When the lock mode is in continuous mode, if the INSERT statement can determine the amount of data inserted in advance, you do not need to acquire the self-increasing lock. For example, a INSERT INTO that can confirm the number in advance is not I can use self-increment locks. This is easy to understand. In terms of self-increment, I can directly INSERT statement, and then continue to execute the next statement.

But if the INSERT statement cannot confirm the amount of data in advance, it will still acquire the self-increasing lock. For example, a statement INSERT INTO ... SELECT ... INSERT is derived from another statement SELECT

The continuous mode graph is similar to the cross mode

Cross mode

In Interleaved mode, all INSERT statements, including INSERT and INSERT INTO ... SELECT , will not use the AUTO-INC self-increasing lock, but use the lighter mutex lock. In this way, multiple INSERT statements can be executed concurrently, which is also the most scalable of the three lock modes.

Concurrent execution brought the side effect of a single INSERT from value-added is not continuous, because AUTO_INCREMENT value will be assigned multiple INSERT cross back and forth statement execution.

The advantages are clear, but the disadvantage is that data consistency cannot be guaranteed in the case of concurrency. This will be discussed below.

Cross mode defect

To understand what the defect is, you must first understand MySQL Binlog. Binlog is generally used for MySQL's data replication , and it is generally used for master-slave synchronization. There are 3 formats of Binlog in MySQL, namely:

• Statement based on statements and only records SQL statements that modify data, which can effectively reduce the amount of data in binlog and improve the performance of reading and replay based on binlog
• Row only records the modified rows, so the amount of binlog recorded by Row is generally more than that in Statement format. The row-based binlog log is very complete and clear, recording all data changes, but the disadvantage is that there may be many, such as a update statement, it may be that all data has been modified; for example, alter table , etc., modify a certain For each field, the same every record has been changed.
• Mixed The combination of Statement and Row, how is the combination method? For example, modifications to the table structure alter table The rest of the data modification such as update and delete are recorded in Row format.

If the format adopted by MySQL is Statement , then the master-slave synchronization of MySQL actually synchronizes one SQL statement. If we use the cross mode at this time, INSERT statement cannot be guaranteed in the case of concurrency.

Maybe you haven't seen the problem. INSERT is executed at the same time, and AUTO_INCREMENT cross allocation will directly lead to the data between the master and the slave. The primary key ID is different . This is disastrous for master-slave synchronization.

In other words, if your DB has master-slave synchronization and the Binlog storage format is Statement, then do not set the InnoDB self-increment lock mode to cross mode, there will be problems. In fact, the process of master-slave synchronization is far more complicated than the picture above. I also wrote a detailed article about MySQL master-slave synchronization

Later, MySQL changed the log storage format from Statement to Row . In this way, the real row data is synchronized between the master and slave, and the primary key ID of has been determined before synchronizing to the slave database, so the synchronization statement The order is not sensitive, so the problem of Statement

Based on the change of MySQL's default Binlog format from Statement to Row , InnoDB also changed its default implementation of self-increasing lock from continuous mode to the more efficient cross mode .

Fish and bear paw

But if your MySQL version still uses continuous mode default, but you want to improve performance at the same time, what should you do? This actually has to make some trade-offs.

If you can conclude that your system will not use Binlog in the future, then you can choose to change the lock mode of self-increasing lock from continuous mode to cross mode , which can improve the concurrency of MySQL. Moreover, without the master-slave synchronization, the problem of the value mismatch of AUTO_INCREMENT caused by the out-of-order execution of the INSERT

Summarize

You might say, why do you have to understand so deeply? What's the use?

In fact, it is true. For example, in business, you have a script that needs to be executed for tens of seconds. The script calls INSERT many times. Then I will ask you this question. In these tens of seconds, it will block other users from using it. Corresponding function?

If you have enough understanding of self-increasing lock, then this problem will be solved.

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

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