Laravel: Using try...catch with DB::transaction()

Question

We all use DB::transaction() for multiple insert queries. In doing so, should a try...catch be placed inside it or wrapping it? Is it even necessary to include a try...catch when a transaction will automatically fail if something goes wrong?

Sample try...catch wrapping a transaction:

// try...catch
try {
    // Transaction
    $exception = DB::transaction(function() {

        // Do your SQL here

    });

    if(is_null($exception)) {
        return true;
    } else {
        throw new Exception;
    }

}
catch(Exception $e) {
    return false;
}

The opposite, a DB::transaction() wrapping a try...catch:

// Transaction
$exception = DB::transaction(function() {
    // try...catch
    try {

        // Do your SQL here

    }
    catch(Exception $e) {
        return $e;
    }

});

return is_null($exception) ? true : false;

Or simply a transaction w/o a try...catch

// Transaction only
$exception = DB::transaction(function() {

    // Do your SQL here

});

return is_null($exception) ? true : false;

Lying

Answer 1

In the case you need to manually 'exit' a transaction through code (be it through an exception or simply checking an error state) you shouldn't use DB::transaction() but instead wrap your code in DB::beginTransaction and DB::commit/DB::rollback():

DB::beginTransaction();

try {
    DB::insert(...);
    DB::insert(...);
    DB::insert(...);

    DB::commit();
    // all good
} catch (\Exception $e) {
    DB::rollback();
    // something went wrong
}

See the transaction docs.

Dessertspoon

What's the difference between DB::beginTransaction() and DB:transaction() ? – Casi

DB::transaction accepts an anonymous function for the DB statements to run inside the transaction, DB::beginTransaction() requires the DB statements to be written 'next to' the invocation (as per the example above) and then a final DB::commit() or DB::rollback() to finish the transaction off. – Dessertspoon

Simple question : What happen if you don't do a rollback after exception , or if you don't catch exception ? Auto rollback after the end of the script ? – Haemostatic

Unfortunately I have no idea, but yes I would imagine that the transaction stays open, happily swallowing up further DB inserts/updates and then finally when the application ends, the whole thing is rolled back. It would be quite easy to throw together a quick test script to try it out. You may find you get a "transaction not exited" style exception as well as an automatic rollback. – Dessertspoon

Laravel5.3 Database Transaction is a big issue it really not work – Fyke

@HengSopheak this question was about Laravel 4 databases so it's quite possible my answer is no longer correct for 5.3. It might be worth you asking a new question with the Laravel 5.3 tag to get the right community support. – Dessertspoon

i have try much about Laravel5.3.19 on DB transaction but it simply not work as well and now I try it more time to valid my practice and I have really more tutorial about this cause but they said is not bug. give me try again. but I really sure with this cause because i have update LR framework with latest version via company update and install – Fyke

i use Db:commit as if doesn't work, how can make it working – Searchlight

FYI we're currently using this pattern successfully with Laravel 5.5. – Mestee

dont forget to rethrow the exception or else your migration will look 'completed' to the runner and the entry will be placed in your migrations table – Mouthpiece

Can I use Eloquent operations inside the Try? That will rollback? – Mochun

After 2hrs of pulling out my hair I have discovered that my table engine is MyISAM. – Endbrain

@Dessertspoon don't you think DB::commit(); should be outside of try. DB::beginTransaction(); try { DB::insert(...); // all good } catch (\Exception $e) { DB::rollback(); // something went wrong } DB::commit(); – Buckish

To me, no. If something goes wrong during the commit, we may still want to try to roll back. That said, this is a very old answer to an old Laravel question and I haven't done PHP proper in a few years now, so I'm certainly not any authority on this any more. It may not even be possible to roll back from an exception raised during commit, so it may have to go outside, though be sure not to call commit after rollback too, which you would if you only move commit to after the try/catch and had an exception during the try part. – Dessertspoon

@Dessertspoon ... If exception occur in try block then control goes in catch and rollback command will execute. and even if after catch block error (before commit statement) the result of sql query in try block will not save in db as the sql statement is is transaction block. Right?? – Buckish

Yeah a rollback will "cancel" the transaction. But then the code will try to to call "commit" I dunno if that'll error itself (nothing to commit/not in a transaction) or whether it'll be a no-op. Just warning you you might want to ensure commit isn't called after rollback if it would error in any way. – Dessertspoon

