MySQL 插入自增主鍵值相等字段的高併發解決方案實戰

MySQL 插入自增主鍵值相等字段的高併發解決方案實戰

在實際業務開發中，有時會遇到這樣的需求：表 t2 擁有自增主鍵 id 和字段 v，插入記錄時要求 v 的值必須與自動生成的 id 保持一致。雖然規範設計通常建議拆表處理，但業務場景可能僅要求部分行滿足此條件，因此需要在單表內解決。

初步嘗試與陷阱

網上常見的做法是先查詢最大 ID 再加一：

SELECT max(id)+1 from t2

然後將獲取的值插入 v 字段。但這種方法在存在刪除行或手動調整過自增值的表中極不準確，極易導致數據不一致。

改進方案：利用 information_schema

轉而從 information_schema 讀取表的下一個自增值：

INSERT INTO `t2` VALUES ( NULL, ( SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME` = 't2' ) );

功能雖然實現了，但在高併發環境下真的安全嗎？

高併發壓測驗證

編寫 PHP 腳本模擬併發插入：

<?php
$sql = "INSERT INTO `t2` VALUES(NULL ,(SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME`='t2'));";
$link = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
mysql_select_db("test");
mysql_query($sql);
mysql_close($link);
?>

使用 ab -n 50000 -c 20 進行壓測，結果大量出現 v 與 id 不相等的情況（select * from t2 where id != v;），證明該方案在高併發下存在嚴重競態條件。

事務與驗證機制保證數據一致性

改寫 PHP，引入事務並插入後驗證：

<?php
$sql = "INSERT INTO `t2` VALUES(NULL ,(SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME`='t2'));";
$link = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
mysql_select_db("test");
mysql_query('START TRANSACTION'); # 開始事務
mysql_query($sql);
$id = mysql_insert_id();
$res = mysql_query("SELECT `v` FROM `t2` WHERE id= ".$id);

if (!$res) {
    mysql_close($link);
    die;
}

$row = mysql_fetch_assoc($res);
if($row['v'] != $id){
    mysql_query(' ROLLBACK '); # 回滾事務
}
mysql_query('COMMIT'); # 提交事務
mysql_close($link);
?>

再次使用 AB 壓測，雖然速度下降，但結果完全正確，所有行的 v 與 id 保持一致。

總結

在高併發場景下，單純依賴 information_schema 獲取自增值不可靠，必須結合事務機制與插入後驗證回滾策略，才能確保數據的絕對準確性。這是一個典型的「樂觀鎖」思想在業務層面的應用實踐。