PHP MySQLi 與 PDO 事務處理完整指南

事務確保多條 SQL 要么全成功、要么全失敗，維護資料一致性。本文詳細說明 MySQLi、PDO 事務用法、自動提交陷阱、隱式提交陷阱與最佳實踐。

核心概念

• ACID：原子性、一致性、隔離性、持久性
• 自動提交：MySQL 預設每條 SQL 即提交，事務需關閉
• 儲存引擎：僅 InnoDB 支援事務，MyISAM 不支援

MySQLi 事務實作

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
if ($mysqli->connect_errno) {
    throw new Exception("連線失敗: {$mysqli->connect_error}");
}

try {
    // 1. 關閉自動提交
    $mysqli->autocommit(false);
    // 等同: $mysqli->query('SET autocommit = 0');

    // 2. 執行多條 SQL
    $mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    $mysqli->query("INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 100)");

    // 3. 檢查錯誤
    if ($mysqli->errno) {
        throw new Exception("SQL 錯誤: {$mysqli->error}");
    }

    // 4. 提交
    $mysqli->commit();
    echo "轉帳成功\n";

} catch (Throwable $e) {
    // 5. 回滾
    $mysqli->rollback();
    echo "轉帳失敗: {$e->getMessage()}\n";
} finally {
    // 6. 恢復自動提交（重要！避免影響後續查詢）
    $mysqli->autocommit(true);
    $mysqli->close();
}

PDO 事務實作（推薦）

<?php
$pdo = new PDO('mysql:host=localhost;dbname=db;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

try {
    // 1. 開始事務
    $pdo->beginTransaction();
    // 等同: $pdo->exec('START TRANSACTION');

    // 2. 參數綁定執行
    $stmt1 = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt1->execute([100, 1]);

    $stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $stmt2->execute([100, 2]);

    $stmt3 = $pdo->prepare("INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)");
    $stmt3->execute([1, 2, 100]);

    // 3. 提交
    $pdo->commit();
    echo "轉帳成功\n";

} catch (Throwable $e) {
    // 4. 回滾（若事務已提交會拋出例外，可忽略）
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "轉帳失敗: {$e->getMessage()}\n";
}

⚠️ 關鍵陷阱：隱式提交

以下 DDL/DCL 語句會**隱式提交**當前事務，導致無法回滾：

ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE,
CREATE INDEX, DROP INDEX,
LOCK TABLES, UNLOCK TABLES,
SET AUTOCOMMIT = 1,
START TRANSACTION,  -- 開始新事務前會提交舊事務
CREATE DATABASE, DROP DATABASE,
RENAME TABLE, LOAD DATA INFILE

錯誤範例

autocommit(false);
$mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$mysqli->query("CREATE TABLE temp_log (id INT)"); // 隱式提交！
$mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
// 若這裡失敗，第一條 UPDATE 已提交，無法回滾
$mysqli->commit();

儲存引擎檢查

query("SHOW TABLE STATUS WHERE Name = 'accounts'");
$row = $result->fetch_assoc();
if ($row['Engine'] !== 'InnoDB') {
    throw new Exception("表 accounts 引擎為 {$row['Engine']}，不支援事務");
}

巢狀事務（Savepoint）

MySQL 不支援巢狀事務，但支援 Savepoint：

beginTransaction();
$pdo->exec("SAVEPOINT sp1");
try {
    $pdo->exec("UPDATE ...");
    $pdo->exec("RELEASE SAVEPOINT sp1");
} catch (Exception $e) {
    $pdo->exec("ROLLBACK TO SAVEPOINT sp1");
}
$pdo->commit();

死鎖處理與重試

beginTransaction();
            $result = $callback($pdo);
            $pdo->commit();
            return $result;
        } catch (PDOException $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            // 死鎖錯誤代碼: 1213 (ER_LOCK_DEADLOCK) 或 SQLSTATE HY000
            if ($e->getCode() === 1213 || $e->getCode() === 'HY000') {
                if ($attempt === $maxRetries) throw $e;
                usleep(100000 * $attempt); // 指數退避
                continue;
            }
            throw $e;
        }
    }
}

最佳實踐清單

• ☑ 使用 PDO + 參數綁定（防注入、效能）
• ☑ 明確關閉自動提交 / 呼叫 beginTransaction()
• ☑ try-catch-finally 確保回滾與恢復自動提交
• ☑ 避免事務中執行 DDL/DCL
• ☑ 確認表引擎為 InnoDB
• ☑ 事務範圍最小化（只包含必要寫操作）
• ☑ 長事務拆分、加入死鎖重試
• ☑ 記錄事務日誌（追蹤 ID、耗時、結果）