Thank you. Let me look at it

@simonangatia

2 obvious issues with your code,

1. Your loop must be OUTSIDE of the try{}
2. You did not DB::rollback if there is exception

• Exit the loop if you manage to DB:commit() successfully
• But since you can return an exception unknown error, will the remote server call your Debit() with the same data again? If you can, you don't need the loop
• Your duplicate data is probably caused by multiple $trxn->save(); due to the loop

public function Debit()
{
    $data = json_decode(file_get_contents('php://input'), true);
    $username = $data['Login'];
    $user = User::where('username', $username)->first();
    if (!$user) {
        $rivalaoResponse = ["d"=>["ErrorCode"=>-10,"HasErrors"=>true,"Message"=>"InvalidPlayer"]];
        return response()->json($rivalaoResponse);
    }
    $type="debit";
    $userId = $user->id;
    $amount = $data['Amount'];
    $gameId = $data['GameId'];
    $roundId = $data['RoundId'];
    $sequence = $data['Sequence'];

    $initialBalance = $user->casino_bakiye;

    $game = XpgTransaction::where('gameId', $gameId)
                                ->where('roundId', $roundId)
                                ->where('sequence', $sequence)
                                ->first();

    DB::beginTransaction();
// for(;;){ // loop here, and exit the loop if you manage to `DB::commit()`
    try {
        if (!$game) {
            $trxn = new XpgTransaction;
            $trxn->user_id = $userId;
            $trxn ->request=json_encode($data);
            $trxn->type =$type;
            $trxn->save();

            do {
                $user->decrement('casino_bakiye', $amount);
                $trxn->casino_balance = $user->refresh()->casino_bakiye;
                $trxn->save();

                DB::commit();
            } while ($user->refresh()->casino_bakiye == $initialBalance);

            $currentBalance = $user->refresh()->casino_bakiye;
            $rivalaoResponse = ["d"=>["Data"=>[(float)$currentBalance],"ErrorCode"=>0,"HasErrors"=>false,"Message"=>""]];
            return  response()->json($rivalaoResponse);
        } else {
            $rivalaoResponse = ["d"=>["ErrorCode"=>-21,"HasErrors"=>true,"Message"=>"Duplicate transaction"]];
            return response()->json($rivalaoResponse);
        }
    } catch (\Exception $e) {

        DB::rollback(); // you have to rollback here

        $rivalaoResponse = ["d"=>["ErrorCode"=>-1,"HasErrors"=>true,"Message"=>"Unknown error"]];
        return  response()->json($rivalaoResponse);
    }
}

Rollingback does not help because the API has no that provision. When debit call comes, balance must be decremented. So Rolling back when the API has returned the same balance without decrementing won't help

@simonangatia what happens when you return ErrorCode = -1, Unknown error. What will the caller do?

That error is in their documentation though they said it should never be returned at any time

@simonangatia

A documentation that should never be followed? Then why document in the first place? This will become a very painful technical debt.

@simonangatia I think we have reached a point where you can handle the rest on your own. Do pick an answer that helped you the most. Thank you.

Even with pessimistic locking, it still happens

public function Debit(){
      $data = json_decode(file_get_contents('php://input'), true);
      $username = $data['Login'];
      $user = User::where('username', $username)->sharedLock()->first();
      if(!$user){
        $rivalaoResponse = ["d"=>["ErrorCode"=>-10,"HasErrors"=>true,"Message"=>"InvalidPlayer"]];
        return response()->json($rivalaoResponse);
      }
      $type="debit";
      $userId = $user->id;
      $amount = $data['Amount'];
      $gameId = $data['GameId'];
      $roundId = $data['RoundId'];
      $sequence = $data['Sequence'];

      $initialBalance = $user->casino_bakiye;
      
        $game = XpgTransaction::where('gameId', $gameId)
                              ->where('roundId', $roundId)
                              ->where('sequence', $sequence)
                              ->sharedLock()->first();

      DB::beginTransaction();
        
          if(!$game){
            $trxn = new XpgTransaction;
            $trxn->user_id = $userId;
            $trxn ->request=json_encode($data);
            $trxn->type =$type;
            $trxn->operatorId = $data["OperatorId"];
            $trxn->session = $data["Session"];
            $trxn->gameId =$data["GameId"];
            $trxn->roundId =$data["RoundId"];
            $trxn->debitDetails = $data["DebitDetails"];
            $trxn->amount = $data["Amount"];
            $trxn->sequence = $data["Sequence"];
            
            
            do {
              $user->casino_bakiye -= $amount;
              $user->save();
              
            } while ($user->refresh()->casino_bakiye == $initialBalance);
            
            if($user->refresh()->casino_bakiye != $initialBalance){
                 $trxn->casino_balance = $user->refresh()->casino_bakiye;
                 $trxn->save();
                 DB::commit();
                $currentBalance = $user->refresh()->casino_bakiye;
                $rivalaoResponse = ["d"=>["Data"=>[(float)$currentBalance],"ErrorCode"=>0,"HasErrors"=>false,"Message"=>""]];
                return  response()->json($rivalaoResponse); 
              }
             
          
          }else{
          $rivalaoResponse = ["d"=>["ErrorCode"=>-21,"HasErrors"=>true,"Message"=>"Duplicate transaction"]];
          return response()->json($rivalaoResponse);
          }

    }

Why is it jumping the if statement? I am just shocked by this thing

Maybe it has to do with not fully understanding race conditions yet.

I solved the PROBLEM but I remembered I didn't come back to thank those who helped me in solving it, more so @laracoft and @snapey I really thank you so much. I used Both DB::transaction and Pessimistic locks to make it work perfectly. Thank you all for your efforts

@simonangatia i'm happy for you. Do pick a best answer. Thank you.

@simonangatia You chose a wrong best reply. Who gave to you as an advice pessimistic locking and transaction in one answer?

I thought you can give two best answers :) All these helped me. The concept of race condition Is what I never came across before. And it is from there that all the answers shoot... So whoever can search for race condition is able to arrive at both locking and db transactions

This issue was solved but then there is another issue related to it. That is when multiple request are sent, since a table is locked, there is a deadlock that arises. How do you handle this so that there will not be a deadlock?

Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: insert into `xpg_transactions` (`user_id`, `request`, `type`, `operatorId`, `session`, `gameId`, `roundId`, `debitDetails`, `amount`, `sequence`, `casino_balance`, `updated_at`, `created_at`) values (75, {"Login":"XPGTEST66","OperatorId":2076,"Session":"","GameId":1,"RoundId":90923816,"Amount":437,"Sequence":1,"DebitDetails":"RO;23:5.00;153:257.00;148:160.00;9:15.00"}, debit, 2076, , 1, 90923816, RO;23:5.00;153:257.00;148:160.00;9:15.00, 437, 1, 101311.00, 2020-10-19 18:22:55, 2020-10-19 18:22:55))