Monday Jun 06, 2011

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.
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