PHP 交易處理實作轉帳功能：MySQLi 與 PDO 兩種方案完整對比

PHP 交易處理實作轉帳功能：MySQLi 與 PDO 兩種方案完整對比

資料庫交易是確保轉帳、扣款、庫存扣減等關鍵業務資料一致性的核心機制。本文提供 MySQLi 程序式與 PDO 物件導向兩套完整實作，涵蓋自動提交控制、錯誤處理、回滾機制、死鎖重試等生產級細節。

核心交易流程

1. 關閉自動提交
2. 執行多條 SQL（扣款、入帳、記錄流水）
3. 檢查每條 SQL 影響列數
4. 全部成功 → 提交；任一失敗 → 回滾
5. 恢復自動提交

方案一：MySQLi 程序式風格

<?php
header("Content-Type: text/html; charset=utf-8");

$mysqli = new mysqli("localhost", "root", "064319", "php");
$mysqli->set_charset("utf8mb4");

if ($mysqli->connect_errno) {
    die('資料庫連線失敗: ' . $mysqli->connect_error);
}

try {
    // 1. 關閉自動提交
    $mysqli->autocommit(false);
    
    $flag = true; // 交易成功旗標
    
    // 2. 扣款：帳戶 3 扣除 1000
    $sql = "UPDATE account SET balance = balance - 1000 WHERE id = 3";
    $result = $mysqli->query($sql);
    $affected = $mysqli->affected_rows;
    
    if (!$result || $affected === 0) {
        throw new Exception("扣款失敗：帳戶 3 不存在或餘額不足");
    }
    
    // 3. 入帳：帳戶 2 增加 1000
    $sql = "UPDATE account SET balance = balance + 1000 WHERE id = 2";
    $result = $mysqli->query($sql);
    $affected = $mysqli->affected_rows;
    
    if (!$result || $affected === 0) {
        throw new Exception("入帳失敗：帳戶 2 不存在");
    }
    
    // 4. 記錄流水（可選）
    $sql = "INSERT INTO transfer_log (from_id, to_id, amount, created_at) VALUES (3, 2, 1000, NOW())";
    $result = $mysqli->query($sql);
    if (!$result) {
        throw new Exception("流水記錄失敗");
    }
    
    // 5. 全部成功，提交交易
    $mysqli->commit();
    echo '轉帳成功';
    
} catch (Exception $e) {
    // 6. 任一失敗，回滾
    $mysqli->rollback();
    echo '轉帳失敗: ' . $e->getMessage();
    
    // 記錄錯誤日誌
    error_log("[TRANSFER FAILED] " . $e->getMessage());
    
} finally {
    // 7. 恢復自動提交並關閉連線
    $mysqli->autocommit(true);
    $mysqli->close();
}

方案二：PDO 物件導向風格（推薦）

<?php
try {
    // 1. 建立 PDO 連線
    $pdo = new PDO(
        "mysql:host=localhost;dbname=php;charset=utf8mb4",
        "root",
        "064319",
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,  // 拋出例外
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,          // 使用原生預處理
        ]
    );
    
    // 2. 關閉自動提交
    $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
    
    // 3. 開始交易
    $pdo->beginTransaction();
    
    $amount = 1000;
    
    // 4. 扣款（使用預處理語句防 SQL 注入）
    $stmt = $pdo->prepare("UPDATE account SET balance = balance - ? WHERE id = ?");
    $stmt->execute([$amount, 3]);
    $affected1 = $stmt->rowCount();
    
    if ($affected1 === 0) {
        throw new PDOException("扣款失敗：帳戶 3 不存在或餘額不足");
    }
    
    // 5. 入帳
    $stmt = $pdo->prepare("UPDATE account SET balance = balance + ? WHERE id = ?");
    $stmt->execute([$amount, 2]);
    $affected2 = $stmt->rowCount();
    
    if ($affected2 === 0) {
        throw new PDOException("入帳失敗：帳戶 2 不存在");
    }
    
    // 6. 記錄流水
    $stmt = $pdo->prepare("INSERT INTO transfer_log (from_id, to_id, amount, created_at) VALUES (?, ?, ?, NOW())");
    $stmt->execute([3, 2, $amount]);
    
    // 7. 提交交易
    $pdo->commit();
    echo "操作成功";
    
} catch (PDOException $e) {
    // 8. 回滾
    if ($pdo->inTransaction()) {
        $pdo->rollback();
    }
    echo "操作失敗: " . $e->getMessage();
    error_log("[TRANSFER FAILED] " . $e->getMessage());
    
} finally {
    // 9. 恢復自動提交
    $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);
    $pdo = null; // 關閉連線
}

