mysqli 或 PDO,**棄用 mysql_* 函數**(PHP 7.0+ 已移除)。$mysqli = new mysqli('localhost', 'user', 'pass', 'bank_db');
$mysqli->set_charset('utf8mb4');
try {
// 1. 關閉自動提交
$mysqli->begin_transaction(); // 等同 START TRANSACTION
// 或 $mysqli->autocommit(false);
// 2. 業務操作
// 扣款
$stmt = $mysqli->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?');
$stmt->bind_param('iii', $amount, $fromId, $amount);
$stmt->execute();
if ($stmt->affected_rows === 0) throw new Exception('餘額不足或賬戶不存在');
// 入賬
$stmt = $mysqli->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');
$stmt->bind_param('ii', $amount, $toId);
$stmt->execute();
if ($stmt->affected_rows === 0) throw new Exception('目標賬戶不存在');
// 記錄流水
$stmt = $mysqli->prepare('INSERT INTO transactions (from_id, to_id, amount, created_at) VALUES (?, ?, ?, NOW())');
$stmt->bind_param('iii', $fromId, $toId, $amount);
$stmt->execute();
// 3. 提交事務
$mysqli->commit();
echo "轉賬成功";
} catch (Throwable $e) {
// 4. 回滾事務
$mysqli->rollback();
echo "轉賬失敗: " . $e->getMessage();
} finally {
$mysqli->close();
}
begin_transaction() / autocommit(false):開啟事務,後續 SQL 不會立即寫入磁盤。commit();任一失敗 rollback() 撤銷所有已執行操作。innodb_lock_wait_timeout (默認 50s),超時自動回滾當前語句,事務可選繼續或回滾。SET TRANSACTION ISOLATION LEVEL READ COMMITTED 調整。$pdo = new PDO('mysql:host=localhost;dbname=bank_db;charset=utf8mb4', 'user', 'pass', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預處理
]);
try {
$pdo->beginTransaction();
// ... 同樣的 prepare/execute ...
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
// 處理異常
}
CREATE/ALTER/DROP TABLE) 會隱式提交。mysql_* 與 mysqli 連接,事務不生效。commit(),導致事務在連接關閉時隱式回滾。事務是關係數據庫保證數據一致性的基石。掌握 begin/commit/rollback、預處理語句、異常處理、死鎖預防,即可構建可靠的金融級業務邏輯。生產環境建議封裝 TransactionManager 統一管理嵌套事務、保存點、重試策略。