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

Table Auto‑Increment ID

MySQL defines an initial value and step for an AUTO_INCREMENT column, but the underlying unsigned integer type has a fixed byte size (e.g., INT UNSIGNED is 4 bytes, max 2^32‑1). When the limit is reached, further INSERTs return the same maximum 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, tables that may exhaust INT UNSIGNED should use BIGINT UNSIGNED (8 bytes).

InnoDB System Auto‑Increment row_id

If an InnoDB table lacks an explicit primary key, InnoDB creates an invisible 6‑byte row_id . The global dict_sys->row_id is a 8‑byte unsigned integer, but only the lower 6 bytes are stored, giving a range 0 – 2^48‑1. When the limit is hit, the next value wraps to 0, causing later rows to overwrite earlier rows with the same row_id.

Verification using GDB to set dict_sys.row_id = 2^48 shows that subsequent inserts reuse row_id 0 and 1, overwriting previous rows.

Xid

Both the redo log and binlog contain an Xid that links a transaction to its log records. MySQL maintains a global global_query_id (8‑byte) that is assigned to query_id for each statement and, for the first statement of a transaction, also to the transaction’s Xid. The counter resets on server restart, so Xids are unique only within a single binlog file; theoretically they could repeat after 2^64 statements, but this is practically impossible.

InnoDB trx_id

InnoDB has its own transaction identifier ( trx_id) maintained by a global max_trx_id. Each new transaction receives the current max_trx_id value, then the counter is incremented. Read‑only transactions do not allocate a trx_id, reducing overhead and the size of the active‑transaction array.

When a row is updated, its stored trx_id is used together with the transaction’s consistency view to decide visibility.

Data Visibility Core Idea

Every row stores the trx_id of the transaction that created it. A reading transaction compares this value with its own view; if the row’s trx_id is less than the transaction’s low‑water‑mark, the row is visible.

Read‑only transactions skip trx_id allocation, so their low‑water‑mark can be much larger than the IDs of read‑write transactions, improving concurrency.

thread_id

MySQL assigns each new connection a thread_id from a global 4‑byte counter ( thread_id_counter). When the counter reaches 2^32‑1 it wraps to 0, but the server ensures uniqueness in the process list by using an internal array.

Summary

Different auto‑increment mechanisms have distinct behaviours at their limits:

Table primary‑key AUTO_INCREMENT stops increasing at its maximum and then raises duplicate‑key errors.

InnoDB hidden row_id wraps to 0, causing later rows to overwrite earlier ones with the same row_id. Xid only needs to be unique within a binlog file; collisions across restarts are theoretically possible but negligible. max_trx_id persists across restarts; if it ever reaches 2^48‑1 and wraps, a specific dirty‑read bug can appear, though it would require many years of continuous operation. thread_id also wraps at 2^32‑1 but remains unique in the active connection list.

Understanding these limits helps design schemas (e.g., using BIGINT UNSIGNED for primary keys) and avoid data loss or availability issues.