PHP MySQL 事務處理實戰：從基礎語法到死鎖避免

前言：事務是資料一致性的基石

事務 (Transaction) 確保一組 SQL 操作**原子性 (全部成功或全部失敗)**。MySQL 中只有 **InnoDB** 引擎支援事務，MyISAM 不支援。本文涵蓋 PHP 事務操作、巢狀事務、死鎖處理、長事務危害與最佳實踐。

基礎語法 (mysqli / PDO)

mysqli 面向物件

$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->begin_transaction(); // 或 $mysqli->autocommit(false);

try {
    $mysqli->query("INSERT INTO orders (user_id, total) VALUES (1, 100)");
    $order_id = $mysqli->insert_id;
    
    $mysqli->query("INSERT INTO order_items (order_id, product_id, qty) VALUES ($order_id, 10, 2)");
    $mysqli->query("UPDATE products SET stock = stock - 2 WHERE id = 10");
    
    $mysqli->commit();
    echo "訂單建立成功: $order_id";
} catch (Exception $e) {
    $mysqli->rollback();
    echo "失敗: " . $e->getMessage();
}

PDO (推薦，支援多資料庫)

$pdo = new PDO('mysql:host=localhost;dbname=db', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預處理
]);

try {
    $pdo->beginTransaction();
    
    $stmt = $pdo->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");
    $stmt->execute([1, 100]);
    $order_id = $pdo->lastInsertId();
    
    $stmt = $pdo->prepare("INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)");
    $stmt->execute([$order_id, 10, 2]);
    
    $stmt = $pdo->prepare("UPDATE products SET stock = stock - 2 WHERE id = 10");
    $stmt->execute();
    
    $pdo->commit();
    echo "成功: $order_id";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "失敗: " . $e->getMessage();
}

進階場景與陷阱

1. 巢狀事務 (MySQL 不支援真正巢狀)

// 錯誤示範：MySQL 會隱式提交外層事務
$pdo->beginTransaction();
$pdo->query("INSERT INTO a VALUES (1)");
    $pdo->beginTransaction(); // 這行會導致外層事務隱式提交！
    $pdo->query("INSERT INTO b VALUES (2)");
    $pdo->commit(); // 內層提交
$pdo->rollback(); // 外層回滾已無效，a 已寫入

解決方案：Savepoint

$pdo->beginTransaction();
$pdo->query("INSERT INTO a VALUES (1)");

$pdo->exec("SAVEPOINT sp1"); // 設定保存點
$pdo->query("INSERT INTO b VALUES (2)");
if ($error) {
    $pdo->exec("ROLLBACK TO SAVEPOINT sp1"); // 回滾到保存點
} else {
    $pdo->exec("RELEASE SAVEPOINT sp1"); // 釋放保存點
}
$pdo->commit();

2. 死鎖

**死鎖條件**：兩個事務互相持有對方需要的鎖，且都在等待對方釋放。

// 事務 A                    // 事務 B
BEGIN;                        BEGIN;
UPDATE accounts SET bal=bal-100 WHERE id=1;  -- 鎖 id=1
                               UPDATE accounts SET bal=bal+100 WHERE id=2; -- 鎖 id=2
UPDATE accounts SET bal=bal+100 WHERE id=2;  -- 等待 B 釋放 id=2
                               UPDATE accounts SET bal=bal-100 WHERE id=1; -- 等待 A 釋放 id=1
                               -- DEADLOCK! MySQL 檢測到並回滾其中一個
COMMIT;                       COMMIT;

避免死鎖原則：

1. 固定存取順序：所有事務按 **相同順序** 存取表/行 (如按 ID 升序)
2. 縮短事務：只在事務中放必要 SQL，業務邏輯、API 呼叫、睡眠移出事務
3. 索引優化：確保 WHERE 條件走索引，減少掃描鎖定範圍
4. 低隔離級別READ COMMITTED 可減少間隙鎖 (需評估一致性需求)
5. 重試機制：捕獲死鎖錯誤碼 (1213) 自動重試 3 次

SELECT * FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60
AND trx_state = 'RUNNING';

// 原子性 upsert，避免 SELECT 檢查再 INSERT 的競態
$stmt = $pdo->prepare("
    INSERT INTO user_points (user_id, points) VALUES (?, 100)
    ON DUPLICATE KEY UPDATE points = points + 100
");
$stmt->execute([$user_id]);

// MY_Model 新增事務方法
public function transaction(callable $callback) {
    $this->db->trans_start(); // CI 事務開始 (支援巢狀模擬)
    try {
        $result = $callback($this->db);
        $this->db->trans_complete();
        return $result;
    } catch (Exception $e) {
        $this->db->trans_rollback();
        throw $e;
    }
}

// 使用
$this->User_model->transaction(function($db) {
    $db->insert('orders', $order_data);
    $order_id = $db->insert_id();
    $db->insert_batch('order_items', $items);
    $db->update('products', ['stock -=' => $qty], ['id' => $pid]);
    return $order_id;
});