Laravel Eloquent query won't not rollback

Question

I've been trying to get this to work but it just doesn't work. I have this function where I insert multiple records into different tables at a time and I am using transaction but it won't rollback when one of the query fails.

See example:

private function valBankInvestment($bank, $investment, $investment1, $data){
   DB::beginTransaction();
   $new_bank = DB::table('bank_accounts')->insert($bank);
   $new_inv = Investments::create($investment);
   $inv_log = DB::table('investment_logs')->insertGetId($investment1);
   $trader = Traders::create($data);
   if ($new_bank && $new_inv && $inv_log && $trader) {
      DB::commit();
      return true;
   }
   DB::rollBack();
   return false;
}

What I want to achieve is that the above function should rollback if any of those queries should fail. Please help me out with this.

when you say `the query fails` is it with an exception or it retuns null in one of the variables ? — N69S, Aug 31 '20 at 15:53
There are some statements that `DB::beginTransaction()` doesn't work with, but I'm not sure if `DB::table()->insert()` is one of them. Aside from that, you'll need to do some debugging. If you do `dd(($new_bank, $new_inv, $inv_log, $trader);` before your `if()` statement, you can see what everything is. Also, since `dd()` kills execution, and `::commit()` is never called, your transaction won't finish, so you won't have fragmented data. — Tim Lewis, Aug 31 '20 at 16:47
@N69S It is with an exception but I still want it the transaction to rollback in case of any other failure like network issue etc. — Sodmond, Aug 31 '20 at 19:24
@TimLewis I still think this Transaction needs more flexibility by the Developers and maybe some more contributions by the community too because it doesn't meet up to the expectation of definition of a Transaction. — Sodmond, Aug 31 '20 at 19:30
@Sodmond Fair enough. The only real issue I've had with `DB::beginTransaction()` is that a statement like `DB::statement('CREATE TABLE ...')` doesn't respect the transaction, but that's not an issue with Laravel, but rather MySQL. So just do some testing on what logic does and doesn't respect the transaction logic and go from there. Beyond that, I don't know how MyISAM handles it differently than InnoDB, so you'll need to do more investigation there too. — Tim Lewis, Aug 31 '20 at 19:34
Can you provide any documentation link that really explains the laravel transaction in details — Sodmond, Aug 31 '20 at 20:49

score 0 · Answer 1 · answered Aug 31 '20 at 19:30

0

You need to catch the exception in order to rollback the transaction. if you dont the code will exit out without calling it

private function valBankInvestment($bank, $investment, $investment1, $data){
   DB::beginTransaction();
   try {
      $new_bank = DB::table('bank_accounts')->insert($bank);
      $new_inv = Investments::create($investment);
      $inv_log = DB::table('investment_logs')->insertGetId($investment1);
      $trader = Traders::create($data);
   } catch (\Exception $e) {
      DB::rollBack();
      return false;
   }
   if ($new_bank && $new_inv && $inv_log && $trader) {
      DB::commit();
      return true;
   } else {
      DB::rollBack();
      return false;
   }
}

answered Aug 31 '20 at 19:30

N69S

16,110
3
22
36

Will this exception catch a network failure or maximum execution time exception? – Sodmond Aug 31 '20 at 19:34
@Sodmond no, it will not. but if you have some heavy request, you should consider using a queue over cli for unlimited execution time. – N69S Aug 31 '20 at 20:32
@Sodmond to use rollback on timeout of code, you need to configure your database to do so and you will be forced to lock en entries. https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded-on-mysql good luck – N69S Aug 31 '20 at 20:40

score 0 · Accepted Answer · answered Mar 24 '23 at 16:34

The rollback method in laravel needs the tables in your database to use InnoDB engine. Rollback method doesn't work MyISAM. You can do this in 2 ways:

First, Change the default engine in your database config config/database.php and change to this line; 'engine' => 'InnoDB', in your mysql driver connection.

Second, add the line below in your migration when create a table;

$table->engine = 'InnoDB';

score -1 · Answer 3 · answered Aug 31 '20 at 21:23

The @N69S answer is also right but I like the way which is quite easy to read

private function valBankInvestment($bank, $investment, $investment1, $data){

return \DB::transaction(function () use ($bank, $investment, $investment1, $data){
    $new_bank = DB::table('bank_accounts')->insert($bank);
    $new_inv = Investments::create($investment);
    $inv_log = DB::table('investment_logs')->insertGetId($investment1);
    $trader = Traders::create($data);
    return  $new_bank && $new_inv && $inv_log && $trader;
  });
}

Laravel Eloquent query won't not rollback

3 Answers3