Websphere connection pool errors:ORA-02396: exceeded maximum idle time, please connect again
Today one of our application experienced Oracle time out errors “ORA-02396: exceeded maximum idle time, please connect again” when ever user submits long running operations and was able to re produce the same issue in my local environment with the help of debugger .
Oracle profile for the app user has idle time out of 45 minutes and when ever application took more than 45 minutes for processing the request and tries to update the results to DB, the application is throwing this error. Oracle discards idle connections after 45 minutes and for some reason websphere is not discarding this stale connections. Application is getting connection from Connection pool via JNDI lookup and closing out resources during method exits and not keeping the connection open.
The data source has the default values for connection pool properties.
Reap time: 180 seconds
Unused timeout: 1800 seconds
Aged timeout: 0 seconds
Global Transactions :No
EJB Transaction Attribute:Default (required) -CMT
Transaction started : Not in Web Layer/EJB Layer. DAO classes are opening connections.
Pattern: open/use/close pattern for DB connections.
I’ve changed “Aged timeout parameter” to 1200 seconds in my local environment which didn’t help. Not sure why Connection pool is keeping stale connections.
Finally I decided to do more research why stale connections are not discarded from Websphere connection pool even with appropriate settings.
The application specifies “required” transaction attribute for all container managed transactions and resource sharing scope is sharable for all local transactions (LTC).
According to websphere documentation when ever new JDBC connection is allocated under LTC (Local transaction containment) it is marked as having an affinity to the current LTC and when application closes the connection it is not returned to the pool until transaction ends and connection remains used by this transaction so connection pool mechanism can’t discard this connection even though it becomes stale connection.
Please refer http://www.ibm.com/developerworks/websphere/library/techarticles/0506_johnsen/0506_johnsen.html for more details.
I have verified this in my local websphere and when ever LTC has started the same logical connection is returned every time application tries to acquire db connection.
Here are some resolutions for this issue
1) Change the resource sharing scope of JDBC connections to “UnSharable” which creates more physical connection to database instead of sharing single physical connection using multiple logical connections and connection will be released to connection pool after application closes the connection.
2Add exception handler to specific part of the code and obtain new connection gracefully
3)Optimize application processing or long running queries
I did more research on the possible implementation of option (1) and it seems that option (1) is not feasible to implement with our application DB access pattern.
Resource sharing property can be specified either in web.xml or ejb-jar.xml along with resource reference (Datasource) and application code (servlet or EJB) should lookup the data source using this reference container will utilize sharable property (res-sharing-scope).How ever our app code is not using resource references to obtain database connection and instead obtain connections in DAO class.
Since the sharable property in CMConfigData (Connection Manager config Data) object in websphere object and it is not exposed outside (no setters) , the possibility of using IBM helper classes to set the sharable property in the opened database connection is blocked
We can implement Option 2 and 3 together and IBM also suggests handling StaleConnection exceptions in the code and more information is available at http://www.redbooks.ibm.com/redbooks/SG246688/axa.htm
Today one of our application experienced Oracle time out errors “ORA-02396: exceeded maximum idle time, please connect again” when ever user submits long running operations and was able to re produce the same issue in my local environment with the help of debugger .
Oracle profile for the app user has idle time out of 45 minutes and when ever application took more than 45 minutes for processing the request and tries to update the results to DB, the application is throwing this error. Oracle discards idle connections after 45 minutes and for some reason websphere is not discarding this stale connections. Application is getting connection from Connection pool via JNDI lookup and closing out resources during method exits and not keeping the connection open.
The data source has the default values for connection pool properties.
Reap time: 180 seconds
Unused timeout: 1800 seconds
Aged timeout: 0 seconds
Global Transactions :No
EJB Transaction Attribute:Default (required) -CMT
Transaction started : Not in Web Layer/EJB Layer. DAO classes are opening connections.
Pattern: open/use/close pattern for DB connections.
I’ve changed “Aged timeout parameter” to 1200 seconds in my local environment which didn’t help. Not sure why Connection pool is keeping stale connections.
Finally I decided to do more research why stale connections are not discarded from Websphere connection pool even with appropriate settings.
The application specifies “required” transaction attribute for all container managed transactions and resource sharing scope is sharable for all local transactions (LTC).
According to websphere documentation when ever new JDBC connection is allocated under LTC (Local transaction containment) it is marked as having an affinity to the current LTC and when application closes the connection it is not returned to the pool until transaction ends and connection remains used by this transaction so connection pool mechanism can’t discard this connection even though it becomes stale connection.
Please refer http://www.ibm.com/developerworks/websphere/library/techarticles/0506_johnsen/0506_johnsen.html for more details.
I have verified this in my local websphere and when ever LTC has started the same logical connection is returned every time application tries to acquire db connection.
Here are some resolutions for this issue
1) Change the resource sharing scope of JDBC connections to “UnSharable” which creates more physical connection to database instead of sharing single physical connection using multiple logical connections and connection will be released to connection pool after application closes the connection.
2Add exception handler to specific part of the code and obtain new connection gracefully
3)Optimize application processing or long running queries
I did more research on the possible implementation of option (1) and it seems that option (1) is not feasible to implement with our application DB access pattern.
Resource sharing property can be specified either in web.xml or ejb-jar.xml along with resource reference (Datasource) and application code (servlet or EJB) should lookup the data source using this reference container will utilize sharable property (res-sharing-scope).How ever our app code is not using resource references to obtain database connection and instead obtain connections in DAO class.
Since the sharable property in CMConfigData (Connection Manager config Data) object in websphere object and it is not exposed outside (no setters) , the possibility of using IBM helper classes to set the sharable property in the opened database connection is blocked
We can implement Option 2 and 3 together and IBM also suggests handling StaleConnection exceptions in the code and more information is available at http://www.redbooks.ibm.com/redbooks/SG246688/axa.htm
No comments:
Post a Comment