MySQL 存儲過程事務管理深度解析：ACID、隔離級別、Savepoint、鎖、死鎖防範

事務支援引擎

• InnoDB：ACID、行級鎖、MVCC、高併發
• BDB：支援事務
• MyISAM：不支援，唯讀高性能場景

四大隔離級別 (InnoDB 全支援)

設置隔離級別

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

警告：READ UNCOMMITTED 導致數據完整性風險；SERIALIZABLE 嚴重影響性能。

事務控制語句

START TRANSACTION;          -- 顯式開啟 (隱式 autocommit=0)
COMMIT;                     -- 提交、釋放鎖
ROLLBACK;                   -- 回滾、釋放鎖
SAVEPOINT sp_name;          -- 設置保存點
ROLLBACK TO SAVEPOINT sp_name; -- 回滾到保存點
RELEASE SAVEPOINT sp_name;  -- 釋放保存點
SET TRANSACTION ...;        -- 設置隔離級別等屬性
LOCK TABLES ...;            -- 表鎖 (隱式提交當前事務，勿在事務中混用)

定義事務兩種方式

1. SET autocommit = 0 (會話級)
2. START TRANSACTION (顯式，推薦，自動隱式 autocommit=0)

若已在事務中執行 SET autocommit=0 無效，START TRANSACTION 會隱式提交當前更改並開啟新事務。

存儲過程事務範本 (含異常處理)

CREATE PROCEDURE t_insert_table()
BEGIN
    DECLARE t_error INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
    
    START TRANSACTION;
    INSERT INTO t_bom_test(parent_id, child_id) VALUES('C','XXXX');
    INSERT INTO t_trigger_test(name, age) VALUES('zhangsan', 34);
    
    IF t_error = 1 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END;

隱式提交語句 (事務中禁用)

ALTER TABLE / CREATE / DROP / TRUNCATE / RENAME / LOCK TABLES / UNLOCK TABLES
SET AUTOCOMMIT = 1 / START TRANSACTION
-- 以上語句會強制提交當前事務

Savepoint 嵌套事務模式

CREATE PROCEDURE nested_tfer_funds(from_acct, to_acct, amount)
BEGIN
    DECLARE txn_error INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET txn_error = 1;
    
    SAVEPOINT sp_tfer;
    UPDATE account_balance SET balance = balance - amount WHERE account_id = from_acct;
    IF txn_error THEN ROLLBACK TO sp_tfer; SELECT 'Aborted'; END IF;
    
    UPDATE account_balance SET balance = balance + amount WHERE account_id = to_acct;
    IF txn_error THEN ROLLBACK TO sp_tfer; SELECT 'Aborted'; END IF;
END;

行級鎖與 SELECT ... FOR UPDATE

SELECT * FROM account WHERE id = 1 FOR UPDATE;        -- 排他鎖，阻塞其他寫/鎖讀
SELECT * FROM account WHERE id = 1 LOCK IN SHARE MODE; -- 共享鎖，允許並發讀、阻塞寫

死鎖原理與防範

• 死鎖：兩事務互相等待對方釋放鎖
• InnoDB 自動檢測，回滾修改行數較少的事務，報錯 ERROR 1213 (40001): Deadlock found
• 預防：
  1. 固定順序訪問表/行 (如按主鍵排序)
  2. 事務盡量短小
  3. 業務層捕獲 1213 重試

悲觀鎖 vs 樂觀鎖

事務設計 7 原則

1. 保持事務短小
2. 盡量避免 ROLLBACK
3. 盡量避免 SAVEPOINT
4. 默認悲觀鎖
5. 高吞吐場景考慮樂觀鎖
6. 顯式 START TRANSACTION
7. 鎖定行越少越好、時間越短越好