Hi Tom,

I'm using spring-jdbc(JDBCTemplate) with oracle.jdbc.pool.OracleDataSource to execute few queries(copying data from one table(table 1) to another(table 2) and deleting copied entries from table 1) within a single transaction(transactions are handled via org.springframework.jdbc.datasource.DataSourceTransactionManager). Please see below sample code block to explain my scenario

try {
<<Strat Transaction>>
jdbcTemplate.update(<<INSERT FROM TABLE 1 TO TABLE2>>)
jdbcTemplate.update(<<DELETE FROM TABLE 1>>)
<<Commit Transaction>>
} catch(Exception e) {
<<Roll back Transaction>>
}

However, there is a possibility that my delete statement gets blocks on some other queries with row locking(which will manage by a separate module/application) I want to timeout my second statement(bold one) after some time if it cannot proceed due to locks in the database. So I have used "oracle.jdbc.ReadTimeout" property to fulfill my requirement. It worked as expected and timed out after the ReadTimeout value with a Socket Read Timeout IOException which is completely okay.

My problem is after the Socket Readtimeout exception my delete statement doesn't get cleared from the database side and it will be there as an active statement which waits for a row lock. I believe this is because I'm already inside a transaction and Oracle waits until it receives either commit or rollback command from the client side to complete the transaction, but I'm not sure how to return after the timeout without keeping any garbage that I have created on the database side.

Can you please explain how should I configure/proceed to fulfil my requirement? Basically, I want to know how to avoid keeping my client waiting indefinitely for a lock and get timed out after some time without keeping any stale locks at the database side.

Your suggestion will be highly appreciated. (Sorry for the lengthy explanation, I wanted to try my best to cover all the required information to understand my question)

Thanks,
Manjula

--
-- Session 1
--
SQL> update t set x = 1;

1 row updated.

SQL> lock table t in exclusive mode wait 10;
lock table t in exclusive mode wait 10
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired