PHP/Symfony与MySQL并发请求处理及重复实体问题排查

我对PHP/Symfony处理并发请求、MySQL处理并发查询的机制存在疑问：

我编写的autocloseAndOpen函数用于自动关闭跨天（UTC时区）的Entry实体（将stop设为23:59:59），并创建次日00:00:00的新Entry实体。但该函数每月会随机生成重复的新Entry实体。

我推测原因是两个客户端同时发起请求时，两个线程同时执行SELECT查询获取stop为NULL的Entry，随后都为该Entry设置stop时间并创建新Entry。

我已添加checkOpenEntries函数检查目标位置是否存在未关闭的Entry，但问题仍偶发。现咨询是否需要通过数据库锁机制来解决该并发问题，相关代码如下：

Controller autocloseAndOpen #

//if entry spans daybreak (midnight) close it and open a new entry at the beginning of next day
private function autocloseAndOpen($units) {

    $now = new \DateTime("now", new \DateTimeZone("UTC"));

    $repository = $this->em->getRepository('App\Entity\Poslog\Entry');
    $query = $repository->createQueryBuilder('e')
    ->where('e.stop is NULL')
        ->getQuery();
    $results = $query->getResult(); 

    if (!isset($results[0])) {
        return null; //there are no open entries at all
    }

    $em = $this->em;
    $messages = "";

    foreach ($results as $r) {
        if ($r->getPosition()->getACRGroup() == $unit) { //only touch the user's own entries

            $start = $r->getStart();

            //Assert entry spanning datebreak
            $startStr = $start->format("Y-m-d"); //Necessary for comparison, if $start->format("Y-m-d") is put in the comparison clause PHP will still compare the datetime object being formatted, not the output of the formatting.
            $nowStr = $now->format("Y-m-d"); //Necessary for comparison, if $start->format("Y-m-d") is put in the comparison clause PHP will still compare the datetime object being formatted, not the output of the formatting.

            if ($startStr < $nowStr) {
                $stop = new \DateTimeImmutable($start->format("Y-m-d")."23:59:59", new \DateTimeZone("UTC"));
                $r->setStop($stop);
                $em->flush();
                
                $txt = $unit->getName() . " had an entry in position (" . $r->getPosition()->getName() . ") spanning datebreak (UTC). Automatically closed at " . $stop->format("Y-m-d H:i:s") . "z.";
                $messages .= "<p>" . $txt . "</p>";

                //Open new entry
                $newStartTime = $stop->modify('+1 second');

                $entry = new Entry();
                $entry->setStart( $newStartTime );
                $entry->setOperator( $r->getOperator() );
                $entry->setPosition( $r->getPosition() );
                $entry->setStudent( $r->getStudent() );
                $em->persist($entry);

                //Assert that there are no future entries before autoopening a new entry
                $futureE = $this->checkFutureEntries($r->getPosition(),true);
                $openE = $this->checkOpenEntries($r->getPosition(), true);

                if ($futureE !== 0 || $openE !== 0) {
                    $txt = "Tried to open a new entry for " . $r->getOperator()->getSignature() . " in the same position (" . $r->getPosition()->getName() . ") next day but there are conflicting entries.";
                    $messages .= "<p>" . $txt . "</p>";
                } else {
                    $em->flush(); //store to DB
                    $txt = "A new entry was opened for " . $r->getOperator()->getSignature() . " in the same position (" . $r->getPosition()->getName() . ")";
                    $messages .= "<p>" . $txt . "</p>";
                }

            }
        }

    }

    return $messages;
}

checkOpenEntries函数 #

private function checkOpenEntries($position,$checkRelatives = false) {

    $positionsToCheck = array();
    if ($checkRelatives == true) {
        $positionsToCheck = $position->getRelatedPositions();
        $positionsToCheck[] = $position;
    } else {
        $positionsToCheck = array($position);
    }

    //Get all open entries for position
    $repository = $this->em->getRepository('App\Entity\Poslog\Entry');
    $query = $repository->createQueryBuilder('e')
    ->where('e.stop is NULL and e.position IN (:positions)')
    ->setParameter('positions', $positionsToCheck)
        ->getQuery();
    $results = $query->getResult();     

    if(!isset($results[0])) {
        return 0; //tells caller that there are no open entries
    } else {
        if (count($results) === 1) {
            return $results[0]; //if exactly one open entry, return that object to caller
        } else {
            $body = 'Found more than 1 open log entry for position ' . $position->getName() . ' in ' . $position->getACRGroup()->getName() . ' this should not be possible, there appears to be corrupt data in the database.';
            $this->email($body);
                
            $output['success'] = false;
            $output['message'] = $body . ' An automatic email has been sent to ' . $this->globalParameters->get('poslog-email-to') . ' to notify of the problem, manual inspection is required.';
            $output['logdata'] = null;
            return $this->prepareResponse($output);
        }
    }
}

我已模拟多种场景测试，大部分时间运行正常，但每月仍会出现一次重复实体问题。

内容的提问来源于stack exchange，提问作者Matt Welander