0

I'm using a dynamic Java Web Application (Tomcat 8.0.15, Java EE 7 Web) with a SQL Server 2008 and after getting the warning/exception

WARNING [Tomcat JDBC Pool Cleaner[510210701:1481713957404]] org.apache.tomcat.jdbc.pool.ConnectionPool.suspect Connection has been marked suspect, possibly abandoned PooledConnection[net.sourceforge.jtds.jdbc.JtdsConnection@510fc080][67975 ms.]:java.lang.Exception

quite too often I wonder somewhere in the depths of my source code I forgot to disconnect a JDBC or Hibernate Connection to the database. I'd like to list them somehow.

A regular 

static 
    {
        try {
            Context context = new InitialContext ();
            dataSource = (DataSource) context.lookup("java:comp/env/jdbc/sqlserv");
        } catch (NamingException ex) {
            Logger.getLogger(Basisverbindung.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

does that job and in my hibernate.cfg.xml it's the same:

<property name="hibernate.connection.datasource">java:comp/env/jdbc/sqlserv</property>

I looked through Stackoverflow and found only a few entries which I consulted already (and even upvoted):

But the issue persists or comes up again after a while so I would like to find a way how to track down where I forgot to close the connection. On my Tomcat there's also a PSI Probe running telling me there are coming up some errors in the requests and sometimes maxing out the Response time. I see a nice list of requests there but don't know which ones are abandoned.

The ActivityMonitor in the SQL-Server Management Studio is not of too much help either it lists quite a few processes of which I know they are closed (or well, should be).

What's the best way to analyze that kind of problem?

Community
  • 1
  • 1
Qohelet
  • 1,459
  • 4
  • 24
  • 41

1 Answers1

0

What you really want to do is enable "abandoned connection" tracking and reporting.

You didn't say which of Tomcat's JDBC DataSource pools you were using (there are two), but they are configured similarly:

  1. commons-dbcp2-based: logAbandoned=true, removeAbandonedOnBorrow=true
  2. tomcat-jdbc-based: logAbandoned=true, removeAbandoned=true

I always recommend everyone run with a maximum pool size of 1 in development environments. This will help you identify pool leakage very quickly, plus catch any potential deadlocks you may have planted in your code.

Christopher Schultz
  • 20,221
  • 9
  • 60
  • 77
  • I'm using tomcat-jdbc and have set both options – Qohelet Dec 16 '16 at 13:50
  • ...and you aren't seeing stack-traces for where your `Connection` or `Statement` objects aren't being properly-closed? – Christopher Schultz Dec 16 '16 at 15:28
  • I'm using a static class which handles the distribution of connections (I can post the rest of the code but it's not too terrific). So mostly the stack just refers to that class... – Qohelet Dec 19 '16 at 17:07
  • @Qohelet Yes, but that class is called by client code which is likely leaking those connections. Look further up the call stack. – Christopher Schultz Dec 19 '16 at 19:24
  • I am already so far. Sometimes it works - if a specific query is taking too long. The problem is: That's more of a symptom than the root of the problem. If there is somewhere an unclosed connection this specific query will run forever - until it dies and I can trace it. But in that case I know already it's there... I sometimes have the feeling the pool runs out of connections... – Qohelet Dec 20 '16 at 14:45
  • @Qohelet You can also try a static analysis tool like [FingBugs](http://findbugs.sourceforge.net/). – Christopher Schultz Dec 20 '16 at 15:02