Re: JDBC techy pooling questions

In article <mcmurtri-6386E0.00225706052000_at_news.sonic.net>,   Kevin McMurtrie <mcmurtri_at_sonic.net> wrote:
> I have some questions about pooling JDBC connections with the Oracle
> thin driver. I'm fairly new to JDBC and I'm not familiar with its
> performance details

My experience has been with the Sybase Type 4 JDBC driver (jConnect), so perhaps not all my recommendations apply to the Oracle Thin driver, but I think they are pretty generic.

> I have a wrapper class in my pooling that keeps a Connection instance
> and a Statement instance together. I am calling
> Connection.createStatement() once then reusing the Statement.
> Is it OK to keep reusing one instance of a Statement?

I believe this is reasonable, as long as the Connection and Statement are not shared across threads simultaneously. I would note, however, that a Statement is a relatively lightweight object (PreparedStatement and CallableStatement objects can involve a database round-trip though).

> I am disabling auto-commit as recommended by Oracle. Some queries
> make changes and perform a commit or rollback, but it's possible to
> execute several thousand read-only queries on various tables without
> commiting, closing a statement, or closing a connection. Docs imply
> that auto-commit is for all statements. With auto-commit off, should
> I call commit() after a batch of read-only queries?

Yes. There will be shared locks held by all your SELECT statements preventing INSERT, UPDATE, and DELETE's.

> Oracle docs warn that failing to close a connection can cause a
> permanent resource leak. Is this true in practice? The JDBC docs
> specify that a Connection will be garbage collected. My code can
> loose its reference to a Connection instance if an unexpected
> exceptions thrown, such as a RuntimeException or Error.

This can be true, depending on how they coded the driver. You should use finally { } blocks to ensure the Connection instance is returned to your pool and that all Statement and ResultSet objects are close()'d.

    ResultSet rs = null;
    Statement st = null;
    Connection dbc = null;
    boolean success = false;
    try {

        dbc = ConnectionPool.checkout();
        st = dbc.createStatement();
        rs = st.executeQuery("SELECT ...");
        while( rs.next() ) {
            // process rows
        }
        // If we got this far, we can commit and mark success flag.
        dbc.commit();
        success = true;

        if(rs != null) try {rs.close();} catch(SQLException ig1) { }
        if(st != null) try {st.close();} catch(SQLException ig2) { }
        if(dbc!= null) {
            if( !success ) dbc.rollback();
            ConnectionPool.checkin(dbc);
        }

The finally block will execute regardless of whether an exception is thrown or not. This is the *only* mechanism that is guaranteed to prevent resource leaks. Also note that this sort of mechanism is *absolutely required* for the jdbc-odbc driver. Note the use of a flag to ensure non-committed transactions are rolled back on failure...

> What part of JDBC holds a cursor? Is is the Connection, the
> Statement, or just the execution of a query? ...

It should either be the Statement or ResultSet, so the mechanism shown above should be sufficient to make sure you are not leaking cursors.

> I have noticed that it's possible for a query to hang forever on
> Socket.read(). Can I set the Oracle driver to use a timeout? I
> cache and share data so a stuck query will block any thread asking
> for the same stuck item.

There is no JDBC-standard mechanism for asynchronous or timed queries (at least not in the spec's that I've read). You need to implement some sort of Thread and wait mechanism to do timed queries. This could be an interesting project...

                   -=- D. J.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat May 06 2000 - 00:00:00 CEST