?
Solved

Thread Safe JDBC Connection Pool

Posted on 2003-02-19
5
Medium Priority
?
399 Views
Last Modified: 2012-08-14
Hi,

I have a JSP Model 2 application, that uses a db connection pooling class. I'm getting sql exceptions when creating a temporary table indicating that the table already exists (MySQL 3.23.51).

Temporary tables are connection specific in mysql, which leads me to think I have a race condition with my code, and that the connection pool class is not threadsafe (i.e. same connection being used, and create temp is immediately followed by another thread calling create temp table).

ConnectionPool.java:
import java.sql.*;
import java.util.*;

public class ConnectionPool
{
      private String name;
      private String URL;
      private int maxConns;
      private int timeOut;

      private int checkedOut;
      private Vector freeConnections = new Vector();

      public ConnectionPool(String name, String URL, int maxConns, int initConns, int timeOut)
      {
            this.name = name;
            this.URL = URL;
            this.maxConns = maxConns;
            this.timeOut = timeOut > 0 ? timeOut : 5;

            initPool(initConns);
            return;
      }

      private void initPool(int initConns)
      {
            for (int i = 0; i < initConns; i++)
            {
                  try {
                        Connection pc = newConnection();
                  freeConnections.addElement(pc);
               } catch (SQLException e) {}
            }
            return;
      }

      public synchronized Connection getConnection() throws SQLException
      {
            Connection conn = getConnection(timeOut * 1000);
            return new ConnectionWrapper(conn, this);
      }

      private synchronized Connection getConnection(long timeout) throws SQLException
      {

            // Get a pooled Connection from the cache or a new one.
            // Wait if all are checked out and the max limit has
            // been reached.
            long startTime = System.currentTimeMillis();
            long remaining = timeout;
            Connection conn = null;
            while ((conn = getPooledConnection()) == null)
            {
                  try {
                        wait(remaining);
                  }
                  catch (InterruptedException e) {}
                  
                  remaining = timeout - (System.currentTimeMillis() - startTime);
                  if (remaining <= 0)
                  {
                  // Timeout has expired
                  throw new SQLException("getConnection() timed-out");
               }
            }

            // Check if the Connection is still OK
            if (!isConnectionOK(conn))
            {
                  // It was bad. Try again with the remaining timeout
                  return getConnection(remaining);
            }
            checkedOut++;
            return conn;
      }

      private boolean isConnectionOK(Connection conn)
      {
            Statement testStmt = null;
            try {
                  if (!conn.isClosed())
                  {
                  // Try to createStatement to see if it's really alive
                        testStmt = conn.createStatement();
                  testStmt.close();
                  }
                  else
                  {
                  return false;
                  }
            } catch (SQLException e) {
                  if (testStmt != null)
                  {
                  try {
                              testStmt.close();
                        } catch (SQLException se) {}
                  }
                  return false;
            }
            return true;
      }

      private Connection getPooledConnection() throws SQLException
      {
            Connection conn = null;
            if (freeConnections.size() > 0)
            {
                  // Pick the first Connection in the Vector
                  // to get round-robin usage
                  conn = (Connection) freeConnections.firstElement();
                  freeConnections.removeElementAt(0);
            }
            else if (maxConns == 0 || checkedOut < maxConns)
            {
                  conn = newConnection();
            }
            return conn;
      }

      private Connection newConnection() throws SQLException
      {
            Connection conn = DriverManager.getConnection(URL);
            if(conn != null)
            {
                  // set auto commit
                  conn.setAutoCommit(true);
            }
            return conn;
      }
      
      private void resetConnection(Connection conn) throws SQLException
      {
            if(conn == null)
                  return;
                  
            // rollback any outstanding un-committed transactions
            if(conn.getAutoCommit() == false)
                  conn.rollback();
            
            // set auto commit
            conn.setAutoCommit(true);
            
            return;
      }

      public synchronized void freeConnection(Connection conn) throws SQLException
      {
            // Put the connection at the end of the Vector
            resetConnection(conn);
            freeConnections.addElement(conn);
            checkedOut--;
            notifyAll();
      }

      public synchronized void release()
      {
            Enumeration allConnections = freeConnections.elements();
            while (allConnections.hasMoreElements())
            {
                  Connection con = (Connection) allConnections.nextElement();
                  try {
                        con.close();
                  } catch (SQLException e) {}
            }
            freeConnections.removeAllElements();
            return;
      }
}

Is this class thread safe? If not, how can it be made threadsafe?
0
Comment
Question by:martinlt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:Mark Roberts
ID: 7982552
Are you sure that each process that checks out a connection is deleting the temporary tables before checking the connection back into the pool?  If not you could get the same result as you are seeing since the checked in connection would still have the temporary tables and error when the next process tries to check it out and create a temp table.

0
 

Author Comment

by:martinlt
ID: 7983649
Yes, sure that the temporary table get's dropped. The code looks like:

try {
   if(connect())
   {
// create temporary tables if needed
      try {
// drop the temporary table              
         execSQL("drop table x ");
      } catch(Exception e) {}
     
      execSQL("create temporary table x");
                   
      ResultSet rs =     execSQL(selectStmt);
      if(rs != null)
      {
         while(rs.next())
      {
// do resultset stuff
// ...
          }
      }
   }
}
finally {
   try {
      // drop the temporary table
      execSQL("drop table x");
    } catch(Exception e) {}
   
    close();
}
0
 

Author Comment

by:martinlt
ID: 7984028
Yes, sure that the temporary table get's dropped. The code looks like:

try {
   if(connect())
   {
// create temporary tables if needed
      try {
// drop the temporary table              
         execSQL("drop table x ");
      } catch(Exception e) {}
     
      execSQL("create temporary table x");
                   
      ResultSet rs =     execSQL(selectStmt);
      if(rs != null)
      {
         while(rs.next())
      {
// do resultset stuff
// ...
          }
      }
   }
}
finally {
   try {
      // drop the temporary table
      execSQL("drop table x");
    } catch(Exception e) {}
   
    close();
}
0
 
LVL 2

Accepted Solution

by:
Mark Roberts earned 225 total points
ID: 7989640
Your connection pool looks thread safe.  The only thing I can see is if your "drop table x" would fail for any reason in your finally or your initial try you would have a problem with the create.  You may want to try catching the examining the exception from the drop and if it indicates the table doesn't exist ignore the exception and if anything else comes back handle it as a true exception since it wasn't able to drop the table anything else is going to be a problem from there on with that connection.
0
 

Author Comment

by:martinlt
ID: 7993104
Thanks for the advice, I'll do that - it could certainly be the problem.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question