MySQL存儲過程事務管理全攻略：ACID、隔離級別、鎖機制與最佳實踐

MySQL存儲過程事務管理全攻略：ACID、隔離級別、鎖機制與最佳實踐

存儲過程是封裝、管理數據庫事務的理想場所。本文系統講解 MySQL/InnoDB 事務支持架構、隔離級別、事務控制語句、Savepoint 應用、鎖與死鎖處理、樂觀/悲觀鎖策略及事務設計原則。

1. MySQL 事務支持架構

事務支持綁定於存儲引擎，而非 MySQL Server 層：

• InnoDB： 完整 ACID、行級鎖、MVCC、支持所有隔離級別（默認引擎）。
• MyISAM： 不支持事務，適合唯讀/讀多寫少場景提升性能。
• BerkeleyDB (BDB)： 早期事務引擎，已棄用。

2. 四大隔離級別 (ANSI 標準，InnoDB 全支持)

設置會話隔離級別：

SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

警告： 生產環境慎用 READ UNCOMMITTED (髒讀) 和 SERIALIZABLE (性能/死鎖)。

3. 事務控制語句

4. 開啟事務的兩種等效方式

-- 方式 1: 關閉自動提交
SET autocommit = 0;
UPDATE ...; UPDATE ...; COMMIT;

-- 方式 2: 顯式 START TRANSACTION (推薦)
START TRANSACTION;
UPDATE ...; UPDATE ...; COMMIT;

關鍵差異： 若已在事務中，SET autocommit=0 無效；START TRANSACTION 會隱式提交舊事務並開啟新事務。

5. 隱式提交陷阱 (DDL 等語句會強制 COMMIT)

ALTER TABLE / CREATE / DROP / TRUNCATE / RENAME / LOCK TABLES / UNLOCK TABLES
SET AUTOCOMMIT=1 / START TRANSACTION (嵌套時)
-- 以上語句在事務中執行會隱式提交當前事務！

事務中盡量避免 DDL，或將 DDL 移至事務外。

6. Savepoint 應用：嵌套事務模式

場景：外層大事務中執行子操作，子操作失敗僅回滾子操作，外層繼續。

CREATE PROCEDURE nested_transfer (...)
BEGIN
    DECLARE txn_error INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET txn_error = 1;
    
    SAVEPOINT sp_transfer;
    UPDATE account SET balance = balance - amt WHERE id = from_id;
    IF txn_error THEN ROLLBACK TO sp_transfer; SELECT 'Aborted'; END IF;
    
    UPDATE account SET balance = balance + amt WHERE id = to_id;
    IF txn_error THEN ROLLBACK TO sp_transfer; SELECT 'Aborted'; END IF;
END;

注意： Savepoint 有性能開銷，簡單邏輯優先用 IF/ELSE 重寫避免。

7. 鎖機制與死鎖處理

行級鎖 (InnoDB 核心優勢)

• 修改同表不同行無阻塞；讀操作 (非鎖定讀) 無等待。
• 鎖持有至 COMMIT/ROLLBACK 釋放。
• 長事務 = 長鎖持有 = 吞吐量下降。

顯式加鎖讀

SELECT ... FOR UPDATE;          -- 排他鎖，阻塞其他讀/寫
SELECT ... LOCK IN SHARE MODE;  -- 共享鎖，允許並發讀，阻塞寫

死鎖成因與 InnoDB 處理

• 兩事務互相等待對方持有的鎖。
• InnoDB 自動檢測，強制回滾修改行數較少的事務，報錯 ERROR 1213 (40001): Deadlock found。

死鎖預防最佳實踐

1. 統一加鎖順序： 所有事務按相同順序訪問表/行 (如按主鍵排序)。
2. 縮短事務： 少做事、快提交。
3. 避免長事務中用戶交互。
4. 必要時捕獲 1213 錯誤重試 (但代碼複雜，優先用順序加鎖)。

-- 範例：轉賬前按 ID 排序加鎖，彻底避免死鎖
CREATE PROCEDURE safe_transfer (from_id INT, to_id INT, amt DECIMAL(10,2))
BEGIN
    DECLARE lock_cursor CURSOR FOR
        SELECT id FROM account WHERE id IN (from_id, to_id) ORDER BY id FOR UPDATE;
    START TRANSACTION;
    OPEN lock_cursor; FETCH lock_cursor INTO @dummy; -- 僅為加鎖
    UPDATE account SET balance = balance - amt WHERE id = from_id;
    UPDATE account SET balance = balance + amt WHERE id = to_id;
    CLOSE lock_cursor;
    COMMIT;
END;

鎖等待超時

• 參數：innodb_lock_wait_timeout (默認 50s)。
• 超時拋錯 ERROR 1205 (HY000): Lock wait timeout exceeded。
• 混合引擎表事務中，InnoDB 無法檢測死鎖，只會觸發鎖等待超時。

8. 悲觀鎖 vs 樂觀鎖

悲觀鎖示例 (轉賬)：

START TRANSACTION;
SELECT balance INTO @bal FROM account WHERE id=from_id FOR UPDATE;
IF @bal >= amt THEN
    UPDATE account SET balance=balance-amt WHERE id=from_id;
    UPDATE account SET balance=balance+amt WHERE id=to_id;
    COMMIT;
ELSE ROLLBACK; END IF;

樂觀鎖示例 (長驗證場景)：

SELECT ts, bal INTO @ts1, @bal FROM account WHERE id=from_id;
IF @bal >= amt THEN
    CALL long_validation(from_id); -- 耗時驗證，不持有鎖
    START TRANSACTION;
    SELECT ts, bal INTO @ts2, @bal2 FROM account WHERE id=from_id FOR UPDATE;
    IF @ts1=@ts2 AND @bal=@bal2 THEN
        UPDATE account SET balance=balance-amt, ts=NOW() WHERE id=from_id;
        UPDATE account SET balance=balance+amt WHERE id=to_id;
        COMMIT;
    ELSE ROLLBACK; END IF;
ELSE ... END IF;

9. 事務設計黃金法則

1. 保持事務短小：少鎖行、短持鎖時間。
2. 盡量避免 ROLLBACK：業務邏輯預判，減少回滾開銷。
3. 盡量避免 SAVEPOINT：簡化邏輯，用條件判斷替代。
4. 默認用悲觀鎖：InnoDB 行級鎖開銷低。
5. 高吞吐/長驗證場景考慮樂觀鎖。
6. 顯式 START TRANSACTION：語義清晰，避免 autocommit 陷阱。
7. 事務中禁用 LOCK TABLES / DDL。