PHP optimistic locking combined with transaction deduction balance failed: How to ensure that the balance is correctly deducted in concurrency situations?

PHP Optimistic Locking Combined with Database Transactions to Deduct Balance: Problem Analysis and Solution

This article discusses how to avoid concurrency problems that lead to failure of balance deduction or inconsistent data when using optimistic locks and database transactions for balance deduction in PHP environment. We will analyze the error code and provide the correct solution.

Problem code analysis and error reasons:

The following code snippet attempts to ensure the correctness of balance deductions in concurrent environments through optimistic locks and transactions, but has flaws:

Error code snippet 1:

 public function userbuy()
{
    $user = $this->getuser(); 
    $oldmoney = $user['balance']; 
    $orderoffer = $this->getordermoney($orderid); 
    if($oldmoney error('Insufficient Account Balance');

    //Optimistic locking solution (wrong points)
    $newmoney = $oldmoney - $orderoffer['price']; 
    $newuser = smsuser::where(['id' => $user['id'],'balance' => $oldmoney])->find();
    if(!$newuser) $this->error('user does not exist');
    //Open database transaction db::transaction(function () use($newuser,$orderid,$newmoney){
        $newuser->balance = $newmoney;
        $result = $newuser->save();
        if(!$result) $this->error('Save balance failed');

         //Create an order code
         //Deduct inventory code
         //Create user balance change record code    
        db::commit(); //Commit transaction (excessive operation)        
    });
}

Cause of error:

1. Optimistic lock position error: find() method is executed outside the transaction. Multiple concurrent requests get the same $oldmoney value. Only save() operation of the first request succeeds, and subsequent requests fail because balance is no longer equal to $oldmoney . The conditional judgment of optimistic locking should be made in update statement.

2. db::commit() redundant: db::transaction() method itself will automatically commit the transaction unless an exception rollback occurs. Manual call to db::commit() is redundant and may mask errors.

Error code snippet 2:

 public function userbuy()
{
    // ... (The code is the same as above, the part is omitted) ...

    //Optimistic locking solution (wrong points)
    $newUser->balance = $newMoney;
    $result = $newUser->save();
    if(!$result) $this->error('Save balance failed');

    //Open database transaction (Error)
    Db::transaction(function() use(){
         //Create an order code
         //Deduct inventory code
         //Create user balance change record code    
        Db::commit(); //Commit transaction (excessive operation)         
    });
}

Cause of error:

The balance update operation is executed outside the transaction. If subsequent operations (creating orders, deducting inventory, etc.) fail, the balance has been updated, resulting in inconsistent data.

Correct solution:

The conditional judgment of optimistic locking should be placed in the database update statement, and all operations that need to ensure atomicity should be included in the same transaction.

Correct code:

 public function userbuy()
{
    $user = $this->getuser();
    $oldmoney = $user['balance'];
    $orderoffer = $this->getordermoney($orderid);
    if ($oldmoney error('Insufficient Account Balance');

    $newmoney = $oldmoney - $orderoffer['price'];

    // Use optimistic locking mechanisms for databases (such as MySQL)
    $affectedRows = smsuser::where('id', $user['id'])
        ->where('balance', $oldmoney)
        ->update(['balance' => $newmoney]);

    if ($affectedRows === 0) {
        $this->error('Balance update failed, there may be concurrent conflict'); // Optimistic lock failed return;
    }

    // Enable the transaction, including all subsequent operations db::transaction(function () use ($orderid, $newmoney, $user) {
        // Create an order code
        // Deduct inventory code
        // Create user balance change record code (make sure these operations are also included in the transaction)
    });
}

This solution places balance updates and subsequent operations in the same transaction and uses the optimistic locking mechanism provided by the database to ensure data consistency. If any operation fails, the transaction will automatically roll back to ensure data security. Avoid manually committing transactions, simplifying code and improving readability. If the number of rows affected by the update statement is 0, it means that the optimistic lock fails and concurrent conflicts need to be handled. Such situations can be handled using retry mechanisms or other strategies. The specific optimism lock implementation depends on the database system used.

The above is the detailed content of PHP optimistic locking combined with transaction deduction balance failed: How to ensure that the balance is correctly deducted in concurrency situations?. For more information, please follow other related articles on the PHP Chinese website!