Closing Statement object prior to committing

[mo sayed]

Are there any problems with closing a statement obj prior to calling the
commit() method?

[Roger Chung-Wee]

I believe that this will be fine as closing a Statement makes it available for garbage collection and releases any JDBC resources which had been acquired. This is why the closure of JDBC objects is recommended as soon as possible.

The Connection does not rely on a Statement for ending a transaction. The Connection encapsulates database connection functionality, the Statement encapsulates SQL queries and execution.

If you think of a JDBC transaction as a set of one or more Statements that executes as a single unit, then you can have something like this.

get connection
connection.setAutoCommit(false) // start of transaction
create and execute Statement
statement.close()
connection.commit() // end of transaction
connection.close()

[Reid M. Pinchback]

FYI, if you are using a mature connection pool (e.g. in WebLogic or JBoss) and prepared statements, the whole situation might be moot. Servers can maintain a cache of the prepared statements, so "close" just closes a wrapper object. Basically you are just telling the server you are done with using the cached object.

The most important objects to close when you are done is ResultSet and some of the JDBC metadata objects. Those are associated with database cursors. If you invoke multiple queries before closing a connection but fail to close cursors between queries, you can run out of cursors pretty rapidly. If you are just doing a single query and then closing the connection, there is no need to explicitly close statements and result sets because the connection close automatically closes all open JDBC objects associated with the connection.

[Roger Chung-Wee]

If you are just doing a single query and then closing the connection, there is no need to explicitly close statements and result sets because the connection close automatically closes all open JDBC objects associated with the connection.

This looks to me like something which is vendor-dependent, so I won't rely on it. You mention WebLogic Server: as far as I know, you need to close all the JDBC objects separately. Here's an example from the BEA web site.

    try {
        ctx = new InitialContext(ht);
        javax.sql.DataSource ds       
          = (javax.sql.DataSource) ctx.lookup ("myDataSource");
        java.sql.Connection conn = ds.getConnection();
        
        // You can now use the conn object to create
        //  a Statement object to execute
        //  SQL statements and process result sets:
        
        Statement stmt = conn.createStatement();
        stmt.execute("select * from someTable");
        ResultSet rs = stmt.getResultSet();
        
        // Do not forget to close the statement and connection objects
        //  when you are finished: 
    }  
        catch (Exception e) {
            // a failure occurred
            log message;
        }
    }
    finally {
    try {
        ctx.close();
        } catch (Exception e) {
            log message; }
        try {
            if (rs != null)
                rs.close();
        } catch (Exception e) {
            log message;
        }  
        try {
            if (stmt != null)
                stmt.close();
        } catch (Exception e) {
                log message; }
        try {
            if (conn != null)
                conn.close();
            } catch (Exception e) {
                log message;
        }
    }

[Reid M. Pinchback]

Originally posted by Roger Chung-Wee:
This looks to me like something which is vendor-dependent, so I won't rely on it.

I invite you to read the java.sql javadoc. It would be interesting to know of a single database vendor or driver or connection pool implementation in use for which you know, from testing or documentation, that it does not behave this way. Having tested for this, poured through connection pool and driver implementations, and used it for many years, I've never ever seen a single situation where closing a connection didn't release all the associated JDBC resources as required by java.sql.Connection.close(). Even the class-level docs for ResultSet make it clear that closing a statement automatically closes the result sets.

This isn't exactly new. You are welcome to have whatever coding habits you want, but habits are just habits, not a source of empirical fact. Habits are a source of confort to us, but don't mean there aren't valid opportunities to learn streamlined ways of achieving the same outcome.

And it doesn't take very many support calls to BEA before you stop being surprised by quality gaps in their information.

[Roger Chung-Wee]

Indeed, closing a Statement does indeed result in closing the ResultSet according to the API documentation. And this is the point. A developer must code to the API, and the API documentation for closing a Connection does not say that a related Statement is also closed. On this basis, a developer must regard the closing of the Statement and Connection objects as separate tasks to be coded.

[Reid M. Pinchback]

Wow, how amazingly interpretive. Let's cut to the chase. If you are correct, by all means provide the evidence. Your opinion is so definite there should be a little bit of data somewhere that could substantiate it. Tell me the one implementation on the face of planet Earth released in the last 5 years that you know does not cause a close on the statement to be called when you call a close on the connection. Just one. Driver or connection pool, in container or outside of container, any one is ok by me. Provide facts. Facts are good. It would be an interesting technical oddity to know about, since it would really obfuscate java.sql javadoc like:

Statement.close():

