Understanding MySQL Auto‑Increment IDs, InnoDB row_id, Xid, trx_id, thread_id and Redis External Keys

# Auto‑increment ID

If you have used MySQL you know about auto‑increment primary keys, which start from an initial value and increase by a defined step (default 1). Although natural numbers are unbounded, when a column length is specified the ID has an upper limit.

# InnoDB System Auto‑increment row_id

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

row_id stored range: 0 to 2^48‑1

When dict_sys.row_id = 2^48, the next allocation yields 0 after truncating to 6 bytes.

# Xid

When MySQL’s redo log and binlog work together they share a field called Xid , which identifies a transaction. A global variable global_query_id is assigned to Query_id for each statement; the first statement of a transaction also copies this value to the transaction’s Xid . The variable resets after a server restart, so Xid values can repeat across restarts.

# InnoDB trx_id

InnoDB maintains a global max_trx_id . Each time a new transaction needs an ID, it receives the current value and the counter is incremented. Visibility of rows is determined by comparing a row’s stored trx_id with the transaction’s snapshot, which can lead to non‑atomic IDs and possible duplicates under dirty‑read scenarios.

# thread_id

The most common auto‑increment ID in MySQL is the connection thread_id , shown as the first column of SHOW PROCESSLIST . A global thread_id_counter (4‑byte) is assigned to each new connection; after reaching 2^32‑1 it wraps to 0, similar to row_id .

# Redis Auto‑increment Primary Key

External auto‑increment keys can be generated with Redis, which provides atomic, thread‑safe increments. By combining a timestamp (e.g., 8‑digit date) with an incrementing counter (e.g., 12 digits), you can create a 20‑character key with very low collision probability, even under high concurrency.

# Summary

Different MySQL auto‑increment identifiers have distinct overflow behaviors: table primary keys stop increasing and cause duplicate‑key errors; row_id wraps to 0 and can overwrite rows; Xid repeats only across binlog files; trx_id persists across restarts but may exhibit dirty‑read bugs; thread_id wraps after 2^32‑1; Redis‑based keys offer millisecond‑level uniqueness with negligible collision risk. Choose the appropriate ID strategy based on your application’s lifespan and data volume.