PHP + MySQL 事務實現轉賬功能：ACID 原則與 mysqli 實戰

業務場景

用戶 A 向用戶 B 轉賬 8000 元，必須保證「扣款」與「入賬」要么同時成功，要么同時失敗，避免資金憑空消失或產生。

數據庫準備

CREATE TABLE `account` (
    `userID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NOT NULL,
    `cash` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  -- 必須使用支持事務的引擎

INSERT INTO `account` (`name`, `cash`) VALUES ('userA', 20000.00), ('userB', 10000.00);

事務處理完整代碼

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

$mysqli = new mysqli("localhost", "root", "", "test");
if ($mysqli->connect_errno) {
    die("連接失敗: " . $mysqli->connect_error);
}

$price = 8000;
$success = true;

// 1. 關閉自動提交，開啟事務
$mysqli->autocommit(false);

try {
    // 2. 查詢餘額（加排他鎖 FOR UPDATE 防止併發超賣）
    $stmt = $mysqli->prepare("SELECT cash FROM account WHERE name = ? FOR UPDATE");
    $stmt->bind_param('s', $name);
    $name = 'userA';
    $stmt->execute();
    $stmt->bind_result($cash);
    $stmt->fetch();
    $stmt->close();

    if ($cash < $price) {
        throw new Exception("餘額不足: $cash");
    }

    // 3. 扣款
    $stmt = $mysqli->prepare("UPDATE account SET cash = cash - ? WHERE name = ?");
    $stmt->bind_param('ds', $price, $name);
    $name = 'userA';
    $stmt->execute();
    if ($stmt->affected_rows !== 1) throw new Exception("扣款失敗");
    $stmt->close();

    // 4. 入賬
    $stmt = $mysqli->prepare("UPDATE account SET cash = cash + ? WHERE name = ?");
    $stmt->bind_param('ds', $price, $name);
    $name = 'userB';
    $stmt->execute();
    if ($stmt->affected_rows !== 1) throw new Exception("入賬失敗");
    $stmt->close();

    // 5. 提交事務
    $mysqli->commit();
    echo "轉賬成功！";

} catch (Exception $e) {
    $mysqli->rollback();
    echo "轉賬失敗: " . $e->getMessage();
} finally {
    // 6. 恢復自動提交
    $mysqli->autocommit(true);
    $mysqli->close();
}

// 驗證結果
$mysqli2 = new mysqli("localhost", "root", "", "test");
$stmt = $mysqli2->prepare("SELECT cash FROM account WHERE name = ?");
$stmt->bind_param('s', $name);
$name = 'userA';
$stmt->execute();
$stmt->bind_result($cash);
$stmt->fetch();
echo "用戶 userA 餘額: $cash";
$stmt->close();
$mysqli2->close();

關鍵點深度解析

擴展：分佈式事務方案

微服務架構下，跨庫/跨服務轉賬需引入 TCC (Try-Confirm-Cancel)、Saga 模式 或 Seata/事務消息 等最終一致性方案，超出單機事務範圍。