donderdag 8 december 2016

Finding jdbc/db connection leaks

Reason

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(ConnectionEnv.java:366)
      at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:373)
      at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:339)
      at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:469)
      at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:363)
      at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:125)
      at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:158)
      at weblogic.jdbc.pool.Driver.connect(Driver.java:132)
      at weblogic.jdbc.jts.Driver.getNonTxConnection(Driver.java:658)
      at weblogic.jdbc.jts.Driver.connect(Driver.java:127)
      at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:548)
      at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:513)
      at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:506)
      at oracle.jbo.server.DBTransactionImpl.establishNewConnection(DBTransactionImpl.java:1056)
      at oracle.jbo.server.DBTransactionImpl.initTransaction(DBTransactionImpl.java:1237)
      at oracle.jbo.server.DBTransactionImpl.initTxn(DBTransactionImpl.java:6964)
      at oracle.jbo.server.DBTransactionImpl2.connectToDataSource(DBTransactionImpl2.java:304)
      at oracle.jbo.server.DBTransactionImpl2.connectToDataSource(DBTransactionImpl2.java:335)
      at oracle.jbo.common.ampool.DefaultConnectionStrategy.connect(DefaultConnectionStrategy.java:203)
      at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolConnect(ApplicationPoolMessageHandler.java:620)
      at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolMessage(ApplicationPoolMessageHandler.java:425)
      at oracle.jbo.server.ApplicationModuleImpl.doPoolMessage(ApplicationModuleImpl.java:9518)
      at oracle.jbo.common.ampool.ApplicationPoolImpl.sendPoolMessage(ApplicationPoolImpl.java:4529)
      at oracle.jbo.common.ampool.ApplicationPoolImpl.prepareApplicationModule(ApplicationPoolImpl.java:2459)
      at oracle.jbo.common.ampool.ApplicationPoolImpl.doCheckout(ApplicationPoolImpl.java:2269)
      at oracle.jbo.common.ampool.ApplicationPoolImpl.useApplicationModule(ApplicationPoolImpl.java:3168)
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:592)
      at oracle.jbo.http.HttpSessionCookieImpl.useApplicationModule(HttpSessionCookieImpl.java:224)
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:525)
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:520)
      at oracle.jbo.client.Configuration.getApplicationModule(Configuration.java:1609)
      at oracle.jbo.client.Configuration.createRootApplicationModule(Configuration.java:1514)
      at oracle.jbo.client.Configuration.createRootApplicationModule(Configuration.java:1485)
      at be.contribute.demo.adf.DocumentManagement.createAMInstance(DocumentManagement.java:127)
      at be.contribute.demo.adf.DocumentManagement.<init>(DocumentManagement.java:110)
      at be.contribute.demo.adf.DocumentManagement.<init>(DocumentManagement.java:67)
      at be.contribute.demo.adf.SimpleDocumentGeneration.<init>(SimpleDocumentGeneration.java:83)
      at be.contribute.demo.adf.bipublisher.SimpleEnvelopeDocumentGeneration.<init>(SimpleEnvelopeDocumentGeneration.java:38)
      at be.contribute.demo.adf.bipublisher.AcknowledgmentRecieptGeneration.<init>(AcknowledgmentRecieptGeneration.java:23)
      at be.contribute.demo.adf.bipublisher.ReportDataControl.generateReportMotives(ReportDataControl.java:503)
      at sun.reflect.GeneratedMethodAccessor1991.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

     at java.lang.reflect.Method.invoke(Method.java:606)
      at oracle.adf.model.binding.DCInvokeMethod.invokeMethod(DCInvokeMethod.java:690)
      at oracle.adf.model.binding.DCDataControl.invokeMethod(DCDataControl.java:2181)
  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.

1 opmerking:

  1. In this manner my friend Wesley Virgin's biography starts with this shocking and controversial VIDEO.

    Wesley was in the army-and soon after leaving-he revealed hidden, "MIND CONTROL" secrets that the CIA and others used to obtain anything they want.

    THESE are the same secrets tons of famous people (especially those who "became famous out of nothing") and top business people used to become rich and famous.

    You probably know how you utilize only 10% of your brain.

    Really, that's because the majority of your BRAINPOWER is UNTAPPED.

    Maybe that conversation has even taken place INSIDE your very own head... as it did in my good friend Wesley Virgin's head 7 years back, while driving an unlicensed, beat-up bucket of a car without a license and with $3.20 on his bank card.

    "I'm absolutely fed up with living paycheck to paycheck! Why can't I turn myself successful?"

    You took part in those conversations, am I right?

    Your success story is waiting to start. All you need is to believe in YOURSELF.

    WATCH WESLEY SPEAK NOW

    BeantwoordenVerwijderen