Releases this Statement object's database and JDBC
resources immediately instead of waiting for this
to happen when it is automatically closed

So, pray tell... what could possibly "automatically close" a statement if it isn't the connection. You can't close a statement via its result set. You can't close it via metadata. GC doesn't wander around seeking out JDBC objects to close on a whim. That leaves the connection. Oh, and how come a ResultSet is ok to be called a JDBC resource of a Statement, but a Statement can't be termed a JDBC resource of a Connection? Hmmm... that answer should be very creatively entertaining.

What you will invariably see, particularly for pools, is something like an AWT-style event notification being sent to all registered listeners (JDBC proxies or native driver proxies) that need to close. It is the only decent way to avoid memory and cursor leaks without really gnarly code interdependencies.

[Roger Chung-Wee]

Tell me the one implementation on the face of planet Earth released in the last 5 years that you know does not cause a close on the statement to be called when you call a close on the connection. Just one. Driver or connection pool, in container or outside of container, any one is ok by me.

This is easy to explain. A server such as WebLogic Server provides a logical Connection from the pool on a dataSource.getConnection() request. What is actually returned is a Connection wrapper object whose close() method, when invoked, returns the Connection to the pool. In other words, the Connection is not closed.

Since a Statement corresponds to a DBMS cursor, opening too many Statements can lead to something like a "too many opened cursors" exception. This may well happen because the Statements have not been closed in the code, causing too many Statement objects waiting to be garbage collected (when the close will be implicitly done).

[Reid M. Pinchback]

*BEEP* thank you for playing. Please try again!

Connection closes on a pool still close the associated JDBC objects. The one and only exception to that is caching of internal prepared statement structures because those have their own cache; they get closed when the server does a hard close on the physical connection or undeploys the pool. Try closing a logical connection from a pool and using your logical result set to get another row of data, and see how far you get.

I think if you do some testing, you'll find that statements have nothing to do with database cursors, not even for prepared statements or SQL batching. You should find that you can create statements until the JVM runs out of memory. Results sets and metadata are the only things in JDBC that use cursors. If this where not the case, you wouldn't have to bother closing result sets between use; *that* is the cursor leak, not the statements themselves. Statements are just strings and communications proxies.


[ January 26, 2006: Message edited by: Reid M. Pinchback ]
[ January 26, 2006: Message edited by: Reid M. Pinchback ]

[Roger Chung-Wee]

Connection closes on a pool still close the associated JDBC objects.

Yes, I conceed that you are correct. I've checked the JDBC API and on this page BEA has this to say.

Although Statements and ResultSets should be closed when a Connection is closed, per JDBC specification, it's a good practice to explicitly close Statements and ResultSets right after you finish using them if you create multiple Statements on one Connection object. If you don't explicitly close Statements and ResultSets right away, cursors may accumulate and exceed the maximum number allowed in your DB before the Connection is closed.

So, although I was mistaken in thinking that closing a Connection does not close the associated Statement, the document confirms an important point that I was making, ie that JDBC objects like Statements should be explicitly closed.

I think if you do some testing, you'll find that statements have nothing to do with database cursors, not even for prepared statements or SQL batching.

This is not necessarily true. According to the above link:

To increase performance, WebLogic Server provides a feature to cache prepared statements and callable statements when you use connection pool. When WebLogic Server caches a prepared or callable statement, in many cases, the DBMS will maintain a cursor for each open statement. Hence, statement caching could be a source of the "maximum open cursors exceeded" problem. The Statement Cache Size attribute determines the total number of prepared and callable statements to cache for each connection in each instance of the connection pool. If you cache too many statements, you may exceed the limit of open cursors on your database server.

Here's another quote from Oracle9i JDBC Developer�s Guide and Reference, Release 2 (9.2):

Important: The cursor associated with a REF CURSOR is closed
whenever the statement object that produced the REF CURSOR is
closed.

Unlike in past releases, the cursor associated with a REF CURSOR
is not closed when the result set object in which the REF CURSOR
was materialized is closed.

[Reid M. Pinchback]

Ah, now that is interesting. I concede that point too. I hadn't seen the situation where ref cursors would be associated with the statement instead of the result set. Bizarre that such is the case, since strictly speaking a statement can generate multiple results; that suggests that a statement is the tracking mechanism for multiple ref cursors. Ick!

[Wei Dai]

>statement.close()
>connection.commit() // end of transaction
>connection.close()
statement.close() should have not infection on transaction, but connection.close() will call connection.rollback();

[Maximilian Xavier Stocker]

