对比实验:https://www.cnblogs.com/xiandedanteng/p/11960320.html
注:setQueryTimeout语句还是好用的,但有些环境不支持,下文是在单位虚拟机上的Oracle发生的事情,而setQueryTimeout语句在我家机器上的Oracle是支持的,详情请见。
本以为,遇到其它session导致行锁发生的情况,设置Statement.setQueryTimeout(seconds)就好了,至少不会让程序等待太长时间,但是事与愿违,我发现无论是设置自动Commit还是手动commit,setQueryTimeout都不好用.
似乎有人也遇到了和我一样的情况:https://community.oracle.com/thread/552257
我的数据库是: 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
两端设置超时无效的程序如下:
package tablelock; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; public class Deleter { private static Logger log = Logger.getLogger(Deleter.class); public void doDelete() { 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 void main(String[] args) { Deleter d=new Deleter(); d.doDelete(); } }
package tablelock; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; public class Deleter2 { private static Logger log = Logger.getLogger(Deleter2.class); public void doDelete() { 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 void main(String[] args) { Deleter2 d=new Deleter2(); d.doDelete(); } }
在以下程序的log.info处设置断点就能使上面两个Deleter执行不下去:
package tablelock; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import org.apache.log4j.Logger; public class Selecter { private static Logger log = Logger.getLogger(Selecter.class); public void doSelectfor() { 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 void main(String[] args) { Selecter d=new Selecter(); d.doSelectfor(); } }
具体是何原因还有待查.
--To be continued-- 2019-11-29 10:47