What to do if the online MySQL auto-increment ID is exhausted

MySQL's auto-increment IDs all define an initial value and then continuously increase the step size. Although there is no upper limit to natural numbers, the byte length used to represent the number is defined, so there is an upper limit to computer storage. For example, an unsigned int is 4 bytes, and its upper limit is 2^32 - 1 . What happens when the auto-increment ID runs out?

Table definition auto-increment id

The logic of the table definition after the auto-increment value reaches the upper limit is: when applying for the next ID, the value obtained remains unchanged.

mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)

mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//Successfully inserted a row of 4294967295
mysql> insert into t values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

After the first insert succeeds, the AUTO_INCREMENT of the table is still 4294967295, causing the second insert to get the same auto-increment id value and then try to execute the insert statement, resulting in a primary key conflict.

2^32 - 1 (4294967295) is not a particularly large number, and a table with frequent insertion and deletion of data may use it up. When creating a table, you need to consider whether your table is likely to reach the upper limit. If so, it should be created as 8-byte bigint unsigned.

InnoDB system automatically increases row_id

If you create an InnoDB table without specifying a primary key, InnoDB automatically creates an invisible, 6-byte row_id . InnoDB maintains a global dict_sys->row_id value

For all InnoDB tables without a primary key, each time a row of data is inserted, the current dict_sys->row_id is used as the row_id of the data to be inserted, and then dict_sys->row_id is increased by 1.

When the code is implemented, row_id is an unsigned long integer (bigint unsigned) with a length of 8 bytes. However, when InnoDB was designed, the length of row_id was only 6 bytes, so only the last 6 bytes were placed when it was written to the data table. Therefore, the value of row_id that can be written to the data table has two characteristics:

• The value range of row_id written into the table is from 0 to 2^48 - 1
• When dict_sys.row_id = 2^48, if there is another data insertion behavior, row_id will be requested. After obtaining it, the last 6 bytes will be 0.

That is, the row_id written into the table ranges from 0~2^48 - 1 . After reaching the upper limit, the next value is 0, and then the cycle continues.
2^48 - 1 is already very large, but if a MySQL instance lives for a long time, it is still possible to reach the upper limit.
In InnoDB, after applying for row_id=N, this row of data is written to the table; if a row with row_id=N already exists in the table, the newly written row will overwrite the original row.

Verify this conclusion: modify the system's auto-increment row_id through gdb. GDB is used to facilitate the reproduction of problems and can only be used in a test environment.

row_id is used up verification sequence

row_id

Verification of the effect after use

It can be seen that after I use gdb to set dict_sys.row_id to 2^48, inserting a=2 will appear in the first row of table t because the row_id of this value is 0.
Then a=3 is inserted. Since row_id=1, the previous row with a=1 is overwritten, because the row_id of a=1 is also 1.

Therefore, you should actively create an auto-increment primary key in the InnoDB table: when the table's auto-increment ID reaches the upper limit, a primary key conflict error will be reported when inserting data.
After all, overwriting data means data loss, which affects data reliability; reporting primary key conflicts and insertion failures affects availability. Generally reliability is preferred over availability.

Xid

The redo log and binlog have a common field Xid, which is used to correspond to transactions. How is Xid generated inside MySQL?

MySQL maintains a global variable global_query_id internally

Each time the statement is executed, it is assigned to query_id and then the variable is incremented by 1:

If the current statement is the first statement executed by the transaction, MySQL will also assign query_id to the Xid of the transaction:

global_query_id is a pure memory variable and will be cleared after restart. Therefore, the Xids of different transactions in the same DB instance may be the same.

However, after MySQL is restarted, a new binlog file will be regenerated, which ensures that the Xid in the same binlog file is unique.

Although a MySQL restart will not cause two identical Xids to appear in the same binlog, if global_query_id reaches the upper limit, it will continue to count from 0. Theoretically, the same Xid may still appear in the same binlog.

Because global_query_id 8 bytes, the upper limit is 2^64 - 1 . For this to happen, the following conditions must be met:

• Execute a transaction, assuming Xid is A
• Next, execute the query statement 2^64 times, and let global_query_id return to A
• 2^64 is too large, and this possibility only exists in theory.
• Start another transaction, the Xid of this transaction is also A

Innodb trx_id

Xid is maintained by the server layer
InnoDB uses Xid internally to associate InnoDB transactions with servers.

But InnoDB's own trx_id is a transaction id maintained separately.

InnoDB maintains a max_trx_id global variable internally. Every time a new trx_id is required, the current value of max_trx_id is obtained and then max_trx_id is increased by 1.

The core idea of ​​InnoDB data visibility

Each row of data records the trx_id that updates it. When a transaction reads a row of data, it determines whether the data is visible by comparing the transaction's consistent view with the trx_id of the row of data.

For the transaction being executed, you can see the transaction's trx_id from the information_schema.innodb_trx table.

See the following example: trx_id of the transaction

Execution record of S2:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 421972504382792 | 70 |
+-----------------+---------------------+
1 row in set (0.00 sec)

mysql> select trx_id, trx_mysql_thread_id from innodb_trx;
+---------+---------------------+
| trx_id | trx_mysql_thread_id |
+---------+---------------------+
| 1355623 | 70 |
+---------+---------------------+
1 row in set (0.01 sec)

