php - 我怎样才能释放繁忙的 table ？

Question

我在数据库中有一个历史表，每 10 分钟使用 cron 从 xml feed 中填充新数据。

Xml 提要很长，我的表一直繁忙，我也在 View 端使用该表。所以我的 View 端进程正在等待，直到 cron 进程完成。

您认为我如何才能释放进程？可能使用多个连接，或者通过将数据复制到另一个表，cron 没有使用什么？

逻辑是这样的:

FUNCTION HISTORIC TO DB
BEGIN TRY
CONNECT
BEGIN TRANSACTION
BEGIN LOOP
EXECUTE
END LOOP
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
CLOSE
END CATCH

完整代码:

function resultsToHistoric(){
try{
    $con = new Connect();
    $countries = $this->ResultCountries;
    foreach($countries as $country){
        $this->PDO->beginTransaction();
        $path = "feeds/results/{$country}res.xml";
        $results = $this->parser->parseFile($path);
        $drzava = $this->filterCountries($results->attributes()->country).":";
        foreach($results->league as $league){
        $leagueid = $league->attributes()->id;
            $liga = $league->attributes()->name;
            $lg2 = '';
            if(!contains(strtolower($liga),strtolower($drzava))){
                $lg2 = ucfirst($drzava) . ' ' . ucfirst($liga);  
            }
            else{
                $lg2 = $liga;
            }
            //var_dump($league->stage->aggregate);
            $leaguename = $this->getMap2($lg2);
            $season = $league->attributes()->season;
            $lgstage = NULL;
            if($league->week){
              $weekroot = $league->week;
            }
            elseif($league->week){
              $weekroot = $league->week;
            }
            elseif($league->aggregate){
                $weekroot = $league->aggregate;
            }
            else{
                //$weekroot = $league->stage;
                foreach($league->stage as $stage){
                    if($stage->week){
                        $weekroot = $stage->week;
                    }
                    elseif($stage->aggregate){
                        $weekroot = $stage->aggregate;
                    }
                    else{
                        $weekroot = $league;
                    }
                }
            }
            foreach($weekroot as $week){
            $round  = 0;
            if($week->attributes()->number!=NULL){
                $round = $week->attributes()->number;
            }
            //print_r($week);
            foreach($week->match as $match){
                //var_dump($match);
                $fixid = 0;
                if($match->attributes()->id == 0 || $match->attributes()->id == ''){
                    if($match->attributes()->alternate_id == 0 || $match->attributes()->id == ''){
                        $fixid = $match->attributes()->alternate_id_2;
                    }
                    else{
                        $fixid = $match->attributes()->alternate_id;
                    }
                }
                else{
                    $fixid = $match->attributes()->id;
                }
                $dbdate = date('Y-m-d',strtotime($match->attributes()->date));
                $dbtime = date('H:i:s', strtotime($match->attributes()->time));

                $datetime = date('Y-m-d H:i:s',strtotime($dbdate . $dbtime));
                $fcountry = $this->filterCountries($results->attributes()->country);    

                //echo $fcountry;
                //$home = $match->home->attributes()->name;
                //$away = $match->away->attributes()->name;
                $home = '';
                $away = '';
                $homeid = 0;
                $awayid = 0;
                $hgoals = 0;
                $agoals = 0;
                if($match->home){
                $home = $this->getMap($fcountry,$match->home->attributes()->name);
                $away = $this->getMap($fcountry,$match->away->attributes()->name);
                $homeid = $this->filterTeams($fcountry,$match->home->attributes()->id);
                $awayid = $this->filterTeams($fcountry,$match->away->attributes()->id); 

                $hgoals = $match->home->attributes()->score;
                $agoals = $match->away->attributes()->score;
                }

                $eventname = $home . ' - ' . $away;

                $halftimehomegoals = 0;
                $halftimeawaygoals = 0;
                if($match->halftime->attributes()->score != NULL){
                    $halftime = explode("-",$match->halftime->attributes()->score);
                    $halftimehomegoals = (int)$halftime[0];
                    if(array_key_exists(1,$halftime)){
                        $halftimeawaygoals = (int)$halftime[1];
                    }
                }

                $homescore = '';
                $awayscore = '';
                if($match->goals->goal != NULL){
                  foreach($match->goals->goal as $goal){
                      if($goal->attributes()->team == 'home'){
                         $homescore .= $goal->attributes()->minute.': '.
                         $goal->attributes()->player.'; ';
                      }
                      elseif($goal->attributes()->team == 'away'){
                         $awayscore .= $goal->attributes()->player.'; ';
                      }
                  }
                }
                $homesquad = '';
                $awaysquad = '';
                if($match->lineups != NULL){
                  foreach($match->lineups as $lineup){
                      if($lineup->home->player != NULL){
                        foreach($lineup->home->player as $player){
                            $homesquad .= $player->attributes()->name . '; ';
                        }
                      }
                      if($lineup->away->player != NULL){
                        foreach($lineup->away->player as $player){
                            $awaysquad .= $player->attributes()->name . '; ';
                        }
                      }
                  }
                }

                $homesub = '';
                $awaysub = '';
                if($match->substitutions != NULL){
                    foreach($match->substitutions as $subs){
                        if($subs->home->substitution != NULL){
                            foreach($subs->home->substitution as $sub){
                                $homesub .= $sub->attributes()->minute."' in: ".
                                $sub->attributes()->player_in_name . '; ' . ' out: ' .
                                $sub->attributes()->player_out_name . '; ';
                            }
                        }
                        if($subs->away->substitution != NULL){
                            foreach($subs->away->substitution as $sub){
                                $awaysub .= $sub->attributes()->minute."' in: ".
                                $sub->attributes()->player_in_name . '; ' .
                                $sub->attributes()->player_out_name . '; ';
                            }
                        }
                    }
                }

                //if($homesquad!='' || $awaysquad != ''){
              $this->execHistoric($fixid,$fixid,$datetime,$round,NULL,$leaguename,$leagueid,strtolower($fcountry),$eventname, $homeid, $home, NULL, $hgoals, $halftimehomegoals,NULL,NULL,NULL,$homescore,NULL, $homesquad,NULL, NULL, NULL, NULL, NULL, NULL, NULL, $away, $awayid, NULL, $agoals, $halftimeawaygoals, NULL, NULL, NULL, $awayscore,NULL,$awaysquad,NULL,NULL,NULL, NULL, NULL, NULL, NULL, $homesub, $awaysub,NULL, NULL, NULL, NULL , 'tipgin',$season);
                //continue; 
                //}
                echo $leaguename . ' ' . $leagueid . ' ' . $fixid . ' ' . $eventname.'<br>';
                echo 'home squad: ' .$homesquad.'<br>';
                echo 'away squad: ' .$awaysquad.'<br>';
                echo 'home sub: ' .$homesub.'<br>';
                echo 'away sub: ' .$awaysub.'<br>';
                echo 'home scorers: ' .$homescore.'<br>';
                echo 'away scorers: ' .$awayscore.'<br>';
            }
             // }else continue;
            }
        }
        //error_log(" RESULT ENDED: ".$country. date('Y-m-d H:i:s'),3,'php/errorlog.txt');
        $this->PDO->commit(); //SAVE
        error_log(" ENDED HISTORIC: ".$country. date('Y-m-d H:i:s'),3,'php/errorlog.txt');
        }
        $con->close(); //CLOSE
}
catch(Exception $e){
    echo $e;
}
}

