Laravel DB transactions with multiple commits?

Question

Can I make some checkpoints during DB transaction?

For example, when the transaction starts, I have many queries, updates, deletes and so on.

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);
    // something else here
    DB::table('posts')->delete();
});

As far as I understand, this kind of function would automatically commit everything in case of success and rollback if something goes wrong.

But would it be possible not to rollback everything in case of error, for example, like

DB::table('users')->update(['votes' => 1]);
// something else here
DB::if_successful_so_far_do_not_rollback_previous_lines();
DB::table('posts')->delete();

Do any "small inner commits" exist?

Answer 1

Yes, you can. But this also depends on the database you are using.

Laravel supports nested transactions. Which is something close to what you need.

To isolate an inner transaction, you have to wrap it inside a try-catch block. So if the inner transaction throws an exception, it will not reach the outer transaction, therefore continuing the process. But if the outer transaction throws an Exception, the whole transaction (including its nested transactions) will roll back.

So you will end up with something like this:

public function testNestedTransactions()
{

    $user = EloquentTestUser::create(['email' => '[email protected]']);

    $this->connection()->transaction(function () use ($user) {

        try {
            $this->connection()->transaction(function () use ($user) {
                $user->email = '[email protected]';
                $user->save();
                throw new Exception;
            });
        } catch (Exception $e) {}

        $user = EloquentTestUser::first();
        $this->assertEquals('[email protected]', $user->email);

    });

}

This is a test written by Otwell in this commit implementing the functionality of nested transactions in Laravel. He first create an user, then start a transaction with a nested transaction where he updates the user email and then throws an exception (meaning an error has ocurred). Right under it, he checks if the user email stills the same when he created it, and it does, because the nested transaction has rolled back, but the outer transaction stills going.