how to work with laravel transaction rollback

Question

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

score 2 · Answer 1 · answered Dec 09 '20 at 08:56

2

Manually Using Transactions

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

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

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

laravel reference

answered Dec 09 '20 at 08:56

Meysam Zandy

296
7
18

manual transaction used for more control over it, I don't need any extra control over it, although I tried manual too. but the main problem is `rollBack()` was not working after creating a row in user_billings table. – MVP Dec 09 '20 at 09:12
1

When you use Transactions, shouldn't create a row in the table before committing it. – Meysam Zandy Dec 10 '20 at 10:16

ml59 · Answer 2 · 2020-12-09T09:39:19.370

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.

I already tried but `catch (\Exception $e) { DB::rollBack(); //throw $e; //sometime you want to rollback AND throw the exception }` is not working, although there is an exception but rollback was not workin — MVP, Dec 09 '20 at 09:09
are you using the `default` connection in your `config/database.php` ? — ml59, Dec 09 '20 at 09:11
try to specify the connection with `DB::connection('mysql')->beginTransaction()` ,`DB::connection('mysql')->commit()` , `DB::connection('mysql')->rollBack()`. Also make sure to use `InnoDb` as your db engine since `MyIsam` doesn't support transaction — ml59, Dec 09 '20 at 09:24

Erubiel · Answer 3 · 2020-12-09T10:17:00.607

1

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.

edited Dec 09 '20 at 10:17

answered Dec 09 '20 at 09:08

Erubiel

2,934
14
32

its not working because I already perfomed an insert query before the exception – MVP Dec 09 '20 at 09:20
before the exception or before the transaction started? because everything in between those two lines should not be persisted if something in there fails. – Erubiel Dec 09 '20 at 09:28
you can see my code, After creating a row on user_billings table it will go for another to create a row on user_journals table and it will fail as intended. now I need to rollback user_billings as both of those should have inserted – MVP Dec 09 '20 at 10:06
Maybe is the create function, that might be taking the transaction out of the process since that is for mass assignment... try using a different approach in there like $billing = new User_billings; $billing->user_id = $user->id .... $billing->save() – Erubiel Dec 09 '20 at 10:14
changed the whole code in that approach, but still the rollback is not working – MVP Dec 09 '20 at 10:32
I think it's what the other user mentioned about MyIsam and InnoDb... are you using InnoDb or MyIsam ? – Erubiel Dec 09 '20 at 10:35
it was null before in database.php but now I have changed it to InnoDB – MVP Dec 09 '20 at 10:47

how to work with laravel transaction rollback

3 Answers3