function execHistoric($hisid,$fixid,$date,$round,$spec,$league,$leagueid, $country, $eventname, $hteamid,$hteam,$hcorn,$hgoals,$hthgoals,$hshots,$hsont,$hfouls,$hgd,$hgk,$hdf,$hmdf,$hfw,$hls,$hlc,$hycard,$hrcard,$htf,$ateam,$ateamid,$acorn,$agoals,$athgoals,$ashots,$asont,$afouls,$agd,$agk,$adf,$amdf,$afw,$als,$alc,$aycard,$arcard,$atf,$hsubd,$asubd,$htycd,$atycd,$htrcd,$atrcd,$source,$season){
    $stmt = $this->PDO->prepare($this->insHis);
    $stmt->bindParam(':hisID', $hisid);
    $stmt->bindParam(':fixID',$fixid);
    $stmt->bindParam(':pdate',$date);
    $stmt->bindParam(':round',$round);
    $stmt->bindParam(':spectators',$spec);
    $stmt->bindParam(':league',$league);
    $stmt->bindParam(':leagueid',$leagueid);
    $stmt->bindParam(':country',$country);
    $stmt->bindParam(':eventname',$eventname);
    $stmt->bindParam(':homeTeam',$hteam);
    $stmt->bindParam(':homeTeamID',$hteamid);
    $stmt->bindParam(':homeCorners',$hcorn);
    $stmt->bindParam(':homeGoals',$hgoals);
    $stmt->bindParam(':halfTimeHomeGoals', $hthgoals);
    $stmt->bindParam(':homeShots', $hshots);
    $stmt->bindParam(':homeShotsOnTarget', $hsont);
    $stmt->bindParam(':homeFouls',$hfouls);
    $stmt->bindParam(':homeGoalDetails', $hgd);
    $stmt->bindParam(':homeLineupGoalkeeper',$hgk);
    $stmt->bindParam(':homeLineupDefense',$hdf);
    $stmt->bindParam(':homeLineupMidfield',$hmdf);
    $stmt->bindParam(':homeLineupForward',$hfw);
    $stmt->bindParam(':homeLineupSubstitutes',$hls);
    $stmt->bindParam(':homeLineupCoach',$hlc);
    $stmt->bindParam(':homeYellowCards',$hycard);
    $stmt->bindParam(':homeRedCards',$hrcard);
    $stmt->bindParam(':homeTeamFormation',$htf);
    $stmt->bindParam(':awayTeam',$ateam);
    $stmt->bindParam(':awayTeamID',$ateamid);
    $stmt->bindParam(':awayCorners', $acorn);
    $stmt->bindParam(':awayGoals', $agoals);
    $stmt->bindParam(':halfTimeAwayGoals', $athgoals);
    $stmt->bindParam(':awayShots',$ashots);
    $stmt->bindParam(':awayShotsOnTarget', $asont);
    $stmt->bindParam(':awayFouls',$afouls);
    $stmt->bindParam(':awayGoalDetails',$agd);
    $stmt->bindParam(':awayLineupGoalkeeper',$agk);
    $stmt->bindParam(':awayLineupDefense',$adf);
    $stmt->bindParam(':awayLineupMidfield',$amdf);
    $stmt->bindParam(':awayLineupForward',$afw);
    $stmt->bindParam(':awayLineupSubstitutes',$als);
    $stmt->bindParam(':awayLineupCoach',$alc);
    $stmt->bindParam(':awayYellowCards',$aycard);
    $stmt->bindParam(':awayRedCards',$arcard);
    $stmt->bindParam(':awayTeamFormation',$atf);
    $stmt->bindParam(':homeSubDetails', $hsubd);
    $stmt->bindParam(':awaySubDetails', $asubd);
    $stmt->bindParam(':homeTeamYellowCardDetails', $htycd);
    $stmt->bindParam(':awayTeamYellowCardDetails',$atycd);
    $stmt->bindParam(':homeTeamRedCardDetails', $htrcd);
    $stmt->bindParam(':awayTeamRedCardDetails', $atrcd);
    $stmt->bindParam(':source',$source);
    $stmt->bindParam(':season',$season);
    $stmt->execute();
}

目前我通过杀死所有进程来解决这个问题......

Answer 1

这里有几种可能的方法。最重要的是拆分读写。写入(数据库插入)是阻塞的，加载和读取 XML 不是。

您使用的是 SimpleXML，因此您的提要足够小，可以加载到内存中。检查是否可以将它们转换为数组并使用批量插入语法。

如果没有，您可以将它们转换为 CSV 并使用命令行或 INSERT FROM 文件语法。您可以将 XMLReader 与 DOM/SimpleXML 结合使用来创建 CSV。我在 another answer 中对此进行了描述.

另一种可能性是使用 XSLT 将提要转换为 MySQL 支持的格式良好的简单 XML LOAD XML syntax . XSLT 专为此类工作而设计。您可以在第二步中将 XML 文件与 XMLReader/XMLWriter 合并。

第三种方法是将数据插入临时表并使用 INSERT INTO SELECT 移动它们。

所有方法的主要目标是尽量减少在已用表上执行的语句。因此，您准备数据的方式可以使用单个 SQL 语句将它们移动到表中。