45 What to Do When Auto_increment ID is Used Up #

In MySQL, there are many auto-incremented IDs, each of which is defined with an initial value and increments by a certain step. Although natural numbers have no upper limit, in computer systems, the upper limit is determined by the length of bytes used to represent the number. For example, an unsigned integer is typically 4 bytes long, with an upper limit of 2^32-1.

Since auto-incremented IDs have an upper limit, it is possible for them to be used up. But what happens when the auto-incremented ID is used up?

In this article, we will explore several types of auto-incremented IDs in MySQL and analyze what happens when their values reach the upper limit.

Auto-incremented ID in table definition #

When it comes to auto-incremented IDs, the first thing that comes to mind is the auto-incremented field in the table structure definition, which I introduced in my previous article [Why is the auto-incremented primary key not consecutive?].

For the auto-incremented ID defined in the table, the logic when it reaches the upper limit is that the value remains unchanged when requesting the next ID.

We can verify this with the following sequence of statements:

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
// Successfully inserted the row with ID 4294967295
show create table t;
/* CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/
 
insert into t values(null);
// Duplicate entry '4294967295' for key 'PRIMARY'

As we can see, after the first insert statement successfully inserts the data, the AUTO_INCREMENT value of the table remains unchanged (still 4294967295). This results in the second insert statement obtaining the same auto-incremented ID value, and when attempting to execute the insert statement again, a primary key conflict error is raised.

232-1 (4294967295) is not a particularly large number, and for a table that frequently inserts and deletes data, it is possible to exhaust the range of auto-incremented IDs. Therefore, when creating a table, you should consider whether it may reach this upper limit. If there is a possibility, it is recommended to create the ID field as an 8-byte bigint unsigned.

InnoDB system-generated row_id #

If you create an InnoDB table without specifying a primary key, InnoDB will create an invisible row_id for you, which is 6 bytes long. InnoDB maintains a global dict_sys.row_id value, and for each row inserted into an InnoDB table without a primary key, it assigns the current dict_sys.row_id value as the row_id for the inserted row and increments the dict_sys.row_id value by 1.

In reality, the row_id is an 8-byte unsigned integer (bigint unsigned). However, when designing InnoDB, only 6 bytes were allocated for the row_id, and when the value is written to the data table, only the last 6 bytes are stored. So, the values of row_id written to the data table have two characteristics:

1. The range of row_id values written to the table is from 0 to 2^48-1.
2. When dict_sys.row_id reaches 2^48, if there is another insert operation that requires a new row_id, when we take the last 6 bytes of the new row_id, the value will be 0.

In other words, the row_id values written to the table start from 0 and range up to 2^48-1. Once the upper limit is reached, the next value will be 0, and the cycle continues.

Of course, 2^48-1 itself is already a very large value, but if a MySQL instance runs for a long enough time, it is still possible to reach this upper limit. In the logical design of InnoDB, when a row_id value of N is assigned, if there is already a row with row_id=N in the table, the new row being inserted will replace the existing row.

To verify this conclusion, you can use gdb to modify the system’s auto-incremented row_id. Please note that using gdb to modify variables is only for reproducing the problem and should only be done in a testing environment.

Figure 1: Sequence to verify row_id being used up.

Figure 2 Verification of row_id exhaustion effect

As we can see, after I set dict_sys.row_id to 248 using gdb, the inserted row with a=2 appears as the first row in table t because its row_id is 0. The subsequent inserted row with a=3 replaces the previous row with a=1 because its row_id is also 1.

From this perspective, we should still actively create auto-increment primary keys in InnoDB tables. This is because when the auto-increment id of a table reaches its limit and a data insertion reports a primary key conflict error, it is more acceptable.

After all, overwriting data means data loss, affecting data reliability, while reporting a primary key conflict means insertion failure, affecting availability. Generally, reliability takes priority over availability.

Xid #

In the 15th article [“Answers to Questions (1): Log and Index Related Questions”], I introduced the redo log and binlog, and mentioned their common field called Xid. It is used to correspond to transactions in MySQL.

So, how is Xid generated internally in MySQL?

MySQL maintains a global variable called global_query_id, and each time a statement is executed, it assigns its value to Query_id, and then increments this variable by 1. If the current statement is the first statement executed in the transaction, MySQL also assigns the Query_id value to the Xid of the transaction.

global_query_id is a purely memory variable that resets to zero after a restart. Therefore, in the same database instance, Xids of different transactions may also be the same.

However, MySQL generates new binlog files after a restart, which ensures that within the same binlog file, Xids are always unique.

Although a restart of MySQL does not result in two identical Xids within the same binlog, if global_query_id reaches its limit, it continues counting from 0. In theory, it is possible to have identical Xids within the same binlog in this case.

Since global_query_id is defined as 8 bytes in length, the upper limit of this auto-increment value is 2^64-1. To meet this condition, the following process is necessary:

1. Execute a transaction, assuming the Xid is A.
2. Execute 2^64-1 query statements consecutively to let global_query_id return to A.
3. Start another transaction, whose Xid is also A.

However, the value of 2^64-1 is too large, so large that you can consider this scenario to be only theoretical.

Innodb trx_id #

Xid and InnoDB’s trx_id are two concepts that are easily confused.

Xid is maintained by the server layer. InnoDB uses the Xid internally to associate InnoDB transactions with the server. However, InnoDB maintains its own trx_id separately.

In fact, you should be very familiar with this trx_id. It is the transaction id used when we discussed transaction visibility in the 8th article [“Isolation or No Isolation?”].

InnoDB maintains a global variable called max_trx_id. Each time a new trx_id needs to be allocated, it gets the current value of max_trx_id and increments max_trx_id by 1.

The core idea of InnoDB data visibility is that each row of data records the trx_id that updated it. When a transaction reads a row of data, the method for determining the visibility of the data is to compare the transaction’s consistency view with the trx_id of that row.

For ongoing transactions, you can see the trx_id from the information_schema.innodb_trx table.

The exercise I left for you at the end of the previous article is about the trx_id obtained from the innodb_trx table. Now, let’s take a look at a transaction scene together:

Figure 3 Trx_id of a transaction In session B, the second field trx_mysql_thread_id retrieved from the innodb_trx table is the thread ID. Displaying the thread ID is to illustrate that the thread ID corresponding to the transactions seen in these two queries is 5, which is the thread where session A is located.

As you can see, the trx_id displayed at T2 is a very large number; the trx_id displayed at T4 is 1289, which appears to be a relatively normal number. What is the reason for this?

In fact, at T1, session A has not yet been involved in any updates and is a read-only transaction. For read-only transactions, InnoDB does not assign a trx_id. In other words:

1. At T1, the value of trx_id is actually 0. The large number you see is just for display. I will tell you more about the logic behind generating this data shortly.
2. It is not until session A executes the insert statement at T3 that InnoDB actually assigns a trx_id. Therefore, at T4, the value of trx_id obtained by session B is 1289.

It is worth noting that in addition to obvious modification statements, if you add FOR UPDATE after a select statement, the transaction is not a read-only transaction either.

In the comment section of the previous article, a student pointed out that during the experiment, the increment was greater than 1. This is because:

1. In addition to the transaction itself, update and delete statements also involve marking and deleting old data, that is, putting the data in the purge queue for subsequent physical deletion. This operation also increments max_trx_id by 1, so at least 2 is added within a transaction.
2. InnoDB’s background operations, such as indexing statistics for tables, also start internal transactions. Therefore, you may find that the trx_id value does not increase by 1.

Then, why is the large number displayed at T2 generated?

In fact, this number is calculated by the system temporarily each time a query is made. The algorithm is to convert the pointer address of the trx variable of the current transaction to an integer and then add 248. Using this algorithm ensures the following two points:

1. Because within the execution period of the same read-only transaction, its pointer address does not change, the trx_id retrieved from either the innodb_trx table or the innodb_locks table will be the same.
2. If there are multiple concurrent read-only transactions, the pointer addresses of each transaction’s trx variable are definitely different. This means that different concurrent read-only transactions will have different trx_id values.

Then, why add 248?

Adding 248 to the displayed value is to ensure that the trx_id value of a read-only transaction is larger and distinguishable from the trx_id of read-write transactions under normal circumstances. However, just like row_id, trx_id has a fixed length of 8 bytes. Therefore, theoretically, it is possible for a read-write transaction and a read-only transaction to have the same trx_id. However, this probability is very low, and it does not have any substantial harm, so it can be ignored.

Another question is, what are the advantages of not assigning trx_id to read-only transactions?

• One advantage is that it reduces the size of the active transaction array in the transaction view. Because currently running read-only transactions do not affect the visibility judgment of data, when creating a consistent view of the transaction, InnoDB only needs to copy the trx_id of read-write transactions.
• Another advantage is that it reduces the number of times trx_id is requested. In InnoDB, even if you only execute a normal select statement, it correspondingly has a read-only transaction. Therefore, after optimizing read-only transactions, normal query statements do not need to request trx_id, greatly reducing locking conflicts for concurrent transactions requesting trx_id.

Since trx_id is not assigned to read-only transactions, one natural result is that the increment rate of trx_id becomes slower.

However, max_trx_id will be persistently stored and will not be reset to 0 even after restarting. Therefore, in theory, as long as a MySQL server runs long enough, it is possible for max_trx_id to reach the upper limit of 248-1 and then start from 0.

Once this state is reached, MySQL will continue to encounter a dirty read bug. Let’s reproduce this bug.

First, we need to change the current max_trx_id to 248-1. Note: This case uses the repeatable read isolation level. The specific operation process is as follows:

Figure 4 Reproducing the dirty read

Since we have set the system’s max_trx_id to 248-1, at the moment the transaction TA in session A starts, the low watermark is 248-1.

At T2, the transaction ID of the first update statement executed by session B is 248-1, and the transaction ID of the second update statement is now 0, resulting in a data version with trx_id of 0 after this update statement is executed. At time T3, when session A executes the select statement, the visibility check determines that the trx_id of data version c=3 is less than the low water mark of transaction TA, so it considers this data to be visible.

However, this is a dirty read.

Since the low water mark value keeps increasing and transaction ids start counting from 0, this causes all queries to have dirty reads after this moment.

Furthermore, when MySQL is restarted, max_trx_id is not cleared to 0, which means that this bug still exists even after restarting MySQL.

So, does this bug only exist in theory?

Assuming the TPS (transactions per second) of a MySQL instance is 500,000, if this pressure continues, this situation will occur after 17.8 years. If the TPS is higher, this duration will naturally be shorter. However, it is unlikely that any instances have reached this limit since MySQL became popular. Nevertheless, this bug will inevitably occur as long as the MySQL instance has been running for a sufficiently long time.

Of course, the more realistic significance of this example is that it deepens our understanding of low water mark and data visibility. You can also take this opportunity to review the relevant content in the 8th article [Isolation or Non-Isolation?].

thread_id #

Next, let’s take a look at thread_id. In fact, thread_id is the most common incrementing ID in MySQL. Usually, when we check various situations, the first column in the show processlist is the thread_id.

The logic of thread_id is easy to understand: the system saves a global variable thread_id_counter, and every time a new connection is established, thread_id_counter is assigned to the thread variable of this new connection.

The size of thread_id_counter is defined as 4 bytes, so after reaching 232-1, it will reset to 0 and continue to increase. However, you won’t see two identical thread_ids in the show processlist.

This is because MySQL has designed a logic for unique arrays. When allocating thread_id to a new thread, the logical code is as follows:

do {
  new_id = thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

This code logic is simple and elegant, and I believe you can understand it at a glance.

Conclusion #

In today’s article, I introduced the behavior of different incrementing IDs in MySQL when they reach their limits. As a database system that may need to provide services 24/7, considering these boundaries is essential.

Each incrementing ID has its own application scenarios, and their behavior after reaching the limit is different:

1. After the auto-incrementing ID of a table reaches its limit, its value does not change when reapplying, resulting in a primary key conflict error when inserting data.
2. After the row_id reaches its limit, it returns to 0 and starts increasing again. If duplicate row_ids occur, the data written last will overwrite the previous data.
3. Xid only needs to avoid duplicate values within the same binlog file. Although duplicate values may theoretically occur, the probability is extremely low and can be neglected.
4. InnoDB’s max_trx_id increment value is saved each time MySQL restarts, so the example of dirty reads mentioned in the article is a bug that will always occur. Fortunately, we still have plenty of time before concluding this bug.
5. thread_id is the most common incrementing ID we use, and it also has the best logic for handling it.

Of course, there are other incrementing IDs in MySQL, such as table_id, binlog file serial number, etc., which I leave for you to explore and verify.

Different incrementing IDs have different upper limit values, and the size of the upper limit value depends on the declared type length. The upper limit ID declared in our column is 45, so this article is also our last technical article.

Since there is no next ID, there is no after-class question. Today, let’s switch to a lighter topic and share your thoughts after reading this column.

This “thought” can be a change in your understanding of certain knowledge points before and after reading the column, or it can be a good method you have accumulated for learning column articles, or it can be criticism or expectations for the future.