Re: JDBC techy pooling questions

Thanks for the help. The help regarding the auto-commit is great. I've been seeing some stuck queries and I wasn't sure whether it was an Oracle server problem (dev server is missing some OS patches) or my error. I'll add code to commit after a batch of queries completes.

The code I'm using for connections generally looks like:

//Get a pooled Statement and Connection for a database. DBConnection dbCon= DBPool.getStatement ("MyDatabaseHandle");

Statement stm= dbCon.getStatement ();
try
{

   //Query.
   //Close ResultSet.
   //If updating, safety-check row count then
   //commit or rollback.

   //I'm not expecting any SQLException so I don't    //want to pool a connection that may have gone bad.    dbCon.close ();
   throw err; //Continue with error
}
dbCon.returnToPool (); //OK, re-pool Statement and Connection.

As you can see, there's a hole in my try/catch. A query could cause an OutOfMemoryError and the connection is lost forever. I will add a finalize function to my DBConnection class to make sure the Connection gets closed. So far that seems like the easiest solution. This DBConnection class has no references to it once it's pulled from the pool so it will get GC'ed if lost. I'm hesitant to use a finally block because I don't want to poison my pool with a Connection that has gone bad because due to network problems or low memory.

I'll add commit() to my code.

I've heard conflicting reports about reusing the Statement class. I can easily switch Statement reusing on and off. I guess I'll have to see what happens when I get to the stress testing stage.

I'll keep in mind that I may need to make a time-bomb class to prevent a query from getting stuck. Calling commit() may fix all of my problems but even 1 stuck query in 100000 would make the server unusable. It would be nice if I could tell Oracle to set a read timeout on its socket.

Thanks again,

   Kevin

In article <8f244b$j3r$1_at_nnrp1.deja.com>, dhagberg_at_millibits.com wrote:

>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;
> }
> finally {
> 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 Sun May 07 2000 - 00:00:00 CEST