I tested the timeout in my application that uses JDBC.
My application use PreparedStatement which resultSetType is ResultSet.TYPE_FORWARD_ONLY, and resultSetConcurrency is ResultSet.CONCUR_UPDATABLE.
I used "SELECT ~ FOR UPDATE WAIT 60". I want it to time out after 60 seconds, but timed out after 120 seconds.
How can I time out after 60 seconds?
I ran SQL("SELECT ~ FOR UPDATE WAIT 60") in my application while locking it with SQL*Plus.
I expected the following results.
[SQL*Plus] UPDATE ZEN_TAIGAI_NOBORI_KEIRO SET SAISHU_TAIGAI_NOBORI_SNO=0 WHERE TAIGAI_NOBORI_KEIRO_NO='0A';
[Application] SELECT t.* FROM ZEN_TAIGAI_NOBORI_KEIRO t WHERE TAIGAI_NOBORI_KEIRO_NO='0A' FOR UPDATE WAIT 60
60 seconds later
[Application] SQLException: ORA-30006:resource busy; acquire with WAIT timeout expired
[SQL*Plus] roll back;
But in reality, the result is as follows.
[SQL*Plus] UPDATE ZEN_TAIGAI_NOBORI_KEIRO SET SAISHU_TAIGAI_NOBORI_SNO=0 WHERE TAIGAI_NOBORI_KEIRO_NO='0A';
[Application] SELECT t.* FROM ZEN_TAIGAI_NOBORI_KEIRO t WHERE TAIGAI_NOBORI_KEIRO_NO='0A' FOR UPDATE WAIT 60
120 seconds later
[Application] SQLException: ORA-30006:resource busy; acquire with WAIT timeout expired
[SQL*Plus] roll back;
I took a JDBC trace. And I found that the JDBC driver downgrades the Concurrency of the ResultSet from CONCUR_UPDATABLE to CONCUR_READ_ONLY and implicitly re-executes the query after the lock timeout.
Pattern A : Rollback after 120 seconds
[SQL*Plus] UPDATE ZEN_TAIGAI_NOBORI_KEIRO SET SAISHU_TAIGAI_NOBORI_SNO=0 WHERE TAIGAI_NOBORI_KEIRO_NO='0A';
[Application] SELECT t.* FROM ZEN_TAIGAI_NOBORI_KEIRO t WHERE TAIGAI_NOBORI_KEIRO_NO='0A' FOR UPDATE WAIT 60
60 seconds later
[JDBC Trace] oracle.jdbc.driver.T4CPreparedStatement doScrollExecuteCommon Downgrading ResultSet type/concurrence due to
SQLException: ORA-30006:resource busy; acquire with WAIT timeout expired
120 seconds later
[Application] SQLException: ORA-30006:resource busy; acquire with WAIT timeout expired
[SQL*Plus] roll back;
Pattern B : Rollback after 60 and within 120 seconds
[SQL*Plus] UPDATE ZEN_TAIGAI_NOBORI_KEIRO SET SAISHU_TAIGAI_NOBORI_SNO=0 WHERE TAIGAI_NOBORI_KEIRO_NO='0A';
[Application] SELECT t.* FROM ZEN_TAIGAI_NOBORI_KEIRO t WHERE TAIGAI_NOBORI_KEIRO_NO='0A' FOR UPDATE WAIT 60
60 seconds later
[JDBC Trace] oracle.jdbc.driver.T4CPreparedStatement doScrollExecuteCommon Downgrading ResultSet type/concurrence due to
SQLException: ORA-30006:resource busy; acquire with WAIT timeout expired
[SQL*Plus] roll back;
[Application] SQLException: Invalid operation for read only resultset: updateObject
Pattern B's JDBC Trace
trace.zip (92.4 KB)
Query took 60 seconds to time out, and JDBC driver downgrades and re-executes ResultSet takes 60 seconds to time out. So it looked like it took a total of 120 seconds.
This happens only the first time after launching the application.
How can I time out after 60 seconds?
I am using the following environment.
Product : Oracle Database - Enterprise Edition
Product Version : 19.15.0.0.0
JDBC : ojdbc8.jar
JDBC Version : Oracle 19.15.0.0.0 JDBC 4.2 compiled with javac 1.8.0_321 on Thu_Mar_17_02:28:35_PDT_2022
Operating System : Red Hat Enterprise Linux
OS Version : 8.6 (Ootpa)
MW : IBM WebSphere Application Server Network Deployment
MW Version : 9.0.5.12