Shulou(Shulou.com)05/31 Report--

This article mainly explains why the MySQL self-increasing primary key is not monotonous or continuous. The explanation in the article is simple and clear and easy to learn and understand. Please follow the editor's train of thought to study and learn why the MySQL self-increasing primary key is not monotonous or continuous.

When we use a relational database, the primary key (Primary Key) is an unavoidable concept. The primary key acts as the identifier of the record, through which we can locate the unique record in a table.

In a relational database, we will select the smallest subset of multiple fields in the record as the unique identifier of the record in the table [^ 1]. According to the definition of the primary key in the relational database, we can select either a single column as the primary key or multiple columns as the primary key, but the primary key must exist and unique in the entire record. The most common way, of course, is to use MySQL's default self-incrementing ID as the primary key, and although it is legal to use primary keys set by other policies, it is not a common and recommended practice.

Figure 1-Primary key for MySQL

The default AUTO_INCREMENT attribute in MySQL ensures the continuity of the primary key in most cases. We can see the current value of the AUTO_INCREMENT attribute in the table definition through the show create table command. When we insert data into the current table, it will use the value of this attribute as the primary key for inserting the record, and every time we get the value, it will add one to it.

CREATE TABLE `trades` (`id` bigint (20) NOT NULL AUTO_INCREMENT,... `created_ at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`),) ENGINE=InnoDB AUTO_INCREMENT=17130 DEFAULT CHARSET=utf8mb4

In the perception of many developers, the primary key of MySQL should be monotonously increasing, but we will encounter two problems in the process of dealing with MySQL. First, the primary key of records is not contiguous, and secondly, multiple records with the same primary key may be created. We will answer the reasons why MySQL is not monotonous and discontinuous from the following two perspectives:

Earlier versions of MySQL stored AUTO_INCREMENT in memory, and when the instance was restarted, it was reset based on the data in the table.

Transaction locks are not used when obtaining AUTO_INCREMENT, and some field conflicts may occur in concurrent insertion transactions, which may lead to insertion failure.

It is important to note that what we are discussing in this article is the most common InnoDB storage engine in MySQL, and the AUTO_INCREMENT implementation principles provided by other engines such as MyISAM are beyond the scope of this article.

Delete record

Although the AUTO_INCREMENT attribute is common in MySQL, in earlier versions of MySQL, its implementation was relatively crude, and the InnoDB engine stored an integer in memory to represent the next assigned ID, and when the client inserted data into the table, it took the AUTO_INCREMENT value and multiplied it.

Figure 2-use of AUTO_INCREMENT

Because the value is stored in memory, each time the MySQL instance is restarted, when the client inserts a record into the table_name table for the first time, MySQL uses the SQL statement shown below to find the maximum value of id in the current table, increments it as the primary key of the record to be inserted, and serves as the initial value of the AUTO_INCREMENT counter in the current table [^ 2].

SELECT MAX (ai_col) FROM table_name FOR UPDATE

If the author were to implement AUTO_INCREMENT, this approach would be used in the first place. However, although this implementation is very simple, if users do not strictly follow the design specifications of relational databases, there will be data inconsistencies such as the following:

Figure 3-AUTO_INCMRENT before version 5.7

Because the instance of MySQL is restarted, the AUTO_INCREMENT counter in memory is reset to the maximum value in the table, and when we insert a new trades record into the table, we re-use 10 as the primary key, so the primary key is not monotonous. After the new trades record is inserted, the record in the executions table misrefers to the new trades, which is actually a serious mistake.

However, this is not entirely a MySQL problem, if we strictly follow the relational database design specifications, using foreign keys to deal with the relationship between different tables, we can avoid the above problems, because the current trades records still have external references, so foreign keys will prohibit the deletion of trades records, but most internal DBA do not recommend or prohibit the use of foreign keys, so there is indeed the possibility of such a problem.

However, in MySQL 8.0, the initialization behavior of the AUTO_INCREMENT counter has changed, and each change of the counter is written to the system's redo log (Redo log) and stored in the engine's private system table at each checkpoint [^ 3].

In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and is saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.

When the MySQL service is restarted or is in crash recovery, it can recover the latest AUTO_INCREMENT counters from persistent checkpoints and redo logs, avoiding non-monotonous primary keys and solving the problems mentioned here.

Concurrent transaction

