|
Problem(Abstract) |
The WebSphere® Application Server Connection Manager
provides multiple options to allow for troubleshooting connection related
issues such as orphaned connections (V4.0 data sources), inadvertent
creation of two-phase commit (2PC) transactions with one-phase commit
(1PC) drivers, or connection wait timeout exceptions. |
|
|
|
Cause |
Customers often encounter connection management issues
that are related to application code or configuration problems, rather
than bugs in the WebSphere connection manager. These issues include
orphaned database connections (V4.0 data sources), inadvertent 2PC
transactions with 1PC drivers, and ConnectionWaitTimeoutExceptions in the
application.
- Orphaned Connections
If an application does not properly close connections after use, the
connection might not return to the connection pool for reuse. After a
given time limit, the connection manager Orphan Alarm returns the
connections to the pool. However, this code pattern often results in slow
performance or ConnectionWaitTimeoutExceptions because no connections are
available for use.
- Inadvertent 2PC transactions with 1PC drivers
If two different resources (such as Java™ Message Service (JMS) or a
remote enterprise bean) are enlisted in the same transaction and 1PC
drivers are used (non-XA drivers), an exception is issued and the
transaction rolls back. In this situation, the drivers must be changed to
the 2PC (XA-compliant) drivers instead of the 1PC drivers.
Another scenario can occur when the application makes multiple calls to
get a connection from the same connection pool. Typically, if the
application requests two or more connections from the same data source,
the connection manager returns a handle to the same connection. This
results in only a single resource involved in the transaction. However, if
the application requests another connection with a different setting (such
as a different user name and password, resource authorization or isolation
level) or a connection from a different data source, a handle to a
different physical connection is returned, thus creating a 2PC
transaction.
- ConnectionWaitTimeoutException
If a request for a new connection is not fulfilled in a given amount
of time due to all connections in the pool being in use, an exception is
returned to the application. An assessment of why connections are being
held must be performed.
|
|
|
Resolving the
problem |
The WebSphere Connection Manager provides some new diagnostic option
settings that allow you to gather information on what pieces of your
applications might be creating orphaned connections, creating inadvertent
2PC transactions or holding connections for longer than expected.
Data sources (Version 4) running under the CM
architecture
The following applies only to the WebSphere Application Server V4.0
style data sources, not the J2C
data sources. This means that if you are using any release of
WebSphere Application Server V5 or higher, this function works only if you
are using the backward-compatible V4.0 style data sources for database
connectivity versus the J2C data sources.
This diagnostic option is available in the following WebSphere
Application Server releases:
- WebSphere Application Server V4.0.6 and below with
Connection Manager cumulative fix dated 30May2003.
- WebSphere Application Server V4.0.7 and above has it
built-in.
- WebSphere Application Server V5.0.2 and below with
Connection Manager cumulative fix dated later than 16June2003.
- WebSphere Application Server releases greater than V5.0.2
have this function built-in.
There are three different types of diagnostic information that can be
gathered. These options can be used individually or in combination.
The first diagnostic option prints an informational message to the
standard out log file each time a connection is returned to the connection
pool by the WebSphere Orphan alarm.
The second diagnostic option also prints a message to the standard out
log file when a connection is collected by the WebSphere Orphan alarm, but
includes a stack trace snapshot taken when the getConnection request is
fulfilled. This allows you to analyze which pieces of your code are not
correctly returning connections.
The third diagnostic option prints the getConnection stack trace
snapshots for each connection in use when a ConnectionWaitTimeoutException
is issued. This allows you to analyze which pieces of code are holding
connections at the time of the exception. This might indicate connections
being held longer than necessary, or being orphaned. It might also
indicate normal usage, in which case you should increase the size of your
connection pool, or of your Connection wait timeout.
To activate the connection manager diagnostic options, choose a data
source you want to monitor and add the property diagOptions with
one (or a combination) of the values below.
Value
|
Description
|
0
|
No CM Diagnostics are enabled |
1
|
Orphaned Connection Notification, a short message each
time a connection is orphaned. |
2
|
Orphaned Connection Application Code Path Tracing |
4
|
Connection Wait Timeout Code Path Tracing |
To set more than one value at a time, add the numeric values and set as
the value in the console. For example, to turn on both the orphan code
path (2) and the connection wait code path (4), set the diagOptions to
6 in the console (2+4=6). To turn on orphan notifications and
connection wait code path tracing, set diagOptions to 5 (1+4=5).
After setting the diagnostic options on the data source you want to
monitor, you must restart the Application Server using that data source
for the changes to be activated. When any of the diagnostic options are
activated, a message displays in the server standard out file upon server
startup indicating what options are activated. All diagnostic output go to
the standard out file also.
The performance impact of turning on the diagnostic options ranges from
1%-to-5% performance degradation, depending on which options and how many
are activated. Option 1 is at the low end of the performance impact range,
options 2 and 4 fall at the high end of the range.
Back to top
Data sources running under the JCA connection
manager
The following explains how to use the diagnostics in a JCA connection
manager.
- Using the Connection Leak Detection in J2C style data
sources
The best way of determining the root cause of a saturated connection
pool or ConnectionWaitTimeoutException is to recreate the error using a
light weight trace specification:
ConnLeakLogic=all=enabled |
This trace specification stores diagnostic data of all requests for a
connection from the connection pool, and outputs them to the trace log
file when a ConnectionWaitTimeoutException occurs. It also displays the
total time that each connection is in-use.
Once the diagnostic data has been captured, it is then possible to track
down the code paths where connection leaks seem prominent. For more
information on detecting and preventing connection leaks in application
code, see the WebSphere
Connection Pool White paper.
- Using the Inadvertent 2PC Transaction Detection
The 2PC transaction detection is automatically enabled as part of the
First Failure Data Capture (FFDC) framework in WebSphere Application
Server. The administrator does not have to turn on special function for
this feature to run.
Detecting the root cause of this second scenario can be difficult. In this
situation, the new function prints out the details of each connection to
the FFDC or StdOut logs in WebSphere Application Server
V4.0 releases. (The FFDC logs are located in the
install_root/logs/ffdc directory.) This helps
determine what setting is different between multiple connections.
The following is some sample output:
PoolName = jdbc/testV5DS
com.ibm.ws.rsadapter.spi.WSConnectionRequestInfoImpl@9e6090f
UserName = user2
Password = ******
Catalog = null
IsReadOnly = null
TypeMap = null
NoEnlist = false
Holdability = DEFAULT CURSOR HOLDABILITY VALUE (0)
ConfigID = 0
Isolation = REPEATABLE READ (4)
Support isolation switching = false
Handle type = java.sql.Connection =
Subject = null
Container Managed Auth = No
MCWrapper = MCWrapper id 3bb860b5 Managed connection
com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl@1aef60a0
State:STATE_TRAN_WRAPPER_INUSE
PoolName = jdbc/testV5DS
com.ibm.ws.rsadapter.spi.WSConnectionRequestInfoImpl@9e6090f
UserName = user1
Password = ******
Catalog = null
IsReadOnly = null
TypeMap = null
NoEnlist = false
Holdability = DEFAULT CURSOR HOLDABILITY VALUE (0)
ConfigID = 0
Isolation = REPEATABLE READ (4)
Support isolation switching = false
Handle type = java.sql.Connection =
Subject = null
Container Managed Auth = No |
Notes:
- In this case, the first connection request had a user name of
user2 while the second had a user name of user1. This
difference in user names results in two connections being returned to the
application. To correct this, the application must be modified to request
both connections using the same user name.
- All passwords will be blocked out to preserve the security of
passwords. If the passwords in both connections are different, a message
is printed under the connection information (for example: “Passwords
do NOT match!”).
There can often be a lot of FFDC files, and searching through all of them
can be tedious. The best way to quickly identify the FFDC log file that
contains the connection information from this error is to do a text search
for the error message ID, WTRN0062E, in the FFDC directory.
Back to top
|
|
|
|
|
Cross Reference information |
Segment |
Product |
Component |
Platform |
Version |
Edition |
Application Servers |
Runtimes for Java Technology |
Java SDK |
|
|
|
|
|
|