With Statement and its subinterfaces it is possible to set query timeouts with setQueryTimeout method. For the queries it is fine, but this does not help with the rest of the database operations, for example with commits and metadata operations. One way to address the issue is to set timeout for the socket read through Connection.setNetworkTimeout method. Documentation talks about rare network conditions, but if you’re blessed with RAC there are additional error scenarios that look like long reads from the socket. Another positive thing about setNetworkTimeout is that it allows millisecond resolution for the timeout, while setQueryTimeout does only seconds.
Let’s have an example:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.junit.runner.RunWith;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest
public class JDBCStatement {
protected final Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
DataSource dataSource;
@Value("${spring.datasource.use_autocommit}")
private boolean autocommit;
static final String DEL_STATEMENT = "BEGIN DELETE FROM TX; DELETE FROM T; END;";
static final String INS_STATEMENT = "INSERT INTO T VALUES(?, ?, ?)";
static final String CLIENT_INFO = "BEGIN DBMS_SESSION.SET_IDENTIFIER(?); END;";
static final String CLIENT_NAME = "C_RK";
static final int BATCH_SIZE = 1000000;
@Autowired
ThreadPoolTaskExecutor ex;
@Test
public void singleStatement() {
try (
Connection conn = dataSource.getConnection();
PreparedStatement ds = conn.prepareStatement(DEL_STATEMENT);
PreparedStatement is = conn.prepareStatement(INS_STATEMENT);
) {
ds.execute();
// Just for testing
// conn.setNetworkTimeout(Executors.newFixedThreadPool(1), 10000);
// Sets 10s timeout
conn.setNetworkTimeout(ex.getThreadPoolExecutor(), 10000);
for(int i = 0; i <= BATCH_SIZE; i++){
is.setInt(1, i);
is.setInt(2, 1);
is.setString(3, "BOOYA!." + i);
is.execute();
// Commit in a loop is to help out with testing. Don't try this at home!
conn.commit();
}
}
catch (SQLException ex) {
throw new RuntimeException("", ex);
}
}
}
Testing
Easiest for me is to stop the Oracle log writer during the test run. This makes commit hang with log file sync wait event and our timeout to fire. So in the database while test case is running
SYS@roadkill 12-03-2021 13:45:23> !ps -u oracle|grep lgwr
30317 ? 00:03:36 ora_lgwr_roadki
SYS@roadkill 12-03-2021 13:45:36> oradebug setospid 30317
Oracle pid: 13, Unix process pid: 30317, image: oracle@roadkill (LGWR)
SYS@roadkill 12-03-2021 13:46:38> oradebug suspend
Statement processed.
SYS@roadkill 12-03-2021 13:46:49> select state, seq#, event from v$session where username ='C_RK';
STATE SEQ# EVENT
------------------- ---------- ----------------------------------------------------------------
WAITING 27879 log file sync
Elapsed: 00:00:00.01
SYS@roadkill 12-03-2021 13:47:03>
(There’s a way to run oradebug from event handler, but I haven’t get that one working ;))
After a few seconds our timeout fired:
java.lang.RuntimeException:
at <redacted>.JDBCStatement.singleStatement(JDBCStatement.java:77)
Caused by: java.sql.SQLRecoverableException: IO Error: Socket read timed out
at <redacted>.JDBCStatement.singleStatement(JDBCStatement.java:71)
Caused by: oracle.net.nt.TimeoutInterruptHandler$IOReadTimeoutException: Socket read timed out
at <redacted>.JDBCStatement.singleStatement(JDBCStatement.java:71)
Fear and Loathing 3.0 