Out of curiosity, why can't you use DB::transaction() instead inside of a try block with a catch block afterwards? – Schelling

Answer 2

If you use PHP7, use Throwable in catch for catching user exceptions and fatal errors.

For example:

DB::beginTransaction();

try {
    DB::insert(...);    
    DB::commit();
} catch (\Throwable $e) {
    DB::rollback();
    throw $e;
}

If your code must be compartable with PHP5, use Exception and Throwable:

DB::beginTransaction();

try {
    DB::insert(...);    
    DB::commit();
} catch (\Exception $e) {
    DB::rollback();
    throw $e;
} catch (\Throwable $e) {
    DB::rollback();
    throw $e;
}

Commiserate

What about the fact that DB::beginTransaction() may also throws \Exception? Should it be included in the try/catch? – Hogen

If transaction has not been started, we do not need to rollback anything. Moreother, it is no good to try rollback not started transaction in catch block. Therefore good place for DB::beginTransaction() is before try block. – Commiserate

Answer 3

You could wrapping the transaction over try..catch or even reverse them, here my example code I used to in laravel 5,, if you look deep inside DB:transaction() in Illuminate\Database\Connection that the same like you write manual transaction.

Laravel Transaction

public function transaction(Closure $callback)
    {
        $this->beginTransaction();

        try {
            $result = $callback($this);

            $this->commit();
        }

        catch (Exception $e) {
            $this->rollBack();

            throw $e;
        } catch (Throwable $e) {
            $this->rollBack();

            throw $e;
        }

        return $result;
    }

so you could write your code like this, and handle your exception like throw message back into your form via flash or redirect to another page. REMEMBER return inside closure is returned in transaction() so if you return redirect()->back() it won't redirect immediately, because the it returned at variable which handle the transaction.

Wrap Transaction

try {
    $result = DB::transaction(function () use ($request, $message) {
        // execute query 1
        // execute query 2
        // ..
    });          
    // redirect the page
    return redirect(route('account.article'));
} catch (\Exception $e) {
    return redirect()->back()->withErrors(['error' => $e->getMessage()]);
}

then the alternative is throw boolean variable and handle redirect outside transaction function or if your need to retrieve why transaction failed you can get it from $e->getMessage() inside catch(Exception $e){...}

Overmaster

I used transaction without try-catch block and It worked well too – Turaco

@hamidrezasamsami yes, the database automatic rolled back, but sometime you need to know are the queries all succeed or not.. – Overmaster

The "Wrap Transaction" example is wrong. This will always commit, even if one of the queries failed because all exceptions are caught within the transaction callback. You want to put the try/catch outside of DB::transaction. – Ewer

After the "Wrap Transaction" code was updated on Jan 20, 2022 to reflect @redmallard's suggestion, I feel that this should be the correct answer. Also I think that the Exception/Throwable dichotomy is mostly a waste of time, so write all of my handlers as catch(\Exception $e){...} with the leading backslash to prevent ambiguity. It seems that \Throwable is meant more for framework internals, but if someone has a legitimate use case, it would be helpful to comment here – Dail

But DB::beginTransaction also throws a Throwable, so based on that, shouldn't it also be inside the try {}? – Needle

Answer 4

I've decided to give an answer to this question because I think it can be solved using a simpler syntax than the convoluted try-catch block. The Laravel documentation is pretty brief on this subject.

Instead of using try-catch, you can just use the DB::transaction(){...} wrapper like this:

// MyController.php
public function store(Request $request) {
    return DB::transaction(function() use ($request) {
        $user = User::create([
            'username' => $request->post('username')
        ]);

        // Add some sort of "log" record for the sake of transaction:
        $log = Log::create([
            'message' => 'User Foobar created'
        ]);

        // Lets add some custom validation that will prohibit the transaction:
        if($user->id > 1) {
            throw AnyException('Please rollback this transaction');
        }

        return response()->json(['message' => 'User saved!']);
    });
};

You should see that in this setup the User and the Log record cannot exist without eachother.

Some notes on the implementation above:

