READ UNCOMMITTED能否识别所有已分配的低自增ID？反向验证是否可靠？

Great question—this dives into some nuanced behavior of auto-increment IDs and transaction isolation that's easy to overlook. Let's unpack your two core questions:

1. 当计数等于或小于首次查询的ID数量时，能保证中间无遗漏ID吗？ #

Short answer: No, you can't fully guarantee it, even when ignoring manual ID inserts or counter resets. Here's why:

Auto-increment ID allocation and row visibility are two separate processes. A database might reserve an auto-increment ID for a transaction before the corresponding row is actually written (or made visible to any queries). For example:

• A transaction starts, reserves ID 11 via an INSERT statement that's still processing (waiting on locks, IO, etc.). The ID is already taken and won't be reused, but the row hasn't been committed—or even written to the storage engine yet.
• With READ UNCOMMITTED, you can see uncommitted (dirty) rows, but only if those rows exist in the storage engine. If the ID is reserved but the row isn't there yet, your count query won't pick it up.

So if your first query finds max ID 12 and returns only that ID (count = 1), the count equals the number of IDs retrieved—but ID 11 could still be reserved but not yet visible. Later, when the transaction for ID 11 commits, you'd miss it if you start your next read from ID >12.

2. 存在ID已被分配但READ UNCOMMITTED查询未感知到的场景吗？ #

Absolutely. Here are common scenarios:

• Pre-allocated auto-increment IDs: Many databases (like MySQL with innodb_autoinc_lock_mode=2) use batch allocation for auto-increment IDs. If a transaction inserts multiple rows (e.g., INSERT ... SELECT), it might reserve a block of IDs at once. Only the rows that have been inserted so far are visible to READ UNCOMMITTED—the remaining reserved IDs in the block have no corresponding rows yet, so they're invisible to queries.
• In-progress INSERTs: A transaction executes an INSERT that reserves an ID, but the statement hasn't finished writing the row to disk or making it visible. Even READ UNCOMMITTED can't see rows that haven't been persisted to the storage engine, even if the ID is already taken.
• Transaction setup delays: A transaction might reserve an ID (e.g., via a prepared statement or delayed insert) but hasn't executed the actual INSERT logic yet. The ID is locked, but no row exists to query.

额外建议 #

If you need rock-solid guarantees against missing IDs, the double-check approach helps but isn't foolproof. Consider alternatives like:

• Change Data Capture (CDC): Use database logs (e.g., MySQL binlog, PostgreSQL WAL) to capture all committed changes in order. This avoids relying on ID gaps entirely.
• Periodic backtracking: After each incremental read, periodically query a small window of IDs before your last processed ID to catch any late-committing rows.
• Track processed IDs with a watermark table: Maintain a table that records the highest ID you've successfully processed, and periodically verify that there are no committed IDs between your last watermark and the current max ID.

内容的提问来源于stack exchange，提问作者Timo