MySQL 事務處理實戰：ACID 原理、BEGIN/COMMIT/ROLLBACK、自動提交陷阱與 PHP 封裝

MySQL 事務處理實戰：ACID 原理、BEGIN/COMMIT/ROLLBACK、自動提交陷阱與 PHP 封裝

為什麼需要事務？

刪除用戶時需同步刪除基本信息、郵件、文章等多張表。若中途失敗且無事務，數據將處於「半刪除」不一致狀態，後果嚴重。

ACID 四大特性

僅 InnoDB/BDB 支援事務

SHOW VARIABLES LIKE 'have_%';
-- have_innodb = YES 才能用事務

兩種事務控制方式

1. 顯式控制（推薦）

BEGIN;                    -- 或 START TRANSACTION
INSERT INTO dbtest VALUES (5);
INSERT INTO dbtest VALUES (6);
COMMIT;                   -- 確認提交

BEGIN;
INSERT INTO dbtest VALUES (7);
ROLLBACK;                 -- 回滾，7 不會寫入

2. 自動提交模式切換

SET autocommit = 0;  -- 關閉自動提交，後續所有語句隱式在一個事務中
... 多條 DML ...
COMMIT;              -- 結束當前事務，**同時隱式開啟下一個事務**
SET autocommit = 1;  -- 恢復自動提交

陷阱： autocommit=0 下，COMMIT 同時開啟新事務，易導致長事務未覺察。生產環境強烈建議顯式 BEGIN/COMMIT。

PHP 事務封裝範例

function Tran(array $sqlList) {
    $judge = 1;
    mysql_query('BEGIN');
    foreach ($sqlList as $sql) {
        if (!mysql_query($sql)) { $judge = 0; break; }
    }
    if ($judge) {
        mysql_query('COMMIT');
        return true;
    } else {
        mysql_query('ROLLBACK');
        return false;
    }
}

// 使用
$sqls = [
    "INSERT INTO trans (id) VALUES ('2')",
    "INSERT INTO trans (id) VALUES ('4')"
];
Tran($sqls);

現代化重寫（PDO）

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $pdo->beginTransaction();
    $pdo->exec("INSERT INTO trans (id) VALUES ('2')");
    $pdo->exec("INSERT INTO trans (id) VALUES ('4')");
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

表鎖與事務配合

• LOCK TABLES t1 WRITE, t2 READ; — 顯式鎖表，**會隱式提交當前事務**。
• 事務中**避免使用 LOCK TABLES**，改用行級鎖（SELECT ... FOR UPDATE）配合索引。

避坑清單

1. 表引擎必須是 InnoDB。
2. 不用 autocommit=0，改用顯式 BEGIN/COMMIT/ROLLBACK。
3. 事務內盡量少查詢、不加鎖、不做耗時運算，持有鎖時間最小化。
4. 異常捕獲後**必須 ROLLBACK**，否則連接池歸還時隱式提交髒數據。
5. 長事務（>5s）需拆分或異步化，防止 Undo Log 膨脹、鎖等待雪崩。

一句話總結： InnoDB + 顯式 BEGIN/COMMIT/ROLLBACK + 短小事務 = 可靠的數據一致性保障。