您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP 支付系統核心設計：賬戶鎖、流水對賬、事務一致性、凍結機制完整架構

編輯：PHP 綜合知識

業務需求

多應用接入：提供餘額查詢、支付扣款、充值入款接口
月度結算：每月 1 號自動結算
賬戶凍結：支持風控凍結/解凍
流水對賬：每日與上游渠道核對，確保資金零差錯
高併發安全：防止超賣、重複扣款、數據不一致

數據庫設計

1. 賬戶狀態表 `tb_status`

CREATE TABLE tb_status (
    appid INT UNSIGNED PRIMARY KEY,
    freeze TINYINT NOT NULL DEFAULT 0,  -- 0正常 1凍結
    create_time DATETIME NOT NULL,
    change_time DATETIME NOT NULL
) ENGINE=InnoDB;

2. 賬戶餘額表 `tb_account_earn`

CREATE TABLE tb_account_earn (
    appid INT UNSIGNED PRIMARY KEY,
    balance BIGINT NOT NULL DEFAULT 0,  -- 分為單位，避免浮點誤差
    seqid INT NOT NULL DEFAULT 500000000, -- 樂觀鎖版本號
    create_time DATETIME NOT NULL,
    change_time DATETIME NOT NULL
) ENGINE=InnoDB;

3. 流水表 `tb_bill` (核心審計表)

CREATE TABLE tb_bill (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    bill_id INT NOT NULL,              -- 業務流水號 (從 tb_assign 分配)
    amt BIGINT NOT NULL,               -- 正數入賬，負數出賬
    bill_info TEXT,                    -- 業務詳情 JSON
    bill_user VARCHAR(128),
    bill_time DATETIME NOT NULL,
    bill_type TINYINT NOT NULL,        -- 1充值 2支付 3回滾 4結算
    bill_channel TINYINT NOT NULL,     -- 渠道來源
    bill_ret TINYINT NOT NULL,         -- -1系統錯 0成功 1邏輯錯
    appid INT UNSIGNED NOT NULL,
    old_balance BIGINT NOT NULL,       -- 操作前餘額
    price_info TEXT,                   -- 單價快照
    src_ip VARCHAR(128),
    UNIQUE KEY uk_bill (bill_id, bill_channel)
) ENGINE=InnoDB;

4. 流水號分配表 `tb_assign`

CREATE TABLE tb_assign (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assign_time DATETIME NOT NULL
) ENGINE=InnoDB;
INSERT INTO tb_assign (id, assign_time) VALUES (100000000, NOW());

5. 單價配置表 `tb_price`

CREATE TABLE tb_price (
    name VARCHAR(128) PRIMARY KEY,
    price INT NOT NULL,
    info TEXT
) ENGINE=InnoDB;

6. 應用級鎖表 `tb_applock`

CREATE TABLE tb_applock (
    appid INT UNSIGNED PRIMARY KEY,
    lock_mode TINYINT NOT NULL DEFAULT 0, -- 0空閒 1持有
    change_time DATETIME NOT NULL
) ENGINE=InnoDB;

核心業務流程

支付扣款流程 (防超賣、防重複、可回滾)

