Understanding MySQL Auto‑Increment Limits, InnoDB row_id, Xid, trx_id and thread_id

MySQL defines auto‑increment IDs with an initial value and a step; the storage size of the column (e.g., INT UNSIGNED uses 4 bytes) limits the maximum value to 2^32‑1 (4294967295). When this limit is reached, further INSERTs return the same ID, causing primary‑key conflicts.

Example table creation and insertion:

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> insert into t values (null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

To avoid this, tables that may exhaust the 4‑byte range should use BIGINT UNSIGNED for the auto‑increment column.

InnoDB also creates an invisible 6‑byte row_id for tables without an explicit primary key. The global dict_sys->row_id increments with each row insertion. When row_id reaches its 2^48‑1 limit, it wraps to 0, and subsequent rows can overwrite earlier rows that share the same row_id.

Verification using gdb to set dict_sys.row_id to 2^48 shows that new inserts receive a row_id of 0, causing data overwrites.

MySQL also maintains a global global_query_id used to generate transaction Xids. Each statement increments this counter; the first statement of a transaction assigns its value to the transaction's Xid. Because global_query_id is in memory, it resets on server restart, but Xids are unique within a single binlog file.

InnoDB tracks a separate max_trx_id for transaction IDs (trx_id). Read‑only transactions do not allocate a trx_id, reducing the size of the active‑transaction array and lock contention. Write transactions increment max_trx_id by at least two (for the row version and purge queue). When max_trx_id reaches 2^48‑1, it wraps to 0, potentially causing a dirty‑read bug where older rows become visible to newer transactions.

The server also assigns a 4‑byte thread_id to each client connection, incrementing a global counter. After reaching 2^32‑1 the counter wraps to 0, but MySQL ensures uniqueness in the process list by using an internal array.

In summary, different auto‑increment mechanisms in MySQL have distinct overflow behaviors: table primary keys stop increasing and raise duplicate‑key errors; InnoDB row_id wraps and can overwrite data; Xid uniqueness is guaranteed per binlog; trx_id can wrap after long uptime, potentially exposing a theoretical dirty‑read issue; and thread_id also wraps but remains unique in the visible process list.