0

Good morning,

I have an issue when selecting the next record with Doctrine when there is concurrency. I have installed supervisord inside a docker container that starts multiple processes on the same "dispatch" command. The dispatch commands basically gets the next job in queue in the db and sends it to the right executor. Right now I have two docker containers that each run multiple processes through supervisord. These 2 containers are on 2 different servers. I'm also using Doctrine Optimistic locking. So the Doctrine query to find the next job in queue is the following:

$qb = $this->createQueryBuilder('job')
        ->andWhere('job.status = :todo')
        ->setMaxResults( 1 )
        ->orderBy('job.priority', 'DESC')
        ->addOrderBy('job.createdAt', 'ASC')
        ->setParameters(array("todo" => Job::STATUS_TO_DO));

        return $qb->getQuery()->getOneOrNullResult();

So the issue is that when a worker tries to get the next job with the above query, I notice that they frequently run into the Optimistic Lock Exception which is fine meaning the record is already used by another worker. When there is an Optimistic Lock Exception, it's caught and then worker stops and another one starts. But I lose a lot of time because of this, because it takes multiple tries for workers to finally get the next job instead of the Optimistic Lock exception.

I thought about getting a random job id in the above Doctrine query.

What's your take on this? Is there a better way to handle this?

Mike
  • 35
  • 7
  • When your worker finds a job to process, change its status to something like `Job::STATUS_PROCESSING` immediately, and flush it. Then proceed with whatever has to be done for this job. This should prevent another worker from selecting a job that is currently being processed. – dmnptr Aug 22 '18 at 20:31
  • That's what I did but there was still an issue. I fixed it by getting a better sql server with better latency. – Mike Aug 22 '18 at 22:06

1 Answers1

0

I finally figured it out. There was a delay between one of the server and the remote mysql so updates were not seen right away and that triggered the Optimistic Lock Exception. I fixed it by moving the mysql DB to Azure which is way faster than the old server and causes no delays.

Mike
  • 35
  • 7