Connection Pool

2005-01-08
2014-01-19
1 2 > >> (Page 1 of 2)
  • Jakob Jenkov
    Jakob Jenkov
    2005-01-08

    Hi,

    I just started looking at the connection pool stuff. A connection pool can always be used, so it's never wasted effort.

    But... does anyone understand the purpose of the
    javax.sql.ConnectionPoolDataSource and javax.sql.PooledConnection interfaces? At first I thought it was simply the interfaces to use for pooling connections, but when looking at them I got utterly confused... they seem unnecessary to me. Most likely because I haven't exactly understood what the purpose of these "extra" interfaces are for.

    The simplest way to create a connection pool seems to me to be to stick x connections in a blocking queue inside a regular javax.sql.DataSource implementation. Each time someone calles getConnection() a connection is taken out of the queue, wrapped in a thin connection wrapper, and returned to the client. The thin wrapper redirects the connection.close() call to a give-back-to-queue call instead.  This could easily be achieved without the javax.sql.ConnectionPoolDataSource and the javax.sql.PooledConnection...

    Any thoughts?

     
    • Fred Toussi
      Fred Toussi
      2005-01-08

      The purpose of javax.sql.ConnectionPoolDataSource is that the user is free to use either a pooled DataSource or an upooled one. Note that we have a jdbcDataSource class. You need to write a different class that contains the data structures you plan to use.

      A connection returnd by the ConnectionPoolDataSource factory is a PooleConnection object.

       
      • Fred Toussi
        Fred Toussi
        2005-01-08

        Sorry, this went out prematurely. The user does not necessarily have the choice according to the standard but we can provide the choice by providing two different factory classes.

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-08

      But that mean that the users will create a ConnectionPoolDataSource, and call it's getConnection() method, and obtain a PooledConnection from it? And then afterwards have to call the PooledConnection's getConnection() method to get the "real" connection?

      ConnectionPoolDataSource datasource = getConnectionPoolDataSource();

      PooledConnection pooledConnection = datasource.getConnection();

      Connection connection = pooledConnection.getConnection();

      ...seems a bit un-userfriendly to me... but I probably just missed something...

       
      • Fred Toussi
        Fred Toussi
        2005-01-08

        This doesn't seem to be the intention of the API.

        The connection that is returned is also an instance of Connection, so the user simply uses it.

        The API states:

        "When an application calls the method DataSource.getConnection, it gets back a Connection object. If connection pooling is being done, that Connection object is actually a handle to a PooledConnection object, which is a physical connection."

        The two interfaces are meant to allow middleware such as appservers some control over the process by providing listeners, logs etc.

        My suggestion is to provide both the existing jdbcDataSource and a new factory jdbcPooledDataSource.

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-09

      ok... but just to agree: A PooledConnection does not extend Connection, so you cannot use the pooled connection directly. It has none of the Connection methods on it. Right?

       
      • Fred Toussi
        Fred Toussi
        2005-01-09

        It implements both interfaces. You can use a PooledConnection directly as a Connection.

        import java.sql.Connection;
        import javax.sql.PooledConnection;

        public class jdbcPooledConnection implements Connection, PooledConnction {
        ..........
        }

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-09

      Okay, I get your point.... I'll have a look at it.

       
      • Fred Toussi
        Fred Toussi
        2005-01-09

        A note on implementation:

        When one is desiging a connection pool that handles Connection objects from a third partly driver, one has to implement all the methods of Connection and then call the methods of Connection object obtained from that driver. In this case a PooledConnection always "has a" real Connection and its methods are mostly implemented as calls to the methods of the embedded Connection:

        import java.sql.Connection;
        import javax.sql.PooledConnection;

        public class jdbcPooledConnection implements Connection, PooledConnction {

        Connection realConn;

        ...

        public PreparedStatement prepareStatement(String sql)
            throws SQLException {

            return realConn.prepareStatement(sql);

        }

        But we don't have to do all this stuff. We can isolate the wrapper from the real connection by isolating the SessionProxy object that is embedded in jdbcConnection. With a new factory method for SessionProxy and a constructor for jdbcConnection, we can reuse the class so that:

        import java.sql.Connection;
        import javax.sql.PooledConnection;

        public class jdbcPooledConnection extends jdbcConnection implements Connection, PooledConnction {

        // no redifinition is needed for prepareStatement(), etc.

        }

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-09

      That may cause some problems with the close method. In case of inheritance you would have to override the close() method somewhat like this:

      public void close() throws SQLException{
         if(!isClosed){
             this.queue.enqueue(this);
         }
      }

      But that means that the connection can never really be closed. Well, only if you do a super.close() from somewhere inside the pooled connection subclass, but where would that be?

       
      • Fred Toussi
        Fred Toussi
        2005-01-09

        Yes, this shared method is a problem no matter how we implement things.

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-09

      About having a wrapper class that implements both
      java.sql.Connection and javax.sql.PooledConnection...

      There seems to be a conflict of interest in these two interfaces. Both of them have a close() method for instance, and both of them seem to close the connection. Really close the connection, not just hand it back to the pool.

      When using a pooled connection the Connection.close() method should just give the connection back to the pool, right? But that is not the definition of the PooledConnection.close() method. Since both close() methods have the same signature, only one of the behaviours could be implemented on the class that implements both interfaces. And which would that be?

      Judging from this it seems to me as if a PooledConnection object is indeed supposed to be a instance different from the connection instance returned from it's PooledConnection.getConnection() method.

      Any comments?

       
      • OK.  Lets clarify this a bit.

        From the java docs:

        Connection Pooling

        Connections made via a DataSource object that is implemented to work with a middle tier connection pool manager will participate in connection pooling. This can improve performance dramatically because creating new connections is very expensive. Connection pooling allows a connection to be used and reused, thus cutting down substantially on the number of new connections that need to be created.

        Connection pooling is totally transparent. It is done automatically in the middle tier of a J2EE configuration, so from an application's viewpoint, no change in code is required. An application simply uses the DataSource.getConnection method to get the pooled connection and uses it the same way it uses any Connection object.

        The classes and interfaces used for connection pooling are:

            * ConnectionPoolDataSource
            * PooledConnection
            * ConnectionEvent
            * ConnectionEventListener

        The connection pool manager, a facility in the middle tier of a three-tier architecture, uses these classes and interfaces behind the scenes. When a ConnectionPoolDataSource object is called on to create a PooledConnection object, the connection pool manager will register as a ConnectionEventListener object with the new PooledConnection object. When the connection is closed or there is an error, the connection pool manager (being a listener) gets a notification that includes a ConnectionEvent object.

        //---

        Sooooo.......

        the javax.sql.PooledConnection and javax.sql.ConnectionPoolDataSource interfaces are to be used when there is a middle tier connection pool.  The interfaces state the contract between the middle tier pooling software and a ConnectionPoolDataSource.

        That is, from the client application's perspective, one is obtaining plain Connection objects from a plain data source.

        It is the middle tier's responsibility to wrap the ConnectionPoolDataSource up as a DataSource.

        That is, the getConnection() method of a PooledConnection is responsible for returning a Connection object that does not really close upon invocation of its close() method, but rather broadcasts a ConnectionEvent that notifies any listeners (i.e. the pool manager) allowing them to take the approriate action (e.g. if there is room in the pool, then making the Connection object available again to be checked out...or calling the "real" close method exposed by the PooledConnection object if the pool is full)

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-09

      I just tested an initial version of the connection pool. Not suprisingly my Mr. Persister unit tests runs even faster than when using the thread pooled server. Down to 4.5 secs now. Nice! They were 5.5 secs witht the thread pooled server, and 10 secs with a new thread pr. connection (and no connection pooling)

      This initial version of the connection pool doesn't use the ConnectionPoolDataSource nor the PooledConnection interfaces. It's just a DataSource implementation and Connection wrapper. Works just fine it seems.

       
      • Fred Toussi
        Fred Toussi
        2005-01-09

        That sounds good. Please use a different name for this, so that is can be tested and used separately from jdbcDataSource.

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-09

      I have called it

      org.hsqldb.jdbc.jdbcPoolDataSource

      If that's okay with you guys, I'll upload it to the patch tracker, when I've done some more testing. I'll try to write some unit tests for it, though it's not so easy to write tests for concurrency.

       
      • Fred Toussi
        Fred Toussi
        2005-01-09

        Thanks, please upload it when you're ready.

        There are several issues I can think of that are related to the semantics of close() and commit() and the values contained in connection properties.

        You should ensure the new leased connection does not inherit changes made by the previous tennat to autocommit and all the rest of settings and properties. With some property pairs, you will have to always fetch a new real connection.

        Test units would be a good idea and should proble the above objectives have been met.

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-10

      ... right now I save the connection default settings (auto-commit, isReadOnly etc.) in a ConnectionDefaults object. Whenever a connection is given out that has been used, these defaults are reset on the connection. That is done to avoid receiving a connection that has the same settings as the thread that used it before set on it.

       
      • Fred Toussi
        Fred Toussi
        2005-01-10

        That sounds good.

        If a used connection is not in auto-commit, you should also issue rollback before reusing it and setting it to auto-commit.

         
        • Absolutely essentail Fred.

          However, I think the timing here should be _when_ the conection is check back into the pool (when the previous client _closes_ the a checked out connection), rather than later, just before checking out the connection again.

          Otherwise, rollback info could sit around for a while, wasting memory until the connection is checked out again.

           
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-12

      Now the this.connectino.rollback is added to the ConnectionWrapper class. This is done at close() time, like you said. What if the rollback() throws an SQLException? Should that simply be forwarded to the client calling close() ??

      The resetting of defaults is done at connection hand-out time. Should resetting defaults also be done at hand-in time instead?

       
      • Fred Toussi
        Fred Toussi
        2005-01-12

        I'm not sure about either. You can find out more about the first issue if you take a look at the code in Session that is called when a connection is closed. On the second issue, I should be able to tell when I see the code.

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-12

      What if the rollback fails?

      Should the connection then be closed, and a new one created and inserted in the pool?

      Should the client get the SQLException from the failed roolback?

      Should the data source log the failed roll back?

       
      • Typically, there are options provided by the pool implementor to define policies regarding such things.

        Take a look at some of the existing connection pool implementations to get an idea.

        Best places to look, initially, might be the hibernate, apache, spring, (jboss?) etc. sites, as they all appear to offer preintegration with several connection pool implementations, such as c3po, dbcp, etc.

        For instance (I've been working with this recently, so it comes to mind easily) apache's dbcp, the default connection pool implementation in Tomcat, allows to set policy regarding what happens when exceptions occur on a connection in the pool, what happens regarding potentially "abandoned" connections, how/if  exceptions are logged, what SQL to use to perform "keep alive" DBMS "pings" (so that timeout settings on the server side or network partitions do not result in accidentlly handing out dead connections from the pool), etc.

         
    • Jakob Jenkov
      Jakob Jenkov
      2005-01-12

      allright, I'll upload the code to the patch tracker so you guys can have a look at it.

       
1 2 > >> (Page 1 of 2)