Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

wreckless's avatar

There is no active transaction on deadlock retries

I have a piece of very intense code that's wrapped in a transaction where I have 5 retry attempts set. Deadlocks occur semi-frequently, likely due to the processing time involved. I need to attempt to optimize the code to reduce the number of deadlocks. However, and regardless, when a deadlock occurs, the retry fails and an exception bubbles all the way up. The related errors are:

SQL Error: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

Laravel Error: [TRANSACTION ERROR] attempts: 2; PDOException: There is no active transaction in /home/forge/myapp/vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php:45

Here's a simplified version of the code (with custom logic inside the transaction removed for brevity):

$attempts = 1;

try {
	DB::transaction(function () use (&$attempts) {
		doAlotOfThings();
		
		$attempts++;
	}, 5);
} catch (\Throwable $ex) {
	Log::error("[TRANSACTION ERROR] attempts: $attempts; " . $e->getMessage());
}

According to the Laravel docs (I'm still on L8) and the framework code, it looks like when a deadlock occurs then it should be retried the number of times specified. However, based on my logging of attempts, it seems that it is retried once, but on the second attempt the transaction is no longer available and a fatal error is thrown which breaks out of the transaction handler.

Have I implemented this incorrectly somehow? Or is there a better way to handle deadlock retries? Any help is appreciated!

0 likes
4 replies
wreckless's avatar

Thanks @tray2 . Yeah, understanding how best to create locks in context of this particular piece of code is something that I need to look into a bit more. However, and unfortunately, I don't think that I can skip the locked records in a conflicting transaction because they have to be included in the logic within the transaction to achieve the necessary outcome. Currently, the operation in question uses a significant amount of data to calculate amounts and actions related to inventory adjustments, and I pre-load the necessary data up front with something like this:

$order->load([
    'products' => function ($q) {
        $q->withTrashed()
            ->with([
                'inventories' => fn ($q) => $q->lockForUpdate(),
                'variant' => fn($q) => $q->withSecondaryInventory(),
                'shopProduct' => function ($q) {
                    $q->with(['inventory' => fn ($q) => $q->lockForUpdate()])
                        ->withOpenInventory();
                },
        ]);
    }
]);

I use lockForUpdate because, if I understand how it works correctly, I don't want another inventory operation to read a value which will likely get updated in the current transaction until the first transaction is complete. Based on this small amount of info, does my understanding of how it works and the lock implementation seem reasonable?

Aside from that, I'm still confused as to why the transaction is dying when a deadlock occurrs instead of being gracefully retried, as the documentation suggests. Is my understanding of what transaction retries are for not correct? Is there a different or better way to handle retrying a transaction when a deadlock occurs? If just kind of feels like the Illuminate\Database\Concerns\ManagesTransactions::transaction() method needs some additional checking to ensure a valid transaction exists on retry, like:

20     public function transaction(Closure $callback, $attempts = 1)
21     {
//...
35              catch (Throwable $e) {
36                  $this->handleTransactionException(
37                      $e, $currentAttempt, $attempts
38                  );
39 
40 +                if ($this->transactions > 0) {
41 +                    //when an exception is caught but allowed to retry,
42 +                    //ensure that a valid PDO transaction exists
43 +                    //and refresh it if not
44 +                }
45  
46                  continue;
47              }
//...
70     }

I'd love any additional tips on good strategies for handling "intense" transactions. But, I'm mostly just trying to understand why the implemented transaction retry isn't working, or, more specifically, why my transaction is dying and therefore rendering the retry useless. Has anyone encountered this issue? Is there a better way to do this? Thanks!

Tray2's avatar

@wreckless That is the way the database reacts, depending on how you tell is to handle locks.

SELECT *
FROM table1
WHERE col1 = 'value'
FOR UPDATE;

Attempts to select the record for update, it will hang until a certain time has passed and then error out, if you give it the NOWAIT directive it errors out directly when the select for update is attempted and fails.

I would in your case probably go with the nowait so that it fails early.

wreckless's avatar

Thanks again, @tray2. Per my code above, I'm using Eloquent's lockForUpdate() method, so I don't think there's a way for me to specify the NOWAIT directive. If I rewrite all this with raw queries to do that, that theoretically means that the transaction attempts can fail faster and then rely on transaction retry attempts to try again.

However, and back to my main question for this post, why is the transaction retry mechanism not retrying... Or, more specifically, why, when a db lock is detected, is the transaction dying, which is then causing the retry to fail with an exception ("no active transaction"). Is there something different about the db locks I'm encountering that are killing the transactions? Isn't this scenario -- db locks -- what the eloquent transaction retry option is specifically built to mitigate?

Please or to participate in this conversation.