MySQL 自增主鍵深度探究：並發事務下的唯一性、不連續性與隔離級別可見性

測試環境

• MariaDB 5.5.5 (MySQL 兼容)
• 隔離級別：REPEATABLE-READ (默認)
• 客戶端：HeidiSQL (Windows)
• 測試表：test_auto (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY)

三客戶端並發實驗

客戶端 1：開啟事務，插入 6 行，未提交

BEGIN;
INSERT INTO test_auto() VALUES(); -- x6
SELECT * FROM test_auto;  -- 見到 id 1-6 (僅本會話可見)

客戶端 2：開啟事務，插入 5 行，未提交

BEGIN;
INSERT INTO test_auto() VALUES(); -- x5
SELECT * FROM test_auto;  -- 見到 id 7-11 (本會話可見，接續 1-6)

客戶端 3：未開事務，直接查詢

SELECT * FROM test_auto;  -- 空結果 (無提交數據可見)

客戶端 1 提交

COMMIT;  -- id 1-6 持久化

客戶端 2 回滾

ROLLBACK;  -- id 7-11 丟棄
SELECT * FROM test_auto;  -- 見到 id 1-6 (客戶端 1 已提交)

核心結論

1. 全局唯一：多事務並發插入，自增值絕不重複 (1-6, 7-11)。
2. 不保證連續：事務回滾導致「跳號」(7-11 消失)，業務若要求連續需自行生成 (如序列表)。
3. 事務隔離可見性：
   • REPEATABLE-READ：事務啟動建立快照，見「快照 + 本事務修改」；未提交他事務修改不可見。
   • READ COMMITTED：只見已提交數據。
4. 自增鎖機制：為性能，InnoDB 批量分配自增值 (預分配)，事務回滾不歸還，故有間隙。

查詢隔離級別

SELECT @@tx_isolation;

業務建議

• 自增主鍵僅作技術主鍵 (單調遞增、無業務含義)，利於聚簇索引性能。
• 業務唯一標識 (訂單號、用戶 ID) 使用雪花算法/UUID/號段模式生成，解耦業務與存儲。
• 若極少數場景需連續無間隙，單獨建序列表 seq_table (id BIGINT)，事務中 UPDATE seq_table SET id=LAST_INSERT_ID(id+1) 獲取。