| SQL 語句 | MySQLi 面向對象 | MySQLi 過程式 | PDO |
|---|---|---|---|
START TRANSACTION | $mysqli->begin_transaction() | mysqli_begin_transaction($link) | $pdo->beginTransaction() |
COMMIT | $mysqli->commit() | mysqli_commit($link) | $pdo->commit() |
ROLLBACK | $mysqli->rollback() | mysqli_rollback($link) | $pdo->rollBack() |
SET autocommit=0 | $mysqli->autocommit(false) | mysqli_autocommit($link, false) | $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false) |
| 保存點 | $mysqli->savepoint('sp1') | mysqli_savepoint($link, 'sp1') | 不支持 (需 SQL) |
| 回滾到保存點 | $mysqli->rollback('sp1') | mysqli_rollback($link, 'sp1') | 不支持 |
| 釋放保存點 | $mysqli->release_savepoint('sp1') | mysqli_release_savepoint($link, 'sp1') | 不支持 |
autocommit(false);
try {
// 2. 業務邏輯：扣款
$stmt = $mysqli->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
$stmt->bind_param('dii', $amount, $fromAccount, $amount); // 余額檢查在 SQL 層
$stmt->execute();
if ($stmt->affected_rows === 0) {
throw new \RuntimeException("Insufficient balance or account not found: $fromAccount");
}
$stmt->close();
// 3. 可選保存點：入賬前
$mysqli->savepoint('before_credit');
// 4. 業務邏輯：入賬
$stmt = $mysqli->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
$stmt->bind_param('di', $amount, $toAccount);
$stmt->execute();
if ($stmt->affected_rows === 0) {
throw new \RuntimeException("Target account not found: $toAccount");
}
$stmt->close();
// 5. 提交事務
$mysqli->commit();
return true;
} catch (\Throwable $e) {
// 6. 異常回滾 (自動釋放鎖、撤銷變更)
$mysqli->rollback();
// 記錄日誌、拋出業務異常
error_log("Transfer failed: " . $e->getMessage());
throw new \RuntimeException("Transfer failed: " . $e->getMessage(), 0, $e);
} finally {
// 7. 恢復自動提交 (歸還連接池時必須)
$mysqli->autocommit(true);
}
}
MySQL 不支持真正嵌套事務，但可用保存點模擬子事務：
function complexOrderProcess(\mysqli $mysqli, array $items): void
{
$mysqli->autocommit(false);
try {
$orderId = createOrderHeader($mysqli); // 內部自行 savepoint/commit/rollback
foreach ($items as $item) {
$sp = "item_{$item['id']}";
$mysqli->savepoint($sp);
try {
createOrderLine($mysqli, $orderId, $item);
deductInventory($mysqli, $item);
$mysqli->release_savepoint($sp); // 子操作成功
} catch (\Throwable $e) {
$mysqli->rollback($sp); // 僅回滾當前商品操作
// 記錄失敗商品，繼續處理下一個
logFailedItem($item, $e);
}
}
$mysqli->commit(); // 整單提交
} catch (\Throwable $e) {
$mysqli->rollback();
throw $e;
} finally {
$mysqli->autocommit(true);
}
}
$mysqli->begin_transaction(MYSQLI_TRANS_START_READ_ONLY);
// 或
$mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); // 顯式讀寫
// 好處：只讀事務不分配回滾段、不加鎖、可在從庫執行、優化器可選擇更快計劃
$mysqli->autocommit(false);
$mysqli->query("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"); // 默認級別
// 事務內所有 SELECT 見到同一快照 (事務啟動時刻)
// 適用：報表導出、對賬、批量計算需一致性視圖
// 注意：長事務持有快照導致 Undo Log 膨脹、History List Length 增長，影響 Purge
function withDeadlockRetry(callable $callback, int $maxRetries = 3, int $baseDelayMs = 50): mixed
{
for ($attempt = 0; $attempt < $maxRetries; $attempt++) {
try {
return $callback();
} catch (\mysqli_sql_exception $e) {
if ($e->getCode() === 1213 || $e->getCode() === 1205) { // ER_LOCK_DEADLOCK / ER_LOCK_WAIT_TIMEOUT
$delay = $baseDelayMs * (2 ** $attempt) + random_int(0, 50); // 指數退避 + 抖動
usleep($delay * 1000);
continue;
}
throw $e;
}
}
throw new \RuntimeException("Deadlock retry exhausted after $maxRetries attempts");
}
// 使用
withDeadlockRetry(function() use ($mysqli, $from, $to, $amt) {
return transferFunds($mysqli, $from, $to, $amt);
});
autocommit(true)，否則下一個借用者繼承「事務中」狀態 → 數據不一致、鎖泄漏。autocommit(false) 下連接斷開會隱式回滾，顯式調用利於日誌審計。READ ONLY 事務，利於從庫分流、減少主庫壓力。mysqli_autocommit() 和 START TRANSACTION SQL → 狀態不同步。finally 塊中漏寫 autocommit(true) → 連接池污染。TransactionalExecutor 服務，業務代碼只寫 callback，事務邊界/重試/日誌/監控全自動化。事務是數據一致性的基石，**MySQLi 提供了完整的面向對象事務 API**，配合 保存點模擬嵌套、只讀事務優化、死鎖指數退避重試、連接池邊界管理，能構建出健壯、高性能、可觀測的數據訪問層。建議將事務控制封裝為基礎設施代碼，業務邏輯零感知。
