在一段更新数据库的代码中,居然出现了虚假的更新动作。
数据库用的是MySql,测试环境是局域网,4核机器,Mysql无事务机制,无存储过程;PHP5.2.x。
这段代码需要更新超过300条记录。
每次更新完自己后,需要调用JobManage::synchroJobStatus做同步,同步过程中有一次数据库操作。
<?php
$ibegin=0;
while (true) {
$sql = "SELECT JI_ID FROM tb_job_info WHERE JI_CompanyID={$companyID} LIMIT {$ibegin},100";
$jobList = DB::sql($sql, DBName_company);
if (empty($jobList)) {
break;
}
foreach ($jobList as $job){
$data = array(
'JS_IsAdminChecked' => $status,
'JS_AdminCheckedRemark' => $remark,
'JS_AdminCheckedTime' => time(),
);
$where = array('JS_JobID' => $job["JI_ID"]);
$statusInfo = DB::getRow('tb_job_status', $where, DBName_company,'JS_JobID');
if(empty($statusInfo)){
$iRnt = $this->addJobStatus($job['JI_ID'],$status,$remark);
if($iRnt<0){
return 0;
}
}
$iRtn += DB::edit('tb_job_status', $data, $where, DBName_company);
$statusInfo = array_merge($statusInfo,$data);
$data = array(
'LC_Type' => $isAdminCheck,
'LC_AdminID'=>$checkAdminID,
'LC_CheckStatus' => $status,
'LC_HitWords' => $remark,
'LC_UpdateTime' => time()
);
$where = array('LC_CompanyID' => $companyID,'LC_JobID' =>$job["JI_ID"]);
$exist = DB::getRow('tb_log_checkjob',$where,DBName_company,'LC_CompanyID');
if(!empty($exist)){
DB::edit('tb_log_checkjob',$data,$where,DBName_company);
}else{
$data = array_merge($data,$where,array('LC_AddTime' => time()));
$result = DB::add('tb_log_checkjob',$data,DBName_company);
}
JobManage::synchroJobStatus($statusInfo);
}
$ibegin+=100;
}
?>
更新的语句关键是这条:DB::edit('tb_job_status', $data, $where, DBName_company);
上面的问题是:
1.查询数据库,一轮300多条记录中,有100多条记录的该语句没有生效。
2.这条语句每次都返回了更新成功。
3.数据能够完整的读取,没有遗漏。
经过修改:
<?php
//$ibegin=0;
//while (true) {
$sql = "SELECT JI_ID FROM tb_job_info WHERE JI_CompanyID={$companyID}";// LIMIT {$ibegin},100";
$jobList = DB::sql($sql, DBName_company);
//if (empty($jobList)) {
// break;
//}
foreach ($jobList as $job){
$data = array(
'JS_IsAdminChecked' => $status,
'JS_AdminCheckedRemark' => $remark,
'JS_AdminCheckedTime' => time(),
);
$where = array('JS_JobID' => $job["JI_ID"]);
$statusInfo = DB::getRow('tb_job_status', $where, DBName_company,'JS_JobID');
if(empty($statusInfo)){
$iRnt = $this->addJobStatus($job['JI_ID'],$status,$remark);
if($iRnt<0){
return 0;
}
}
$iRtn += DB::edit('tb_job_status', $data, $where, DBName_company);
$statusInfo = array_merge($statusInfo,$data);
$data = array(
'LC_Type' => $isAdminCheck,
'LC_AdminID'=>$checkAdminID,
'LC_CheckStatus' => $status,
'LC_HitWords' => $remark,
'LC_UpdateTime' => time()
);
$where = array('LC_CompanyID' => $companyID,'LC_JobID' =>$job["JI_ID"]);
$exist = DB::getRow('tb_log_checkjob',$where,DBName_company,'LC_CompanyID');
if(!empty($exist)){
DB::edit('tb_log_checkjob',$data,$where,DBName_company);
}else{
$data = array_merge($data,$where,array('LC_AddTime' => time()));
$result = DB::add('tb_log_checkjob',$data,DBName_company);
}
JobManage::synchroJobStatus($statusInfo);
}
//$ibegin+=100;
//}
?>
问题不再存在· ·
何种原因,导致这个错误出现呢?
初步断定 Limit 出现了这个误差!
具体如何产生的,还有待分析。