oracle setquerytimeout(0),[JDBC/Oracle]设置Statement.setQueryTimeout(seconds)并不好用 原因：环境问题...

注：setQueryTimeout语句还是好用的，但有些环境不支持，下文是在单位虚拟机上的Oracle发生的事情，而setQueryTimeout语句在我家机器上的Oracle是支持的，详情请见。

本以为,遇到其它session导致行锁发生的情况,设置Statement.setQueryTimeout(seconds)就好了,至少不会让程序等待太长时间,但是事与愿违,我发现无论是设置自动Commit还是手动commit,setQueryTimeout都不好用.

我的数据库是: select * from v$version;

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PL/SQL Release 12.2.0.1.0 - Production

"CORE 12.2.0.1.0 Production"

TNS for Linux: Version 12.2.0.1.0 - Production

NLSRTL Version 12.2.0.1.0 - Production

两端设置超时无效的程序如下:

packagetablelock;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;importjava.sql.Statement;importorg.apache.log4j.Logger;public classDeleter {private static Logger log = Logger.getLogger(Deleter.class);public voiddoDelete() {

Connection conn= null;

Statement stmt= null;try{

Class.forName(DBParam.Driver).newInstance();

conn=DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);

conn.setAutoCommit(false);

stmt=conn.createStatement();

stmt.setQueryTimeout(1);//It dosen't work

String sql="delete from TestTB17 where id=2";int deleted=stmt.executeUpdate(sql);

log.info("Deleter deleted "+deleted+" records.");

conn.commit();

log.info("committed.");

}catch(Exception e) {

e.printStackTrace();//System.out.print(e.getMessage());

} finally{try{

stmt.close();

conn.close();

}catch(SQLException e) {

System.out.print("Can't close stmt/conn because of " +e.getMessage());

}

}

}public static voidmain(String[] args) {

Deleter d=newDeleter();

d.doDelete();

}

}

packagetablelock;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;importjava.sql.Statement;importorg.apache.log4j.Logger;public classDeleter2 {private static Logger log = Logger.getLogger(Deleter2.class);public voiddoDelete() {

Connection conn= null;

Statement stmt= null;try{

Class.forName(DBParam.Driver).newInstance();

conn=DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);

stmt=conn.createStatement();

stmt.setQueryTimeout(1);//It dosen't work

String sql="delete from TestTB17 where id=3";int deleted=stmt.executeUpdate(sql);

log.info("Deleter deleted "+deleted+" records.");

}catch(Exception e) {

e.printStackTrace();//System.out.print(e.getMessage());

} finally{try{

stmt.close();

conn.close();

}catch(SQLException e) {

System.out.print("Can't close stmt/conn because of " +e.getMessage());

}

}

}public static voidmain(String[] args) {

Deleter2 d=newDeleter2();

d.doDelete();

}

}

在以下程序的log.info处设置断点就能使上面两个Deleter执行不下去:

packagetablelock;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;importjava.sql.Statement;importorg.apache.log4j.Logger;public classSelecter {private static Logger log = Logger.getLogger(Selecter.class);public voiddoSelectfor() {

Connection conn= null;

Statement stmt= null;try{

Class.forName(DBParam.Driver).newInstance();

conn=DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd);

conn.setAutoCommit(false);

stmt=conn.createStatement();

String sql="select * from TestTB17 for update";

stmt.executeUpdate(sql);

log.info("Will block other session before commit/rollback.");

conn.commit();

log.info("committed.");

}catch(Exception e) {

System.out.print(e.getMessage());

}finally{try{

stmt.close();

conn.close();

}catch(SQLException e) {

System.out.print("Can't close stmt/conn because of " +e.getMessage());

}

}

}public static voidmain(String[] args) {

Selecter d=newSelecter();

d.doSelectfor();

}

}

具体是何原因还有待查.

--To be continued-- 2019-11-29 10:47