How do PHP and MySQL handle concurrent requests?

Question

I must be missing something about how PHP/Symfony handles concurrent requests, or perhaps how to handle potential concurrent queries on the database...

This code seems to be doing the impossible - it randomly (about once a month) creates a copy of the new entity at the bottom. My conclusion is that when two clients make the same request twice, and both threads execute a SELECT query at the same time, select an entry with stop == NULL, and then they both (?) set the stop time of that entry, it must When this happens, they both write a new entry.

As far as I know, this is my logical outline:

1. Get all entries with NULL stop time
2. Loop through the entries
3. Continue only if the input date (UTC) is different from the current date (UTC)
4. Set the stop time for open entries to 23:59:59 and flush to database
5. Build a new entry starting at 00:00:00 the next day
6. Assert that there are no other open entries at this location
7. Assert that there are no future entries at this location
8. Only this - flush new entries to the database

Controller automatically turns off and on

//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() 运行额外的检查，以查看此时该位置是否存在任何 stoptime == NULL 的条目。Initially, I thought this was redundant because I thought that if one request was running and operating on the database, another request would not start until the first request completed.

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);
        }
    }
}

Do I need to use some kind of "lock database" method to enable this functionality to achieve what I want to do?

I've tested all functionality and when I simulate various states (entering NULL for stop times, etc. even when it shouldn't be), everything works fine. Most of the time, everything works fine, but one day in the middle of the month, this happens...

Answer 1

You can never guarantee ordering (or implicit exclusive access). Give it a try and you'll dig yourself deeper and deeper.

As Matt and KIKO mentioned in the comments, you can use constraints and transactions, these should help a lot as your database will remain clean, but remember your application needs to be able to capture the database layer error generated. Definitely worth trying first.

Another way to deal with this problem is to force database/application level locking.

Database Level Locking is much cruder and very inexcusable if you forget to release the lock somewhere (in a long running script).

MySQL Documentation:

Locking the entire table is generally a bad idea, but it is possible. This depends a lot on the application.

Some ORMs support object versioning out of the box and will throw an exception if the version changes during execution. In theory, your application would hit an exception and when you try again, you'd find that someone else had already populated the field and it's no longer a candidate for updating.

Application Level Locking More granular, but all points in the code need to respect the locking, otherwise, you're back to square #1. If your application is distributed (like K8S, or just deployed on multiple servers), then your locking mechanism must also be distributed (not instance local)