CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
MVP
MVP

Reputation: 113

how to work with laravel transaction rollback

I am trying to execute some queries which are dependant on each other, so what I want is if any error occurs then rollback all the queries inside transaction. what I've tried so far is


        DB::transaction(function () use($user,$token,$request) {
            $billing = User_billings::create([
                'users_id' => $user->id,
                'agent_id' => null,
                'purpose' => 'Some Purpose',
                'agent_token_id'=>$token->id,
                'amount' => $request->amount,
                'payment_method' => 'Agent Token',
                'status' => 1
            ]);


            if($billing){
                $user_jorunal = User_journal::create([
                    'bill2_id' => $billing->id, //Intentionally made this error to test transaction, this should be 'bill_id'
                    'u_id' => $user->id,
                    'purpose' => 'Topup via Agent Token',
                    'debit' => $billing->amount,
                    'invoice_number' => time()
                ]);
                if($user_jorunal){

                    if($this->agentTokenBalance($request->token_id) == 0){
                        $token->status=1;
                        $token->update();
                    }
                    return response()->json(['status'=>true,'message'=>'TopUp was Successful!']);
                }
            }
        });

so when I execute this query It generates an error as SQLSTATE[HY000]: General error: 1364 Field 'bill_id' doesn't have a default value, but it also creates a row on user_billings table. Can you please specify where I am wrong?

all of the above code is running fine, be sure that there is no logical error in query except the intentional one . I am using laravel 5.7 in this project

PHP version is 7.2.19

following laravel documentation

Upvotes: 4

Views: 10479

Answers (3)

Erubiel
Erubiel

Reputation: 2972

Haven't used the DB::transaction with a callback method... but you can do the following

DB::beginTransaction(); 

$billing = new User_billings; 
$billing->users_id = $user->id;
// rest of the assignments
$billing->save();

// Rest of your code... Inserts, Deletes, Updates...

DB::commit();

You don't need to implement the DB::rollBack() in this case, if anything fails between those two lines, the transaction won't commit.

Upvotes: 3

ml59
ml59

Reputation: 1641

Create a $status variable that will make sure that everything has been creeted in db. If any error occur, all db action will be rolled back.
Below, i have adapted your code with that logic.

 $status = true;
        try
        {
            DB::beginTransaction();
            $billing = User_billings::create([
                'users_id'       => $user->id,
                'agent_id'       => null,
                'purpose'        => 'Some Purpose',
                'agent_token_id' => $token->id,
                'amount'         => $request->amount,
                'payment_method' => 'Agent Token',
                'status'         => 1,
            ]);
            $user_jorunal = User_journal::create([
                'bill2_id'       => $billing->id, //Intentionally made this error to test transaction, this should be 'bill_id'
                'u_id'           => $user->id,
                'purpose'        => 'Topup via Agent Token',
                'debit'          => $billing->amount,
                'invoice_number' => time(),
            ]);
            $status = $status && $billing && $user_jorunal;
        } catch (\Exception $e)
        {
            DB::rollBack();
            //throw $e; //sometime you want to rollback AND throw the exception
        }
        //if previous DB action are OK
        if ($status)
        {
            DB::commit();
            if ($this->agentTokenBalance($request->token_id) == 0)
            {
                $token->status = 1;
                $token->update();
            }

            return response()->json(['status' => true, 'message' => 'TopUp was Successful!']);
        } else
        {
            DB::rollBack();
            //return somme errors
        }

Please note that MyIsam engine doesn't support transaction as explained here MyIsam engine transaction support.

Upvotes: 1

Meysam Zandy
Meysam Zandy

Reputation: 296

Manually Using Transactions

use DB::beginTransaction(); to start transaction.

use DB::rollBack(); after each error.

use DB::commit(); when transaction confirmed. ;

laravel reference

Upvotes: 2

Related Questions