String sqlInsert="insert into "+T_EqpRwphRecords+" values('"+oldWphBean.getEqptype()+"','"+oldWphBean.getEqpid()+"','"+oldWphBean.getRecipe()+"','"+oldWphBean.getBatchsize()+"'," +
"'"+oldWphBean.getWph()+"','"+oldWphBean.getSpt()+"','"+oldWphBean.getConfirm()+"'," +
"'"+oldWphBean.getUpdate_time()+"',"+oldWphBean.getPeakwph()+","+oldWphBean.getPeakspt()+")";
String sqlUpdate="update "+T_EqpRWph+" set batchsize='"+wphBean.getBatchsize()+"',wph='"+wphBean.getWph()+"'," +
"spt='"+wphBean.getSpt()+"',confirm='"+wphBean.getConfirm()+"',update_time=sysdate,peak_wph='"+wphBean.getPeakwph()+"'," +
"peak_spt='"+wphBean.getPeakspt()+"' where eqptype = '"+wphBean.getEqptype()+"'and eqpid='"+wphBean.getEqpid()+"' and recipe='"+wphBean.getRecipe()+"'";
System.out.println(sqlInsert+"\n"+sqlUpdate);
double peakwph2=0,peakspt2=0,batchsize=0;
List Beans = null;//一个tool多条数据,
OeeWphBean bean = null; //一个recipe的数据
String eqptype="",eqpid="",recipe="";
try {
conn = this.dbc.getConn();
st=conn.createStatement();
st.addBatch(sqlInsert);
st.addBatch(sqlUpdate);
st.executeBatch();
eqptype = wphBean.getEqptype();
eqpid = wphBean.getEqpid();
recipe = wphBean.getRecipe();
batchsize = wphBean.getBatchsize();
if (!this.isWphAllNull(eqptype, eqpid)&&!this.isSPTAllNull(eqptype, eqpid)){
peakwph2 = this.getAvgWph(eqptype, eqpid);
peakspt2 = this.getAvgSPT(eqptype, eqpid);
//this.updateBeanBatch(eqptype, eqpid,batchsize, peakwph2, peakspt2);
String sql_update = " update EQP_R_WPH set peak_wph='" +peakwph2 + "',peak_spt='" + peakspt2 + "',batchsize='" + batchsize+"',update_time=sysdate where eqptype='" + eqptype +"' and eqpid='" + eqpid + "' ";
st.addBatch(sql_update);
st.executeBatch();
在昨天测试自己开发的项目过程出现了出现从没见过的BUG,
在单步调试的过程,发现执行st.executeBatch();语句时,调试的光标就消失了,第一次这种感觉很神奇的事情,在反复调试的过程中,发现还是出现这个问题,
然后DBA查看oracle发现有一个正在执行程序中消失光标出的sql语句,并且一直都不能执行完成,然后杀掉session后,继续反复测试和杀session后(必须杀了,如果不杀,程序在第一次执行executeBatch方法时就丢失单步调试的光标),怀疑是事务提交的问题,后改代码为:
conn = this.dbc.getConn();
conn.setAutoCommit(false);
st=conn.createStatement();
st.addBatch(sqlInsert);
st.addBatch(sqlUpdate);
st.executeBatch();
this.dbc.getConn().commit();
conn.setAutoCommit(true);
//更新平均值
eqptype = wphBean.getEqptype();
eqpid = wphBean.getEqpid();
recipe = wphBean.getRecipe();
batchsize = wphBean.getBatchsize();
if (!this.isWphAllNull(eqptype, eqpid)&&!this.isSPTAllNull(eqptype, eqpid)){
peakwph2 = this.getAvgWph(eqptype, eqpid);
peakspt2 = this.getAvgSPT(eqptype, eqpid);
//this.updateBeanBatch(eqptype, eqpid,batchsize, peakwph2, peakspt2);
String sql_update = " update EQP_R_WPH set peak_wph='" +peakwph2 + "',peak_spt='" + peakspt2 + "',batchsize='" + batchsize+"',update_time=sysdate where eqptype='" + eqptype +"' and eqpid='" + eqpid + "' ";
st.addBatch(sql_update);
st.executeBatch();
测试后程序正常,总结原因发现,在数据库进行增、删、改操作记录时时未提交事务,如果其他事务也操作相同记录时,就出现死锁现象。
感谢DBA和另一位同事的细心配合和指导。