When we work with relational databases, the primary key is a concept that cannot be avoided. The primary key acts as an identifier for a record, and we can locate a unique record in a table by its identifier.

In a relational database, we choose the smallest subset of multiple fields in a record as the unique identifier for that record in the table. Depending on the definition of a primary key in a relational database, we can choose either a single column as the primary key or multiple columns as the primary key, but the primary key must exist and be unique throughout the record. The most common way is of course to use MySQL’s default self-incrementing ID as the primary key, and while it is legal to use primary keys set by other policies, it is not a common or recommended practice.

The default AUTO_INCREMENT attribute in MySQL ensures primary key continuity in most cases. We can see the current value of the AUTO_INCREMENT attribute in the table definition with the show create table command, and when we insert data into the current table, it will use the value of this attribute as the primary key of the inserted record, and it will be added by one each time we get the value.

1
2
3
4
5
6

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, MySQL’s primary keys are supposed to be monotonically increasing, but we encounter two problems in our dealings with MySQL. First, the primary keys of records are not consecutive, and second, multiple records with the same primary key may be created, and we will answer the reasons why MySQL is not monotonic and consecutive from the following two perspectives.

  • Earlier versions of MySQL stored AUTO_INCREMENT in memory, and the value was reset after the instance restarted based on the data in the table.
  • Transaction locks are not used when obtaining AUTO_INCREMENT, and concurrent insert transactions may have partial field conflicts resulting in insert failures.

Note that we are discussing the most common InnoDB storage engine in MySQL in this article. The implementation principle of AUTO_INCREMENT provided by other engines such as MyISAM is out of the scope of this article.

Deleting Records

The AUTO_INCREMENT attribute is common in MySQL, but in earlier versions of MySQL, its implementation was relatively rudimentary. The InnoDB engine stores an integer in memory indicating the next ID to be assigned, and when the client inserts data into the table it gets the AUTO_INCREMENT value and adds one to it.

Because this value is stored in memory, the first time a client inserts a record into the table_name table after each MySQL instance restart, MySQL uses the SQL statement shown below to find the maximum value of id in the current table, add it by one as the primary key for the record to be inserted, and use it as the initial value for the AUTO_INCREMENT counter in the current table.

1

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

If the author had been asked to implement AUTO_INCREMENT, this approach would have been used in the beginning as well. However, although this implementation is very simple, if the user does not strictly follow the relational database design specifications, the data inconsistency problem will occur as shown below.

Because the MySQL instance 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 reuse 10 as the primary key, and the primary key is not monotonic anymore. After the new trades record is inserted, the record in the executions table incorrectly references the new trades, which is actually a rather serious error.

However, this is not entirely a MySQL problem. If we strictly follow the relational database design specifications and use foreign keys to handle links between different tables, we can avoid the above problem, because the current trades record still has an external reference, so the foreign key will prohibit the deletion of the trades record, but most internal DBAs do not recommend or prohibit the use of foreign keys, so there is a real possibility of this problem.

However, in MySQL 8.0, the initialization behavior of the AUTO_INCREMENT counter has changed so that each change in the counter is written to the system redo log and stored in the engine’s private system table at each checkpoint

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 in crash recovery, it can recover the latest AUTO_INCREMENT counters from persistent checkpoints and redo logs, avoiding unmonotonic primary keys also solving the problem mentioned here.

Concurrent Transactions

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

However, if at the end Transaction 1 rolls back due to a unique key conflict in the inserted record, and Transaction 2 commits normally without error, we will find that the primary key in the current table is disjoint, and subsequent new inserts will no longer use 10 as the primary key of the record.

The reason behind this phenomenon is also simple. Although a lock is added when fetching AUTO_INCREMENT, the lock is a statement lock, and its purpose is to ensure that fetching AUTO_INCREMENT does not lead to thread contention, not to ensure the continuity of primary keys in MySQL.

The above behavior is controlled by the innodb_autoinc_lock_mode configuration provided by the InnoDB storage engine, which determines the lock that needs to be obtained first when acquiring the AUTO_INCREMENT timer, and which exists in three different modes, Traditional, Consecutive, and Interleaved, with MySQL using Consecutive as the default lock mode.

  • Traditional mode innodb_autoinc_lock_mode = 0.
    • When inserting data in a table containing the AUTO_INCREMENT attribute, all INSERT statements acquire the AUTO_INCREMENT lock at the table level, which is released after the current statement is executed.
  • Continuous mode innodb_autoinc_lock_mode = 1.
    • INSERT ... SELECT, REPLACE ... SELECT and LOAD DATA bulk inserts require an TABLE LEVEL AUTO_INCREMENT lock, which is released after the current statement is executed.
    • simple insert statements (statements where the number of rows to be inserted is known in advance) only need to acquire a mutex lock for the AUTO_INCREMENT counter and release it directly after the primary key is acquired, without waiting for the current statement to finish executing.
  • Cross mode innodb_autoinc_lock_mode = 2.
    • All insert statements are not required to acquire an TABLE LEVEL AUTO_INCREMENT lock, but there may be a risk of assigning the same primary key when multiple statements insert an indeterminate number of rows of data.

The ultimate solution to this problem is to serialize all transactions containing insert operations, that is, to use the highest isolation level of the database - Serialiable. Of course, directly modifying the isolation level of the database is relatively simple and brutal, and implementing fully serialized inserts based on MySQL or other storage systems can also ensure that the primary key ** is consecutive at the time of insertion**, but it still does not avoid the discontinuity caused by deleting data.

Summary

Early MySQL’s primary keys were neither monotonic nor continuous, these were some of the engineering choices made at the time, and the original design of MySQL had a low probability of causing problems if the relational database design specifications were strictly followed, and only when the deleted primary key was referenced by an external system would it affect data consistency, but the differences in how it is used today increase the error MySQL has also made AUTO_INCREMENT persistent in 8.0 to avoid this problem.

Discontinuous primary keys in MySQL are another example of engineering to performance, sacrificing primary key continuity to support concurrent insertion of data, ultimately increasing the throughput of MySQL services. It is a very interesting process to re-understand the design decisions behind this problem today. Let’s briefly summarize this article here and get back to the question of the day - why MySQL’s self-incrementing primary keys are not monotonic or sequential.

  • MySQL stored AUTO_INCREMENT counters in memory before MySQL version 5.7, which would be reset after instance restart based on data in the table, and could result in duplicate primary keys when restarting after deleting records, a problem solved in version 8.0 using redo logging to ensure monotonicity of primary keys.
  • MySQL does not use transaction locks when inserting data to get AUTO_INCREMENT, but will use mutually exclusive locks, and concurrent insert transactions may have partial field conflicts that lead to insert failures, and you need to execute insert statements serially if you want to ensure the continuity of primary keys.

To conclude, let’s look at some more open-ended related questions, and interested readers can think carefully about the following.

  • How do MyISAM and other storage engines store AUTO_INCREMENT counters?
  • What are auto_increment_increment and auto_increment_offset used for in MySQL?