MySQLTransactionRollbackException: Deadlock found when trying to get lock

0 0

MySQLTransactionRollbackException: Deadlock found when trying to get lock0

代码:

logger.info("第一步：获取参数...");
List<CodeDO> codeDoList = adSspDataMapperDAO
.selectListByKey(AppConstants.SSP_API_ADXZONE_PARAM);
// 获取job时间参数
String datastatus = getSspParam(codeDoList,
AppConstants.SSP_API_ADXZONE_PARAM_DATASTATUS);
/**
* 查询ssp 同步数据时的设定同步的时间首先判断状态位 name: 1 跑全量,2跑增量,date
* 跑特定时间段的数据(设定一个比当前早的时间段格式：yyyy-mm-dd) 默认为2
*
* @param key
* @return
*/
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<NameValuePair> formParams = new ArrayList<NameValuePair>();
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<AdZoneSspDo> 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<AdZoneSspDo> addAdZoneSspDoLists = new ArrayList<AdZoneSspDo>();
for (AdZoneSspDo adZoneSspDo : adZoneSspDoList) {
String creativeType = CreativeTypeUtils.getCreativeType(adZoneSspDo
.getCreative_type());
/**
* 轮播
*/
List<String> 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<List<AdZoneSspDo>> adZoneSspDoLists = ListUtils.splitList(
addAdZoneSspDoLists, batchCount);
for (List<AdZoneSspDo> 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
<foreach collection="list" item="item" index="index"
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配置:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
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">

<context:property-placeholder location="classpath:jdbc.properties" />
    <context:component-scan base-package="com.emar.adx.accurate.dsp.dal"/>


<bean id="accurateDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="${db.mysql.jdbc.driverClassName}" />
<property name="jdbcUrl" value="${db.mysql.jdbc.url}" />
<property name="user" value="${db.mysql.jdbc.username}" />
<property name="password" value="${db.mysql.jdbc.password}" />
<property name="acquireIncrement" value="5" />
<property name="idleConnectionTestPeriod" value="3000" />
<property name="checkoutTimeout" value="3000" />
<property name="maxPoolSize" value="80" />
<property name="minPoolSize" value="1" />
<property name="maxStatements" value="6000" />
<property name="initialPoolSize" value="5" />
</bean>


<bean id="accurateTransactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="accurateDataSource" />
</bean>
<tx:annotation-driven transaction-manager="accurateTransactionManager" />
<aop:aspectj-autoproxy proxy-target-class="true" />
<aop:config proxy-target-class="true" />


<bean id="accurateSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="accurateDataSource" />
<property name="typeAliasesPackage" value="com.emar.adx.accurate.dsp.dal.dataobject" />
<property name="plugins">
<array>
<bean class="com.eamr.adx.accurate.utils.MapInterceptor" />
</array>
</property>
</bean>


<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.emar.adx.accurate.dsp.dal" />
<property name="sqlSessionFactoryBeanName" value="accurateSqlSessionFactory" />
</bean>

</beans>

问题补充：之前是一个一个更新也报这个错误,不是每次都抱错,是久不久报一次这样的错误.
下面是那段代码,原来是一条一条更新,最后改称REPLACE into了.
logger.info("第六步：操作ad_zone表...");
//操作ad_zone
adSspDataMapperDAO.batchinsertAdZone(adZoneSspDoList);
//List<AdZoneSspDo> newAdZoneList = new ArrayList<AdZoneSspDo>();
/*for (AdZoneSspDo adZoneSspDo : c) {
int count = adSspDataMapperDAO.updateAdZoneById(adZoneSspDo);
if (count == 0) {
newAdZoneList.add(adZoneSspDo);
}
}*/
/*List<List<AdZoneSspDo>> newAdZoneLists = ListUtils.splitList(
newAdZoneList, batchCount);
for (List<AdZoneSspDo> list : newAdZoneLists) {

}*/