Connection pool not releasing Connection

Some times we receive an unexpected behavious in our BPEL process.The connections from connection pool does not get released even if the process completes.

YOu might also receive errors like

Exception: Unable to get a physical connection from the database.there are no connections available

This can be also seen from the em console.

Login to the Application server control

Go to the instance (oc4j_soa)

GO to Administration tab

Choose JDBC Resources

then click 'go to task' icon

In the Connection Pools section click 'Monitor Performance' of that connection pool, the "Pool Usage"

You will get some data like this

Connections In Use 400

Free Connections 100

Total Connections in Pool 500

Threads Waiting for Connections 0


Now even if your bpel process has ended the Free connections are still 100.This is because your connection pool is not releasing the used connections.

So we have to make some manual changes in our configuration files in order to make the connections die once the bpel process manager is done with the connection

YOu need to make the following changes in your data-source.xml

Go to SOA_ORACLE_HOME/j2ee//config/data-sources.xml, add the inactivity-timeout attribute and set to a positive value for the connection-pool.

Change
connection-pool name="BPELPM_CONNECTION_POOL" max-connections="50"

to

connection-pool name="BPELPM_CONNECTION_POOL" max-connections="50"
abandoned-connection-timeout="180" inactivity-timeout="50"


Please take a backup of the data-source.xml before applying any changes to it.

Once the changes is done.Save the data-source.xml and restart the server.

Now you should see that all the connection has been freed.If you now check in the em console you will get output something like this

Connections In Use 0

Free Connections 500

Total Connections in Pool 500

Threads Waiting for Connections 0

there is another common issue in BPEL that the maxmium connection setting defined in oc4j-ra.xml does not limit the connection.This happens mostly in the case of JMS/AQ adapter.So we should not set the maxConnections parameter for the connection pool in oc4j-ra.xml for the JMS/AQ adapter.

You should rather define it in the data-source.xml

connection-pool name="arpit" min-connections="10" max-connections="20" initial-limit="0" used-connection-wait-timeout="60" inactivity-timeout="60" connection-retry-interval="30" max-connect-attempts="10"

That is how it should be defined in data-source.xml.Once it is done restart the server and check.