Originally posted by Wei Dai:
statement.close() should have not infection on transaction, but connection.close() will call connection.rollback();

While I agree in theory I feel it is neccessary to point out as I have before that this is technically wrong.

The JDBC specifications nor the API do NOT specify the behaviour for transactions when a connection is closed without a specific commit or rollback.

This is from the API but the principle in the specification is the same

If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback.

Note that no mention is made of what happens when a connection is closed without either a commit or rollback.

At any rate I believe that transactions inside a connection that is closed (either because you actually closed it or the connection is lost (bad network, time out etc)) should be rolled back. But one should be aware that nowhere is this behaviour garaunteed.

I cannot speak too much as to why this behaviour is not specified other than I would speculate that one or more databases may well not rollback in the case of close and so it was decided not to include it in the specification. I would guess that in those instances the transaction may be just left abandoned resulting in orphaned locks on tables and or rows used by the transactions in question.

The bottom line is that you MUST test the behaviour of your database to be sure it does what you expect because while any sensible person would think that a rollback is the only appropriate behaviour according to the actual JDBC spec a database could do a commit, a rollback or anything inbetween and such behaviour would not be, by specification, incorrect.

[Maximilian Xavier Stocker]

Originally posted by Roger Chung-Wee:
Indeed, closing a Statement does indeed result in closing the ResultSet according to the API documentation. And this is the point. A developer must code to the API, and the API documentation for closing a Connection does not say that a related Statement is also closed. On this basis, a developer must regard the closing of the Statement and Connection objects as separate tasks to be coded.

This advice is 100% correct.

If you do not explictly close your statements and result sets but only close your connection then you are dependent upon the implementation of the driver and database to do this for you.

If you fail to close statements you may well leave database resources tied up for your connection even after you close it. There are several drivers for several databases that use internal statement caching and use of stored procedures to improve performance and stability and if you fail to take appropriate action (re: closing statements specifically) you can cause your database to leak resources.

[Wei Dai]

Thank Maximilian. Yeah. When a connection is closed or corrupted client/server connection, database has to decide whether to rollback/commit the pending transaction. Maybe most of engines will rollback that transaction. But maybe few engines won't rollback READ UNCOMMITTED transaction, but rollback READ COMMITTED, REPEATABLE READ, and SERIALIZABLE transaction.

[Maximilian Xavier Stocker]

Note that no mention is made of what happens when a connection is closed without either a commit or rollback

--------------------------------------------------------------------------------


The JDBC 3.0 spec has this to say about transactions (when auto-commit mode has been disabled).

quote:
--------------------------------------------------------------------------------

When auto-commit is disabled, each transaction must be explicitly commited by calling the Connection method commit or else explicitly rolled back by calling the Connection method rollback.

--------------------------------------------------------------------------------

And?? This is exactly what I just said. There is nothing in there describing what happens without a specific close or commit.

We are talking about what happens to a transaction on close. Wei said it should be rolled back and he is right in that is what SHOULD happen. But I am just pointing out that you should test because what you think might happen and what the specification says should happen are not the same in this case. The behaviour is unspecified.

The quote you posted makes that exact point. Note it does not say if commit, if rollback, else... it says if commit if else rollback. Nothing about what happens on close without either of those.

Furthermore, should the Connection not be closed, it will be if it is later garbage collected.

I am unsure who are addressing and what point you are trying to make there. Relying on garbage collection to clean up your JDBC resources is a patently bad idea. You must always consider in JDBC that it is not only resources for your application that are bing used but resources on another application (the database server) as well.

If you use bad practices in JDBC you may well escape problems in your application but that does not mean that you are not leaking resources on the server.

[Maximilian Xavier Stocker]

Oi what happened? There was another post here and now it's gone.

[Roger Chung-Wee]

At any rate I believe that transactions inside a connection that is closed (either because you actually closed it or the connection is lost (bad network, time out etc)) should be rolled back. But one should be aware that nowhere is this behaviour garaunteed.

I cannot speak too much as to why this behaviour is not specified other than I would speculate that one or more databases may well not rollback in the case of close and so it was decided not to include it in the specification. I would guess that in those instances the transaction may be just left abandoned resulting in orphaned locks on tables and or rows used by the transactions in question.

The situation is clearer for application servers which are managing JTA transactions. I am most familiar with WebLogic Server, and I'm sure that most of the other similar servers behave in the same way. What happens is that there is a transaction timeout (say 30 seconds), and the server will rollback any transaction which exceeds this timeout period and will also return the Connection to the pool.