// 10. 驗證結果
echo "\n操作結果為:\n";
$sql = "SELECT * FROM account";
foreach ($pdo->query($sql) as $row) {
    echo $row['id'] . " " . $row['balance'] . "\n";
}

兩方案詳細對比

生產級進階模式

模式 1：死鎖重試機制

function transferWithRetry(PDO $pdo, int $from, int $to, int $amount, int $maxRetries = 3) {
    for ($attempt = 1; $attempt <= $maxRetries; $attempt++) {
        try {
            $pdo->beginTransaction();
            
            // 鎖定行（SELECT ... FOR UPDATE）防止並發問題
            $stmt = $pdo->prepare("SELECT balance FROM account WHERE id IN (?, ?) FOR UPDATE");
            $stmt->execute([$from, $to]);
            $accounts = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
            
            if (($accounts[$from] ?? 0) < $amount) {
                throw new Exception("餘額不足");
            }
            
            $pdo->prepare("UPDATE account SET balance = balance - ? WHERE id = ?")->execute([$amount, $from]);
            $pdo->prepare("UPDATE account SET balance = balance + ? WHERE id = ?")->execute([$amount, $to]);
            $pdo->prepare("INSERT INTO transfer_log (from_id, to_id, amount) VALUES (?, ?, ?)")->execute([$from, $to, $amount]);
            
            $pdo->commit();
            return true;
            
        } catch (PDOException $e) {
            if ($pdo->inTransaction()) $pdo->rollback();
            
            // 死鎖錯誤碼：MySQL 1213, SQLSTATE HY000
            if ($e->getCode() === 'HY000' && $attempt < $maxRetries) {
                usleep(rand(50000, 200000)); // 50-200ms 隨機延遲
                continue;
            }
            throw $e;
        }
    }
    return false;
}

模式 2：樂觀鎖（版本號/時間戳）

-- 資料表加欄位
ALTER TABLE account ADD version INT DEFAULT 0;

-- 更新時檢查版本
UPDATE account 
SET balance = balance - ?, version = version + 1 
WHERE id = ? AND version = ?;

-- PHP 檢查 affected_rows，為 0 表示衝突需重試

模式 3：分散式交易（Saga 模式）

微服務架構下跨資料庫轉帳，採用補償交易：

// 步驟 1：凍結金額（建立待處理記錄）
// 步驟 2：嘗試入帳目標帳戶
// 步驟 3：確認入帳成功 → 確認扣款
// 任一步驟失敗 → 執行補償操作（解凍、退款）

最佳實踐檢查清單

• ✅ 使用 utf8mb4 字元集
• ✅ 所有 SQL 使用預處理語句
• ✅ 捕獲具體例外類型（PDOException、mysqli_sql_exception）
• ✅ finally 區塊確保連線正確關閉
• ✅ 錯誤訊息不洩露敏感資訊（僅記錄日誌）
• ✅ 關鍵業務加入死鎖重試
• ✅ 高併發場景加入行鎖 FOR UPDATE
• ✅ 交易內避免外部 API 呼叫、耗時運算
• ✅ 審計日誌記錄：誰、何時、轉什麼、結果