|
|
By default, any connection obtained from a one-phase datasource (implementing javax.sql.ConnectionPoolDataSource) is in auto-commit mode, when there is no scoping transaction. When in auto-commit mode, each database action (statement) is executed and committed in a single database transaction. Servlets often use connections in auto-commit, because transaction semantics are not necessary.
Enterprise applications do not usually use auto-commit connections, because they frequently require multiple statements to be executed, and serially committing each would be quite cumbersome. Auto-commit can be explicitly disabled by calling setAutoCommit() on a Connection object. When a StaleConnectionException is caught from a connection in auto-commit mode, recovery is a simple matter of closing all of the associated JDBC resources and retrying the operation with a new connection.
However, in some cases the cause of a database outage might be transient. In these cases, adding a delay in the retry logic can allow a database service to be restored. The number of retries as well as any delay should be small, so as to keep a client from waiting an inordinate amount of time. Sample code is shown in Example A-1.
Example: A-1 Sample code for connections in auto-commit mode
public void myConnPool() throws java.rmi.RemoteException {
// retry indicates whether to retry or not
// numOfRetries states how many retries have been attempted
boolean retry = false;
int numOfRetries = 0;
java.sql.Connection conn = null;
java.sql.Statement stmt = null;
do {
try {
//Assumes that a datasource has already been obtained from JNDI
conn = ds.getConnection();
stmt = conn.createStatement();
stmt.execute(
"INSERT INTO ORG VALUES (10, 'Pacific', '270', 'Western', 'Seattle')");
retry = false;
} catch (com.ibm.websphere.ce.cm.StaleConnectionException sce) {
//if a StaleConnectionException is caught rollback and retry the action
if (numOfRetries < 2) {
retry = true;
numOfRetries++;
// add an optional pause
Thread.sleep(10000);
} else {
retry = false;
}
} catch (java.sql.SQLException sqle) {
//deal with other database exception
} finally {
//always cleanup JDBC resources
try {
if (stmt != null)
stmt.close();
} catch (java.sql.SQLException sqle) {
//usually can ignore
}
try {
if (conn != null)
conn.close();
} catch (java.sql.SQLException sqle) {
//usually can ignore
}
}
} while (retry);
}
