JDBC Connection Timeout

JDBC connection timeout works quite differently. E.g. for HTTP connection if you specify timeout, it will timeout and thread control will be given back to the caller. In JDBC connection timeout, it doesn't work that way, at least with JDBC driver. E.g. if we are making call to a stored procedure with timeout of 10 seconds, if stored procedure takes 5 minutes, it will throw Timeout Exception after 5 minutes.

E.g. for below simple stored procedure with sleep:

CREATE OR REPLACE PACKAGE SLEEP_PACKAGE AS
    PROCEDURE SLEEP_PROC (
        P_SECONDS IN NUMBER, 
        P_MESSAGE OUT VARCHAR2 
    );
    PROCEDURE HELLO_WORLD(
        P_INPUT IN VARCHAR2, 
        P_OUTPUT OUT VARCHAR2 
    );
END SLEEP_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY SLEEP_PACKAGE AS

    PROCEDURE SLEEP_PROC (
        P_SECONDS IN NUMBER, 
        P_MESSAGE OUT VARCHAR2 
        ) IS
        MESSAGE       VARCHAR2(1000);
        START_TIME    TIMESTAMP;
        END_TIME      TIMESTAMP;
    BEGIN
        SELECT SYSTIMESTAMP INTO START_TIME FROM DUAL;
        MESSAGE := 'START: ' || START_TIME;
        DBMS_OUTPUT.PUT_LINE('SLEEP PROCEDURE START : ' || START_TIME );
        DBMS_LOCK.SLEEP( P_SECONDS );
        SELECT SYSTIMESTAMP INTO END_TIME FROM DUAL;
        MESSAGE := MESSAGE || ' END: ' || END_TIME || ' TOTAL_TIME: ' || P_SECONDS || ' SECONDS';
        DBMS_OUTPUT.PUT_LINE('SLEEP PROCEDURE END   : ' || END_TIME );
        DBMS_OUTPUT.PUT_LINE(MESSAGE);
        P_MESSAGE := MESSAGE;
    END SLEEP_PROC;

    PROCEDURE HELLO_WORLD (
        P_INPUT IN VARCHAR2, 
        P_OUTPUT OUT VARCHAR2 
        ) IS
    BEGIN
        P_OUTPUT := UPPER(P_INPUT);
    END HELLO_WORLD;
    

END SLEEP_PACKAGE;
/

Below is java code which calls this stored procedure with 5 second timeout and 120 seconds sleep in database stored procedure.

    public String callAdvanceSleepService(String dataSourceName, String input) throws Exception {
        try {
            Connection connection = getConnection(dataSourceName);
            CallableStatement statement = connection.prepareCall("{call SPRINGJMS.SLEEP_PACKAGE.SLEEP_PROC(?,?)}");
            statement.setInt(1, Integer.parseInt(input));
            statement.registerOutParameter(2, Types.VARCHAR);
            statement.setQueryTimeout(5);
            System.out.println(new Timestamp(System.currentTimeMillis()) + " Executing SPRINGJMS.SLEEP_PACKAGE.SLEEP_PROC ");
            int execution = statement.executeUpdate();
            String output = statement.getString(2);
            System.out.println(" execution : " + execution + " output " + output );
            return output;
        } catch(Exception e) {
            System.out.println(new Timestamp(System.currentTimeMillis()) + " Exception ");
            e.printStackTrace();
            throw e;
        }
    }        
    
    public static void main(String args[]) throws Exception {
        WaitService waitService = new WaitService();
        waitService.callAdvanceSleepService("local", "120");
    }

In above example, the code will throw an exception after 120 seconds instead of timeout of 5 seconds.

Weblogic Data Source

Now if we configure the datasource at weblogic level and execute the code as deployed service, the same thing remains true. The data source does have Statement Query Timout, but as I have it in my code, it will overwrite the connection level query timeout.

SOA DB Adapter

For SOA DB adapter, the behavior remains the same, and Query Timeout can be specified either at data source level or in JCA file as below.

<adapter-config name="PLSQL_WAIT" adapter="Database Adapter" wsdlLocation="PLSQL_WAIT.wsdl" xmlns="http://platform.integration.oracle/blocks/adapter/fw/metadata">
  
  <connection-factory location="eis/DB/springjms" UIConnectionName="springjms" adapterRef=""/>
  <endpoint-interaction portType="PLSQL_WAIT_ptt" operation="PLSQL_WAIT">
    <interaction-spec className="oracle.tip.adapter.db.DBStoredProcedureInteractionSpec">
      <property name="SchemaName" value="SPRINGJMS"/>
      <property name="PackageName" value="SLEEP_PACKAGE"/>
      <property name="ProcedureName" value="SLEEP_PROC"/>
      <property name="QueryTimeout" value="60"/>
      <property name="GetActiveUnitOfWork" value="false"/>
    </interaction-spec>
    <output/>
  </endpoint-interaction>

</adapter-config>

However, in DB Adapter, BPEL EJB timeout and JTA would also play critical role. JTA/BPEL timeout would take precedence over any other.

Query Timeout	Actual Query Execution Time	JTA and BPEL engine timeout	Actal Output
30	120	60	Get JTA timeout exception after 60 seconds - Exception is not catched in BPEL
30	120	300	Get SQLTimeoutException exception after 120 seconds - Exception in catched in BPEL
None	120	60	Get JTA timeout after 60 seconds - Exception is not catched in BPEL
600	120	60	Get JTA timeout after 60 seconds - Exception no catched in BPEL

For a specific use case, if we want to achieve similar effect as HTTP connection timeout, we can put the DB adapter service in separate composite, that way caller process (Composite A) will continue its work after timeout and Composite B can be stuck if procedure is slow.

Composite A (BPEL) -> Composite B (BPEL + DB Adapter)