PHP实现Coins表多行列选择与更新：600枚币跨用户划转方案

Hey there! Let's break down how to solve this problem effectively. The goal is to deduct exactly 600 coins from the coins table (which holds 2000 coins across other users) either from one user who has ≥600 coins, or a combination of users whose total adds up to exactly 600. We need to make sure this is done safely (no partial deductions, no race conditions) and efficiently.

Before diving into code, let's outline the non-negotiables:

• Atomicity: Use database transactions to ensure all updates succeed or fail together—no half-done deductions that leave the database in an inconsistent state.
• Row Locking: Prevent concurrent modifications to the same rows while we're processing them. Without this, two transactions could try to deduct from the same user at the same time, leading to incorrect balances.
• Efficiency: Prioritize users with larger balances first when we need multiple users—this minimizes the number of rows we have to update, which is faster and cleaner.

We'll handle this in two sequential scenarios:

Scenario 1: Single User with Enough Coins #

First, check if any single user has ≥600 coins. If we find one, deduct exactly 600 from their balance—this is the simplest and most efficient case.

Scenario 2: Multiple Users #

If no single user has enough, we'll select users in descending order of their balance (so we take the biggest chunks first) until we've accumulated at least 600 coins. Then we adjust the last deduction to make the total exactly 600 (since we might have overshot if the last user's balance is more than the remaining amount we need).

We'll use PDO here because it's secure, supports transactions, and is widely adopted. Let's start with the code:

First, Set Up the Database Connection #

$dsn = 'mysql:host=your_db_host;dbname=your_database;charset=utf8mb4';
$dbUser = 'your_username';
$dbPass = 'your_password';

try {
    $pdo = new PDO($dsn, $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Throw errors as exceptions
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

Core Deduction Function #

function deductExactCoins(PDO $pdo, int $targetAmount = 600): bool {
    try {
        // Start a transaction to ensure atomicity
        $pdo->beginTransaction();

        // --- Scenario 1: Check for a single user with enough coins ---
        $singleUserStmt = $pdo->prepare("
            SELECT id, user_id, balance 
            FROM coins 
            WHERE balance >= :target 
            FOR UPDATE
        ");
        $singleUserStmt->execute([':target' => $targetAmount]);
        $singleUser = $singleUserStmt->fetch(PDO::FETCH_ASSOC);

        if ($singleUser) {
            // Deduct exactly the target amount from this user
            $updateStmt = $pdo->prepare("
                UPDATE coins 
                SET balance = balance - :amount 
                WHERE id = :id
            ");
            $updateStmt->execute([
                ':amount' => $targetAmount,
                ':id' => $singleUser['id']
            ]);
            $pdo->commit();
            return true;
        }

        // --- Scenario 2: Combine multiple users ---
        // Select users with positive balances, sorted by largest first, lock rows for update
        $multiUserStmt = $pdo->prepare("
            SELECT id, user_id, balance 
            FROM coins 
            WHERE balance > 0 
            ORDER BY balance DESC 
            FOR UPDATE
        ");
        $multiUserStmt->execute();
        $availableUsers = $multiUserStmt->fetchAll(PDO::FETCH_ASSOC);

        $totalDeducted = 0;
        $updateQueue = [];

        foreach ($availableUsers as $user) {
            if ($totalDeducted >= $targetAmount) break;

            // Calculate how much to take from this user (either their full balance or the remaining amount needed)
            $deductAmount = min($user['balance'], $targetAmount - $totalDeducted);
            $updateQueue[] = [
                'id' => $user['id'],
                'deduct' => $deductAmount
            ];
            $totalDeducted += $deductAmount;
        }

        // Check if we can't reach the target amount (shouldn't happen per your problem statement, but good to handle)
        if ($totalDeducted < $targetAmount) {
            $pdo->rollBack();
            throw new Exception("Insufficient total coins available across all users");
        }

        // Perform all the update queries
        foreach ($updateQueue as $update) {
            $updateStmt = $pdo->prepare("
                UPDATE coins 
                SET balance = balance - :deduct 
                WHERE id = :id
            ");
            $updateStmt->execute($update);
        }

        // Commit the transaction if all steps succeeded
        $pdo->commit();
        return true;

    } catch (Exception $e) {
        // Roll back any changes if something went wrong
        $pdo->rollBack();
        error_log("Coin deduction failed: " . $e->getMessage());
        return false;
    }
}

// Usage example
$deductionSuccess = deductExactCoins($pdo);
if ($deductionSuccess) {
    echo "Successfully deducted exactly 600 coins!";
} else {
    echo "Deduction failed. Check server logs for details.";
}

• FOR UPDATE Clause: This is essential—it locks the rows we select so other transactions can't modify them until our transaction is done. This prevents race conditions where two processes try to deduct from the same user simultaneously.
• Transaction Safety: Wrapping everything in beginTransaction(), commit(), and rollBack() ensures that if any part of the process fails (e.g., a database error), all changes are reverted. No partial deductions!
• Efficiency: By sorting users by balance descending, we minimize the number of rows we need to update. For example, taking 500 from one user and 100 from another is better than taking 100 from six different users.

内容的提问来源于stack exchange，提问作者Yusuf Sarikaya