Detailed explanation of the implementation of MySQL auto-increment primary key

1. Where is the self-incremented value saved?

Different engines have different strategies for saving auto-increment values.

1. The MyISAM engine's self-increment value is saved in the data file

2. The self-increment value of the InnoDB engine is stored in the memory and is not persistent in MySQL 5.7 and earlier versions. After each restart, when the table is opened for the first time, the maximum value of the auto-increment value max(id) is found, and then max(id) + step size is used as the current auto-increment value of the table.

select max(ai_col) from table_name for update;

In MySQL 8.0, the changes of the auto-increment value are recorded in the redo log. When restarting, the redo log is used to restore the value before the restart.

2. Self-value modification mechanism

If the id field is defined as AUTO_INCREMENT, when inserting a row of data, the behavior of the auto-increment value is as follows:

1. If the id field is specified as 0, null, or unspecified when inserting data, then the current AUTO_INCREMENT value of the table is filled in the auto-increment field

2. If a specific value is specified for the id field when inserting data, the value specified in the statement is used directly

Suppose the value to be inserted is X and the current auto-increment value is Y

1. If X<Y, then the table's auto-increment value remains unchanged

2. If X>=Y, you need to change the current auto-increment value to the new auto-increment value

The new auto-increment generation algorithm is: starting from auto_increment_offset (initial value), with auto_increment_increment (step length) as the step length, continue to add until the first value greater than X is found as the new auto-increment value

3. When to modify the auto-increment value

Create a table t, where id is the auto-increment primary key field and c is the unique index. The table creation statement is as follows:

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
)ENGINE=InnoDB;

Assume that the table t already has the record (1,1,1). Now execute another command to insert data:

insert into t values(null, 1, 1); 

The execution flow is as follows:

1. The executor calls the InnoDB engine interface to write a row. The value of the row passed in is (0,1,1)

2. InnoDB finds that the user does not specify the value of the auto-increment id, and obtains the current auto-increment value of table t 2

3. Change the value of the incoming row to (2,1,1)

4. Change the table's auto-increment value to 3

5. Continue to insert data. Since there is already a record with c=1, a Duplicate key error is reported and the statement returns

The corresponding execution flow chart is as follows:

After that, when a new row of data is inserted, the auto-increment id obtained is 3. The auto-increment primary key is discontinuous

Unique key conflicts and transaction rollbacks can cause discontinuity in the auto-increment primary key ID.

4. Optimization of self-increment lock

The auto-increment ID lock is not a transaction lock, but is released immediately after each application to allow other transactions to apply again.

But in MySQL version 5.0, the scope of the auto-increment lock is statement level. That is to say, if a statement applies for a table auto-increment lock, the lock will not be released until the statement is executed.

MySQL version 5.1.22 introduces a new strategy, a new parameter innodb_autoinc_lock_mode, the default value is 1

1. Setting this parameter to 0 means that the strategy of the previous MySQL 5.0 version is adopted, that is, the lock is released only after the statement is executed.

2. This parameter is set to 1

• For ordinary insert statements, the auto-increment lock is released immediately after it is applied.
• For statements that insert data in batches, such as insert ... select, the auto-increment lock is still released after the statement is completed.

3. If this parameter is set to 2, all actions that apply for auto-increment primary keys will release the lock after application.

For data consistency, the default setting is 1

If sessionB releases the auto-increment lock immediately after applying for the auto-increment, the following situation may occur:

• SessionB first inserts two rows of data (1,1,1) and (2,2,2)
• SessionA applies for an auto-increment id and gets id=3, and inserts (3,5,5)
• After that, sessionB continues to execute and inserts two records (4,3,3) and (5,4,4)

When binlog_format=statement, the two sessions execute the insert data command at the same time, so there are only two situations for the update log of table t2 in binlog: either sessionA is recorded first, or sessionB is recorded first. Regardless of the method, this binlog is taken to the slave database for execution, or used to restore a temporary instance. In the slave database and the temporary instance, the sessionB statement is executed, and the ids in the generated results are continuous. At this time, data inconsistency occurs in this library

Ideas to solve this problem:

1) Make the batch insert statements of the original database generate continuous ID values. Therefore, the self-increment lock is not released until the statement is executed, just to achieve this purpose.

2) All operations of inserting data are recorded truthfully in the binlog. When the standby database is executed, it no longer relies on the auto-increment primary key to generate data. That is, set innodb_autoinc_lock_mode to 2 and binlog_format to row

If there is a scenario of batch inserting data (insert...select, replace...select, and load data), from the perspective of concurrent inserting data performance, it is recommended to set innodb_autoinc_lock_mode to 2 and binlog_format to row. This can achieve concurrency without causing data consistency issues.

For statements that insert data in batches, MySQL has a strategy for applying for auto-increment IDs in batches:

1. During statement execution, the first time you apply for an auto-increment id, 1 will be allocated

2. After the first one is used up, this statement will allocate 2 more ids if it applies for the second auto-increment id.

3. After the 2 are used up, the same statement is used again. The third time the auto-increment id is requested, 4 will be allocated

4. By analogy, the same statement is used to apply for an auto-increment ID, and the number of auto-increment IDs applied for each time is twice the previous one

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert ... select actually inserts 4 rows of data into table t2. However, these four rows of data were applied for auto-increment ids three times. The first time they were applied for id=1, the second time they were assigned id=2 and id=3, and the third time they were assigned id=4 to id=7.

Since this statement actually only uses 4 ids, id=5 to id=7 are wasted. After that, execute insert into t2 values(null, 5,5) , the data actually inserted is (8,5,5)

This is the third reason why the primary key ID is not continuous.

5. The auto-increment primary key is used up

If you insert another row of records after the auto-increment primary key field reaches the upper limit of the defined type, a primary key conflict error will be reported.

Take an unsigned integer (4 bytes, the upper limit is 2 32 − 1 2^{32}-1 232−1) as an example, and verify it with the following statement sequence:

CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(NULL);

After the first insert statement successfully inserts data, the AUTO_INCREMENT of this table does not change (it is still 4294967295), which causes the second insert statement to get the same auto-increment id value. When the insert statement is tried again, a primary key conflict error is reported.

Recommended materials :

https://time.geekbang.org/column/article/80531

This is the end of this article on the detailed explanation of the implementation of MySQL auto-increment primary key. For more relevant MySQL auto-increment primary key content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!