DoeJohn's avatar

Transaction Inside Transaction (nested transactions) - any problems?

I'm using Laravel 5.8 & MySQL (InnoDB). Will nested transactions work? For example:

        DB::transaction(function () {
            // Some code

            DB::transaction(function () {
                // Some code
            });

            // Some code
        });

I know that this may look weird and you may ask "why would you have something like this at all?"... but I was just wondering :) I tested and it seems to work normally, but maybe there's something I need to be aware of??

0 likes
3 replies
Tray2's avatar

No, it will not work properly since you can't commit each session by itself in MySQL. In the Oracle database you can you autonomous transactions where each statement can be commited without commiting everything in the session.

While it would work to write it that way you wouldn't benifit from it.

Filip_Zdravkovic's avatar

If you are using InnoDB, then it will work properly.

Take a look at: https://github.com/laravel/framework/commit/9957329279fced0a24ac3d9d02cfe99407fa8f54

So, Laravel will recognize the nested transactions (by checking the transaction level: $this->transactions > 1) and then it will check if the savepoints are supported ($this->queryGrammar->supportsSavepoints()). MySQL/InnoDB supports savepoints: https://www.w3resource.com/mysql/mysql-transaction.php#SDT.

But I'm not sure why this isn't mentioned in the documentation...

14 likes
commodoretim's avatar

@Filip_Zdravkovic Wow, this capability is tremendous. Thank you for the description and links to the commit.

Would enabling a function to encapsulate its database operations in a transaction regardless of whether its caller is using a transaction be a valid use case for nested transactions?

/**
 * Update several database records atomically
 *
 * Function does not care whether caller is using transactions
 */
function updateData()
{
    // Some logic

    DB::transaction(function () {
        // Some database updates
    });

    // Some logic
}

We are using MariaDB/InnoDB, which supports savepoints. https://mariadb.com/kb/en/savepoint/

Please or to participate in this conversation.