Skip to content
MySQL Auto-Increment ID Exhaustion: Table IDs, row_id, Xid, trx_id, thread_id Limits Explained
Databases 17 min read

MySQL Auto-Increment ID Exhaustion: Table IDs, row_id, Xid, trx_id, thread_id Limits Explained

This article analyzes what happens when various MySQL auto-increment identifiers reach their maximum values, covering table auto_increment IDs (2^32-1), InnoDB row_id (2^48-1 wraparound causing data overwrites), Xid (2^64-1, resets on restart), trx_id (persistent max_trx_id, potential dirty reads at 2^48), and thread_id (2^32-1 with uniqueness enforcement).

Java Architect Handbook
Java Architect Handbook
Java Architect Handbook

Table Auto-Increment ID Exhaustion

When a table's auto-increment column reaches its maximum value, subsequent inserts fail with a duplicate key error. For an unsigned int (4 bytes), the limit is 2^32 - 1 (4,294,967,295). The article demonstrates this with a table created at auto_increment=4294967295:

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> show create table t;
| t | CREATE TABLE `t` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
1 row in set (0.00 sec)

// First insert succeeds with id 4294967295
mysql> insert into t values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't.PRIMARY'

After the first insert, AUTO_INCREMENT remains 4,294,967,295, so the next insert attempts the same value, causing a primary key conflict. The author notes that 2^32 - 1 is not extremely large; a table with frequent inserts and deletes could exhaust it. The recommendation is to use bigint unsigned (8 bytes) for tables that might approach this limit.

InnoDB System row_id

If an InnoDB table has no explicit primary key, InnoDB creates a hidden 6-byte row_id. A global dict_sys->row_id (8-byte unsigned long) is maintained; each insert into a table without a primary key uses the current value, then increments it. Only the lower 6 bytes are written to the data page, so the stored row_id ranges from 0 to 2^48 - 1. When dict_sys.row_id reaches 2^48, the next value wraps to 0.

Because row_id acts as the clustered index key, a wrap causes new rows to overwrite existing rows with the same row_id. The author verifies this using gdb to set dict_sys.row_id = 2^48 in a test environment:

Insert a=2 appears as the first row because its row_id=0.

Insert a=3 gets row_id=1, overwriting the previous row with a=1 (which also had row_id=1).

This demonstrates data loss (reliability impact). The author concludes that explicitly defining an auto-increment primary key is safer: exhaustion causes a duplicate key error (availability impact) rather than silent data corruption.

Xid

Xid

is a transaction identifier shared between redo log and binlog. MySQL maintains a global global_query_id (8-byte unsigned). For each statement, query_id = global_query_id++. If the statement is the first in a transaction, Xid = query_id. global_query_id is in-memory and resets to 0 on restart. A new binlog file is created on restart, so Xid uniqueness is guaranteed within a single binlog file.

Theoretically, if global_query_id wraps at 2^64 - 1, two transactions in the same binlog could share an Xid. The scenario requires: (1) a transaction with Xid=A, (2) 2^64 subsequent queries to wrap the counter back to A, (3) another transaction starting with Xid=A. The author considers this purely theoretical due to the enormous number.

InnoDB trx_id

InnoDB maintains its own transaction ID, trx_id, via a global max_trx_id (8-byte). Each new read-write transaction gets the current max_trx_id and increments it. max_trx_id is persisted across restarts.

Read-Only Transaction trx_id Display

Read-only transactions do not receive a real trx_id at start. Instead, when queried from information_schema.innodb_trx, a temporary large value is shown: pointer_address_of_trx_struct + 2^48. This ensures:

The same read-only transaction shows a consistent trx_id across innodb_trx and innodb_locks.

Concurrent read-only transactions have distinct displayed trx_id values.

When the read-only transaction performs a write (e.g., INSERT or SELECT ... FOR UPDATE), a real trx_id is allocated from max_trx_id.

Why Add 2^48?

To make read-only transaction IDs visibly larger than typical read-write IDs, reducing confusion. A collision between a read-write trx_id and a read-only displayed ID is theoretically possible but extremely unlikely and harmless.

Why Read-Only Transactions Don't Allocate trx_id

Reduces the active transaction array size in consistent read views. Read-only transactions don't affect visibility, so only read-write trx_id s are copied.

Reduces lock contention on max_trx_id. Every SELECT starts a read-only transaction; avoiding trx_id allocation significantly lowers contention.

Because read-only transactions don't consume trx_id, max_trx_id grows slower. However, it persists across restarts, so a long-running instance could eventually reach 2^48 - 1 and wrap to 0.

Dirty Read Bug at max_trx_id Wrap

The author reproduces a dirty read scenario by setting max_trx_id = 2^48 - 1 via gdb (repeatable read isolation):

Session A starts transaction TA; its low watermark becomes 2^48 - 1.

Session B executes first UPDATE (transaction ID = 2^48 - 1), second UPDATE (transaction ID wraps to 0). The new row version has trx_id=0.

Session A runs SELECT. Visibility check: row version trx_id=0 < low watermark (2^48 - 1) → considered visible. But this is a dirty read because Session B's transaction is uncommitted.

After the wrap, all subsequent queries suffer dirty reads because the low watermark keeps increasing while new trx_id s start from 0. Restarting MySQL does not reset max_trx_id, so the bug persists. At 500k TPS, this would take ~17.8 years; no known production instance has hit it yet, but it is inevitable given enough time.

thread_id

A global thread_id_counter (4-byte) assigns IDs to new connections. When it reaches 2^32 - 1, it wraps to 0. However, show processlist never shows duplicate thread_id s because MySQL uses a unique array ( thread_id_hash) to track in-use IDs. The allocation logic loops until it finds an unused ID.

Summary

Each auto-increment ID behaves differently at its limit:

Table auto_increment : value stops increasing → duplicate key error on further inserts.

row_id : wraps to 0 → new rows overwrite old rows with same row_id (data loss).

Xid : only needs uniqueness within a binlog file; wrap at 2^64 is theoretically possible but practically negligible.

max_trx_id : persists across restarts; wrap at 2^48 causes persistent dirty reads (a real bug, but far in the future).

thread_id : wraps at 2^32, but uniqueness enforced via hash array.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

InnoDBMySQLauto-incrementdatabase internalsrow_idthread_idtrx_idXid
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.