Understanding MySQL Auto‑Increment Limits, InnoDB row_id, Xid, trx_id, and Thread ID Behaviors

MySQL's auto‑increment primary key uses a fixed‑size unsigned integer; when the maximum value (e.g., 2^32‑1 for INT UNSIGNED) is reached the next insert returns the same value, causing a duplicate‑key error.

InnoDB tables without an explicit primary key get an invisible 6‑byte row_id generated from a global dict_sys->row_id. The row_id range is 0‑2^48‑1; after reaching the limit it wraps to 0, and rows with the same row_id overwrite previous rows.

The server maintains a global global_query_id that is assigned to each statement and, for the first statement of a transaction, also becomes the transaction’s Xid. Xid must be unique within a binlog file, but the 64‑bit counter can theoretically wrap.

InnoDB also tracks a separate trx_id for each read‑write transaction using a global max_trx_id. Read‑only transactions do not allocate a trx_id, which reduces overhead and keeps the visible‑transaction list smaller.

Thread identifiers are generated from a 4‑byte thread_id_counter that wraps at 2^32‑1. MySQL ensures uniqueness by keeping an internal array of active thread IDs.

Example SQL used to illustrate the auto‑increment limit:

mysql> create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
mysql> insert into t values(null);
-- first insert succeeds, id = 4294967295
mysql> insert into t values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

The article demonstrates these behaviours with SQL commands and gdb manipulation, and warns that long‑running MySQL instances could eventually encounter wrap‑around bugs that affect data visibility and cause dirty reads.