Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Thread Safe JDBC Connection Pool

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
martinlt
Asked:
martinlt
  • 3
  • 2
1 Solution
 
Mark RobertsIncident ManagerCommented:
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
 
martinltAuthor Commented:
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
 
martinltAuthor Commented:
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
 
Mark RobertsIncident ManagerCommented:
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
 
martinltAuthor Commented:
Thanks for the advice, I'll do that - it could certainly be the problem.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now