function pay($appid, $amount, $billInfo, $billChannel) {
    // 1. 獲取全局唯一流水號
    $billId = getNextBillId(); // INSERT INTO tb_assign VALUES (NULL, NOW()) 返回 insert_id
    
    // 2. 獲取應用鎖 (防併發)
    $lock = new AppLock($appid);
    if (!$lock->alloc()) return ['code' => -5, 'msg' => '系統繁忙'];
    
    try {
        // 3. 讀取賬戶狀態
        $status = db()->get("SELECT freeze FROM tb_status WHERE appid=?", $appid);
        if ($status['freeze']) throw new Exception('賬戶凍結', -4);
        
        // 4. 讀取餘額 (樂觀鎖)
        $account = db()->get("SELECT balance, seqid FROM tb_account_earn WHERE appid=?", $appid);
        if ($account['balance'] < $amount) throw new Exception('餘額不足', -3);
        
        // 5. 寫入流水 (初始狀態：未處理)
        $billData = [
            'bill_id' => $billId,
            'amt' => -$amount,  // 支出為負
            'bill_info' => json_encode($billInfo),
            'bill_type' => 2,   // 支付
            'bill_channel' => $billChannel,
            'bill_ret' => -2,   // 處理中
            'appid' => $appid,
            'old_balance' => $account['balance'],
            'price_info' => getPriceSnapshot($billInfo),
            'src_ip' => getClientIp(),
        ];
        db()->insert('tb_bill', $billData);
        
        // 6. 扣款更新餘額 (樂觀鎖 CAS)
        $newSeq = $account['seqid'] + 1;
        $affected = db()->execute(
            "UPDATE tb_account_earn SET balance=balance-?, seqid=?, change_time=NOW() 
             WHERE appid=? AND seqid=?",
            [$amount, $newSeq, $appid, $account['seqid']]
        );
        if (!$affected) throw new Exception('併發衝突，請重試', -1);
        
        // 7. 更新流水為成功
        db()->execute("UPDATE tb_bill SET bill_ret=0 WHERE bill_id=? AND bill_channel=?", [$billId, $billChannel]);
        
        $lock->free();
        return ['code' => 0, 'msg' => '成功', 'bill_id' => $billId];
        
    } catch (Exception $e) {
        // 8. 失敗寫入流水 (bill_ret = 錯誤碼)
        db()->execute("UPDATE tb_bill SET bill_ret=? WHERE bill_id=? AND bill_channel=?", [$e->getCode(), $billId, $billChannel]);
        $lock->free();
        return ['code' => $e->getCode(), 'msg' => $e->getMessage()];
    }
}

應用鎖實現 (基於數據庫行鎖 + 超時自動釋放)

class AppLock {
    private $appid;
    private $got = false;
    const MODE_FREE = 0;
    const MODE_LOCKED = 1;
    const EXPIRE_SEC = 30; // 鎖超時自動釋放
    
    public function __construct($appid) { $this->appid = $appid; }
    
    public function alloc() {
        if ($this->got) return true;
        
        // 修復過期鎖
        $this->repair();
        
        // 嘗試獲取鎖 (CAS)
        $affected = db()->execute(
            "UPDATE tb_applock SET lock_mode=?, change_time=NOW() 
             WHERE appid=? AND lock_mode=?",
            [self::MODE_LOCKED, $this->appid, self::MODE_FREE]
        );
        
        if ($affected <= 0) return false;
        $this->got = true;
        return true;
    }
    
    public function free() {
        if (!$this->got) return true;
        db()->execute(
            "UPDATE tb_applock SET lock_mode=?, change_time=NOW() WHERE appid=?",
            [self::MODE_FREE, $this->appid]
        );
        $this->got = false;
        return true;
    }
    
    private function repair() {
        // 釋放超過 30 秒未釋放的鎖 (防死鎖)
        db()->execute(
            "UPDATE tb_applock SET lock_mode=?, change_time=NOW() 
             WHERE appid=? AND lock_mode=? AND change_time < DATE_SUB(NOW(), INTERVAL ? SECOND)",
            [self::MODE_FREE, $this->appid, self::MODE_LOCKED, self::EXPIRE_SEC]
        );
    }
    
    public function __destruct() { $this->free(); }
}

對賬機制

日終對賬 SQL

-- 1. 應用端成功流水汇总
SELECT appid, SUM(amt) AS app_total
FROM tb_bill
WHERE bill_ret = 0 AND bill_time >= '2024-01-01' AND bill_time < '2024-01-02'
GROUP BY appid;

-- 2. 渠道端成功流水汇总 (需渠道提供)
-- 對比兩者，差異 > 0 觸發告警人工介入

-- 3. 餘額一致性校驗
SELECT a.appid, a.balance, COALESCE(SUM(b.amt),0) AS bill_sum
FROM tb_account_earn a
LEFT JOIN tb_bill b ON b.appid = a.appid AND b.bill_ret = 0
GROUP BY a.appid
HAVING a.balance != COALESCE(SUM(b.amt),0);

錯誤碼體系

碼值	含義	調用方處理
0	成功	記錄流水，繼續業務
-1	系統繁忙/併發衝突	重試 (指數退避)
-2	數據庫讀錯	告警，人工介入
-3	數據庫寫錯	告警，人工介入
-4	賬戶凍結	提示用戶聯繫客服
-5	獲取鎖失敗	重試
1	無數據	業務判斷
2	無權限	安全審計
3	餘額不足	提示充值