donderdag 8 december 2016

Finding jdbc/db connection leaks


Once I started creating my own application module instances, I got database connections not being released anymore.  And because we are all working in complex and big application, I didn't found the cause of the leak.
This blog will help you in finding the cause for the jdbc leak.
All my knowledge is based on the blog from Raul Castillo:JDBC Connection Leak

Finding that you have leaks

The first thing to do is finding whether you have leaks.  This can be done through the end-users stating that the application returns an error stating no connections are available anymore.
Now you can start investigating the cause of this issue.
You can use the Fusion Middleware console to investigate from which datasource the connections are not freed anymore.  It has a lot of helpfull statistics.
  1. Log in the Fusion Middleware Console : http(s)://<host>:<port>/em
  2. Go to your application on the left side under Farm and Application Deployments
  3. Click on the name of your application
  4. In the Application Deployment menu you can choose for Performance Summary.
  5. This will bring you to a new screen showing some default selected diagrams.  
  6. Click on the "Show Metric Palette" button to change the diagrams being showed.
    1. For this use case, you want to go to Related Targets > ServerName > Metrics > Datasource metrics > YourDataSourceName, domain level
    2. Here can select the following interessanting ones:
      1. Datasource - Available connections
      2. Datasource - Connection Leaks
      3. Datasource - Connection Pool size
      4. Datasource - Connections in use
These graphs are very handy when you want to see the evolution of the use of the connections of the datasource.  You can open multiple graphs from different datasource at the same time.  So  you can correctly define the datasource that is being leaking connections.

Once you have determined the datasource causing the leaks, you need to make sure WebLogic Server is cleaning them up after a while.  This can be configured in the WebLogic console:
  1. Login to WLS Console : http(s)://<host>:<port>/console
  2. Go to the datasources defined in the domain
  3. Select the datasource causing the leaks
  4. Go to Configuration > Connection Pool > Advanced 
  5. Make sure the parameter Inactive Connection Timeout is a positive number.  Put it to 5 for testing.  This parameter will cause WebLogic Server to cleanup stuck db connections  and causing a nice error message in the log files.
  6. You can find the logging in your <WLS managed server name>.log file.
  7. It is this error message that you need.  The Inactive Connection Timeout identifies after how many seconds he will try to cleanup the db connections.  When he finds db connections leaking, he will log a java stack trace indicating from where the leak is originating.  Here is an example:

    ####<Dec 8, 2016 8:12:07 AM CET> <Warning> <Common> <zcorinthe-1> <dev01-S1mc> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <1bf3554408c0f552:-3a194c43:158d428de6f:-8000-0000000000002b61> <1481181127371> <BEA-000620> <Forcibly releasing inactive resource "autoCommit=false,enabled=true,isXA=false,isJTS=false,vendorID=100,connUsed=false,doInit=false,'null',destroyed=true,poolname=MCPrintDev,appname=null,moduleName=null,connectTime=87,dirtyIsolationLevel=false,initialIsolationLevel=2,infected=false,lastSuccessfulConnectionUse=1481181100045,secondsToTrustAnIdlePoolConnection=10,currentUser=java.lang.Exception
      at weblogic.jdbc.common.internal.ConnectionEnv.setup(
      at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(
      at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(
      at weblogic.jdbc.common.internal.ConnectionPool.reserve(
      at weblogic.jdbc.common.internal.ConnectionPool.reserve(
      at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(
      at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(
      at weblogic.jdbc.pool.Driver.connect(
      at weblogic.jdbc.jts.Driver.getNonTxConnection(
      at weblogic.jdbc.jts.Driver.connect(
      at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(
      at weblogic.jdbc.common.internal.RmiDataSource.getConnection(
      at weblogic.jdbc.common.internal.RmiDataSource.getConnection(
      at oracle.jbo.server.DBTransactionImpl.establishNewConnection(
      at oracle.jbo.server.DBTransactionImpl.initTransaction(
      at oracle.jbo.server.DBTransactionImpl.initTxn(
      at oracle.jbo.server.DBTransactionImpl2.connectToDataSource(
      at oracle.jbo.server.DBTransactionImpl2.connectToDataSource(
      at oracle.jbo.common.ampool.DefaultConnectionStrategy.connect(
      at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolConnect(
      at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolMessage(
      at oracle.jbo.server.ApplicationModuleImpl.doPoolMessage(
      at oracle.jbo.common.ampool.ApplicationPoolImpl.sendPoolMessage(
      at oracle.jbo.common.ampool.ApplicationPoolImpl.prepareApplicationModule(
      at oracle.jbo.common.ampool.ApplicationPoolImpl.doCheckout(
      at oracle.jbo.common.ampool.ApplicationPoolImpl.useApplicationModule(
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(
      at oracle.jbo.http.HttpSessionCookieImpl.useApplicationModule(
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(
      at oracle.jbo.client.Configuration.getApplicationModule(
      at oracle.jbo.client.Configuration.createRootApplicationModule(
      at oracle.jbo.client.Configuration.createRootApplicationModule(
      at be.contribute.demo.adf.DocumentManagement.createAMInstance(
      at be.contribute.demo.adf.DocumentManagement.<init>(
      at be.contribute.demo.adf.DocumentManagement.<init>(
      at be.contribute.demo.adf.SimpleDocumentGeneration.<init>(
      at be.contribute.demo.adf.bipublisher.SimpleEnvelopeDocumentGeneration.<init>(
      at be.contribute.demo.adf.bipublisher.AcknowledgmentRecieptGeneration.<init>(
      at be.contribute.demo.adf.bipublisher.ReportDataControl.generateReportMotives(
      at sun.reflect.GeneratedMethodAccessor1991.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(

     at java.lang.reflect.Method.invoke(
      at oracle.adf.model.binding.DCInvokeMethod.invokeMethod(
      at oracle.adf.model.binding.DCDataControl.invokeMethod(
  8. Now you look for your classes in the stack trace and make sure that for every creation of an am instance, you also close it correctly.
Hope this can help you in finding database leaks.

Have fun.