How do PHP and MySQL handle concurrent requests?
<p>I must be missing something about how PHP/Symfony handles concurrent requests, or perhaps how to handle potential concurrent queries on the database...</p>
<p>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.</p>
<p>As far as I know, this is my logical outline:</p>
<ol>
<li>Get all entries with NULL stop time</li>
<li>Loop through the entries</li>
<li>Continue only if the input date (UTC) is different from the current date (UTC)</li>
<li>Set the stop time for open entries to 23:59:59 and flush to database</li>
<li>Build a new entry starting at 00:00:00 the next day</li>
<li>Assert that there are no other open entries at this location</li>
<li>Assert that there are no future entries at this location</li>
<li>Only this - flush new entries to the database</li>
</ol>
<p>Controller automatically turns off and on</p>
<pre class="brush:php;toolbar:false;">//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;
}</pre>
<p>我什至在这里使用 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. </p>
<pre class="brush:php;toolbar:false;">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);
}
}
}</pre>
<p>Do I need to use some kind of "lock database" method to enable this functionality to achieve what I want to do? </p>
<p>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...</p>
