Example of MySQL auto-increment ID exhaustion

Display Definition ID

When the auto-increment ID defined in the table reaches the upper limit, the value obtained when applying for the next ID remains unchanged

-- (2^32-1) = 4,294,967,295
-- It is recommended to use BIGINT UNSIGNED
CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=4294967295;
INSERT INTO t VALUES (null);

-- AUTO_INCREMENT has not changed mysql> SHOW CREATE TABLE t;
+-------+------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------+
| t | CREATE TABLE `t` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------+

mysql> INSERT INTO t VALUES (null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

InnoDB row_id

1. If the created InnoDB table does not specify a primary key, InnoDB will create an invisible row_id with a length of 6 bytes.

2. InnoDB maintains a global dict_sys.row_id value for all InnoDB tables without primary keys.

• The current dict_sys.row_id value is used as the row_id to insert the data, and then the value of dict_sys.row_id + 1

3. In the code implementation, row_id is an 8-Byte BIGINT UNSIGNED

• However, when InnoDB was designed, only 6 bytes of space were reserved for row_id, and only the last 6 bytes were stored when writing to the data table.
• The value range of row_id is: 0 ~ 2^48-1
• After reaching the upper limit, the next value is 0

4. In InnoDB, after applying for row_id=N, write this row of data into the table

• If there is already a row with row_id=N in the table, the newly written row will overwrite the original row.

5. It is recommended to create an auto-increment primary key

• When the table's auto-increment ID reaches the upper limit, a primary key conflict error will be reported when inserting data, which affects availability.
• Overwriting data means data loss, which affects reliability.
• Generally speaking, reliability is better than availability

XID

1. When redolog and binlog are used together, there is a common field XID, which corresponds to a transaction

2. Generate logic

• MySQL maintains a global variable global_query_id internally
• Each time a statement is executed, global_query_id is assigned to Query_id, and then global_query_id+1
• If the current statement is the first statement executed by this transaction, assign Query_id to the XID of this transaction.

3. global_query_id is a pure memory variable and is cleared after restart

• Therefore, in the same database instance, the XIDs of different transactions may be the same.
• After MySQL restarts, a new binlog will be regenerated
  • Guarantee: XID is unique in the same binlog file
• When global_query_id reaches the upper limit, it will continue to count from 0
  • Therefore, in theory, the same XID will appear in the same binlog, but the probability is extremely low.

4. global_query_id is 8 bytes, with an upper limit of 2^64-1

• Execute a transaction, assuming XID is A
• Next, execute the query statement 2^64 times, and let global_query_id return to A
• Start another transaction, the XID of this transaction is also A

InnoDB trx_id

1. XID is maintained by the Server layer

2. InnoDB uses trx_id internally to associate InnoDB transactions with the server layer.

3. InnoDB maintains a global variable max_trx_id internally

• Each time you need to apply for a new trx_id, get the current value of max_trx_id, then max_trx_id+1

4. The core idea of ​​InnoDB data visibility

• Each row of data records the trx_id that updated it
• When a transaction reads a row of data, the method to determine data visibility
  • Compare the transaction's consistent view with the trx_id of this row of data

5. For the transaction being executed, you can see the trx_id of the transaction through information_schema.innodb_trx

Operation sequence

-- At T2, mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 281479812572992 | 30 |
+-----------------+---------------------+

-- At T4, mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 7417540 | 30 |
+-----------------+---------------------+

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 344051 | Waiting on empty queue | NULL |
| 30 | root | localhost | test | Sleep | 274 | | NULL |
| 31 | root | localhost | information_schema | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+

1. trx_mysql_thread_id=30 is the thread ID, that is, the thread where session A is located

2. At T1, the value of trx_id is actually 0, and the large value is only for display (different from ordinary read and write transactions)

3. At time T2, trx_id is a large number because at time T1, session A did not involve any update operations and was a read-only transaction.

• For read-only transactions, InnoDB does not assign a trx_id

4. When session A executes the INSERT statement at time T3, InnoDB actually allocates trx_id

Read-only transactions

1. At time T2 above, the large trx_id is temporarily calculated by the system

• Convert the pointer address of the trx variable of the current transaction into an integer and add 2^48

2. During the execution of the same read-only transaction, its pointer address will not change

• Whether in the innodb_trx or innodb_locks table, the trx_id found for the same read-only transaction is the same

3. If there are multiple parallel read-only transactions, the pointer address of the trx variable of each transaction must be different

• Different concurrent read-only transactions have different trx_ids.

4. The purpose of adding 2^48 is to ensure that the trx_id value displayed by read-only transactions is relatively large, which is used to distinguish ordinary read-write transactions.

5. The logic of trx_id is similar to row_id, and the length is defined as 8 Bytes

• In theory, it is possible for a read-write transaction to display the same trx_id as a read-only transaction.
• But the probability is extremely low and there is no real harm

6. Benefits of not allocating trx_id to read-only transactions

• You can reduce the size of the active array in the transaction view
  • The currently running read-only transaction does not affect the visibility of the data.
  • Therefore, when creating a consistent view of a transaction, you only need to copy the trx_id of the read and write transaction.
• Can reduce the number of trx_id applications
  • In InnoDB, even if you execute only one ordinary SELECT statement, it will correspond to a read-only transaction during the execution process.
  • If ordinary query statements do not apply for trx_id, lock conflicts caused by concurrent transactions applying for trx_id can be greatly reduced.
  • Since read-only transactions do not allocate trx_id, the increase rate of trx_id will be slower

7. max_trx_id will be persistently stored and will not be reset to 0 after restart. It will only be reset to 0 after reaching the upper limit of 2^48-1.

thread_id

1. The first column of SHOW PROCESSLIST is thread_id

2. The system saves an environment variable thread_id_counter

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

3. thread_id_counter is defined as 4 bytes, so it will be reset to 0 after reaching 2^32-1

• But you will not see two identical thread_ids in SHOW PROCESSLIST
• Because MySQL has designed a unique array logic to assign thread_id to new threads, the logic code is as follows

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

References

"MySQL Practice 45 Lectures"

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.