How to make DB::transaction wait between tries?

Question

I have a scheduled import process that is run queued in a background. Each iteration is complex enough to better be run in transaction to keep data consistent. Everything goes well while process performs sequintially.

But sometimes user may run some part of this process manually, and in this case user-initiated process may interfere with background one and cause deadlocks.

I tried to decrease amount of deadlocks by passing the $attempts argument to DB::transaction() method, but it seems like all attempts are usually run immediately one after one. It doesn't help a lot to avoid deadlocks because another process usually doesn't have time to finish its part of the work.

So, the question is: how can I make it wait a bit more before the next try to let other processes finish their work and free locked tables? Haven't found anything in the docs so far.

---

Of cause it'd be better to optimize iteration to make transactions shorter, but this approach seems to take too much time and cost more than is appropriate, so I'm looking for a fast-n-dirty way to fix it just by moving steps apart in time. The first step was to schedule all parts of this process chain, one by one. It helped, but the user's activity still causes deadlocks so far.

Answer 1

Ideally, in such cases I would usually remove the DB transaction ( I have encountered too many deadlocks over the years, finally stopped using it ) and manually undo any changes in case of fails, or make your code flexible enough to understand that there's something wrong and attempt to fix itself.

But I understand that you wish to just get a quick solution for this. You've mentioned background queues, so I'm gonna assume it's Laravel's queue. If that is indeed the case, and the user initiated process also is processed by queue, then you can use withoutOverlapping middleware to ensure that the jobs do not get executed in parallel.

So suppose you have job A ( big job ) and job B ( small job ). You wish to ensure both of these are never executed in parallel. So your code would be something like

use Illuminate\Queue\Middleware\WithoutOverlapping;

class A
{
    ....
    /**
     * Get the middleware the job should pass through.
     *
     * @return array<int, object>
     */
    public function middleware(): array
    {
        return [new WithoutOverlapping($this->someIdentifier)];
    }
}

class B
{
    ....
    /**
     * Get the middleware the job should pass through.
     *
     * @return array<int, object>
     */
    public function middleware(): array
    {
        return [new WithoutOverlapping($this->someIdentifier)];
    }
}

The value returned by $this->someIdentifier ( just an example, you can use different variables, constants, etc ) would be used as a lock key, and jobs having this same lock key will not be executed in parallel.

Tinkering around with this should help you with most of the cases you encounter.