JDBC Support for Automatic Client Failover

About Automatic Client Failover

Automatic client failover is for use in High Availability scenarios, for either TimesTen Scaleout or TimesTen Classic. There are two scenarios for TimesTen Classic, one with active standby pair replication and one referred to as generic automatic client failover.

If there is a failure of the database or database element to which the client is connected, then failover (connection transfer) to an alternate database or database element occurs:

For TimesTen Scaleout, failover is to an element from a list of available elements in the grid.
For TimesTen Classic with active standby replication, failover is to the new active (original standby) database.
For TimesTen Classic using generic automatic client failover, where you can ensure that the schema and data are consistent on both databases, failover is to a database from a list that is configured in the client odbc.ini file.

A typical use case for generic automatic failover is a set of databases using read-only caching, where each database has the same set of cached data. For example, if you have several read-only cache groups, then you would create the same read-only cache groups on all TimesTen Classic databases included in the list of failover servers. When the client connection fails over to an alternate TimesTen database, the cached data is consistent because TimesTen automatically refreshes the cache data (as needed) from the Oracle database.

Applications are automatically reconnected to the new data database or database element. TimesTen provides features that enable applications to be alerted when this happens, so they can take any appropriate action.

Any of the following error conditions indicates automatic client failover.

Native error 30105 with SQL state 08006
Native error 47137

Note:

Automatic client failover applies only to client/server connections. The functionality described here does not apply to a direct connection.
Automatic client failover is complementary to Oracle Clusterware in situations where Oracle Clusterware is used, though the two features are not dependent on each other. You can also refer to Using Oracle Clusterware to Manage Active Standby Pairs in Oracle TimesTen In-Memory Database Replication Guide.

Features and Functionality of JDBC Support for Automatic Client Failover

There are TimesTen JDBC features related to client failover, including functionality relating specifically to pooled connections.

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information about the TimesTen JDBC classes, interfaces, and methods discussed here.

General Client Failover Features

TimesTen JDBC support for automatic client failover provides two mechanisms for detecting a failover.

Synchronous detection, through a SQL exception: After an automatic client failover, JDBC objects created on the failed connection—such as statements, prepared statements, callable statements, and result sets—can no longer be used. A Java SQL exception is thrown if an application attempts to access any such object. By examining the SQL state and error code of the exception, you can determine whether the exception is the result of a failover situation.
Asynchronous detection, through an event listener: An application can register a user-defined client failover event listener, which is notified of each event that occurs during the process of a failover.

TimesTen JDBC provides the following features, in package com.timesten.jdbc, to support automatic client failover.

ClientFailoverEvent class

This class is used to represent events that occur during a client failover: begin, end, abort, or retry.
ClientFailoverEventListener interface

An application interested in client failover events must have a class that implements this interface, which is the mechanism to listen for client failover events. At runtime, the application must register ClientFailoverEventListener instances through the TimesTen connection (see immediately below).

You can use a listener to proactively react to failure detection, such as by refreshing connection pool statement caches, for example.
Methods in the TimesTenConnection interface

This interface specifies the methods addConnectionEventListener() and removeConnectionEventListener() to register or remove, respectively, a client failover event listener.
A constant, TT_ERR_FAILOVERINVALIDATION, in the TimesTenVendorCode interface

This enables you to identify an event as a failover event.

Client Failover Features for Pooled Connections

TimesTen recommends that applications using pooled connections (javax.sql.PooledConnection) or connection pool data sources (javax.sql.ConnectionPoolDataSource) use the synchronous mechanism noted previously to handle stale objects on the failed connection.

Java EE application servers manage pooled connections, so applications are not able to listen for events on pooled connections. And application servers do not implement and register an instance of ClientFailoverEventListener, because this is a TimesTen extension.

Configuration of Automatic Client Failover

Refer to Configuring Automatic Client Failover for TimesTen Classic in Oracle TimesTen In-Memory Database Operations Guide or Client Connection Failover in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

In TimesTen Classic, failover DSNs must be specifically configured through TTC_Server2 and TTC_Servern connection attributes.

Note:

Setting any of TTC_Server2, TTC_Server_DSN2, TTC_Servern, or TCP_Port2 implies that you intend to use automatic client failover. For the active standby pair scenario, it also means a new thread is created for your application to support the failover mechanism.

Be aware of these TimesTen connection attributes:

TTC_NoReconnectOnFailover: If this is set to 1 (enabled), TimesTen is instructed to do all the usual client failover processing except for the automatic reconnect. (For example, statement and connection handles are marked as invalid.) This is useful if the application does its own connection pooling or manages its own reconnection to the database after failover. The default value is 0 (reconnect). Also see TTC_NoReconnectOnFailover in Oracle TimesTen In-Memory Database Reference.
TTC_REDIRECT: If this is set to 0 and the initial connection attempt to the desired database or database element fails, then an error is returned and there are no further connection attempts. This does not affect subsequent failovers on that connection. Also see TTC_REDIRECT in Oracle TimesTen In-Memory Database Reference.
TTC_Random_Selection: For TimesTen Classic using generic automatic client failover, the default setting of 1 (enabled) specifies that when failover occurs, the client randomly selects an alternative server from the list provided in TTC_Servern attribute settings. If the client cannot connect to the selected server, it keeps redirecting until it successfully connects to one of the listed servers. With a setting of 0, TimesTen goes through the list of TTC_Servern servers sequentially. Also see TTC_Random_Selection in Oracle TimesTen In-Memory Database Reference.

Note:

If you set any of these in odbc.ini or the connection string, the settings are applied to the failover connection. They cannot be set in your application (including by ALTER SESSION).

Synchronous Detection of Automatic Client Failover in JDBC

If, in a failover situation, an application attempts to use objects created on the failed connection, then JDBC throws a SQL exception. The vendor-specific exception code is set to TimesTenVendorCode.TT_ERR_FAILOVERINVALIDATION.

Detecting a failover through this mechanism is referred to as synchronous detection. The following example demonstrates this.

try {
   // ...
   // Execute a query on a previously prepared statement.
   ResultSet theResultSet = theStatement.executeQuery("select * from dual");
   // ...

} catch (SQLException sqlex) {
   sqlex.printStackTrace();
   if (sqlex.getErrorCode() == TimesTenVendorCode.TT_ERR_FAILOVERINVALIDATION) {
   // Automatic client failover has taken place; discontinue use of this object.
   }
}

Asynchronous Detection of Automatic Client Failover in JDBC

Asynchronous failover detection requires an application to implement a client failover event listener and register an instance of it on the TimesTen connection.

This section describes the steps involved:

Implement a Client Failover Event Listener

TimesTen JDBC provides the com.timesten.jdbc.ClientFailoverEventListener interface for use in listening for events.

This interface is highlighted by the following method:

void notify(ClientFailoverEvent event)

To use asynchronous failover detection, you must create a class that implements this interface, then register an instance of the class at runtime on the TimesTen connection (discussed shortly).

When a failover event occurs, TimesTen calls the notify() method of the listener instance you registered, providing a ClientFailoverEvent instance that you can then examine for information about the event.

The following example shows the basic form of a ClientFailoverEventListener implementation.

   private class MyCFListener implements ClientFailoverEventListener {
      /* Applications can build state system to track states during failover.
         You may want to add methods that talks about readiness of this Connection
         for processing. 
      */
      public void notify(ClientFailoverEvent event) {
         
         /* Process connection failover type */
         switch(event.getTheFailoverType()) {
         case TT_FO_CONNECTION:
            /* Process session fail over */
            System.out.println("This should be a connection failover type " +
                                event.getTheFailoverType());
            break;
            
         default:
            break;
         }
         /* Process connection failover events */
         switch(event.getTheFailoverEvent()) {
         case BEGIN:
            System.out.println("This should be a BEGIN event " +
                                event.getTheFailoverEvent());
            /* Applications cannot use Statement, PreparedStatement, ResultSet,
               etc. created on the failed Connection any longer.
            */
            break;
            
         case END:
            System.out.println("This should be an END event " +
                                event.getTheFailoverEvent());
            
            /* Applications may want to re-create Statement and PreparedStatement
               objects at this point as needed.
            */
            break;
         
         case ABORT:
            System.out.println("This should be an ABORT event " +
                                event.getTheFailoverEvent());
            break;
            
         case ERROR:
            System.out.println("This should be an ERROR event " +
                                event.getTheFailoverEvent());
            break;
            
         default:
            break;
         }
      }
   }

The event.getTheFailoverType() call returns an instance of the nested class ClientFailoverEvent.FailoverType, which is an enumeration type. In TimesTen, the only supported value is TT_FO_CONNECTION, indicating a connection failover.

The event.getTheFailoverEvent() call returns an instance of the nested class ClientFailoverEvent.FailoverEvent, which is an enumeration type where the value can be one of the following:

BEGIN, if the client failover has begun
END, if the client failover has completed successfully
ERROR, if the client failover failed but will be retried
ABORT, if the client failover has aborted

Register the Client Failover Listener Instance

At runtime you must register an instance of your failover event listener class with the TimesTen connection object, so that TimesTen can call the notify() method of the listener class as needed for failover events.

TimesTenConnection provides the following method for this.

void addConnectionEventListener (ClientFailoverEventListener listener)

Create an instance of your listener class, then register it using this method. The following example establishes the connection and registers the listener. Assume theDsn is the JDBC URL for a TimesTen client/server database and theCFListener is an instance of your failover event listener class.

      try {
         /* Assume this is a client/server conn; register for conn failover. */
         Class.forName("com.timesten.jdbc.TimesTenClientDriver");
         String url = "jdbc:timesten:client:" + theDsn;
         theConnection = (TimesTenConnection)DriverManager.getConnection(url);
         theConnection.addConnectionEventListener(theCFListener);
         /* Additional logic goes here; connection failover listener is
            called if there is a fail over.
         */
      }
      catch (ClassNotFoundException cnfex) {
         cnfex.printStackTrace();
      }
      catch (SQLException sqlex) {
         sqlex.printStackTrace();
      }

Remove the Client Failover Listener Instance

The TimesTenConnection interface defines the following method to deregister a failover event listener.

void removeConnectionEventListener (ClientFailoverEventListener listener)

Use this method to deregister a listener instance.

JDBC Application Action in the Event of Failover

There are actions for an application to take if there is a failover.

Application Steps for Failover

If you receive any of the error conditions noted at the beginning of automatic client failover discussion in JDBC Support for Automatic Client Failover in response to an operation in your application, then application failover is in progress. There are actions you can perform to recover after failover.

Roll back all transactions on the connection.
Clean up all objects from the previous connection. None of the state or objects associated with the previous connection are preserved.
Assuming TTC_NoReconnectOnFailover=0 (the default), sleep briefly, as discussed in the next section, Failover Delay and Retry Settings. If TTC_NoReconnectOnFailover=1, then you must instead manually reconnect the application to an alternate database or database element.
Recreate and reprepare all objects related to your connection.
Restart any in-progress transactions from the beginning.

Failover Delay and Retry Settings

The reconnection to another database or database element during automatic client failover may take some time. If your application attempts recovery actions before TimesTen has completed its client failover process, you may receive another failover error condition.

See JDBC Support for Automatic Client Failover.

Therefore, your application should place all recovery actions within a loop with a short delay before each subsequent attempt, where the total number of attempts is limited. If you do not limit the number of attempts, the application may appear to freeze if the client failover process does not complete successfully. For example, your recovery loop could use a retry delay of 100 milliseconds with a maximum number of retries limited to 100 attempts. The ideal values depend on your particular application and configuration.

This code example, using the synchronous detection method, illustrates how you might handle the retrying of connection failover errors in a JDBC application. Code not directly relevant is omitted (...).

// Database connection object
Connection        dbConn;
 
// Open the connection  to the database
...
 
// Disable auto-commit
dbConn.setAutoCommit( false ); 
 
...
 
// Prepre the SQL statements
PreparedStatement stmtQuery = dbConn.prepare("SELECT ...");
PreparedStatement stmtUpdate = dbConn.prepare("UPDATE ...");
 
...
 
// Set max retries to 100
int retriesLeft = 100;
// and retry delay to 100 ms
int retryDelay = 100;
 
// Records outcome
boolean success = false;
Boolean needReprepare = false;
 
// Execute transaction with retries until success or retries exhausted
while (  retriesLeft > 0  )
{
    try {
 
        // Do we need to re-prepare
        if ( needReprepare )
        {
            Thread.sleep( retryDelay ); // delay before proceeding
            stmtQuery = dbConn.prepare("SELECT ...");
            stmtUpdate = dbConn.prepare("UPDATE ...");
            needReprepare = false;
        }
 
        // First execute the query
 
        // Set input values
        stmtQuery.setInt(1, ...);
        stmtQuery.setString(2, ...);
 
        // Execute and process results
        ResultSet rs = stmtQuery.executeQuery();
        while (  rs.next()  )
        {
            int val1 = rs.getInt(1);
            String val2 = rs.getString(2);
            ...
        }
        rs.close();
        rs = null;
          
        // Now execute the update
 
        // Set input values
        stmtUpdate.setInt(1,...);
        stmtUpdate.setString(2,...);
 
        // Execute and check number of rows affected
        int updCount = stmtUpdate.executeUpdate();
        if (  updCount < 1  )
        {
            ...
        }
 
        // And finally commit
        dbConn.commit();
 
        // We are done
        success = true;
        break;
 
    } catch ( SQLException sqe ) {
 
       if (  (sqe.getErrorCode() == 47137) ||
             ( (sqe.getErrorCode() == 30105) && (sqe.getSQLState().equals("08006")) )  )
        // connection failover error
        {
            // decrement retry count
            retriesLeft--;
            // rollback the transaction ready for retry
            dbConn.rollback();
            // and indicate that we need to re-prepare
            needReprepare = true;
        }
        else
        {
            // handle other kinds of error
            ...
        }
 
    }
 
} // end of retry loop if (  ! success  ){    // Handle the failure    ...}