S2 retrieves these two fields from the innodb_trx table. The second field trx_mysql_thread_id is the thread id. The thread ID is displayed to illustrate that the transactions seen in these two queries correspond to thread ID 5, which is the thread where S1 is located.

The trx_id displayed at t2 is a very large number; the trx_id displayed at t4 is 1289, which looks like a relatively normal number. Why is this?
At t1, S1 has not yet been updated and is a read-only transaction. For read-only transactions, InnoDB does not assign trx_id:

• At t1, the value of trx_id is 0. This large number is only used for display.
• InnoDB does not actually assign trx_id until S1 executes the insert at t3. Therefore, at t4, S2 finds that the value of trx_id is 1289.

In addition to the obvious modification statements, if for update is added after the select statement, it is not a read-only transaction.

• 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 to wait for subsequent physical deletion. This operation will also increase max_trx_id by 1, so at least 2 are added in a transaction.
• InnoDB's background operations, such as table index information statistics, will also start internal transactions, so you may see that the trx_id value does not increase by 1.

Where did the large number found at t2 come from?
Each time a query is made, the system temporarily calculates:當前事務的trx變量的指針地址轉成整數，再加上248

This ensures that:

• Because the pointer address of the same read-only transaction does not change during execution, the trx_id found for the same read-only transaction will be the same whether in the innodb_trx or innodb_locks table.
• If there are parallel read-only transactions, the pointer address of the trx variable of each transaction must be different. In this way, the trx_id found for different concurrent read-only transactions is different.

Why add 248?

Ensure that the trx_id value displayed for read-only transactions is relatively large, so that it can be distinguished from the id of read-write transactions under normal circumstances. However, the logic of trx_id is similar to that of row_id and is defined as 8 bytes.
Theoretically, it is still possible for a read-write transaction to display the same trx_id as a read-only transaction. But the probability is very low and there is no real harm, so don't worry.

Why are read-only transactions not assigned trx_id?

• Reduce the size of the active transactions array in the transaction view. Because the currently running read-only transaction does not affect the visibility of the data. Therefore, when creating a consistent view of a transaction, InnoDB only needs to copy the trx_id of the read-write transaction.
• Reduce the number of trx_id applications. When InnoDB executes a normal select statement, it also corresponds to a read-only transaction. Therefore, after read-only transactions are optimized, ordinary query statements do not need to apply for trx_id, which greatly reduces the lock conflicts of concurrent transactions applying for trx_id.

Since read-only transactions do not allocate trx_id, the growth rate of trx_id obviously slows down.
However, max_trx_id is stored persistently and will not be reset to 0 after a restart . Theoretically, as long as a MySQL instance runs long enough, max_trx_id may reach 2^48 - 1 and then start the cycle from 0.

Once this state is reached, MySQL will continue to have a dirty read bug:
First, modify the current max_trx_id to 2^48 - 1. This is a repeatable read.

Reproduce dirty read

Because the system's max_trx_id is set to 2^48 - 1, the low watermark of transaction TA started in session A is 2^48 - 1.

At t2:

• Session B executes the first update事務id=2^48 - 1
• The second transaction id is 0, and the data version generated after this update is executed has trx_id=0

At t3:

Session A performs select visibility judgment: the trx_id (0) of the data version c=3 is less than the low watermark of transaction TA (2^48 - 1), so the data is considered visible.

But this is a dirty read.
Since the low water mark value will continue to increase and the transaction ID starts counting from 0, all queries in the system will have dirty reads after this moment.

And max_trx_id will not be cleared to 0 when MySQL is restarted, that is, this bug still exists after restarting MySQL. Does this bug only exist in theory?
Assuming that the TPS of a MySQL instance is 50w, if it continues like this, this situation will occur in 17.8 years. But from the time MySQL really became popular to now, I'm afraid no instance has ever reached this limit. However, as long as the MySQL instance service time is long enough, this bug will inevitably appear.

This can also provide a deeper understanding of low water marks and data visibility.

thread_id

The system saves a global variable thread_id_counter

Each time a new connection is created, thread_id_counter is assigned to the thread variable new_id of the new connection.

thread_id_counter is defined as 4 bytes, so when it reaches 2^32 - 1 , it will be reset to 0 and continue to increase.

But you will not see two identical thread_id in show processlist . Because MySQL uses a unique array

The logic of assigning thread_id to a new thread is:

Summarize

Each auto-increment ID has its own application scenario and behaves differently after reaching the upper limit:

• When the table's auto-increment ID reaches the upper limit, its value will not change when it is applied again, which will result in a primary key conflict error when continuing to insert data.
• When row_id reaches the upper limit, it will return to 0 and then increment again. If the same row_id appears, the later written data will overwrite the previous data
• Xid only needs to avoid duplicate values ​​in the same binlog file. Although there will be duplicate values ​​in theory, the probability is extremely small and can be ignored.
• InnoDB's max_trx_id increment value is saved every time MySQL is restarted, so the dirty read example mentioned in our article is a bug that is bound to occur. Fortunately, we still have plenty of time.
  

This is the end of this article about what to do when the online MySQL auto-increment ID is exhausted. For more information about MySQL auto-increment ID exhaustion, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!