Under the hood: How database transactions work in Laravel

Continuing the under the hood series, this time about database transactions in Laravel. I won’t repeat all the things on how you can use the transactions in Laravel. If you are not familiar with the topic you can find everything in the documentation here. We’re now focusing on how these implementations work in the background, what caused us some headache lately and how to avoid it. So let’s dive in.

Transactions with closure

As you are probably already familiar with it, you can use closures in the DB facade’s transaction method, like this:

DB::transaction(function () {
    DB::update('update users set votes = 1');
    DB::delete('delete from posts');
});

Using this method you don’t need to worry about starting the transaction, committing and rolling back if anything goes wrong, it does all of that automatically.

The transaction related methods are located in the Illuminate/Database/Concerns/ManagesTransactions.php trait. You can also pass a second argument to the DB::transaction method which is the number of attempts to try the transaction again when an exception or deadlock occur. The code loops through the given number of attempt and starts a new transaction.

public function transaction(Closure $callback, $attempts = 1)
{
    for ($currentAttempt = 1; $currentAttempt <= $attempts; $currentAttempt++) {
        $this->beginTransaction();

It will try to execute the callback function, and if any exception occurs in the callback, rolls back the transaction. It will try again until it reaches the defined number of attempts:

try {
    $callbackResult = $callback($this);
}

// If we catch an exception we'll rollback this transaction and try again if we
// are not out of attempts. If we are out of attempts we will just throw the
// exception back out and let the developer handle an uncaught exceptions.
catch (Throwable $e) {
    $this->handleTransactionException(
        $e, $currentAttempt, $attempts
    );

    continue;
}

If everything went fine commits the transaction,

try {
    if ($this->transactions == 1) {
        $this->getPdo()->commit();

        optional($this->transactionsManager)->commit($this->getName());
    }

    $this->transactions = max(0, $this->transactions - 1);
} catch (Throwable $e) {
    $this->handleCommitTransactionException(
        $e, $currentAttempt, $attempts
    );

    continue;
}

returns the callback result and fires a committed event:

$this->fireConnectionEvent('committed');

return $callbackResult;

Using closures is the simplest and easiest way to use transactions in Laravel.

Manual transaction handling

Laravel allows you to control the transaction yourselves. And that is what caused us problems lately. We’ll come to that point soon, but first let’s see how it works:

• Start a transaction with DB::beginTransaction();
• If everything went well commit using DB::commit();
• Or rollback otherwise with DB::rollBack();

It works just fine, but what if you’d like to use nested transactions. MySQL for example doesn’t support nested transactions, but the InnoDB engine supports savepoints.

Laravel makes use of this feature (if supported). So when we call DB::beginTransaction() it counts the nesting level of transactions. According to this nesting level it either creates a new transaction (first time), or creates a savepoint.

if ($this->transactions == 0) {
    $this->reconnectIfMissingConnection();

    try {
        $this->getPdo()->beginTransaction();
    } catch (Throwable $e) {
        $this->handleBeginTransactionException($e);
    }
} elseif ($this->transactions >= 1 && $this->queryGrammar->supportsSavepoints()) {
    $this->createSavepoint();
}

In situations when the nesting level is more than one, calling the DB::rollBack() will decrement the counter, and roll back to the previous savepoint.

protected function performRollBack($toLevel)
{
    if ($toLevel == 0) {
        $this->getPdo()->rollBack();
    } elseif ($this->queryGrammar->supportsSavepoints()) {
        $this->getPdo()->exec(
            $this->queryGrammar->compileSavepointRollBack('trans'.($toLevel + 1))
        );
    }
}

And here comes the tricky part: the DB::commit() will only really commit to the database when the counter equals to 1. Otherwise it only decrements the counter and fires the committed event.

public function commit()
{
    if ($this->transactions == 1) {
        $this->getPdo()->commit();

        optional($this->transactionsManager)->commit($this->getName());
    }

    $this->transactions = max(0, $this->transactions - 1);

    $this->fireConnectionEvent('committed');
}

Obviously this is fine, but you need make sure that for every started transaction you call either commit or rollback. Especially when doing it in a loop.

An example when things go wrong

So here is an example, what caused headache in our case (it is pseudo code, but the essence is the same):

$products = Products::all()
foreach($products as $product){
    try { 
        DB::beginTransaction();

        if ($product->isExpensiveEnough()) {
            continue;
        }

        $product->price += 100;
        $product->save();
        DB::commit();
    }
    catch(Exception $e){
        DB::rollback();    
    }
}

So basically we want to loop through the products and change some attributes according to some conditions. In this pseudo code we want to raise the price for the products which are not expensive enough :-).

So what is the problem with the above example? In case when we update the product and everything goes well, we begin a transaction, update the model, commit. It even works well when an exception occurs, and we do a rollback.

However when the conditions are not fulfilled, and we continue , in the next iteration a new transaction gets created. The transaction counter will be increased to 2. The following commit would not do an actual commit to the database, just decrement the counter. The next iteration would begin a new transaction increase the counter to 2 etc. Once the number of beginTransaction() and commit()/rollback() method get’s “out of sync” the following iterations won’t update the records.

It is not funny when it is a command that runs for couple of hours, and at the end doesn’t update anything it should.

So the lesson we learned here is simple. Always keep in mind that every opened transaction should be concluded with a commit or rollback. Especially in loops.

The post Under the hood: How database transactions work in Laravel appeared first on Daniel Werner.