Command app:validations - avoid concurrent process on the same validation ids

[mborne]

I am facing problems scaling the number of background process :

sudo docker-compose up -d --scale orchestrator=3

The use of findNextPending in ValidationRepository might have to be replaced by something like :

class ValidationRepository  extends ServiceEntityRepository {

    /**
     * Update next "pending" validation to "processing" and returns it
     *
     * @return Validation|null
     */
    public function popNextPending()
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();

        $conn->beginTransaction();
        $conn->executeQuery('LOCK TABLE validation IN ACCESS EXCLUSIVE MODE;');

        /** @var Validation $result */
        $result = $this->createQueryBuilder('v')
            ->where('v.status = :status')
            ->setParameters(['status' => Validation::STATUS_PENDING])
            ->orderBy('v.dateCreation', 'ASC')
            ->getQuery()
            ->getOneOrNullResult()
        ;
        if ( is_null($result) ){
            return $result;
        }

        $result->setStatus(Validation::STATUS_PROCESSING);
        $em->flush($result);

        $conn->commit();
        return $result;
    }

}

(not tested, adapted from an old message queue implementation inspired by this kind of approach in pure SQL : https://stackoverflow.com/a/6500830)