Oracle JDBC ReadTimeout QueryTimeout
If a query exceeds the oracle.jdbc.ReadTimeout without receiving any data, an exception is thrown and the connection is terminated by the Oracle driver on the client. Unfortunately the session will still be queued on the database and continue to wait for locks, hold any current locks, and complete any DML/PL*SQL procedures that are pending on the server-side of the orphaned connection.
If an application, on a another connection, due to ReadTimeout exception, retries DML/PL*SQL which requires locks, those queries will queue behind the initial DML/PL*SQL. This will increase the workload exacerbating the situation. On applications with retries, this can be observed by querying the v$session table or gv$session on RAC and noting new sessions started periodically based on the ReadTimeout interval. The sessions may often have the same SQL_ID and/or SQL_HASH_VALUE.
If stmt.setQueryTimeout(Seconds) is issued and the statement exceeds the timeout, it will attempt to cancel the associated in-progress or queued DML/PL*SQL. Assuming the connection is still alive, it will not leave locks and pending or in-progress DML/PL*SQL procedures on the database. One potential down side with QueryTimeout is that if data is trickling in from the server, it won't flexibly extend the timeout like ReadTimeout.
Whenever a ReadTimeout occurs, an attempt should be made to cleanup the orphaned server-side session to eliminate locks, and pending and in-progress DML/PL*SQL. Of course this would have to occur out-of-band on another connection. You will need a connection ID.
When connections are abruptly terminated (no TCP fin/ack), they will continue to be queued, execute, hold locks, and block other queries until detected and terminated which by default is ~2 hours. In order to detect and terminate orphaned database connection/sessions, sqlnet.ora sqlnet.expire_time and/or the OS level tcp_keepalive_interval should be set. The sqlnet.ora sqlnet.expire_time is not set by default and the tcp_keepalive_interval is set to ~2 hours. To set tcp_keepalive settings, see below or follow this link: How to set tcp_keepalive options. The expire_time and tcp_keepalive will not detect and terminate connections killed by JDBC Connection ReadTimeout.
Abnormal TCP connection termination can occur due to OS or VM crashes, network issues, or Firewall failures. You can easily simulate this scenario by pulling the network cable on the JDBC client system. Issuing Ctrl+C or kill -9 does not cause the issues as they both appear to cause the TCP connection to close.
If an application, on a another connection, due to ReadTimeout exception, retries DML/PL*SQL which requires locks, those queries will queue behind the initial DML/PL*SQL. This will increase the workload exacerbating the situation. On applications with retries, this can be observed by querying the v$session table or gv$session on RAC and noting new sessions started periodically based on the ReadTimeout interval. The sessions may often have the same SQL_ID and/or SQL_HASH_VALUE.
If stmt.setQueryTimeout(Seconds) is issued and the statement exceeds the timeout, it will attempt to cancel the associated in-progress or queued DML/PL*SQL. Assuming the connection is still alive, it will not leave locks and pending or in-progress DML/PL*SQL procedures on the database. One potential down side with QueryTimeout is that if data is trickling in from the server, it won't flexibly extend the timeout like ReadTimeout.
Whenever a ReadTimeout occurs, an attempt should be made to cleanup the orphaned server-side session to eliminate locks, and pending and in-progress DML/PL*SQL. Of course this would have to occur out-of-band on another connection. You will need a connection ID.
When connections are abruptly terminated (no TCP fin/ack), they will continue to be queued, execute, hold locks, and block other queries until detected and terminated which by default is ~2 hours. In order to detect and terminate orphaned database connection/sessions, sqlnet.ora sqlnet.expire_time and/or the OS level tcp_keepalive_interval should be set. The sqlnet.ora sqlnet.expire_time is not set by default and the tcp_keepalive_interval is set to ~2 hours. To set tcp_keepalive settings, see below or follow this link: How to set tcp_keepalive options. The expire_time and tcp_keepalive will not detect and terminate connections killed by JDBC Connection ReadTimeout.
Abnormal TCP connection termination can occur due to OS or VM crashes, network issues, or Firewall failures. You can easily simulate this scenario by pulling the network cable on the JDBC client system. Issuing Ctrl+C or kill -9 does not cause the issues as they both appear to cause the TCP connection to close.
| Oracle® Database JDBC Developer's Guide 11g Release 2 (11.2) Similar issues have been documented by others Howto set tcp_keepalive settings: Persistent changes: Edit /etc/sysctl.conf and add/update net.ipv4.tcp_keepalive_time=7200 net.ipv4.tcp_keepalive_intvl=75 net.ipv4.tcp_keepalive_probes=9 sudo /etc/rc.d/init.d/network restart Temporary changes: sudo sysctl -w net.ipv4.tcp_keepalive_time=60 net.ipv4.tcp_keepalive_intvl=6 net.ipv4.tcp_keepalive_probes=10 cat /proc/sys/net/ipv4/tcp_keepalive_time cat /proc/sys/net/ipv4/tcp_keepalive_intvl cat /proc/sys/net/ipv4/tcp_keepalive_probes OR sudo su echo 7200 > /proc/sys/net/ipv4/tcp_keepalive_time echo 75 > /proc/sys/net/ipv4/tcp_keepalive_intvl echo 9 > /proc/sys/net/ipv4/tcp_keepalive_probes Sqlnet.ora timeout setting: Typical path and format: /oracle_ee/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora SQLNET.EXPIRE_TIME=1 Tests:
Oracle 11g R2 EE 11.2.0.1.0 64 bit with
Partitioning
Oracle Linux Server Release 6.2
Kernel Linux
2.6.32-300.3.1.el6uek.x86_64
ojdbc6-11.2.0.2.0.jar
sqlnet.ora sqlnet.EXPIRE_TIME set to 1
tcp_keepalive set to defaults
net.ipv4.tcp_keepalive_time=7200
net.ipv4.tcp_keepalive_intvl=75
net.ipv4.tcp_keepalive_probes=9
create table foo (name varchar2(50)); insert into foo(name) values('foo'); insert into foo(name) values('bar'); commit; select * from foo where name='bar' for update; set serveroutput on; declare startDate date; tmpVar foo.name%TYPE; begin select sysdate into startDate from dual; select name into tmpVar from foo where name='foo' for update; dbms_output.put_line('total time' || numtodsinterval(sysdate-startDate, 'day')); end; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class JdbcIdleTestB { public static void main(String[] args) throws ClassNotFoundException, SQLException { String jdbcUrl = "jdbc:oracle:thin:@10.20.10.44:1521:orcl"; String user = "system"; String pwd = "fubar1"; //see below for formmatted version String lockFooAndSleepSql = "DECLARE startDate date := sysdate; tmpVar1 foo.name%TYPE; tmpVar2 foo.name%TYPE; waitTimeSec NUMBER := ?; endDate date; curDate date; BEGIN select sysdate+(waitTimeSec*(1/86400)) into endDate from dual; SELECT name INTO tmpVar1 FROM foo WHERE name='foo' FOR UPDATE; SELECT name INTO tmpVar2 FROM foo WHERE name='bar' FOR UPDATE; WHILE sysdate < endDate LOOP select sysdate into curDate from dual; END LOOP; END;"; String waitTimeSec = "600000"; lockFooAndSleepSql = lockFooAndSleepSql.replace("?", waitTimeSec); String readTimeout = "10000"; // mS System.setProperty("oracle.jdbc.ReadTimeout", readTimeout); Class.forName("oracle.jdbc.OracleDriver"); System.out.println("Loaded driver: oracle.jdbc.OracleDriver"); Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection(jdbcUrl, user, pwd); System.out.println("Connected"); conn.setAutoCommit(false); stmt = conn.createStatement(); System.out.println("Performing sql: " + lockFooAndSleepSql); stmt.executeUpdate(lockFooAndSleepSql); } finally { if (conn != null) { // System.out.println("stmt.close"); // conn.close(); } } } } /* DECLARE startDate DATE := sysdate; tmpVar1 foo.name%TYPE; tmpVar2 foo.name%TYPE; waitTimeSec NUMBER := ?; endDate DATE; curDate DATE; BEGIN SELECT sysdate+(waitTimeSec*(1/86400)) INTO endDate FROM dual; SELECT name INTO tmpVar2 FROM foo WHERE name='foo' FOR UPDATE; SELECT name INTO tmpVar1 FROM foo WHERE name='bar' FOR UPDATE; WHILE sysdate < endDate LOOP SELECT sysdate INTO curDate FROM dual; END LOOP; END; */ |
Comments