Hi Tom,

I have a problem related to row lock handling when there are can be network level failure between client and Oracle db server. I want to make sure that there won't be any stale locks at the server side. Let me explain my issue with an example scenario.

I have used the following code to get a row lock(using for update) and before calling resultSet.updateRow(); statement I tried to block the communication between client and server(using a firewall rule to block port 1521)

String forUpdateQuery = "SELECT NAME, AGE FROM MY_TEST WHERE AGE = ? FOR UPDATE";
Connection con = null;
try {
System.setProperty("oracle.jdbc.ReadTimeout", "30000");
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("URL", "UserName", "Password");

forUpdatePS = con.prepareStatement(forUpdateQuery, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
forUpdatePS.setInt(1, 25); //Setting age value to the prepared statement
ResultSet rs = forUpdatePS.executeQuery();
while (rs.next()) {
if (rs.getString("NAME") != null) {
rs.updateString("NAME", "New Name");
}
try {
Thread.sleep(10000);
//Within this 10 seconds I'm blocking the port 1521 thorugh the firewall
} catch (InterruptedException e) {
e.printStackTrace();
}
rs.updateRow();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//cleanup
}

Following is DDL of the test table

CREATE TABLE APS_EMW.MY_TEST (
ID INT PRIMARY KEY NOT NULL ,
NAME VARCHAR(255) NULL ,
ADDRESS VARCHAR(255) NULL ,
AGE INTEGER NULL
);

As I beleive there should be a proper way to deal with these type of network level failures agaist row locks. I have tried reducing CONNET_TIME and IDLE_TIME to some finite values to check whether after that value my locks will disapear, but unfortunately neither of them works for me. Can you please help me to handle this type of connection failures when there are already acquired row locks? If there is a way to configure some timeout value to remove stale locks from the database server side which will be the best option for me, unfortunately I couldn't find a way to do this

Thanks,
Manjula