What Happens When MySQL Auto‑Increment IDs Reach Their Limits?

Table Auto‑Increment ID Behavior

MySQL tables define an auto‑increment column with an initial value and step. When the unsigned int (4‑byte) limit 2^32‑1 (4294967295) is reached, further INSERTs return the same value, causing duplicate‑key errors.

mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
Query OK, 0 rows affected
mysql> insert into t values(null);
Query OK, 1 row affected
mysql> insert into t values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

Therefore, for tables with high insert/delete rates, use BIGINT UNSIGNED (8‑byte) to avoid hitting the limit.

InnoDB System Row_id

If an InnoDB table has no explicit primary key, InnoDB creates an invisible 6‑byte row_id and maintains a global dict_sys->row_id counter.

The counter ranges from 0 to 2^48‑1. When it reaches 2^48, the next value wraps to 0, and subsequent inserts may reuse existing row_id values, causing later rows to overwrite earlier ones.

row_id stored in the table ranges from 0 to 2^48‑1.

When dict_sys.row_id = 2^48, the next allocated row_id becomes 0 (the low 6 bytes of the 8‑byte value).

Testing with gdb to set dict_sys.row_id to 2^48 shows that the next INSERT receives row_id 0, overwriting the previous row with the same row_id.

Thus, defining an explicit auto‑increment primary key prevents silent data loss; when the auto‑increment reaches its limit, MySQL reports a duplicate‑key error instead of overwriting data.

Xid Generation

Both redo log and binlog contain an Xid field that identifies a transaction. MySQL maintains a global global_query_id (8‑byte) which is assigned to query_id for each statement and, for the first statement of a transaction, also to the transaction’s Xid.

Because global_query_id is reset to 0 on server restart, different transactions in the same MySQL instance may share the same Xid, but within a single binlog file Xid values remain unique. The theoretical possibility of wrap‑around (2^64 statements) is practically impossible.

InnoDB trx_id

InnoDB also tracks a transaction‑specific trx_id (different from Xid). A global max_trx_id is incremented each time a new trx_id is allocated.

Read‑only transactions do not allocate a trx_id, reducing the size of the active‑transaction array and avoiding unnecessary lock contention.

When a transaction updates or deletes rows, InnoDB also increments max_trx_id for the purge operation, so trx_id values may not increase strictly by one.

Data Visibility and Dirty‑Read Bug

Each row stores the trx_id of the transaction that created it. A transaction’s visibility view compares its own low‑water‑mark with row trx_id values. Because read‑only transactions reuse the same trx_id pointer address (plus a constant 248), their displayed trx_id appears large, distinguishing them from write transactions.

If max_trx_id reaches 2^48‑1 and wraps to 0, the low‑water‑mark can become larger than newly allocated trx_id values, causing the system to consider newer rows visible to older transactions—a dirty‑read scenario that persists across restarts because max_trx_id is persisted.

Although reaching this limit would require an extremely long‑running MySQL instance (e.g., 50 000 TPS for 17 years), the bug is theoretically guaranteed to appear eventually.

Thread_id Counter

MySQL maintains a global thread_id_counter (4‑byte). Each new connection receives the current counter value as its thread_id, after which the counter increments. When the counter reaches 2^32‑1, it wraps to 0.

Duplicate thread_id values are avoided because MySQL stores active thread IDs in a unique array, ensuring they are not shown simultaneously in SHOW PROCESSLIST.

Summary

When a table’s auto‑increment reaches its limit, further INSERTs reuse the same value, causing duplicate‑key errors.

InnoDB row_id wraps to 0 after 2^48‑1, and rows with identical row_id overwrite each other.

Xid uniqueness is guaranteed only within a single binlog file; wrap‑around is practically negligible.

max_trx_id persists across restarts, so a theoretical dirty‑read bug can manifest after enough counter wrap‑around.

thread_id also wraps at 2^32‑1 but MySQL’s internal bookkeeping prevents visible duplicates.

(Copyright belongs to the original author, please delete if infringing)