how to handle table lock or query queue

Question

I got some duplicated row in my table but its impossible to insert via http request because I have validations for user balance that will change by every request and a request must be accepted by admin before another request. In this case The user had a balance of USD 1000. According to my code he can request once for USD 1000. but all of a sudden I got two rows at the same time in my database. how it may happen and what is the solution of this problem?

what I assume is when the 1st request arrived, the withdraw table was locked due to some other calculation so the balance was unchanged and the query was queued, as user got no response due to table lock he requested again then both requests were inserted!

please be informed that this is not happening everyday. It happens very rarely like 1 or 2 duplicate request in a year

    public function withdrawBalance(Request $request)
    {
        if ($request->api_key == ApiKey()) {

            $userInfo = User::find($request->user_id);

            $checkPendingRequest = Withdraw::where(['user_id' => $request->user_id, 'status' => 0])->get(); //status column have default 0 until admin approval

            if (count($checkPendingRequest) > 0) {
                return response()->json(['status' => false, 'action' => 'already_pending_one', 'message' => 'Already have a pending request!.']);
            }

            $gs = GeneralSetting::first();
            $balance = HoldingBalance($request->user_id)['withdrawble_balance'];


            if ($request->amount > $balance) {
                return response()->json(['status' => false, 'action' => 'insufficiant_balance', 'message' => 'Insufficient amount!.']);
            }


            if ($request->withdraw_method == 'bkash') {
                $method_name = 'Digital';
            } else {
                $method_name = $request->withdraw_method;
            }

            Withdraw::create([
                'branch_id' => $request->branch_id ?? 1,
                'user_id' => $userInfo->id ?? null,
                'purpose' => "$method_name Withdraw",
                'withdraw_method' => $request->withdraw_method ?? null,
                'withdraw_account' => $request->withdraw_account ?? null,
                'amount' => round($request->amount, 2),
                'payble_amount' => $request->amount,
                'status' => 0
            ]);
            
            return response()->json(['status' => true, 'action' => 'success', 'message' => 'Successfully submitted withdrawn request!.']);
            
        } else {
            return "Access Denied";
        }
    }

user_6700591 · Answer

I guess any user double-click "Submit" So 2 receipt requests arrived, each one performed a code level check and did not see that there was an execution of another query

I have not seen use of the table lock or transaction.

DB::beginTransaction();
check_if_data_already_exist();
add_data();
DB::commit();

This solution will greatly slow down the performance of your system.

The simplest solution is the option for each user to send one request at a given moment.

There are quite a few implementations of this problem. In order to offer possible solutions we will need to get more information: Is it possible to use the session? Can the queue be used to enter the queries? Maybe each user contains one row in the database and you can simply create a unique key at the database level

how to handle table lock or query queue

1 Answers