什么是开始交易的更好方法?内部过程还是PHP函数?
例如,我像这样调用MySQL过程:
function sendLeaguesToDb(){
$leagues = "";
try{
$this->PDO->beginTransaction();
$stmt = $this->PDO->prepare("call insupd_Leagues(:id,:name,:country,:sport_id,:his_data,:fixtures,:livescore,
:numofmatches,:latestmatch)");
$leagues=$this->soccer->GetAllLeagues();
foreach($leagues as $key=>$value){
$stmt->bindParam(':id',$value->Id);
$stmt->bindParam(':name',$value->Name);
$stmt->bindParam(':country',$value->Country);
$stmt->bindParam(':sport_id',$value->Sport_Id);
$stmt->bindParam(':his_data',$value->Historical_Data);
$stmt->bindParam(':fixtures',$value->Fixtures);
$stmt->bindParam(':livescore',$value->Livescore);
$stmt->bindParam(':numofmatches',$value->NumberOfMatches);
$stmt->bindParam(':latestmatch',$value->LatestMatch);
$stmt->execute();
$this->PDO->commit();
}
}
catch(XMLSoccerException $e){
echo "XMLSoccerException: ".$e->getMessage();
}
catch(PDOException $e){
echo "PDOException: ".$e->getMessage();
$this->PDO->rollback();
}
}
如果我想每分钟/每小时最快地发送/获取数据,这是好方法吗?
这取决于您要实现的目标。
如果要将所有插入操作视为"原子操作",则操作正确,就像对 SP 的一次调用失败一样,回滚将撤消先前调用所做的所有更改
否则,如果要"隔离"每个 SP 调用,确保如果成功,结果存储在数据库中,则必须在 SP 中启动和结束事务
我认为首选解决方案是第一个
编辑:我现在注意到的一件事:提交应该在 for 之后:
try{
$this->PDO->beginTransaction();
$stmt = $this->PDO->prepare("call insupd_Leagues(:id,:name,:country,:sport_id,:his_data,:fixtures,:livescore,
:numofmatches,:latestmatch)");
$leagues=$this->soccer->GetAllLeagues();
foreach($leagues as $key=>$value){
$stmt->bindParam(':id',$value->Id);
$stmt->bindParam(':name',$value->Name);
$stmt->bindParam(':country',$value->Country);
$stmt->bindParam(':sport_id',$value->Sport_Id);
$stmt->bindParam(':his_data',$value->Historical_Data);
$stmt->bindParam(':fixtures',$value->Fixtures);
$stmt->bindParam(':livescore',$value->Livescore);
$stmt->bindParam(':numofmatches',$value->NumberOfMatches);
$stmt->bindParam(':latestmatch',$value->LatestMatch);
$stmt->execute();
}
//move your commit here
$this->PDO->commit();
}