• Make sure to return anything the transaction, so that you can use the response() you return within its callback as the response of the controller.
• Make sure to throw an exception if you want the transaction to be rollbacked (or have a nested function that throws the exception for you automatically, like any SQL exception from within Eloquent).
• The id, updated_at, created_at and any other fields are AVAILABLE AFTER CREATION for the $user object (for the duration of this transaction at least). The transaction will run through any of the creation logic you have. HOWEVER, the whole record is discarded when SomeCustomException is thrown. An auto-increment column for id does get incremented though on failed transactions.

Tested on Laravel 5.8

Acrefoot

Answer 5

I'm using Laravel 8 and you should wrap the transaction in a try-catch as follows:

try {
    DB::transaction(function () {
        // Perform your queries here using the models or DB facade
    });
}
catch (\Throwable $e) {
    // Do something with your exception
}

Nassir

Answer 6

In Laravel 8, you can use DB::transaction in try-catch. for example :

try{
    DB::transaction(function() {
        // do anything
    });
}
catch(){
    // do anything
}

if each query fails on try, the catch block be run.

New update on November 18, 2023. Answer to the Carlos's comment

You don't need to use "DB::rollback" in the catch block because if an exception is thrown within the transaction closure, the transaction will automatically be rolled back and the exception will be re-thrown. If you want to handle "DB::rollback" in the catch block, it's better to use transactions manually:

DB::beginTransaction();

try {
    DB::update('update users set votes = 1');
    DB::delete('delete from posts');
    //if everything goes well
    DB::commit();
    
} catch (\Exception $e) {
    //if something goes wrong
    DB::rollback();
}   

You can follow Laravel Documents.

Boeschen

Can I use "DB::rollback" in catch despite the fact that there is no a transaction there? or for using that I need to move "DB:transaction" before "try"? – Intractable

I edited my post to answer Carlos's comment. – Boeschen

Answer 7

First: using PostgreSQL database in Laravel makes things more tricky.

If you don't rollback after a transaction error, each futher queries will throw this error In failed sql transaction: ERROR: current transaction is aborted, commands ignored until end of transaction block. So if you can't save original error message in a table BEFORE the rollback.

try {
    DB::beginTransaction(); //start transaction
    $user1 = User::find(1);
    $user1->update(['money' => 'not_a_number']); //bad update
}
catch(Exception $exception) {
    $user2 = User::find(2); // ko, "In failed sql transaction" error
    $user2->update(['field' => 'value']);
}

try {
    DB::beginTransaction(); //start transaction
    $user1 = User::find(1);
    $user1->update(['money' => 'not_a_number']); //bad update
}
catch(Exception $exception) {
    DB::rollBack();
    $user2 = User::find(2); // ok, go on
    $user2->update(['field' => 'value']);
}

Second: pay attention to Eloquent model attributes system.

Eloquent model keeps changed attributes after an update error, so if we want to update that model inside the catch block, we need to discard bad attributes. This isn't a dbtransaction affair, so the rollback command is useless.

try {
    DB::beginTransaction(); //start transaction
    $user1 = User::find(1);
    $user1->update(['money' => 'not_a_number']); //bad update
}
catch(Exception|Error $exception) {
    DB::rollBack();
    $user1->update(['success' => 'false']); // ko, bad update again
}

try {
    DB::beginTransaction(); //start transaction
    $user1 = User::find(1);
    $user1->update(['money' => 'not_a_number']); //bad update
}
catch(Exception|Error $exception) {
    DB::rollBack();
    $user1->discardChanges(); // remove attribute changes from model
    $user1->update(['success' => 'false']); // ok, go on
}

Dude

Answer 8

I would suggest you head over to your config/database.php and change the engine to InnoDB.

over MyISAM which is the default engine for MySQL database MyISAM supports transactions. adding it in through the config will ensure that all table created afterwards will have it as default.

  <?php

use Illuminate\Support\Str;

return [

  // Other settings we don't care about.

    'connections' => [
      
        // Other connections we don't care about

        'mysql' => [
            // Other mysql configurations we don't care about
            'engine' => 'InnoDB',
        ],
    ]
];

Intonate

Answer 9

Laravel documentation:

You may use the transaction method provided by the DB facade to run a set of operations within a database transaction. If an exception is thrown within the transaction closure, the transaction will automatically be rolled back and the exception is re-thrown. If the closure executes successfully, the transaction will automatically be committed. You don't need to worry about manually rolling back or committing while using the transaction method

Kinder