Обсуждение: executeQuery() throws "Statement has been closed"
Hi everyone, sometimes my application (jdbc-1101, hibernate, desktop application) reports the following "Statement has been closed" error: Caused by: org.postgresql.util.PSQLException: Die Anweisung wurde geschlossen. at org.postgresql.jdbc2.AbstractJdbc2Statement.checkClosed(AbstractJdbc2Statement.java:2631) at org.postgresql.jdbc2.AbstractJdbc2Statement.getMaxRows(AbstractJdbc2Statement.java:659) at org.postgresql.jdbc4.Jdbc4Statement.createResultSet(Jdbc4Statement.java:34) at org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler.handleResultRows(AbstractJdbc2Statement.java:219) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1839) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80) ... 19 more When looking at the code of getMaxRows() (https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2Statement.java line 665): public int getMaxRows() throws SQLException { checkClosed(); return maxrows; } ... I'm wondering why there is even checkClosed() called before returning the value of a field? Could that check be safely removed? It may be the case that there are concurrent calls from multiple threads to the same Connection object, but shouldn't that be thread-safe? (Un)fortunately the exception is logged very seldomly, so there has not yet a pattern emerged when they happen. Thanks for your comments, best regards -hannes
Well if another thread has closed the connection which I suspect is happening here, then the results of checkClosed is correct.
I suspect you may have a concurrency problem in your code?
On 31 July 2014 04:56, Hannes Erven <hannes@erven.at> wrote:
Hi everyone,
sometimes my application (jdbc-1101, hibernate, desktop application) reports the following "Statement has been closed" error:
Caused by: org.postgresql.util.PSQLException: Die Anweisung wurde geschlossen.
at org.postgresql.jdbc2.AbstractJdbc2Statement.checkClosed(AbstractJdbc2Statement.java:2631)
at org.postgresql.jdbc2.AbstractJdbc2Statement.getMaxRows(AbstractJdbc2Statement.java:659)
at org.postgresql.jdbc4.Jdbc4Statement.createResultSet(Jdbc4Statement.java:34)
at org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler.handleResultRows(AbstractJdbc2Statement.java:219)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1839)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
... 19 more
When looking at the code of getMaxRows() (https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2Statement.java line 665):
public int getMaxRows() throws SQLException
{
checkClosed();
return maxrows;
}
... I'm wondering why there is even checkClosed() called before returning the value of a field?
Could that check be safely removed?
It may be the case that there are concurrent calls from multiple threads to the same Connection object, but shouldn't that be thread-safe?
(Un)fortunately the exception is logged very seldomly, so there has not yet a pattern emerged when they happen.
Thanks for your comments,
best regards
-hannes
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Hi Dave, > Well if another thread has closed the connection which I suspect is > happening here, then the results of checkClosed is correct. The JDBC connection is only closed when the application exits. Since the message is "statement closed" (as opposed to "connection closed"), I quite sure thats not happening here. It may well be a concurrency issue, since we are sharing one JDBC connections among multiple Hibernate-Sessions. thanks again, -hannes
Sorry I mis-spoke.
Sharing one connection across multiple hibernate sessions seems fraught with danger ?
On 31 July 2014 10:21, Hannes Erven <hannes@erven.at> wrote:
Hi Dave,The JDBC connection is only closed when the application exits. Since the message is "statement closed" (as opposed to "connection closed"), I quite sure thats not happening here.
> Well if another thread has closed the connection which I suspect ishappening here, then the results of checkClosed is correct.
It may well be a concurrency issue, since we are sharing one JDBC connections among multiple Hibernate-Sessions.
thanks again,
-hannes
Hannes Erven <hannes@erven.at> wrote: > sometimes my application (jdbc-1101, hibernate, desktop application) > reports the following "Statement has been closed" error: > ... I'm wondering why there is even checkClosed() called before > returning the value of a field? > Could that check be safely removed? Not according to the API documentation: http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getMaxRows%28%29 | Throws: | SQLException - if a database access error occurs or this method is called on a closed Statement Besides, if a Statement has been closed, why would you expect it to still be usable? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Dave, > Sharing one connection across multiple hibernate sessions seems > fraught with danger ? sorry, I should have explained better. This is a read-only ("SELECT") workload, so there are no commits etc. interfering between threads. This is a session-per-view pattern so the indiviual sessions have minimal size and may be closed and garbage collected independently. Access to the backend is again pooled through a pgbouncer statement pool (which might be another place to look at). According to http://jdbc.postgresql.org/documentation/81/thread.html , JDBC connections are threadsafe and can be used by multiple threads concurrently. That page also reads, "If a thread attempts to use the connection while another one is using it, it will wait until the other thread has finished its current operation. If the operation is a regular SQL statement, then the operation consists of sending the statement and retrieving any ResultSet (in full)." As I understand it, even if one thread closes the connection, a call to executeQuery() should either fail early or complete. It appears to me that is is not the case in these rare instances where the exception is thrown only when the results have already been processed successfully. As Kevin pointed out (thanks!), getMaxRows() is required by the spec to check whether the connection is still open. So let me rephrase the question: why is AbstractJdbc4Statement.createResultSet() calling getMaxResult() and friends [which in turn check for openness] instead of directly using the already prepared field values? In any case, I'll see if I can come up with a test case to demonstrate. Thanks again, best regards -hannes
Hannes Erven <hannes@erven.at> wrote: > As Kevin pointed out (thanks!), getMaxRows() is required by the > spec to check whether the connection is still open. It's more strict than that -- it is required to throw an exception if the *Statement* has been closed. Of course, closing a Connection will automatically close all Statement objects associated with that connection, but the Statement object can, and often is, closed before the Connection. So, something is closing the Statement (directly or indirectly) and then there is an attempt to use it. You need to figure out how that happens. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Candidly I think it's time to review this statement:
According to http://jdbc.postgresql.org/documentation/81/thread.html , JDBC connections are threadsafe and can be used by multiple threads concurrently.
That page also reads,
"If a thread attempts to use the connection while another one is using it, it will wait until the other thread has finished its current operation. If the operation is a regular SQL statement, then the operation consists of sending the statement and retrieving any ResultSet (in full)."
According to http://jdbc.postgresql.org/documentation/81/thread.html , JDBC connections are threadsafe and can be used by multiple threads concurrently.
That page also reads,
"If a thread attempts to use the connection while another one is using it, it will wait until the other thread has finished its current operation. If the operation is a regular SQL statement, then the operation consists of sending the statement and retrieving any ResultSet (in full)."
There are some things which will not be concurrent safe. For instance if you change the transaction isolation in one thread, it will change it for all the threads.
On 31 July 2014 15:33, Hannes Erven <hannes@erven.at> wrote:
Dave,sorry, I should have explained better. This is a read-only ("SELECT") workload, so there are no commits etc. interfering between threads. This is a session-per-view pattern so the indiviual sessions have minimal size and may be closed and garbage collected independently.
> Sharing one connection across multiple hibernate sessions seems
> fraught with danger ?
Access to the backend is again pooled through a pgbouncer statement pool (which might be another place to look at).
According to http://jdbc.postgresql.org/documentation/81/thread.html , JDBC connections are threadsafe and can be used by multiple threads concurrently.
That page also reads,
"If a thread attempts to use the connection while another one is using it, it will wait until the other thread has finished its current operation. If the operation is a regular SQL statement, then the operation consists of sending the statement and retrieving any ResultSet (in full)."
As I understand it, even if one thread closes the connection, a call to executeQuery() should either fail early or complete. It appears to me that is is not the case in these rare instances where the exception is thrown only when the results have already been processed successfully.
As Kevin pointed out (thanks!), getMaxRows() is required by the spec to check whether the connection is still open.
So let me rephrase the question: why is AbstractJdbc4Statement.createResultSet() calling getMaxResult() and friends [which in turn check for openness] instead of directly using the already prepared field values?
In any case, I'll see if I can come up with a test case to demonstrate.
Thanks again,
best regards
-hannes
Hi, > http://jdbc.postgresql.org/documentation/81/thread.html > > "If a thread attempts to use the connection while another one is using > it, it will wait until the other thread has finished its current > operation. If the operation is a regular SQL statement, then the > operation consists of sending the statement and retrieving any ResultSet > (in full)." Consider this test case: one thread calls executeQuery("SELECT ..."), and while that query is still executing, a second thread calls close() on the statement. Given the documentation above, I would expect the regular SQL statement to complete, the statement then to be closed, and no exception thrown. I'll attach a sample code for this scenario. With the current git driver, this fails at getMaxRows(). When you change Jdbc4Statement.createResultset() so it uses this.maxrows instead of getMaxRows() and hence skip checkClosed, then that test will pass. Patch also attached, although I have no idea whether this really is the right thing to do. Still, I will also check my code. I think this happens when a user closes a view while the data inside is (re)loading, so I'll probably have the error handling discard that exception in this case instead of ringing the alarm bell... Thanks for your comments, David and Kevin! Best regards, -hannes
Вложения
While it may be possible to share connections across threads, I am pretty sure you cannot share Statements
On 1 August 2014 04:43, Hannes Erven <hannes@erven.at> wrote:
Hi,
> http://jdbc.postgresql.org/documentation/81/thread.htmlConsider this test case: one thread calls executeQuery("SELECT ..."), and while that query is still executing, a second thread calls close() on the statement.
>"If a thread attempts to use the connection while another one is using
it, it will wait until the other thread has finished its current
operation. If the operation is a regular SQL statement, then the
operation consists of sending the statement and retrieving any ResultSet
(in full)."
Given the documentation above, I would expect the regular SQL statement to complete, the statement then to be closed, and no exception thrown.
I'll attach a sample code for this scenario. With the current git driver, this fails at getMaxRows().
When you change Jdbc4Statement.createResultset() so it uses this.maxrows instead of getMaxRows() and hence skip checkClosed, then that test will pass. Patch also attached, although I have no idea whether this really is the right thing to do.
Still, I will also check my code. I think this happens when a user closes a view while the data inside is (re)loading, so I'll probably have the error handling discard that exception in this case instead of ringing the alarm bell...
Thanks for your comments, David and Kevin!
Best regards,
-hannes