To improve transaction throughput, MySQL can handle multiple transactions executed concurrently, but if multiple SQL statements that insert new records are executed concurrently, it may result in discontiguous primary keys. As shown in the following figure, transaction 1 inserts records with id = 10 into the database, and transaction 2 inserts two records with id = 11 and id = 12 into the database:

Figure 4-execution of concurrent transactions

However, if transaction 1 is rolled back due to a unique key conflict in the inserted record, and transaction 2 commits normally without an error, we will find that the primary key in the current table is discontiguous. Subsequent newly inserted data will no longer use 10 as the primary key of the record.

Figure 5-discontiguous primary key

The reason behind this phenomenon is also very simple. Although a lock is added when obtaining the AUTO_INCREMENT, the lock is a statement lock, and its purpose is to ensure that the acquisition of the AUTO_INCREMENT does not lead to thread competition, not to guarantee the continuity of the primary key in the MySQL [^ 4].

The above behavior is controlled by the innodb_autoinc_lock_mode configuration provided by the InnoDB storage engine, which determines the locks that need to be obtained first when obtaining AUTO_INCREMENT timers. There are three different modes in this configuration, namely, traditional mode (Traditional), continuous mode (Consecutive) and cross mode (Interleaved) [^ 5], where MySQL uses continuous mode as the default lock mode:

(1) traditional mode innodb_autoinc_lock_mode = 0

When you insert data into a table that contains the AUTO_INCREMENT attribute, all INSERT statements acquire a table-level AUTO_INCREMENT lock that is released after the current statement is executed

(2) continuous mode innodb_autoinc_lock_mode = 1

INSERT... SELECT 、 REPLACE... Bulk insert operations such as SELECT and LOAD DATA require the acquisition of a table-level AUTO_INCREMENT lock, which is released after the current statement is executed

A simple insert statement (a statement that knows in advance how many records to insert) only needs to acquire the mutex of the AUTO_INCREMENT counter and release it directly after acquiring the primary key, without waiting for the execution of the current statement to be completed.

(3) Cross mode innodb_autoinc_lock_mode = 2

All insert statements do not need to acquire table-level AUTO_INCREMENT locks, but when the number of rows inserted by multiple statements is uncertain, there may be a risk of assigning the same primary key

None of these three modes can solve the problem of self-increasing primary key discontinuity in MySQL. The ultimate solution to this problem is to serially execute all transactions that include insert operations, that is, to use the highest isolation level of the database-serializable (Serialiable). Of course, it is relatively simple and rough to directly modify the isolation level of the database. Fully serial insertion based on MySQL or other storage systems can also ensure the continuity of the primary key during insertion, but it still can not avoid the discontinuity caused by deleting data.

Summary

The primary key of early MySQL is neither monotonous nor continuous, these are some choices made in the project at that time. If the design specification of relational database is strictly followed, the probability of problems caused by the initial design of MySQL is relatively low, and the consistency of data will be affected only when the deleted primary key is referenced by the external system, but the different ways of use today increase the possibility of error. MySQL persisted AUTO_INCREMENT in 8.0 to avoid this problem.

The discontiguous primary key in MySQL is another example of engineering design bowing to performance, sacrificing the continuity of the primary key to support the concurrent insertion of data, and finally improving the throughput of MySQL services. The author encountered this problem when using MySQL a few years ago, but he did not delve into the reasons behind it at that time, and it is also a very interesting process to re-understand the design decisions behind the problem today. Let's briefly summarize the contents of this article and return to today's question-why MySQL's self-increasing primary keys are not monotonous or contiguous:

Before version 5.7 of MySQL, AUTO_INCREMENT counters are stored in memory. After the instance is restarted, it will be reset according to the data in the table. Duplicate primary keys may occur after restarting after deleting records. This problem is solved by using redo logs in version 8.0, which ensures the monotonicity of primary keys.

When MySQL inserts data to obtain AUTO_INCREMENT, it will not use transaction lock, but will use mutex lock. Concurrent insert transaction may cause insertion failure due to some field conflicts. In order to ensure the continuity of primary key, insert statements need to be executed sequentially.

Thank you for your reading, the above is the "why MySQL self-increasing primary key is not monotonous nor continuous" content, after the study of this article, I believe that why MySQL self-increasing primary key is not monotonous and not continuous this problem has a deeper understanding, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.