TOTD #164: JDBC Statement Leak Detection and Reclaim in GlassFish

If a JDBC Statement is not closed after use then the application will eventually run out of cursors. Enabling JDBC statement leak detection identifies such statements. These statements can be reclaimed as well, and cursors closed. Shalini blogged about how statement leakage can be detected and reclaimed. More details about this feature can also be found in Oracle GlassFish Server Online Docs.

This Tip Of The Day (TOTD) will show you can detect JDBC statement leakage and reclaim them using GlassFish 3.1.

  1. Edit the “glassfish/domains/domain1/config.xml” file and search for “jdbc-connection-pool” element with the name “DerbyPool”. Add the following attributes to the element:
    connection-leak-timeout-in-seconds="10" statement-timeout-in-seconds="6" 
    statement-leak-timeout-in-seconds="2"
    

    The attribute names are intuitive and specify the timeout options for JDBC statement and connection leakage. Notice, it follows the criteria of:
    statement-leak-timeout-in-seconds < statement-timeout-in-seconds < connection-leak-timeout-in-seconds
    Save the changes and (re)start the application server in NetBeans.
  2. Create a Java EE 6 project using NetBeans. Right-click on the project and select “New”, “Session Bean...”, select “Singleton” and specify the name as “TestBean”, a package name of “org.glassfish.samples” and click on “Finish”.
  3. Mark the bean for eager initialization by specifying the "@javax.ejb.Startup" at the class. This ensures that the bean is loaded during application deployment.
  4. Add the following method to the generated bean:
    @javax.annotation.PostConstruct
     void startup() {
       Connection c = null;
       Statement s = null;
       try {
         InitialContext ctx = new InitialContext();
         DataSource ds = (DataSource)ctx.lookup("jdbc/__default");
         c = ds.getConnection();
         s = c.createStatement();
       } catch (SQLException ex) {
         System.err.println("ouch!");
       } catch (NamingException ex) {
         System.err.println("ouch!");
       } finally {
         try {
           if (s != null)
             s.close();
           if (c != null)
             c.close();
         } catch (SQLException ex) {
           System.err.println("ouch!");;
         }
       }
     }
    


    This is a working version of the code with JDBC connection and statements correctly closed.
  5. Deploy the project by right-clicking and selecting “Deploy”. Now comment the two lines in the “finally” block where the statement is checked for null and closed. The project gets autodeployed and the bean is initialized. The following message is displayed in the GlassFish logs in NetBeans:
    
    WARNING: A potential statement leak detected for connection pool DerbyPool. The
     stack trace of the thread is provided below :
    com.sun.gjc.spi.base.StatementWrapper.(StatementWrapper.java:81)
    com.sun.gjc.spi.jdbc40.StatementWrapper40.(StatementWrapper40.java:68)
    com.sun.gjc.spi.jdbc40.ConnectionWrapper40.createStatement(ConnectionWrapper40.java:75)
    org.glassfish.samples.TestBean.startup(TestBean.java:37)
    

    This ensures that JDBC statement leak detection is working. Notice the exact line where the statement is leaked is also highlighted in the stack trace.
  6. Now uncomment the lines previously commented and comment the lines where the connection is checked for null and closed. The following message is displayed in the GlassFish logs in NetBeans:
    
    WARNING: A potential connection leak detected for connection pool DerbyPool. The 
    stack trace of the thread is provided below : com.sun.enterprise.resource.pool.ConnectionPool.setResourceStateToBusy(ConnectionPool.java:324)
    com.sun.enterprise.resource.pool.ConnectionPool.getResourceFromPool(ConnectionPool.java:754)
    com.sun.enterprise.resource.pool.ConnectionPool.getUnenlistedResource(ConnectionPool.java:632)
    

    This ensures that JDBC connection leak detection is working.
  7. Edit “glassfish/domains/domain1/config/domain.xml” and add the following attribute:
    
    statement-leak-reclaim=”true”
    

    to the “jdbc-connection-pool” element with the name “DerbyPool”. This will ensure that the leaked statement is reclaimed and the cursor is closed. Similarly adding

    connection-leak-reclaim=”false”

    ensures that the JDBC connection is reclaimed and returned to the pool.
Comments:

Great entry. Thank you. I assume your values for the timeouts are for this example. What do you recommend for values in a production environment?

Posted by guest on October 19, 2011 at 03:21 AM PDT #

Ideally, the statements used in applications should be closed in finally block. When this is not done, the statement objects are leaked. The statement leak timeout value purely depends on the number of statements used in the application and also the time for which the connection object is held before closing it. A value of 15 seconds or so will be ideal, but again, it needs to increase or decrease depending on the way statement objects are used in the application.

Posted by Shalini on November 01, 2011 at 06:02 PM PDT #

Great page, thank you. But something seems backwards in the “jdbc-connection-pool” setting for #7. Isn't that exactly backwards? Setting the reclaim value to "false" sounds to me like that not going to reclaim those marked as leaked and thus not return connections to the pool. Am I misunderstanding the values for "connection-leak-reclaim"?

"Similarly adding

connection-leak-reclaim=”false”

ensures that the JDBC connection is reclaimed and returned to the pool."

Posted by guest on May 04, 2012 at 08:23 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

profile image
Arun Gupta is a technology enthusiast, a passionate runner, author, and a community guy who works for Oracle Corp.


Java EE 7 Samples

Stay Connected

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today