代碼:
logger.info("第一步：擷取參數...");
List codeDoList = adSspDataMapperDAO
.selectListByKey(AppConstants.SSP_API_ADXZONE_PARAM);
// 擷取job時間參數
String datastatus = getSspParam(codeDoList,
AppConstants.SSP_API_ADXZONE_PARAM_DATASTATUS);
logger.info("第二步：擷取job 時間形式...");
String date = null;
if (BADASTATUS_1.equals(datastatus)) {
updateExecuteDateFlag(AppConstants.SSP_API_ADXZONE_PARAM,
BATCHCOUNT_2, AppConstants.SSP_API_ADXZONE_PARAM_DATASTATUS);
} else if (BATCHCOUNT_2.equals(datastatus)) {
date = DateUtils.addDay(new Date(), DATA_COUNT);
} else {
date = datastatus;
updateExecuteDateFlag(AppConstants.SSP_API_ADXZONE_PARAM,
BATCHCOUNT_2, AppConstants.SSP_API_ADXZONE_PARAM_DATASTATUS);
}
logger.info("擷取job 時間形式date:" + date);
// 參數
String url = getSspParam(codeDoList,
AppConstants.SSP_API_ADXZONE_PARAM_URL);
String id = getSspParam(codeDoList,
AppConstants.SSP_API_ADXZONE_PARAM_ID);
String publicKey = getSspParam(codeDoList,
AppConstants.SSP_API_ADXZONE_PARAM_PUBLICKEY);
logger.info("...參數[url:" + url + "][date:" + date + "]...");
String signTime = DateUtils.format_yyyyMMddhhmmss(new Date());
String token = Md5Encrypt.md5(publicKey + signTime + id);
List formParams = new ArrayList();
formParams.add(new BasicNameValuePair("statDate", date));
formParams.add(new BasicNameValuePair("id", id));
formParams.add(new BasicNameValuePair("signTime", signTime));
formParams.add(new BasicNameValuePair("token", token));
logger.info("第三步：擷取接口資料...");
String result = HttpUtils.getRetMsgFromHttpPost(url, formParams);
if (StringUtils.isBlank(result)) {
logger.info("第三步：擷取接口資料 result is null ");
logger.info("操作到第三步結束..");
return true;
}
ObjectMapper objectMapper = new ObjectMapper();
AdZoneSspResultDo ado = null;
try {
ado = objectMapper.readValue(result, AdZoneSspResultDo.class);
} catch (Exception e) {
logger.error("error:" + e);
}
if (ado == null) {
logger.info("第三步：擷取接口資料 AdZoneSspResultDo is null ");
logger.info("操作到第三步結束..");
return true;
}
List adZoneSspDoList = ado.getRecords();
if (adZoneSspDoList == null || adZoneSspDoList.size() == 0) {
logger.info("第三步：擷取接口資料 adZoneSspDoList is null or size is 0");
logger.info("操作到第三步結束..");
return true;
}
logger.info("第四步：更新原有資料,添加新增資料...");
List addAdZoneSspDoLists = new ArrayList();
for (AdZoneSspDo adZoneSspDo : adZoneSspDoList) {
String creativeType = CreativeTypeUtils.getCreativeType(adZoneSspDo
.getCreative_type());
List codetypeList = adZoneSspDo.getCodeTypeList();
if (codetypeList.contains(AppConstants.CODE_TYPE_2)
|| codetypeList.contains(AppConstants.CODE_TYPE_4)
|| codetypeList.contains(AppConstants.CODE_TYPE_6)) {
if (StringUtils.isBlank(creativeType)) {
creativeType = AppConstants.CREATIVE_TYPE_2;
} else {
creativeType = creativeType + ","
+ AppConstants.CREATIVE_TYPE_2;
}
}
adZoneSspDo.setCreative_type(creativeType);
adZoneSspDo.transCreativeType();
int count = adSspDataMapperDAO.updateAdZoneSspData(adZoneSspDo);
if (count == 0) {
addAdZoneSspDoLists.add(adZoneSspDo);
}
}
logger.info("第五步：插入新加資料...");
List> adZoneSspDoLists = ListUtils.splitList(
addAdZoneSspDoLists, batchCount);
for (List list : adZoneSspDoLists) {
adSspDataMapperDAO.batchInsertAdSspDaTa(list);
}
logger.info("第六步：操作ad_zone表...");
//操作ad_zone
adSspDataMapperDAO.batchinsertAdZone(adZoneSspDoList);
logger.info("操作結束...");
return true;
到adSspDataMapperDAO.batchinsertAdZone(adZoneSspDoList);
的時候報了:
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
2014-06-09 08:00:01,176 [] ERROR core.JobRunShell - Job DEFAULT.adSspDataTask threw an unhandled Exception:
java.lang.RuntimeException: 11org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.emar.adx.accurate.dsp.dal.AdSspDataMapperDAO.batchinsertAdZone-Inline
### The error occurred while setting parameters
### SQL: REPLACE into `ad_zone`(id ,ad_zone_name ,ad_zone_status ,space_width ,space_height ,campaign_cpm_min_value ,fixed_campaign_cpm_min_value ,campaign_cpc_min_value ,fixed_campaign_cpc_min_value ,cpt_day_price ,cpt_hour_price ,is_cpt_day_type ,is_cpt_hour_type ,site_id ,site_name ,site_url ,site_domain ,category_id ,page_type ,page_vertical ,ad_put_modality2 ,ad_put_modality3 ,ad_put_modality1 ,slot_visi_bility ,view_form ,creative_type ,creative_attr ,code_type ,excluded_ad_category ,excluded_sensitive_category ,excluded_ad_domain ,decleare_ppb ,decleare_gd ,decleare_pab ,advertiser_black_list ,advertiser_white_list,create_date,is_img_type,richmedia_type,is_flash_type) values (? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?,now(),?,?,?) , (? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?,now(),?,?,?) , (? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?,now(),?,?,?));
問題補充：job 10分鐘跑一次;
sql 如下
REPLACE into `ad_zone`(id
,ad_zone_name
,ad_zone_status
,space_width
,space_height
,campaign_cpm_min_value
,fixed_campaign_cpm_min_value
,campaign_cpc_min_value
,fixed_campaign_cpc_min_value
,cpt_day_price
,cpt_hour_price
,is_cpt_day_type
,is_cpt_hour_type
,site_id
,site_name
,site_url
,site_domain
,category_id
,page_type
,page_vertical
,ad_put_modality2
,ad_put_modality3
,ad_put_modality1
,slot_visi_bility
,view_form
,creative_type
,creative_attr
,code_type
,excluded_ad_category
,excluded_sensitive_category
,excluded_ad_domain
,decleare_ppb
,decleare_gd
,decleare_pab
,advertiser_black_list
,advertiser_white_list,create_date,is_img_type,richmedia_type,is_flash_type) values
separator=",">
(#{item.ad_slot_id}
,#{item.ad_slot_name}
,#{item.status}
,#{item.space_width}
,#{item.space_height}
,#{item.minimum_orin_cpm}
,#{item.minimum_loc_cpm}
,#{item.minimum_orin_cpc}
,#{item.minimum_loc_cpc}
,#{item.minimum_cpd}
,#{item.minimum_cph}
,#{item.cpd_bargain}
,#{item.cph_bargain}
,#{item.site_id}
,#{item.site_name}
,#{item.page}
,#{item.domain}
,#{item.category}
,#{item.page_type}
,#{item.page_vertical}
,#{item.page_vertical_pos}
,#{item.page_horizontal_pos}
,#{item.site_pos}
,#{item.slot_visibility}
,#{item.view_form}
,#{item.creative_type}
,#{item.creative_attr}
,#{item.code_type}
,#{item.excluded_ad_category}
,#{item.excluded_sensitive_category}
,#{item.excluded_ad_domain}
,#{item.decleare_ppb}
,#{item.decleare_gd}
,#{item.decleare_pab}
,#{item.advertiser_black_list}
,#{item.advertiser_white_list},now(),#{item.isImgType},#{item.richmediaType},#{item.flashType})
問題補充：spring配置:
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
destroy-method="close">
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
問題補充：之前是一個一個更新也報這個錯誤,不是每次都抱錯,是久不久報一次這樣的錯誤.
下面是那段代碼,原來是一條一條更新,最後改稱REPLACE into了.
logger.info("第六步：操作ad_zone表...");
//操作ad_zone
adSspDataMapperDAO.batchinsertAdZone(adZoneSspDoList);
//List newAdZoneList = new ArrayList();