DB Connection Issues
1. Application gives "1c86bdf0 SharedPool I J2CA0086W: Shareable connection MCWrapper id 686bbdf9 Managed connection com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl@6156bdf9
State:STATE_TRAN_WRAPPER_INUSE" message when trying to do a db update within loop which is querying a view and carrying out some operations.
Shareable connections are not allowed within a local transaction containment boundary.
The above scenario suggests a possible problem in the application logic and how it is using the connection code. The J2CA0086 message is telling you that your application is using a shared connection in a LocalTransaction. Since the connection is enlisted in a Local Transaction, and not a Global Transaction, different rules are followed when it comes to connection sharing. The connection usage has to follow the pattern shown below:
For example, if the application calls the getConnection() method it gets connection1, uses connection1, and then, if it calls getConnection() method again and connection1 is not ready to be reused, connection2 is obtained. Both connections remain in the shared pool and both are associated with the Local Transaction until the Local Transaction ends (is committed or rolled back, or the method ends).
This can result in more connections being created than is expected, which is why the application is reaching the maximum connections, and getting ConnectionWaitTimeoutExceptions among others. This can cause a hang if the pool is at a maximum, and none of the threads that have connections can complete because they are waiting to get another connection. Another consequence could be that while these connections are persisting in the used pool, that other threads making connections to the same tables get timed out by the backend database while waiting for a lock to be returned, thus causing the exception:
SQL0911N: The current transaction has been rolled back because of a deadlock or timeout. Reason code 68
State:STATE_TRAN_WRAPPER_INUSE" message when trying to do a db update within loop which is querying a view and carrying out some operations.
In IBM this problem is documented under the topic "Application hangs with prior J2CA0086W warning messages"
The solutions are
1. The application must be modified to use serial reuse
or
2. The connection pool can be changed to unshareable.
or
2. The connection pool can be changed to unshareable.
Shareable connections are not allowed within a local transaction containment boundary.
The above scenario suggests a possible problem in the application logic and how it is using the connection code. The J2CA0086 message is telling you that your application is using a shared connection in a LocalTransaction. Since the connection is enlisted in a Local Transaction, and not a Global Transaction, different rules are followed when it comes to connection sharing. The connection usage has to follow the pattern shown below:
- get connection,
- use connection,
- close connection,
- commit transaction
For example, if the application calls the getConnection() method it gets connection1, uses connection1, and then, if it calls getConnection() method again and connection1 is not ready to be reused, connection2 is obtained. Both connections remain in the shared pool and both are associated with the Local Transaction until the Local Transaction ends (is committed or rolled back, or the method ends).
This can result in more connections being created than is expected, which is why the application is reaching the maximum connections, and getting ConnectionWaitTimeoutExceptions among others. This can cause a hang if the pool is at a maximum, and none of the threads that have connections can complete because they are waiting to get another connection. Another consequence could be that while these connections are persisting in the used pool, that other threads making connections to the same tables get timed out by the backend database while waiting for a lock to be returned, thus causing the exception:
SQL0911N: The current transaction has been rolled back because of a deadlock or timeout. Reason code 68
Comments
Post a Comment