您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 事務實現高併發安全自增 ID：FOR UPDATE 鎖與 LAST_INSERT_ID 兩大方案

編輯：MySQL 綜合教學

場景痛點

分表/分庫架構下，無法使用單表 AUTO_INCREMENT。
需全域唯一、單調遞增、高併發安全的 ID 生成器。
Redis/雪花演算法雖好，但引入外部依賴，MySQL 原生方案零依賴、強一致。

方案一：單表 + FOR UPDATE 排他鎖（強一致、易理解）

表結構


CREATE TABLE `auto_id` (
    `idname` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '業務標識，如 order_id, user_id',
    `id` BIGINT(20) NOT NULL DEFAULT 0 COMMENT '當前最大 ID',
    PRIMARY KEY (`idname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='全域自增 ID 生成器';

儲存程序


DELIMITER //
DROP PROCEDURE IF EXISTS `get_increment_id`;
CREATE PROCEDURE `get_increment_id`(
    IN `idname_in` VARCHAR(20),
    IN `start_val` BIGINT,
    OUT `id_out` BIGINT
)
BEGIN
    DECLARE `old_id` BIGINT;

    START TRANSACTION;

    -- 關鍵：FOR UPDATE 排他鎖，阻塞其他事務讀取同一行
    SELECT `id` INTO `old_id` FROM `auto_id`
    WHERE `idname` = `idname_in`
    FOR UPDATE;

    IF `old_id` IS NULL THEN
        -- 首次初始化
        INSERT INTO `auto_id` (`idname`, `id`) VALUES (`idname_in`, `start_val`);
        SET `id_out` = `start_val`;
    ELSE
        -- 遞增並更新
        UPDATE `auto_id` SET `id` = `id` + 1 WHERE `idname` = `idname_in`;
        SET `id_out` = `old_id` + 1;
    END IF;

    COMMIT;
END //
DELIMITER ;

PHP 呼叫範例


$sql = "CALL get_increment_id('order_id', 100000, @new_id)";
$mysqli->query($sql);
$result = $mysqli->query("SELECT @new_id AS new_id");
$row = $result->fetch_assoc();
$new_order_id = $row['new_id'];

原理解析

SELECT ... FOR UPDATE 在事務中對該行加 排他鎖 (X-lock)。
其他事務嘗試讀取同一 idname 時會 阻塞等待，直到持有鎖的事務 COMMIT。
保證同一時間只有一個事務能讀取並更新該 ID，實現線性化。
效能：單行熱點，極高併發 (QPS > 5000) 時鎖爭用明顯，可考慮方案二或分段預取。

方案二：利用 AUTO_INCREMENT + LAST_INSERT_ID（無鎖、極高效能）

表結構（每種 ID 一張表）


CREATE TABLE `seq_order_id` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '自增 ID',
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='訂單 ID 序列';

⚠️ 必須用 MyISAM 或 InnoDB 但關閉 gap lock，因 InnoDB 預設會對 AUTO_INCREMENT 加表級鎖/間隙鎖，影響併發。MyISAM 無事務、表級鎖但 LAST_INSERT_ID 機制極輕量。

核心 SQL（兩條語句，應用層封裝）


-- 1. 更新並取得新 ID
UPDATE `seq_order_id` SET `id` = LAST_INSERT_ID(`id` + 1);

-- 2. 取得剛生成的 ID（連線隔離，無併發干擾）
SELECT LAST_INSERT_ID();

原理解析

LAST_INSERT_ID(expr) 將 expr 設為下一個 LAST_INSERT_ID() 返回值，僅對當前連線生效。
無需鎖定、無事務開銷、極快（單次 < 0.1ms）。
每種 ID 需一張表，表數量隨業務增長（可接受）。
MyISAM 崩潰可能導致自增值跳躍（不連續），但不會重複，通常可接受。

方案對比與選型建議

維度	方案一：FOR UPDATE	方案二：LAST_INSERT_ID
一致性	強一致（ACID）	最終一致（MyISAM 可能跳號）
併發效能	中等（單行熱點鎖）	極高（無鎖）
表數量	1 表存所有類型	每類型 1 表
引擎要求	InnoDB	MyISAM 推薦
實現複雜度	儲存程序	兩條簡單 SQL
適用場景	金融/核心業務、嚴格連續	高併發訂單/日誌/非核心 ID

進階優化：批次預取（減少資料庫往返）


-- 方案一改造：一次取 100 個 ID
UPDATE `auto_id` SET `id` = `id` + 100 WHERE `idname` = 'order_id';
SELECT `id` - 99 AS start_id, `id` AS end_id FROM `auto_id` WHERE `idname` = 'order_id';
-- 應用層緩存 100 個 ID，用完再取下一批

大幅降低資料庫 QPS，適用於極高併發場景。