Understanding MySQL Auto‑Increment IDs and Their Limits

Auto‑increment ID

When you use MySQL, the auto‑increment primary key starts from an initial value and increases by a defined step (default 1). If a column length is specified, the ID has an upper bound; once this limit is reached, further inserts will cause primary‑key conflicts.

InnoDB System Auto‑increment row_id

If an InnoDB table has no explicit primary key, InnoDB creates an invisible 6‑byte row_id. Internally it is stored as an 8‑byte unsigned bigint, but only the lower 6 bytes are written to the table, giving a range of 0 to 2^48‑1. When the counter reaches 2^48, it wraps to 0, potentially overwriting existing rows.

Xid

MySQL’s redo log and binlog share a field called Xid that identifies a transaction. A global variable global_query_id is assigned to Query_id for each statement and incremented. The first statement of a transaction also copies this value to the transaction’s Xid. After a server restart the counter resets, so different transactions may reuse the same Xid, but the chance of conflict is negligible.

InnoDB trx_id

InnoDB maintains a global max_trx_id. Each new transaction receives the current value and then increments the counter. Every row stores the trx_id that created it; visibility is determined by comparing a transaction’s snapshot with the row’s trx_id. Because the ID is not strictly atomic, duplicate values can occur, leading to dirty‑read anomalies.

thread_id

The most common auto‑increment identifier in MySQL is the connection thread_id, shown as the first column in SHOW PROCESSLIST. A global thread_id_counter (4 bytes) is assigned to each new connection. After reaching 2^32‑1 it wraps to 0, which can cause record overwrites similar to row_id.

Redis‑based External Auto‑increment Keys

External keys can be generated with Redis, which provides atomic increments and is thread‑safe under high concurrency. A typical scheme combines a date component (e.g., YYYYMMDD) with a numeric counter, yielding a 20‑character key. The probability of duplicate values within the same millisecond is extremely low.

Summary

Different MySQL auto‑increment identifiers have distinct behaviors when they reach their limits: table primary keys stop increasing and cause conflicts; row_id wraps to zero and may overwrite data; Xid collisions are rare; trx_id can exhibit dirty‑read bugs; thread_id wraps similarly to row_id. External solutions like Redis offer a practical alternative with negligible collision risk.