https://issues.jboss.org/browse/AS7-1461 reflects a problem reported by two users. One user reported, that in a transaction that included multiple different entities being created, that Hibernate is trying to use a closed prepared statement. My question at this point is, what would happen if the AS7 prepared statement cache were to close a prepared statement during a transaction. For example, assuming that the prepared statement cache was configured to a very low limit, like two. After two unique sql statements are in the prepared statement cache, when a third unique sql statement is created, one of the two cached statements will be closed. Could Hibernate be holding onto one of these cached prepared statements (for reuse in the transaction) that are closed? Example execution order from user report: 1. insert into ASSEMBLIES (NAME, A_ID) values (?, ?) 2. other sql statements are executed. 3. insert into ASSEMBLIES (NAME, A_ID) values (?, ?) 4. other sql statements are executed. 5. At this point, I believe that "insert into ASSEMBLIES (NAME, A_ID) values (?, ?)" could be aged out of the AS7 prepared statement cache. 6. "insert into ASSEMBLIES (NAME, A_ID) values (?, ?)" fails to execute because of error "com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed" For my local testing, I'm using H2 and get error "org.h2.jdbc.JdbcSQLException: The object is already closed [90007-145]" Does/should Hibernate check the prepared statement to see if its have been closed before its reused in a transaction? Or should users disable the AS7 prepared statement cache (current workaround)? If Hibernate is currently checking if the prepared statement is closed before reusing it, I must of missed that code in my inspection. At this point, I'm still trying to find the root cause and answer the above question as well. Scott _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

From: "Scott Marlow" <smarlow(a)redhat.com&gt; To: hibernate-dev(a)lists.jboss.org, "Jeremy Whiting" <jwhiting(a)redhat.com&gt; Sent: Tuesday, September 20, 2011 7:22:57 AM Subject: Re: [hibernate-dev] Using AS7 prepared Statement caching and Hibernate 4.0.0.Beta4 + 4.0.0.Beta5 This went away for a while but Jeremy is now recreating this (http://pastie.org/2563226). Can anyone tell us how long Hibernate 4.0.0 can hold onto a PreparedStatement? And whether its possible that Hibernate may be holding onto a PreparedStatement for reuse after Hibernate has called the PreparedStatement.close method? This problem goes away, if the AS7 PreparedStatement cache is disabled but for best performance, it would be nice to support using Hibernate with a PreparedStatement cache. Its possible that the bug is also in the AS7 PreparedStatement cache and that we just haven't found it yet. What do you think? Thanks, Scott On 08/04/2011 03:41 PM, Scott Marlow wrote: > Let me add, that in theory, the AS7 prepared statement cache, > should not > close the prepared statements while they are in use by Hibernate. > Currently in AS7, if the cache fills up, the statement should be > removed > from the pool but not closed. > > I still would like to understand why the prepared statement is > getting > closed while its still being used. > > On 08/04/2011 10:00 AM, Scott Marlow wrote: >> https://issues.jboss.org/browse/AS7-1461 reflects a problem >> reported by >> two users. One user reported, that in a transaction that included >> multiple different entities being created, that Hibernate is >> trying to >> use a closed prepared statement. >> >> My question at this point is, what would happen if the AS7 >> prepared >> statement cache were to close a prepared statement during a >> transaction. >> >> For example, assuming that the prepared statement cache was >> configured >> to a very low limit, like two. After two unique sql statements >> are in >> the prepared statement cache, when a third unique sql statement is >> created, one of the two cached statements will be closed. Could >> Hibernate be holding onto one of these cached prepared statements >> (for >> reuse in the transaction) that are closed? >> >> Example execution order from user report: >> >> 1. insert into ASSEMBLIES (NAME, A_ID) values (?, ?) >> >> 2. other sql statements are executed. >> >> 3. insert into ASSEMBLIES (NAME, A_ID) values (?, ?) >> >> 4. other sql statements are executed. >> >> 5. At this point, I believe that "insert into ASSEMBLIES (NAME, >> A_ID) >> values (?, ?)" could be aged out of the AS7 prepared statement >> cache. >> >> 6. "insert into ASSEMBLIES (NAME, A_ID) values (?, ?)" fails to >> execute >> because of error >> "com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: >> No operations allowed after statement closed" >> >> For my local testing, I'm using H2 and get error >> "org.h2.jdbc.JdbcSQLException: The object is already closed >> [90007-145]" >> >> Does/should Hibernate check the prepared statement to see if its >> have >> been closed before its reused in a transaction? Or should users >> disable >> the AS7 prepared statement cache (current workaround)? >> >> If Hibernate is currently checking if the prepared statement is >> closed >> before reusing it, I must of missed that code in my inspection. >> >> At this point, I'm still trying to find the root cause and answer >> the >> above question as well. >> >> Scott >> _______________________________________________ >> hibernate-dev mailing list >> hibernate-dev(a)lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev

From: "Steve Ebersole" <steve(a)hibernate.org&gt; To: hibernate-dev(a)lists.jboss.org Sent: Wednesday, September 21, 2011 10:20:50 AM Subject: Re: [hibernate-dev] Using AS7 prepared Statement caching and Hibernate 4.0.0.Beta4 + 4.0.0.Beta5 On 09/20/2011 09:22 AM, Scott Marlow wrote: > This went away for a while but Jeremy is now recreating this > (http://pastie.org/2563226). > > Can anyone tell us how long Hibernate 4.0.0 can hold onto a > PreparedStatement? And whether its possible that Hibernate may be > holding onto a PreparedStatement for reuse after Hibernate has > called > the PreparedStatement.close method? It is possible to get "Hibernate to hold onto a PreparedStatement" indefinitely if the application leverages scrollable result sets and never closes them. The same is potentially true (depending on how the prepared statement and result set connection is implemented) using Hibernate's notion of "iterated query results" which hold open a result set until closed. Another case where you can see "Hibernate to hold onto a PreparedStatement" is in the case where its JDBC batching capability is enabled. There it holds onto a prepared statement in order to apply the batches Outside of those 2 cases, unless something is seriously amiss in the Hibernate code, we should generally be using a prepared statement, extracting any results and then immediately closing them. > > This problem goes away, if the AS7 PreparedStatement cache is > disabled > but for best performance, it would be nice to support using > Hibernate > with a PreparedStatement cache. > > Its possible that the bug is also in the AS7 PreparedStatement > cache and > that we just haven't found it yet. > > What do you think? What is the type of statement that is being prepared where this issue pops up? DML operation? A load select? A query? Are the statement "handles" returned by the JBoss cache thread-safe? Is threading used in whatever test causes this condition? -- steve(a)hibernate.org http://hibernate.org _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev