Exceptions pertaining to data access
Java Database Connectivity (JDBC) applications receive a standard SQL exception if any JDBC operation fails. The product provides special exceptions for its relational resource adapter (RRA), to indicate that the connection currently held is no longer valid.
- The connection wait timeout exception indicates that the application has waited for the number of seconds specified by the connection timeout setting and has not received a connection. This situation can occur when the connection pool is at maximum size and all of the connections are in use by other applications for the duration of the wait. In addition, there are no connections currently in use that the application can share because either the connection properties do not match, or the connection is in a different transaction.
For a Version 4.0 data source, the ConnectionWaitTimeout object creates an exception that is instantiated from the com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException class.
For Java 2 Connector (J2C) connection factories, the ConnectionWaitTimeout object generates a resource exception of the com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException class.
When the error detection model is configured to exception mapping, later versions of data sources issue an SQL exception of the com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException subclass. When the error detection model is configured to exception checking, later versions of data sources issue an SQL exception of the java.sql.SQLTransientConnectionException class with a chained exception of the com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException class.
- When the error detection model is configured to exception mapping, the stale connection exception indicates that the connection is no longer valid. When the error detection model is configured to exception checking, the JDBC driver raises a JDBC 4.0 exception, such as java.sql.SQLRecoverableException or java.sql.SQLNonTransientConnectionException, or the JDBC driver specifies an appropriate SQLState to indicate that the connection is no longer valid.
Enterprise bean container-managed persistence (CMP) beans under the EJB 2.x specification receive a standard EJB exception when an operation fails.
The userDefinedErrorMap custom property overlays existing entries in the error map by starting the DataStoreHelper.setUserDefinedMap method. Use the custom property to add, change, or remove entries from the error map.
- Entries are delimited by a ; (semicolon).
- Each entry consists of a key and value, where the key is an error code (numeric value) or SQLState, which is text enclosed in quotation marks.
- Keys and values are separated by the = (equals sign).
For example, to remove the mapping of SQLState S1000, add a mapping of error code 1062 to duplicate key, and add a mapping of SQLState 08004 to stale connection, we can specify the following value for userDefinedErrorMap:
"S1000"=;1062=com.ibm.websphere.ce.cm.DuplicateKeyException;"08004"= com.ibm.websphere.ce.cm.StaleConnectionException
userDefinedErrorMap can be located in the administrative console by selecting the data source and configuring the custom properties.
Stale Connections
The product provides a special subclass of the java.sql.SQLException class for using connection pooling to access a relational database. This com.ibm.websphere.ce.cm.StaleConnectionException subclass exists in both a WebSphere 4.0 data source and in the most recent version data source that use the relational resource adapter. This class serves to indicate that the connection currently held is no longer valid.
This situation can occur for many reasons, including the following:
- The application tries to get a connection and fails, as when the database is not started.
- A connection is no longer usable because of a database failure. When an application tries to use a previously obtained connection, the connection is no longer valid. In this case, all connections currently in use by the application can get this error when they try to use the connection.
- The connection is orphaned (because the application had not used it in at most two times the value of the unused timeout setting) and the application tries to use the orphaned connection. This case applies only to Version 4.0 data sources.
- The application tries to use a JDBC resource, such as a statement, obtained on a stale connection.
- A connection is closed by the Version 4.0 data source auto connection cleanup feature and is no longer usable. Auto connection cleanup is the standard mode in which connection management operates. This mode indicates that at the end of a transaction, the transaction manager closes all connections enlisted in that transaction. This enables the transaction manager to ensure that connections are not held for excessive periods of time and that the pool does not reach its maximum number of connections prematurely.
A negative ramification does ensue, however, when the transaction manager closes the connections and returns the connection to the free pool after a transaction ends. An application cannot obtain a connection in one transaction and attempt to use it in another transaction. If the application attempts this, an ObjectClosedException, which in turn is a StaleConnectionException, occurs because the connection is already closed.
To avoid encountering StaleConnectionExceptions:
- By setting the minimum connection pool size to 0, the connection pool is maintained empty when the application server is inactive for prolonged periods. This helps to avoid connections getting stale due to maintenance activity on the database side.
- Set the connection pool Unused Timeout to a value less than the value configured for the firewall timeout. This ensures that the connection does not go stale until the Unused Timeout, and enables the connection to remain in the free pool for reuse.
- Set the connection pool reap time to a value less than the unused timeout. The smaller the value, the more often the pool maintenance thread check and the more accurate the unused timer becomes. However, frequent pool maintenance thread runs can degrade performance.
If we are trying to use an orphaned connection or a connection that is made unavailable by auto connection cleanup, a stale connection exception indicates that the application has attempted to use a connection already returned to the connection pool. It does not indicate an actual problem with the connection. However, other cases of a stale connection exception indicate that the connection to the database has gone bad, or stale. Once a connection has gone stale, we cannot recover it, and we must completely close the connection rather than returning it to the pool.
Detecting stale connections
When a connection to the database becomes stale, operations on that connection result in an SQL exception from the JDBC driver. Because an SQL exception is a rather generic exception, it contains state and error code values we can use to determine the meaning of the exception. However, the meanings of these states and error codes vary depending on the database vendor. The connection pooling run time maintains a mapping of which SQL state and error codes indicate a stale connection exception for each database vendor supported. When the connection pooling run time catches an SQL exception, it checks to see if this SQL exception is considered a stale connection exception for the database server in use.
Recovering from stale connections
An application can catch a stale connection exception, depending on the type of error detection model configured on the data source:
- When the error detection model is configured to exception mapping, the application server replaces the exception that is raised by the JDBC driver with StaleConnectionException. In this case, the application might trap for a stale connection exception.
- When the error detection model is configured to exception checking, the application server still consults the error map in order to manage the connection pool, but it does not replace the exception. In this case, the application should not trap for a stale connection exception.
Because of the differences between error detection models, the application server provides an API that applications can use with either case to identify stale connections. The API is com.ibm.websphere.rsadapter.WSCallHelper.getDataStoreHelper(datasource).isConnectionError(sqlexception).
Applications are not required to explicitly identify a stale connection exception. Applications are already required to catch the java.sql.SQLException, and the stale connection exception or the exception that is raised by the JDBC driver, always inherits data from the java.sql.SQLException. The stale connection exception, which can result from any method that is declared to raise SQLException, is caught automatically in the general catch-block. However, explicitly identifying a stale connection exception makes it possible for an application to recover from bad connections. When application code identifies a stale connection exception, it should take explicit steps to recover, such as retrying the operation under a new transaction and new connection.
Example: Handling data access exception - stale connection
These code samples demonstrate how to programmatically address stale connection exceptions for different types of data access clients in different transaction scenarios.
When an application receives a stale connection exception on a database operation, it indicates that the connection currently held is no longer valid. Although it is possible to get an exception for a stale connection on any database operation, the most common time to see a stale connection exception issued is after the first time the connection is retrieved. Because connections are pooled, a database failure is not detected until the operation immediately following its retrieval from the pool, which is the first time communication to the database is attempted. It is only when a failure is detected that the connection is marked stale. The stale connection exception occurs less often if each method that accesses the database gets a new connection from the pool.
Many stale connection exceptions are caused by intermittent problems with the network of the database server. Obtaining a new connection and retrying the operation can result in successful completion without exceptions. In some cases it is advantageous to add a small wait time between the retries to give the database server more time to recover. However, applications should not retry operations indefinitely, in case the database is down for an extended time.
If we are developing applications for the WebSphere Application Server with an integrated development environment (IDE) like Eclipse, you might must import the app_server_root/plugins/com.ibm.ws.runtime.jar file into the development environment to take advantage of code that is provided.
Before the application can obtain a new connection for a retry of the operation, roll back the transaction in which the original connection was involved and begin a new transaction. You can break down details on this action into the following two categories:
- Objects operating in a bean-managed global transaction context begun in the same method as the database access
- A servlet or session bean with bean-managed transactions (BMT) can start a global transaction explicitly by calling begin() on a javax.transaction.UserTransaction object, which we can retrieve from naming or from the bean EJBContext object. To commit a bean-managed transaction, the application calls commit() on the UserTransaction object. To roll back the transaction, the application calls rollback(). Entity beans and non-BMT session beans cannot explicitly begin global transactions.
If an object that explicitly started a bean-managed transaction receives a stale connection exception on a database operation, close the connection and roll back the transaction. At this point, the application developer can decide to begin a new transaction, get a new connection, and retry the operation.
The following code fragment shows an example of handling stale connection exceptions in this scenario:
//get a userTransaction javax.transaction.UserTransaction tran = getSessionContext().getUserTransaction(); //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 { //begin a transaction tran.begin(); //Assumes that a datasource has already been obtained //from JNDI conn = ds.getConnection(); conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute("INSERT INTO EMPLOYEES VALUES (0101, 'Bill', 'R', 'Smith')"); tran.commit(); retry = false; } catch(java.sql.SQLException sqlX) { // If the error indicates the connection is stale, then // rollback and retry the action if (com.ibm.websphere.rsadapter.WSCallHelper .getDataStoreHelper(ds) .isConnectionError(sqlX)) { try { tran.rollback(); } catch (java.lang.Exception e) { //deal with exception //in most cases, this can be ignored } if (numOfRetries < 2) { retry = true; numOfRetries++; } else { retry = false; } } else { //deal with other database exception retry = false } } 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) ;
- Objects operating in a global transaction context and transaction not begun in the same method as the database access.
- When the object which receives the stale connection exception does not have direct control over the transaction, such as in a container-managed transaction case, the object must mark the transaction for rollback, and then indicate to its caller to retry the transaction. In most cases, we can do this by creating an application exception that indicates to retry that operation. However this action is not always allowed, and often a method is defined only to create a particular exception. This is the case with the ejbLoad() and ejbStore() methods on an enterprise bean. The next two examples explain each of these scenarios.
- Example 1: Database access method creates an application exception
- When the method that accesses the database is free to create whatever exception is required, the best practice is to catch the stale connection exception and create some application exception that we can interpret to retry the method.
The following example shows an EJB client calling a method on an entity bean with transaction demarcation TX_REQUIRED, which means that the container begins a global transaction when insertValue() is called:
public class MyEJBClient { //... other methods here ... public void myEJBClientMethod() { MyEJB myEJB = myEJBHome.findByPrimaryKey("myEJB"); boolean retry = false; do { try { retry = false; myEJB.insertValue(); } catch(RetryableConnectionException retryable) { retry = true; } catch(Exception e) { /* handle some other problem */ } } while (retry); } } //end MyEJBClient public class MyEJB implements javax.ejb.EntityBean { //... other methods here ... public void insertValue() throws RetryableConnectionException, java.rmi.EJBException { try { conn = ds.getConnection(); stmt = conn.createStatement(); stmt.execute("INSERT INTO my_table VALUES (1)"); } catch(java.sql.SQLException sqlX) { // Find out if the error indicates the connection is stale if (com.ibm.websphere.rsadapter.WSCallHelper .getDataStoreHelper(ds) .isConnectionError(sqlX)) { getSessionContext().setRollbackOnly(); throw new RetryableConnectionException(); } else { //handle other database problem } } 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 } } } } //end MyEJBMyEJBClient first gets a MyEJB bean from the home interface, assumed to have been previously retrieved from the Java Naming and Directory Interface (JNDI). It then calls insertValue() on the bean. The method on the bean gets a connection and tries to insert a value into a table. If one of the methods fails with a stale connection exception, it marks the transaction for rollbackOnly (which forces the caller to roll back this transaction) and creates a new retryable connection exception, cleaning up the resources before the exception is thrown. The retryable connection exception is simply an application-defined exception that tells the caller to retry the method. The caller monitors the retryable connection exception and, if it is caught, retries the method. In this example, because the container is beginning and ending the transaction; no transaction management is needed in the client or the server. Of course, the client could start a bean-managed transaction and the behavior would still be the same, provided that the client also committed or rolled back the transaction.
- Example 2: Database access method creates an onlyRemote exception or an EJB exception
- Not all methods are allowed to throw exceptions defined by the application. If we use bean-managed persistence (BMP), use the ejbLoad() and ejbStore() methods to store the bean state. The only exceptions issued from these methods are the java.rmi.Remote exception or the javax.ejb.EJB exception, so we cannot use something similar to the previous example.
If we use container-managed persistence (CMP), the container manages the bean persistence, and it is the container that sees the stale connection exception. If a stale connection is detected, by the time the exception is returned to the client it is simply a remote exception, and so a simple catch-block does not suffice. There is a way to determine if the root cause of a remote exception is a stale connection exception. When a remote exception is created to wrap another exception, the original exception is usually retained. All remote exception instances have a detail property, which is of type java.lang.Throwable. With this detail, we can trace back to the original exception and, if it is a stale connection exception, retry the transaction. In reality, when one of these remote exceptions flows from one Java Virtual Machine API to the next, the detail is lost, so it is better to start a transaction in the same server as the database access occurs. For this reason, the following example shows an entity bean accessed by a session bean with bean-managed transaction demarcation.
public class MySessionBean extends javax.ejb.SessionBean { ... other methods here ... public void mySessionBMTMethod() throws java.rmi.EJBException { javax.transaction.UserTransaction tran = getSessionContext().getUserTransaction(); boolean retry = false; do { try { retry = false; tran.begin(); // causes ejbLoad() to be invoked myBMPBean.myMethod(); // causes ejbStore() to be invoked tran.commit(); } catch(java.rmi.EJBException re) { try { tran.rollback(); } catch(Exception e) { //can ignore } if (causedByStaleConnection(re)) retry = true; else throw re; } catch(Exception e) { // handle some other problem } 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); } public boolean causedByStaleConnection(java.rmi.EJBException re) { // Search the exception chain for errors // indicating a stale connection for (Throwable t = re; t != null; t = t.getCause()) if (t instanceof RetryableConnectionException) return true; // Not found to be stale return false; } } public class MyEntityBean extends javax.ejb.EntityBean { ... other methods here ... public void ejbStore() throws java.rmi.EJBException { try { conn = ds.getConnection(); stmt = conn.createStatement(); stmt.execute("UPDATE my_table SET value=1 WHERE primaryKey=" + myPrimaryKey); } catch(java.sql.SQLException sqlX) { // Find out if the error indicates the connection is stale if (com.ibm.websphere.rsadapter.WSCallHelper .getDataStoreHelper(ds) .isConnectionError(sqlX)) { // rollback the tran when method returns getEntityContext().setRollbackOnly(); throw new java.rmi.EJBException( "Exception occurred in ejbStore", new RetryableConnectionException(sqlX)); } else { // handle some other problem } } 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 } } } }In mySessionBMTMethod() of the previous example:
- The session bean first retrieves a UserTransaction object from the session context and then begins a global transaction.
- Next, it calls a method on the entity bean, which calls the ejbLoad() method. If ejbLoad() runs successfully, the client then commits the transaction, causing the ejbStore() method to be called.
- In ejbStore(), the entity bean gets a connection and writes its state to the database; if the connection retrieved is stale, the transaction is marked rollbackOnly and a new EJBException that wraps the RetryableConnectionException is thrown. That exception is then caught by the client, which cleans up the JDBC resources, rolls back the transaction, and calls causedByStaleConnection(), which determines if a stale connection exception is buried somewhere in the exception.
- If the method returns true, the retry flag is set and the transaction is retried; otherwise, the exception is re-issued to the caller.
- The causedByStaleConnection() method looks through the chain of detail attributes to find the original exception. ultiple wrapping of exceptions can occur by the time the exception finally gets back to the client, so the method keeps searching until it encounters stale connection exception and true is returned; otherwise, there is no stale connection exception in the list and false is returned.
- If we are talking to a CMP bean instead of to a BMP bean, the session bean is the same. The CMP bean ejbStore() method would most likely be empty, and the container after calling it would persist the bean with generated code.
- If a stale connection exception occurs during persistence, it is wrapped with a remote exception and returned to the caller. The causedByStaleConnection() method would again look through the exception chain and find the root exception, which would be stale connection exception.
- Objects operating in a local transaction context
- When a database operation occurs outside of a global transaction context, a local transaction is implicitly begun by the container. This includes servlets or JSPs that do not begin transactions with the UserTransaction interface, as well as enterprise beans running in unspecified transaction contexts. As with global transactions, we must roll back the local transaction before the operation is retried. In these cases, the local transaction containment usually ends when the business method ends. The one exception is if we are using activity sessions. In this case the activity session must end before attempting to get a new connection.
When the local transaction occurs in an enterprise bean running in an unspecified transaction context, the enterprise bean client object, outside of the local transaction containment, could use the method described in the previous bullet to retry the transaction. However, when the local transaction containment takes place as part of a servlet or JSP file, there is no client object available to retry the operation. For this reason, IBM recommends to avoid database operations in servlets and JSP files unless they are a part of a user transaction.
Stale Connection on Linux systems
We might must set a loopback to access DB2 databases from the application server on a Linux platform.
A Linux semaphore issue can interfere with JDBC access to your DB2 database in either of these configurations:
- Use the DB2 Universal JDBC Type 2 driver to connect to a local DB2 database
- Use the DB2 Universal JDBC Type 2 driver to access DB2 for z/OS through a DB2 Connect™ installation on the same machine as the application server. The problem occurs only if DB2 Connect restricts local clients from running within an agent. (That is, if the DB2_IN_APP_PROCESS setting is not the default value, or if the setting is Yes. Set the value to No to fix the problem and avoid performing the following procedure.)
The issue often triggers the JVM logs to display the DB2 stale connection exception SQL1224. Because the SQL exception code can vary, however, check the DB2 trace log when we encounter a stale connection. If we see the following error data, the Linux semaphore behavior is the problem:
'71' -SQLCC_ERR_CONN_CLOSED_BY_PARTNER and SQLCODE -XXXX
To work around the problem, set the loopback for our database. For example, if the database name is WAS, host name is LHOST, and database service port number is 50000, issue the following commands from the DB2 command-line window:
db2 catalog TCPIP node RHOST remote LHOST server 50000
db2 uncatalog db WAS
db2 catalog db WAS as WASAlias at node loop authentication server
//If we connect to WASAlias, it is connect through loopback;
//If we connect to WAS, it is "normal" connect.
db2 catalog db WASAlias as WAS at node RHOST
Example: Handling servlet JDBC connection exceptions
The following code sample demonstrates how to set transaction management and connection management properties, such as operation retries, to address stale connection exceptions within a servlet JDBC transaction.
This example code performs the following actions:
- initializes a servlet
- looks up a data source
- specifies error messages, connection retries, and transaction rollback requirements
//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; import java.io.*; import javax.servlet.*; import javax.servlet.http.*; import java.util.*; // Import JDBC packages and naming service packages. import java.sql.*; import javax.sql.*; import javax.naming.*; import javax.transaction.*; import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException; import com.ibm.websphere.rsadapter.WSCallHelper; public class EmployeeListTran extends HttpServlet { private static DataSource ds = null; private UserTransaction ut = null; private static String title = "Employee List"; // **************************************************************** // * Initialize servlet when it is first loaded. * // * Get information from the properties file, and look up the * // * DataSource object from JNDI to improve performance of the * // * the servlet's service methods. * // **************************************************************** public void init(ServletConfig config) throws ServletException { super.init(config); getDS(); } // **************************************************************** // * Perform the JNDI lookup for the DataSource and * // * User Transaction objects. * // * This method is invoked from init(), and from the service * // * method of the DataSource is null * // **************************************************************** private void getDS() { try { Hashtable parms = new Hashtable(); parms.put(Context.INITIAL_CONTEXT_FACTORY, com.ibm.websphere.naming.WsnInitialContextFactory); InitialContext ctx = new InitialContext(parms); // Perform a naming service lookup to get the DataSource object. ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB"); ut = (UserTransaction) ctx.lookup("java:comp/UserTransaction"); } catch (Exception e) { System.out.println("Naming service exception:" + e.getMessage()); e.printStackTrace(); } } // **************************************************************** // * Respond to user GET request * // **************************************************************** public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { Connection conn = null; Statement stmt = null; ResultSet rs = null; Vector employeeList = new Vector(); // Set retryCount to the number of times we would like to retry after a // stale connection exception int retryCount = 5; // If the Database code processes successfully, we will set error = false boolean error = true; do { try { //Start a new Transaction ut.begin(); // Get a Connection object conn using the DataSource factory. conn = ds.getConnection(); // Run DB query using standard JDBC coding. stmt = conn.createStatement(); String query = "Select FirstNme, MidInit, LastName" + "from Employee ORDER BY LastName"; rs = stmt.executeQuery(query); while (rs.next()) { employeeList.addElement(rs.getString(3) + ", "+ rs.getString(1)" + " + rs.getString(2)); } //Set error to false to indicate successful completion of the database work error=false; } catch (SQLException sqlX) { // Determine if the connection request timed out. // This code works regardless of which error detection // model is used. If exception mapping is enabled, then // we need to look for ConnectionWaitTimeoutException. // If exception checking is enabled, then look for // SQLTransientConnectionException with a chained // ConnectionWaitTimeoutException. if ( sqlX instanceof ConnectionWaitTimeoutException || sqlX instanceof SQLTransientConnectionException && sqlX.getCause() instanceof ConnectionWaitTimeoutException) { // This exception is thrown if a connection can not be obtained from the // pool within a configurable amount of time. Frequent occurrences of // this exception indicate an incorrectly tuned connection pool System.out.println("Connection Wait Timeout Exception during get connection or process SQL:" + c.getMessage()); //In general, we do not want to retry after this exception, so set retry count to 0 //and roll back the transaction try { ut.setRollbackOnly(); } catch (SecurityException se) { //Thrown to indicate that the thread is not allowed to roll back the transaction. System.out.println("Security Exception setting rollback only!" + se.getMessage()); } catch (IllegalStateException ise) { //Thrown if the current thread is not associated with a transaction. System.out.println("Illegal State Exception setting rollback only!" + ise.getMessage()); } catch (SystemException sye) { //Thrown if the transaction manager encounters an unexpected error condition System.out.println("System Exception setting rollback only!" + sye.getMessage()); } retryCount=0; } else if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX)) { // This exception indicates that the connection to the database is no longer valid. //Roll back the transaction, then retry several times to attempt to obtain a valid //connection, display an error message if the connection still can not be obtained. System.out.println("Connection is stale:" + sc.getMessage()); try { ut.setRollbackOnly(); } catch (SecurityException se) { //Thrown to indicate that the thread is not allowed to roll back the transaction. System.out.println("Security Exception setting rollback only!" + se.getMessage()); } catch (IllegalStateException ise) { //Thrown if the current thread is not associated with a transaction. System.out.println("Illegal State Exception setting rollback only!" + ise.getMessage()); } catch (SystemException sye) { //Thrown if the transaction manager encounters an unexpected error condition System.out.println("System Exception setting rollback only!" + sye.getMessage()); } if (--retryCount == 0) { System.out.println("Five stale connection exceptions, displaying error page."); } } else { System.out.println("SQL Exception during get connection or process SQL: " + sq.getMessage()); //In general, we do not want to retry after this exception, so set retry count to 0 //and rollback the transaction try { ut.setRollbackOnly(); } catch (SecurityException se) { //Thrown to indicate that the thread is not allowed to roll back the transaction. System.out.println("Security Exception setting rollback only!" + se.getMessage()); } catch (IllegalStateException ise) { //Thrown if the current thread is not associated with a transaction. System.out.println("Illegal State Exception setting rollback only!" + ise.getMessage()); } catch (SystemException sye) { //Thrown if the transaction manager encounters an unexpected error condition System.out.println("System Exception setting rollback only!" + sye.getMessage()); } retryCount=0; } } catch (NotSupportedException nse) { //Thrown by UserTransaction begin method if the thread is already associated with a //transaction and the Transaction Manager implementation does not support nested //transactions. System.out.println("NotSupportedException on User Transaction begin:" + nse.getMessage()); } catch (SystemException se) { //Thrown if the transaction manager encounters an unexpected error condition System.out.println("SystemException in User Transaction:" +se.getMessage()); } catch (Exception e) { System.out.println("Exception in get connection or process SQL:" + e.getMessage()); //In general, we do not want to retry after this exception, so set retry count to 5 //and roll back the transaction try { ut.setRollbackOnly(); } catch (SecurityException se) { //Thrown to indicate that the thread is not allowed to roll back the transaction. System.out.println("Security Exception setting rollback only!" + se.getMessage()); } catch (IllegalStateException ise) { //Thrown if the current thread is not associated with a transaction. System.out.println("Illegal State Exception setting rollback only!" + ise.getMessage()); } catch (SystemException sye) { //Thrown if the transaction manager encounters an unexpected error condition System.out.println("System Exception setting rollback only!" + sye.getMessage()); } retryCount=0; } finally { // Always close the connection in a finally statement to ensure proper // closure in all cases. Closing the connection does not close and // actual connection, but releases it back to the pool for reuse. if (rs != null) { try { rs.close(); } catch (Exception e) { System.out.println("Close Resultset Exception:" + e.getMessage()); } } if (stmt != null) { try { stmt.close(); } catch (Exception e) { System.out.println("Close Statement Exception:" + e.getMessage()); } } if (conn != null) { try { conn.close(); } catch (Exception e) { System.out.println("Close connection exception:" + e.getMessage()); } } try { ut.commit(); } catch (RollbackException re) { //Thrown to indicate that the transaction has been rolled back rather than committed. System.out.println("User Transaction Rolled back!" + re.getMessage()); } catch (SecurityException se) { //Thrown to indicate that the thread is not allowed to commit the transaction. System.out.println("Security Exception thrown on transaction commit:" + se.getMessage()); } catch (IllegalStateException ise) { //Thrown if the current thread is not associated with a transaction. System.out.println("Illegal State Exception thrown on transaction commit:" + ise.getMessage()); } catch (SystemException sye) { //Thrown if the transaction manager encounters an unexpected error condition System.out.println("System Exception thrown on transaction commit:" + sye.getMessage()); } catch (Exception e) { System.out.println("Exception thrown on transaction commit:" + e.getMessage()); } } } while ( error==true && retryCount > 0 ); // Prepare and return HTML response, prevent dynamic content from being cached // on browsers. res.setContentType("text/html"); res.setHeader("Pragma", "no-cache"); res.setHeader("Cache-Control", "no-cache"); res.setDateHeader("Expires", 0); try { ServletOutputStream out = res.getOutputStream(); out.println("<HTML>"); out.println("<HEAD><TITLE>" + title + "</TITLE></HEAD>"); out.println("<BODY>"); if (error==true) { out.println("<H1>There was an error processing this request.</H1>" + "Please try the request again, or contact" + "the <a href='mailto:sysadmin@my.com'>System Administrator</a>"); } else if (employeeList.isEmpty()) { out.println("<H1>Employee List is Empty</H1>"); } else { out.println("<H1>Employee List </H1>"); for (int i = 0; i < employeeList.size(); i++) { out.println(employeeList.elementAt(i) + "<BR>"); } } out.println("</BODY></HTML>"); out.close(); } catch (IOException e) { System.out.println("HTML response exception:" + e.getMessage()); } } }
Example: Handling connection exceptions for session beans in container-managed database transactions
The following code sample demonstrates how to roll back transactions and issue exceptions to the bean client in cases of stale connection exceptions.
//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; import java.util.*; import java.sql.*; import javax.sql.*; import javax.ejb.*; import javax.naming.*; import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException; import com.ibm.websphere.rsadapter.WSCallHelper; /************************************************************************************* * This bean is designed to demonstrate Database Connections in a * Container Managed Transaction Session Bean. Its transaction attribute * * should be set to TX_REQUIRED or TX_REQUIRES_NEW. * ************************************************************************************** */ public class ShowEmployeesCMTBean implements SessionBean { private javax.ejb.SessionContext mySessionCtx = null; final static long serialVersionUID = 3206093459760846163L; private javax.sql.DataSource ds; //************************************************************************************ //* ejbActivate calls the getDS method, which does the JNDI lookup for the DataSource. //* Because the DataSource lookup is in a separate method, we can also invoke it from //* the getEmployees method in the case where the DataSource field is null. //************************************************************************************ public void ejbActivate() throws java.rmi.EJBException { getDS(); } /** * ejbCreate method * @exception javax.ejb.CreateException * @exception java.rmi.EJBException */ public void ejbCreate() throws javax.ejb.CreateException, java.rmi.EJBException {} /** * ejbPassivate method * @exception java.rmi.EJBException */ public void ejbPassivate() throws java.rmi.EJBException {} /** * ejbRemove method * @exception java.rmi.EJBException */ public void ejbRemove() throws java.rmi.EJBException {} //************************************************************************************ //* The getEmployees method runs the database query to retrieve the employees. //* The getDS method is only called if the DataSource variable is null. //* Because this session bean uses Container Managed Transactions, it cannot retry the //* transaction on a StaleConnectionException. However, it can throw an exception to //* its client indicating that the operation is retriable. //************************************************************************************ public Vector getEmployees() throws ConnectionWaitTimeoutException, SQLException, RetryableConnectionException { Connection conn = null; Statement stmt = null; ResultSet rs = null; Vector employeeList = new Vector(); if (ds == null) getDS(); try { // Get a Connection object conn using the DataSource factory. conn = ds.getConnection(); // Run DB query using standard JDBC coding. stmt = conn.createStatement(); String query = "Select FirstNme, MidInit, LastName" + "from Employee ORDER BY LastName;" rs = stmt.executeQuery(query); while (rs.next()) { employeeList.addElement(rs.getString(3) + ", "+ rs.getString(1)" + " + rs.getString(2)); } } catch (SQLException sqlX) { // Determine if the connection is stale if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX)) { // This exception indicates that the connection to the database is no longer valid. // Roll back the transaction, and throw an exception to the client indicating they // can retry the transaction if desired. System.out.println("Connection is stale:" + sqlX.getMessage()); System.out.println("Rolling back transaction and throwing RetryableConnectionException"); mySessionCtx.setRollbackOnly(); throw new RetryableConnectionException(sqlX.toString()); } // Determine if the connection request timed out. else if ( sqlX instanceof ConnectionWaitTimeoutException || sqlX instanceof SQLTransientConnectionException && sqlX.getCause() instanceof ConnectionWaitTimeoutException) { // This exception is thrown if a connection can not be obtained from the // pool within a configurable amount of time. Frequent occurrences of // this exception indicate an incorrectly tuned connection pool System.out.println("Connection Wait Timeout Exception during get connection or process SQL:" + sqlX.getMessage()); throw sqlX instanceof ConnectionWaitTimeoutException ? sqlX : (ConnectionWaitTimeoutException) sqlX.getCause(); } else { //Throwing a remote exception will automatically roll back the container managed //transaction System.out.println("SQL Exception during get connection or process SQL:" + sqlX.getMessage()); throw sqlX; } } finally { // Always close the connection in a finally statement to ensure proper // closure in all cases. Closing the connection does not close and // actual connection, but releases it back to the pool for reuse. if (rs != null) { try { rs.close(); } catch (Exception e) { System.out.println("Close Resultset Exception:" + e.getMessage()); } } if (stmt != null) { try { stmt.close(); } catch (Exception e) { System.out.println("Close Statement Exception:" + e.getMessage()); } } if (conn != null) { try { conn.close(); } catch (Exception e) { System.out.println("Close connection exception:" + e.getMessage()); } } } return employeeList; } /** * getSessionContext method * @return javax.ejb.SessionContext */ public javax.ejb.SessionContext getSessionContext() { return mySessionCtx; } //************************************************************************************ //* The getDS method performs the JNDI lookup for the data source. //* This method is called from ejbActivate, and from getEmployees if the data source //* object is null. //************************************************************************************ private void getDS() { try { Hashtable parms = new Hashtable(); parms.put(Context.INITIAL_CONTEXT_FACTORY, com.ibm.websphere.naming.WsnInitialContextFactory); InitialContext ctx = new InitialContext(parms); // Perform a naming service lookup to get the DataSource object. ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB"); } catch (Exception e) { System.out.println("Naming service exception:" + e.getMessage()); e.printStackTrace(); } } /** * setSessionContext method * @param ctx javax.ejb.SessionContext * @exception java.rmi.EJBException */ public void setSessionContext(javax.ejb.SessionContext ctx) throws java.rmi.EJBException { mySessionCtx = ctx; } }
//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; /** * This is a Home interface for the Session Bean */ public interface ShowEmployeesCMTHome extends javax.ejb.EJBHome { /** * create method for a session bean * @return WebSphereSamples.ConnPool.ShowEmployeesCMT * @exception javax.ejb.CreateException * @exception java.rmi.RemoteException */ WebSphereSamples.ConnPool.ShowEmployeesCMT create() throws javax.ejb.CreateException, java.rmi.RemoteException; }
//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; /** * This is an Enterprise Java Bean Remote Interface */ public interface ShowEmployeesCMT extends javax.ejb.EJBObject { /** * * @return java.util.Vector */ java.util.Vector getEmployees() throws java.sql.SQLException, java.rmi.RemoteException, ConnectionWaitTimeoutException, WebSphereSamples.ConnPool.RetryableConnectionException; }//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; /** * Exception indicating that the operation can be retried * Creation date: (4/2/2001 10:48:08 AM) * @author: Administrator */ public class RetryableConnectionException extends Exception { /** * RetryableConnectionException constructor. */ public RetryableConnectionException() { super(); } /** * RetryableConnectionException constructor. * @param s java.lang.String */ public RetryableConnectionException(String s) { super(s); } }
Example: Handling connection exceptions for session beans in bean-managed database transactions
The following code sample demonstrates your options for addressing stale connection exceptions. We can set different transaction management and connection management parameters, such as the number of operation retries, and the connection timeout interval.
//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; import java.util.*; import java.sql.*; import javax.sql.*; import javax.ejb.*; import javax.naming.*; import javax.transaction.*; import com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException; import com.ibm.websphere.rsadapter.WSCallHelper; /********************************************************************************** * This bean is designed to demonstrate Database Connections in a * * Bean-Managed Transaction Session Bean. Its transaction attribute * * should be set to TX_BEANMANAGED. **********************************************************************************/ public class ShowEmployeesBMTBean implements SessionBean { private javax.ejb.SessionContext mySessionCtx = null; final static long serialVersionUID = 3206093459760846163L; private javax.sql.DataSource ds; private javax.transaction.UserTransaction userTran; //************************************************************************************ //* ejbActivate calls the getDS method, which makes the JNDI lookup for the DataSource //* Because the DataSource lookup is in a separate method, we can also invoke it from //* the getEmployees method in the case where the DataSource field is null. //************************************************************************************ public void ejbActivate() throws java.rmi.EJBException { getDS(); } /** * ejbCreate method * @exception javax.ejb.CreateException * @exception java.rmi.EJBException */ public void ejbCreate() throws javax.ejb.CreateException, java.rmi.EJBException {} /** * ejbPassivate method * @exception java.rmi.EJBException */ public void ejbPassivate() throws java.rmi.EJBException {} /** * ejbRemove method * @exception java.rmi.EJBException */ public void ejbRemove() throws java.rmi.EJBException {} //************************************************************************************ //* The getEmployees method runs the database query to retrieve the employees. //* The getDS method is only called if the DataSource or userTran variables are null. //* If a stale connection occurs, the bean retries the transaction 5 times, //* then throws an EJBException. //************************************************************************************ public Vector getEmployees() throws EJBException { Connection conn = null; Statement stmt = null; ResultSet rs = null; Vector employeeList = new Vector(); // Set retryCount to the number of times we would like to retry after a // stale connection int retryCount = 5; // If the Database code processes successfully, we will set error = false boolean error = true; if (ds == null || userTran == null) getDS(); do { try { //try/catch block for UserTransaction work //Begin the transaction userTran.begin(); try { //try/catch block for database work //Get a Connection object conn using the DataSource factory. conn = ds.getConnection(); // Run DB query using standard JDBC coding. stmt = conn.createStatement(); String query = "Select FirstNme, MidInit, LastName" + "from Employee ORDER BY LastName"; rs = stmt.executeQuery(query); while (rs.next()) { employeeList.addElement(rs.getString(3) + ", "+ rs.getString(1) +" " + rs.getString(2)); } //Set error to false, as all database operations are successfully completed error = false; } catch (SQLException sqlX) { if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(sqlX)) { // This exception indicates that the connection to the database is no longer valid. // Rollback the transaction, and throw an exception to the client indicating they // can retry the transaction if desired. System.out.println("Stale connection:" + se.getMessage()); userTran.rollback(); if (--retryCount == 0) { //If we have already retried the requested number of times, throw an EJBException. throw new EJBException("Transaction Failure:" + sqlX.toString()); } else { System.out.println("Retrying transaction, retryCount =" + retryCount); } } else if (sqlX instanceof ConnectionWaitTimeoutException || sqlX instanceof SQLTransientConnectionException && sqlX.getCause() instanceof ConnectionWaitTimeoutException) { // This exception is thrown if a connection can not be obtained from the // pool within a configurable amount of time. Frequent occurrences of // this exception indicate an incorrectly tuned connection pool System.out.println("Connection request timed out:" + sqlX.getMessage()); userTran.rollback(); throw new EJBException("Transaction failure:" + sqlX.getMessage()); } else { // This catch handles all other SQL Exceptions System.out.println("SQL Exception during get connection or process SQL:" + sqlX.getMessage()); userTran.rollback(); throw new EJBException("Transaction failure:" + sqlX.getMessage()); } finally { // Always close the connection in a finally statement to ensure proper // closure in all cases. Closing the connection does not close and // actual connection, but releases it back to the pool for reuse. if (rs != null) { try { rs.close(); } catch (Exception e) { System.out.println("Close Resultset Exception:" + e.getMessage()); } } if (stmt != null) { try { stmt.close(); } catch (Exception e) { System.out.println("Close Statement Exception:" + e.getMessage()); } } if (conn != null) { try { conn.close(); } catch (Exception e) { System.out.println("Close connection exception:" + e.getMessage()); } } } if (!error) { //Database work completed successfully, commit the transaction userTran.commit(); } //Catch UserTransaction exceptions } catch (NotSupportedException nse) { //Thrown by UserTransaction begin method if the thread is already associated with a //transaction and the Transaction Manager implementation does not support nested transactions. System.out.println("NotSupportedException on User Transaction begin:" + nse.getMessage()); throw new EJBException("Transaction failure:" + nse.getMessage()); } catch (RollbackException re) { //Thrown to indicate that the transaction has been rolled back rather than committed. System.out.println("User Transaction Rolled back!" + re.getMessage()); throw new EJBException("Transaction failure:" + re.getMessage()); } catch (SystemException se) { //Thrown if the transaction manager encounters an unexpected error condition System.out.println("SystemException in User Transaction:" + se.getMessage()); throw new EJBException("Transaction failure:" + se.getMessage()); } catch (Exception e) { //Handle any generic or unexpected Exceptions System.out.println("Exception in User Transaction:" + e.getMessage()); throw new EJBException("Transaction failure:" + e.getMessage()); } } while (error); return employeeList; } /** * getSessionContext method comment * @return javax.ejb.SessionContext */ public javax.ejb.SessionContext getSessionContext() { return mySessionCtx; } //************************************************************************************ //* The getDS method performs the JNDI lookup for the DataSource. //* This method is called from ejbActivate, and from getEmployees if the DataSource //* object is null. //************************************************************************************ private void getDS() { try { Hashtable parms = new Hashtable(); parms.put(Context.INITIAL_CONTEXT_FACTORY, com.ibm.websphere.naming.WsnInitialContextFactory); InitialContext ctx = new InitialContext(parms); // Perform a naming service lookup to get the DataSource object. ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB"); //Create the UserTransaction object userTran = mySessionCtx.getUserTransaction(); } catch (Exception e) { System.out.println("Naming service exception:" + e.getMessage()); e.printStackTrace(); } } /** * setSessionContext method * @param ctx javax.ejb.SessionContext * @exception java.rmi.EJBException */ public void setSessionContext(javax.ejb.SessionContext ctx) throws java.rmi.EJBException { mySessionCtx = ctx; } }//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; /** * This is a Home interface for the Session Bean */ public interface ShowEmployeesBMTHome extends javax.ejb.EJBHome { /** * create method for a session bean * @return WebSphereSamples.ConnPool.ShowEmployeesBMT * @exception javax.ejb.CreateException * @exception java.rmi.RemoteException */ WebSphereSamples.ConnPool.ShowEmployeesBMT create() throws javax.ejb.CreateException, java.rmi.RemoteException; }//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; /** * This is an Enterprise Java Bean Remote Interface */ public interface ShowEmployeesBMT extends javax.ejb.EJBObject { /** * * @return java.util.Vector */ java.util.Vector getEmployees() throws java.rmi.RemoteException, javax.ejb.EJBException; }
Example: Handling connection exceptions for BMP beans in container-managed database transactions
The following code sample demonstrates how to roll back transactions and issue exceptions to the bean client in cases of stale connection exceptions.
//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2005,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; import java.util.*; import javax.ejb.*; import java.sql.*; import javax.sql.*; import javax.ejb.*; import javax.naming.*; import com.ibm.websphere.rsadapter.WSCallHelper; /** * This is an Entity Bean class with five BMP fields * String firstName, String lastName, String middleInit * String empNo, int edLevel */ public class EmployeeBMPBean implements EntityBean { private javax.ejb.EntityContext entityContext = null; final static long serialVersionUID = 3206093459760846163L; private java.lang.String firstName; private java.lang.String lastName; private String middleInit; private javax.sql.DataSource ds; private java.lang.String empNo; private int edLevel; /** * ejbActivate method * ejbActivate calls getDS(), which performs the * JNDI lookup for the datasource. */ public void ejbActivate() { getDS(); } /** * ejbCreate method for a BMP entity bean * @return WebSphereSamples.ConnPool.EmployeeBMPKey * @param key WebSphereSamples.ConnPool.EmployeeBMPKey * @exception javax.ejb.CreateException */ public WebSphereSamples.ConnPool.EmployeeBMPKey ejbCreate(String empNo, String firstName, String lastName, String middleInit, int edLevel) throws javax.ejb.CreateException { Connection conn = null; PreparedStatement ps = null; if (ds == null) getDS(); this.empNo = empNo; this.firstName = firstName; this.lastName = lastName; this.middleInit = middleInit; this.edLevel = edLevel; String sql = "insert into Employee (empNo, firstnme, midinit, lastname, edlevel) values (?,?,?,?,?)"; try { conn = ds.getConnection(); ps = conn.prepareStatement(sql); ps.setString(1, empNo); ps.setString(2, firstName); ps.setString(3, middleInit); ps.setString(4, lastName); ps.setInt(5, edLevel); if (ps.executeUpdate() != 1){ System.out.println("ejbCreate Failed to add user."); throw new CreateException("Failed to add user."); } } catch (SQLException se) { if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se)) { // This exception indicates that the connection to the database is no longer valid. // Rollback the transaction, and throw an exception to the client indicating they // can retry the transaction if desired. System.out.println("Connection is stale:" + se.getMessage()); throw new CreateException(se.getMessage()); } else { System.out.println("SQL Exception during get connection or process SQL:" + se.getMessage()); throw new CreateException(se.getMessage()); } } finally { // Always close the connection in a finally statement to ensure proper // closure in all cases. Closing the connection does not close an // actual connection, but releases it back to the pool for reuse. if (ps != null) { try { ps.close(); } catch (Exception e) { System.out.println("Close Statement Exception:" + e.getMessage()); } } if (conn != null) { try { conn.close(); } catch (Exception e) { System.out.println("Close connection exception:" + e.getMessage()); } } } return new EmployeeBMPKey(this.empNo); } /** * ejbFindByPrimaryKey method * @return WebSphereSamples.ConnPool.EmployeeBMPKey * @param primaryKey WebSphereSamples.ConnPool.EmployeeBMPKey * @exception javax.ejb.FinderException */ public WebSphereSamples.ConnPool.EmployeeBMPKey ejbFindByPrimaryKey(WebSphereSamples.ConnPool.EmployeeBMPKey primaryKey) javax.ejb.FinderException { loadByEmpNo(primaryKey.empNo); return primaryKey; } /** * ejbLoad method */ public void ejbLoad() { try { EmployeeBMPKey pk = (EmployeeBMPKey) entityContext.getPrimaryKey(); loadByEmpNo(pk.empNo); } catch (FinderException fe) { throw new EJBException("Cannot load Employee state from database."); } } /** * ejbPassivate method */ public void ejbPassivate() {} /** * ejbPostCreate method for a BMP entity bean * @param key WebSphereSamples.ConnPool.EmployeeBMPKey */ public void ejbPostCreate(String empNo, String firstName, String lastName, String middleInit, int edLevel) {} /** * ejbRemove method * @exception javax.ejb.RemoveException */ public void ejbRemove() throws javax.ejb.RemoveException { if (ds == null) GetDS(); String sql = "delete from Employee where empNo=?"; Connection con = null; PreparedStatement ps = null; try { con = ds.getConnection(); ps = con.prepareStatement(sql); ps.setString(1, empNo); if (ps.executeUpdate() != 1) { throw new EJBException("Cannot remove employee:" + empNo); } } catch (SQLException se) { if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se)) { // This exception indicates that the connection to the database is no longer valid. // Rollback the transaction, and throw an exception to the client indicating they // can retry the transaction if desired. System.out.println("Connection is stale:" + se.getMessage()); throw new EJBException(se.getMessage()); } else { System.out.println("SQL Exception during get connection or process SQL:" + se.getMessage()); throw new EJBException(se.getMessage()); } } finally { // Always close the connection in a finally statement to ensure proper // closure in all cases. Closing the connection does not close an // actual connection, but releases it back to the pool for reuse. if (ps != null) { try { ps.close(); } catch (Exception e) { System.out.println("Close Statement Exception:" + e.getMessage()); } } if (con != null) { try { con.close(); } catch (Exception e) { System.out.println("Close connection exception:" + e.getMessage()); } } } } /** * Get the employee's edLevel * Creation date: (4/20/2001 3:46:22 PM) * @return int */ public int getEdLevel() { return edLevel; } /** * getEntityContext method * @return javax.ejb.EntityContext */ public javax.ejb.EntityContext getEntityContext() { return entityContext; } /** * Get the employee's first name * Creation date: (4/19/2001 1:34:47 PM) * @return java.lang.String */ public java.lang.String getFirstName() { return firstName; } /** * Get the employee's last name * Creation date: (4/19/2001 1:35:41 PM) * @return java.lang.String */ public java.lang.String getLastName() { return lastName; } /** * get the employee's middle initial * Creation date: (4/19/2001 1:36:15 PM) * @return char */ public String getMiddleInit() { return middleInit; } /** * Lookup the DataSource from JNDI * Creation date: (4/19/2001 3:28:15 PM) */ private void getDS() { try { Hashtable parms = new Hashtable(); parms.put(Context.INITIAL_CONTEXT_FACTORY, com.ibm.websphere.naming.WsnInitialContextFactory); InitialContext ctx = new InitialContext(parms); // Perform a naming service lookup to get the DataSource object. ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB"); } catch (Exception e) { System.out.println("Naming service exception:" + e.getMessage()); e.printStackTrace(); } } /** * Load the employee from the database * Creation date: (4/19/2001 3:44:07 PM) * @param empNo java.lang.String */ private void loadByEmpNo(String empNoKey) throws javax.ejb.FinderException { String sql = "select empno, firstnme, midinit, lastname, edLevel from employee where empno = ?"; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; if (ds == null) getDS(); try { // Get a Connection object conn using the DataSource factory. conn = ds.getConnection(); // Run DB query using standard JDBC coding. ps = conn.prepareStatement(sql); ps.setString(1, empNoKey); rs = ps.executeQuery(); if (rs.next()) { empNo= rs.getString(1); firstName=rs.getString(2); middleInit=rs.getString(3); lastName=rs.getString(4); edLevel=rs.getInt(5); } else { throw new ObjectNotFoundException("Cannot find employee number" + empNoKey); } } catch (SQLException se) { if (WSCallHelper.getDataStoreHelper(ds).isConnectionError(se)) { // This exception indicates that the connection to the database is no longer valid. // Roll back the transaction, and throw an exception to the client indicating they // can retry the transaction if desired. System.out.println("Connection is stale:" + se.getMessage()); throw new FinderException(se.getMessage()); } else { System.out.println("SQL Exception during get connection or process SQL:" + se.getMessage()); throw new FinderException(se.getMessage()); } } finally { // Always close the connection in a finally statement to ensure // proper closure in all cases. Closing the connection does not // close an actual connection, but releases it back to the pool // for reuse. if (rs != null) { try { rs.close(); } catch (Exception e) { System.out.println("Close Resultset Exception:" + e.getMessage()); } } if (ps != null) { try { ps.close(); } catch (Exception e) { System.out.println("Close Statement Exception:" + e.getMessage()); } } if (conn != null) { try { conn.close(); } catch (Exception e) { System.out.println("Close connection exception:" + e.getMessage()); } } } } /** * set the employee's education level * Creation date: (4/20/2001 3:46:22 PM) * @param newEdLevel int */ public void setEdLevel(int newEdLevel) { edLevel = newEdLevel; } /** * setEntityContext method * @param ctx javax.ejb.EntityContext */ public void setEntityContext(javax.ejb.EntityContext ctx) { entityContext = ctx; } /** * set the employee's first name * Creation date: (4/19/2001 1:34:47 PM) * @param newFirstName java.lang.String */ public void setFirstName(java.lang.String newFirstName) { firstName = newFirstName; } /** * set the employee's last name * Creation date: (4/19/2001 1:35:41 PM) * @param newLastName java.lang.String */ public void setLastName(java.lang.String newLastName) { lastName = newLastName; } /** * set the employee's middle initial * Creation date: (4/19/2001 1:36:15 PM) * @param newMiddleInit char */ public void setMiddleInit(String newMiddleInit) { middleInit = newMiddleInit; } /** * unsetEntityContext method */ public void unsetEntityContext() { entityContext = null; } }
//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; /** * This is an Enterprise Java Bean Remote Interface */ public interface EmployeeBMP extends javax.ejb.EJBObject { /** * * @return int */ int getEdLevel() throws java.rmi.RemoteException; /** * * @return java.lang.String */ java.lang.String getFirstName() throws java.rmi.RemoteException; /** * * @return java.lang.String */ java.lang.String getLastName() throws java.rmi.RemoteException; /** * * @return java.lang.String */ java.lang.String getMiddleInit() throws java.rmi.RemoteException; /** * * @return void * @param newEdLevel int */ void setEdLevel(int newEdLevel) throws java.rmi.RemoteException; /** * * @return void * @param newFirstName java.lang.String */ void setFirstName(java.lang.String newFirstName) throws java.rmi.RemoteException; /** * * @return void * @param newLastName java.lang.String */ void setLastName(java.lang.String newLastName) throws java.rmi.RemoteException; /** * * @return void * @param newMiddleInit java.lang.String */ void setMiddleInit(java.lang.String newMiddleInit) throws java.rmi.RemoteException; }//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; /** * This is an Enterprise Java Bean Remote Interface */ public interface EmployeeBMP extends javax.ejb.EJBObject { /** * * @return int */ int getEdLevel() throws java.rmi.RemoteException; /** * * @return java.lang.String */ java.lang.String getFirstName() throws java.rmi.RemoteException; /** * * @return java.lang.String */ java.lang.String getLastName() throws java.rmi.RemoteException; /** * * @return java.lang.String */ java.lang.String getMiddleInit() throws java.rmi.RemoteException; /** * * @return void * @param newEdLevel int */ void setEdLevel(int newEdLevel) throws java.rmi.RemoteException; /** * * @return void * @param newFirstName java.lang.String */ void setFirstName(java.lang.String newFirstName) throws java.rmi.RemoteException; /** * * @return void * @param newLastName java.lang.String */ void setLastName(java.lang.String newLastName) throws java.rmi.RemoteException; /** * * @return void * @param newMiddleInit java.lang.String */ void setMiddleInit(java.lang.String newMiddleInit) throws java.rmi.RemoteException; }//===================START_PROLOG====================================== // // 5630-A23, 5630-A22, // (C) COPYRIGHT International Business Machines Corp. 2002,2008 // All Rights Reserved // Licensed Materials - Property of IBM // US Government Users Restricted Rights - Use, duplication or // disclosure restricted by GSA ADP Schedule Contract with IBM Corp. // // IBM DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, INCLUDING // ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR // PURPOSE. IN NO EVENT SHALL IBM BE LIABLE FOR ANY SPECIAL, INDIRECT OR // CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF // USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR // OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE // OR PERFORMANCE OF THIS SOFTWARE. // //===================END_PROLOG======================================== package WebSphereSamples.ConnPool; /** * This is a Primary Key Class for the Entity Bean **/ public class EmployeeBMPKey implements java.io.Serializable { public String empNo; final static long serialVersionUID = 3206093459760846163L; /** * EmployeeBMPKey() constructor */ public EmployeeBMPKey() { } /** * EmployeeBMPKey(String key) constructor */ public EmployeeBMPKey(String key) { empNo = key; } /** * equals method * - user must provide a proper implementation for the equal method. The generated * method assumes the key is a String object. */ public boolean equals (Object o) { if (o instanceof EmployeeBMPKey) return empNo.equals(((EmployeeBMPKey)o).empNo); else return false; } /** * hashCode method * - user must provide a proper implementation for the hashCode method. The generated * method assumes the key is a String object. */ public int hashCode () { return empNo.hashCode();
Example: Handling data access exception - ConnectionWaitTimeoutException (for the JDBC API)
This code sample demonstrates how we specify the conditions under which the application server issues the ConnectionWaitTimeoutException for a JDBC application.
In all cases in which the ConnectionWaitTimeoutException is caught, there is little that can be done to recover.
public void test1() { java.sql.Connection conn = null; java.sql.Statement stmt = null; java.sql.ResultSet rs = null; try { // Look for datasource java.util.Properties props = new java.util.Properties(); props.put( javax.naming.Context.INITIAL_CONTEXT_FACTORY, com.ibm.websphere.naming.WsnInitialContextFactory); ic = new javax.naming.InitialContext(props); javax.sql.DataSource ds1 = (javax.sql.DataSource) ic.lookup(jndiString); // Get Connection. conn = ds1.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery("select * from mytable where this = 54"); } catch (java.sql.SQLException sqlX) { if (sqlX instanceof com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException || sqlX instanceof java.sql.SQLTransientConnectionException && sqlX.getCause() instanceof com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException) { //notify the user the system could not provide a //connection to the database. This usually happens when the //connection pool is full and there is no connection //available for to share. } else { // handle other database problems. } } finally { if (rs != null) try { rs.close(); } catch (java.sql.SQLException sqle1) { } if (stmt != null) try { stmt.close(); } catch (java.sql.SQLException sqle1) { } if (conn != null) try { conn.close(); } catch (java.sql.SQLException sqle1) { } } }
Example: Handling data access exception - ConnectionWaitTimeoutException for Java EE Connector Architecture
This code sample demonstrates how we specify the conditions under which WAS issues the ConnectionWaitTimeout exception for a JCA application.
In all cases in which the ConnectionWaitTimeout exception is caught, there is little to do for recovery.
The following code fragment shows how to use this exception in Java EE Connector Architecture (JCA):
/** * This method does a simple Connection test. */ public void testConnection() throws javax.naming.NamingException, javax.resource.ResourceException, com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException { javax.resource.cci.ConnectionFactory factory = null; javax.resource.cci.Connection conn = null; javax.resource.cci.ConnectionMetaData metaData = null; try { // lookup the connection factory if (verbose) System.out.println("Look up the connection factory..."); try { factory = (javax.resource.cci.ConnectionFactory) (new InitialContext()).lookup("java:comp/env/eis/Sample"); } catch (javax.naming.NamingException ne) { // Connection factory cannot be looked up. throw ne; } // Get connection if (verbose) System.out.println("Get the connection..."); conn = factory.getConnection(); // Get ConnectionMetaData metaData = conn.getMetaData(); // Print out the metadata Information. System.out.println("EISProductName" is + metaData.getEISProductName()); } catch (com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException cwtoe) { // Connection Wait Timeout throw cwtoe; } catch (javax.resource.ResourceException re) { // Something wrong with connections. throw re; } finally { if (conn != null) { try { conn.close(); } catch (javax.resource.ResourceException re) { } } } }
Example: Handling data access exception - error mapping in DataStoreHelper
The application server provides a DataStoreHelper interface for mapping different database SQL error codes to the appropriate exceptions in the application server.
Error mapping is necessary because various database vendors can provide different SQL errors and codes that represent that same issue. For example, the stale connection exception has different codes in different databases. The DB2 SQLCODEs of 1015, 1034, 1036, and so on indicate that the connection is no longer available because of a temporary database problem. The Oracle SQLCODEs of 28, 3113, 3114, and so on, indicate the same situation.
Mapping these error codes to standard exceptions provides the consistency that makes applications portable across different installations of the application server. The following code segment illustrates how to add two error codes into the error map:public class NewDSHelper extends GenericDataStoreHelper { public NewDSHelper(java.util.Properties dataStoreHelperProperties) { super(dataStoreHelperProperties); java.util.Hashtable myErrorMap = null; myErrorMap = new java.util.Hashtable(); myErrorMap.put(new Integer(-803), myDuplicateKeyException.class); myErrorMap.put(new Integer(-1015), myStaleConnectionException.class); myErrorMap.put("S1000", MyTableNotFoundException.class); setUserDefinedMap(myErrorMap); ... } }A configuration option known as the Error Detection odel controls how the error map is used. At V6 and earlier, Exception apping was the only option available for the Error Detection Model. At V7 and later, another option called Exception Checking is also available. Under the Exception Mapping model, the application server consults the error map and replaces exceptions with the corresponding exception type listed in the error map. Under the Exception Checking model, the application server still consults the error map for its own purposes but does not replace exceptions. To continue to use Exception Mapping, we do not need to change anything. Exception apping is the default Error Detection Model. To use the Exception Checking Model, see the topic "Changing the Error Detection odel to use the Exception Checking Model" in the related links.
Database deadlock and foreign key conflicts
Repetition of certain SQL error messages indicates problems, such as database referential integrity violations, that we can prevent using the container managed persistence (CMP) sequence grouping feature.
Exceptions resulting from foreign key conflicts due to violations of database referential integrityA database referential integrity (RI) policy prescribes rules for how data is written to and deleted from the database tables to maintain relational consistency. Runtime requirements for managing bean persistence, however, can cause an enterprise JavaBeans (EJB) application to violate RI rules, which can cause database exceptions.
Your EJB application is violating database RI if you see an exception message in the WebSphere Application Server trace or log file that is similar to one of the following messages (which were produced in an environment running DB2):
>The insert or update value of the FOREIGN KEY table1.name_of_foreign_key_constraint is not equal to any value of the parent key of the parent table.
or
A parent row cannot be deleted because the relationship table1.name_of_foreign_key_constraint is not equal to any value of the parent key of the parent table.
To prevent these exceptions, we must designate the order in which entity beans update relational database tables by defining sequence groups for the beans.
Exceptions resulting from deadlock caused by optimistic concurrency control schemes
Additionally, sequence grouping can minimize transaction rollback exceptions for entity beans that are configured for optimistic concurrency control. Optimistic concurrency control dictates that database locks be held for minimal amounts of time, so that a maximum number of transactions consistently have access to the data. In such a highly available database, concurrent transactions can attempt to lock the same table row and create deadlock. The resulting exceptions can generate messages similar to the following (which was produced in an environment running DB2):
Unsuccessful execution caused by deadlock or timeout.
Use the sequence grouping feature to order bean persistence so that database deadlock is less likely to occur.
Subtopics
Related:
Data sources Relational resource adapters and JCA Connection considerations when migrating servlets, JavaServer Pages, or enterprise session beans Connection pool settings Connection pool (Version 4) settings (deprecated)