You are viewing a plain text version of this content. The canonical link for it is here.

Posted to users@openjpa.apache.org by Andrew Hastie <an...@ahastie.net> on 2013/10/01 13:27:25 UTC

Connection is closed across multiple EntityManager instances in a JEE Container

Hi all,

OK, I suspect what I need here is more along the lines of advice on how 
to proceed diagnosing a problem .....

I have a JEE EJB3 application which makes us of Statefull Session Beans 
where my EntityManager is rooted. Connections are non-XA and managed by 
the container (Glassfish or WebSphere) and transactions under JTA control.

OpenJPA version = v2.1.1
Persistence database = PostgreSQL 9.2
OS = Linux and Windows 2008 Server
JEE Container = Glassfish V3.2 and IBM WebSphere V8.5.5

Here's the persistence.xml (stripped down for salient bits and removal 
of sensitive data items):-

*<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
              version="2.0">

     <persistence-unit name="..........." transaction-type="JTA">
         <description>
             ............
         </description>
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<jta-data-source>jdbc/.......</jta-data-source>
         <class>.....</class>
***        <class>.....</class>
*
         <properties>
             <property name="openjpa.TransactionMode" value="managed"/>
             <!-- use OpenJPA's built-in DataSource for unmanaged 
connections
             <!-- Following property is required for Postgres v9.1 and 
later due to change in Escape String handling -->
             <!-- Reported as 
http://openjpa.208410.n2.nabble.com/Postgres-V9-1-issue-with-LIKE-clause-and-Escape-Strings-td6848069.html 
-->
             <property name="openjpa.jdbc.DBDictionary" 
value="postgres(SearchStringEscape=\)"/>
         </properties>
     </persistence-unit>
</persistence>*


When stress testing the application, I see failures emanating from the 
underlying database connection associated with the EntityManager stating 
that the "connection is closed". The problem only appears to show up 
when the application is under load with multiple session bean instances, 
but I can find no fixed pattern that triggers the error.

Here's a snippet from the stack trace:-

[#|2013-09-30T15:43:39.848+0100|SEVERE|glassfish3.1.2|com.rocketsoftware.ascentserver.dataaccess.catalog.JpaDAO|_ThreadID=34;_ThreadName=Thread-2;|JPA 
Nested Throwable: org.apache.openjpa.lib.jdbc
.ReportingSQLException:*Connection closed* {SELECT t0.appcode_id, 
t0.asRecNoMax, t0.asTableType, t0.comment, t0.creationDate, 
t0.CULTURE_ID, t0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification,
  t0.efficiency, t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, 
t0.internalName, t0.lastModified, t0.lastModifyCommand, 
t0.lockTimestamp, t0.locked, t0.lockingUserID, t0.maxCycle, t0.panel,
t0.password, t0.passwordProtected, t0.recordCount, t0.recordSize, 
t0.reservationLock, t0.reserved, t0.reservedTimestamp, 
t0.reservingUserID, t0.shareable, t0.tableName, t0.tableNamekey, t0.tablePr
otected, t0.title, t0.usable, t0.writtenTo FROM ASCENTTABLE t0 WHERE 
t0.id = ?} [code=0, state=null]
java.lang.RuntimeException: 
org.apache.openjpa.lib.jdbc.ReportingSQLException: Connection closed 
{SELECT t0.appcode_id, t0.asRecNoMax, t0.asTableType, t0.comment, 
t0.creationDate, t0.CULTURE_ID, t
0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification, t0.efficiency, 
t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, t0.internalName, 
t0.lastModified, t0.lastModifyCommand, t0.lockTimestam
p, t0.locked, t0.lockingUserID, t0.maxCycle, t0.panel, t0.password, 
t0.passwordProtected, t0.recordCount, t0.recordSize, t0.reservationLock, 
t0.reserved, t0.reservedTimestamp, t0.reservingUserID,
t0.shareable, t0.tableName, t0.tableNamekey, t0.tableProtected, 
t0.title, t0.usable, t0.writtenTo FROM ASCENTTABLE t0 WHERE t0.id = ?} 
[code=0, state=null]
         at 
org.apache.openjpa.jdbc.kernel.FinderQueryImpl.execute(FinderQueryImpl.java:160)
         at 
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getInitializeStateResult(JDBCStoreManager.java:566)
         at 
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initializeState(JDBCStoreManager.java:378)
         at 
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:333)
         at 
org.apache.openjpa.kernel.DelegatingStoreManager.initialize(DelegatingStoreManager.java:112)
         at 
org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreManager.java:57)
         at 
org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:1027)
         at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:985)
         at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:907)
         at 
org.apache.openjpa.kernel.BrokerImpl.isDetached(BrokerImpl.java:4563)
...
...
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: 
*Connection closed* {SELECT t0.appcode_id, t0.asRecNoMax, 
t0.asTableType, t0.comment, t0.creationDate, t0.CULTURE_ID, 
t0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification, t0.efficiency, 
t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, t0.internalName, 
t0.lastModified, t0.lastModifyCommand, t0.lockTimestamp, t0.locked, 
t0.lockingUserID, t0.maxCycle, t0.panel, t0.password, 
t0.passwordProtected, t0.recordCount, t0.recordSize, t0.reservationLock, 
t0.reserved, t0.reservedTimestamp, t0.reservingUserID, t0.shareable, 
t0.tableName, t0.tableNamekey, t0.tableProtected, t0.title, t0.usable, 
t0.writtenTo FROM ASCENTTABLE t0 WHERE t0.id = ?} [code=0, state=null]
         at 
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:281)
         at 
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:261)
         at 
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:72)
         at 
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:313)
         at 
org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
         at 
org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:158)
         at 
org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
         at 
org.apache.openjpa.jdbc.sql.PostgresDictionary$PostgresConnection.prepareStatement(PostgresDictionary.java:980)
         at 
org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
         at 
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1653)
         at 
org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:144)
         at 
org.apache.openjpa.jdbc.sql.SelectImpl.prepareStatement(SelectImpl.java:490)
         at 
org.apache.openjpa.jdbc.kernel.FinderQueryImpl.execute(FinderQueryImpl.java:144)
         ... 125 more

So far I've come up with the following conclusions:-
a. On the assumption I may have got a database connection in a closed 
state in the containers connection pool, I configured the connection 
pool for "connection validation", but this made no difference.
b. After the failures, multiple EJB Statefull Session Beans, each with 
their own private EntityManager, experience the same "connection closed" 
exception
c. No errors relating to the connection pool (i.e. exhaustion of 
connections or connection timeouts) are logged by the container
d. No errors reported by the PostgreSQL database layer

Anybody able to offer any advice on what to look for here? Any 
diagnostics in OpenJPA that would help me track this down?

Thanks
Andrew

Re: Connection is closed across multiple EntityManager instances in a JEE Container

Posted by Andrew Hastie <an...@ahastie.net>.

Thanks for the comments guys.

After trawling our codebase it appears that there were several places 
where the code was obtaining the underlying connection from the 
EntityManager, running a standard SQL query against it, and then closing 
it prior to committing the transaction and exiting the EJB method. I've 
now modified that code to use the NativeQuery mechanism such that we 
don't interfere with how the EntityManager is managing the connection, 
and all appears to be holding up under stress testing.

To answer your questions Kevin, I'm using PostgreSQL for the persistence 
store, and running my EJBs in both Glassfish and WebSphere. I've not 
considered using the "hold cursors over commit" as our code avoids 
keeping resultsets open over transaction boundaries.

So hands up - Application coding error on my part :-/

Many thanks,
Andrew


On 01/10/13 16:31, Kevin Sutter wrote:
> I was going to suggest that as well, Paulo, but Andrew indicated that this
> same issue applies to Glassfish...  And, Andrew is using Postgres...  I
> thought the resultSetHoldability is a DB2 only property...  Maybe Glassfish
> and Postgres have similar functionality, but I kind of doubt it...
>
> Andrew, can you verify my statements and your environment?
>
>
> On Tue, Oct 1, 2013 at 9:44 AM, Paulo Borges <ab...@hotmail.com> wrote:
>
>> Andrew:
>>
>> In WebSphere under the custom properties for your datasource change the
>>
>> resultSetHoldability from 2 (CLOSE_CURSORS_AT_COMMIT) to 1
>> (HOLD_CURSORS_OVER_COMMIT)
>>
>> and see if it solves your problem.
>>
>> ----------------------------------------
>>> Date: Tue, 1 Oct 2013 12:27:25 +0100
>>> From: andrew@ahastie.net
>>> To: users@openjpa.apache.org
>>> Subject: Connection is closed across multiple EntityManager instances in
>> a JEE Container
>>> Hi all,
>>>
>>> OK, I suspect what I need here is more along the lines of advice on how
>>> to proceed diagnosing a problem .....
>>>
>>> I have a JEE EJB3 application which makes us of Statefull Session Beans
>>> where my EntityManager is rooted. Connections are non-XA and managed by
>>> the container (Glassfish or WebSphere) and transactions under JTA
>> control.
>>> OpenJPA version = v2.1.1
>>> Persistence database = PostgreSQL 9.2
>>> OS = Linux and Windows 2008 Server
>>> JEE Container = Glassfish V3.2 and IBM WebSphere V8.5.5
>>>
>>> Here's the persistence.xml (stripped down for salient bits and removal
>>> of sensitive data items):-
>>>
>>> *<?xml version="1.0" encoding="UTF-8" ?>
>>> <persistence xmlns="http://java.sun.com/xml/ns/persistence"
>>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>>> xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
>>> http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
>>> version="2.0">
>>>
>>> <persistence-unit name="..........." transaction-type="JTA">
>>> <description>
>>> ............
>>> </description>
>>>
>> <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
>>> <jta-data-source>jdbc/.......</jta-data-source>
>>> <class>.....</class>
>>> *** <class>.....</class>
>>> *
>>> <properties>
>>> <property name="openjpa.TransactionMode" value="managed"/>
>>>
>>> <property name="openjpa.jdbc.DBDictionary"
>>> value="postgres(SearchStringEscape=\)"/>
>>> </properties>
>>> </persistence-unit>
>>> </persistence>*
>>>
>>>
>>> When stress testing the application, I see failures emanating from the
>>> underlying database connection associated with the EntityManager stating
>>> that the "connection is closed". The problem only appears to show up
>>> when the application is under load with multiple session bean instances,
>>> but I can find no fixed pattern that triggers the error.
>>>
>>> Here's a snippet from the stack trace:-
>>>
>>>
>> [#|2013-09-30T15:43:39.848+0100|SEVERE|glassfish3.1.2|com.rocketsoftware.ascentserver.dataaccess.catalog.JpaDAO|_ThreadID=34;_ThreadName=Thread-2;|JPA
>>> Nested Throwable: org.apache.openjpa.lib.jdbc
>>> .ReportingSQLException:*Connection closed* {SELECT t0.appcode_id,
>>> t0.asRecNoMax, t0.asTableType, t0.comment, t0.creationDate,
>>> t0.CULTURE_ID, t0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification,
>>> t0.efficiency, t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded,
>>> t0.internalName, t0.lastModified, t0.lastModifyCommand,
>>> t0.lockTimestamp, t0.locked, t0.lockingUserID, t0.maxCycle, t0.panel,
>>> t0.password, t0.passwordProtected, t0.recordCount, t0.recordSize,
>>> t0.reservationLock, t0.reserved, t0.reservedTimestamp,
>>> t0.reservingUserID, t0.shareable, t0.tableName, t0.tableNamekey,
>> t0.tablePr
>>> otected, t0.title, t0.usable, t0.writtenTo FROM ASCENTTABLE t0 WHERE
>>> t0.id = ?} [code=0, state=null]
>>> java.lang.RuntimeException:
>>> org.apache.openjpa.lib.jdbc.ReportingSQLException: Connection closed
>>> {SELECT t0.appcode_id, t0.asRecNoMax, t0.asTableType, t0.comment,
>>> t0.creationDate, t0.CULTURE_ID, t
>>> 0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification, t0.efficiency,
>>> t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, t0.internalName,
>>> t0.lastModified, t0.lastModifyCommand, t0.lockTimestam
>>> p, t0.locked, t0.lockingUserID, t0.maxCycle, t0.panel, t0.password,
>>> t0.passwordProtected, t0.recordCount, t0.recordSize, t0.reservationLock,
>>> t0.reserved, t0.reservedTimestamp, t0.reservingUserID,
>>> t0.shareable, t0.tableName, t0.tableNamekey, t0.tableProtected,
>>> t0.title, t0.usable, t0.writtenTo FROM ASCENTTABLE t0 WHERE t0.id = ?}
>>> [code=0, state=null]
>>> at
>>>
>> org.apache.openjpa.jdbc.kernel.FinderQueryImpl.execute(FinderQueryImpl.java:160)
>>> at
>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getInitializeStateResult(JDBCStoreManager.java:566)
>>> at
>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initializeState(JDBCStoreManager.java:378)
>>> at
>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:333)
>>> at
>>>
>> org.apache.openjpa.kernel.DelegatingStoreManager.initialize(DelegatingStoreManager.java:112)
>>> at
>>>
>> org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreManager.java:57)
>>> at
>>> org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:1027)
>>> at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:985)
>>> at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:907)
>>> at
>>> org.apache.openjpa.kernel.BrokerImpl.isDetached(BrokerImpl.java:4563)
>>> ...
>>> ...
>>> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
>>> *Connection closed* {SELECT t0.appcode_id, t0.asRecNoMax,
>>> t0.asTableType, t0.comment, t0.creationDate, t0.CULTURE_ID,
>>> t0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification, t0.efficiency,
>>> t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, t0.internalName,
>>> t0.lastModified, t0.lastModifyCommand, t0.lockTimestamp, t0.locked,
>>> t0.lockingUserID, t0.maxCycle, t0.panel, t0.password,
>>> t0.passwordProtected, t0.recordCount, t0.recordSize, t0.reservationLock,
>>> t0.reserved, t0.reservedTimestamp, t0.reservingUserID, t0.shareable,
>>> t0.tableName, t0.tableNamekey, t0.tableProtected, t0.title, t0.usable,
>>> t0.writtenTo FROM ASCENTTABLE t0 WHERE t0.id = ?} [code=0, state=null]
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:281)
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:261)
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:72)
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:313)
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:158)
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
>>> at
>>>
>> org.apache.openjpa.jdbc.sql.PostgresDictionary$PostgresConnection.prepareStatement(PostgresDictionary.java:980)
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
>>> at
>>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1653)
>>> at
>>>
>> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:144)
>>> at
>>>
>> org.apache.openjpa.jdbc.sql.SelectImpl.prepareStatement(SelectImpl.java:490)
>>> at
>>>
>> org.apache.openjpa.jdbc.kernel.FinderQueryImpl.execute(FinderQueryImpl.java:144)
>>> ... 125 more
>>>
>>> So far I've come up with the following conclusions:-
>>> a. On the assumption I may have got a database connection in a closed
>>> state in the containers connection pool, I configured the connection
>>> pool for "connection validation", but this made no difference.
>>> b. After the failures, multiple EJB Statefull Session Beans, each with
>>> their own private EntityManager, experience the same "connection closed"
>>> exception
>>> c. No errors relating to the connection pool (i.e. exhaustion of
>>> connections or connection timeouts) are logged by the container
>>> d. No errors reported by the PostgreSQL database layer
>>>
>>> Anybody able to offer any advice on what to look for here? Any
>>> diagnostics in OpenJPA that would help me track this down?
>>>
>>> Thanks
>>> Andrew
>>>
>>>
>>>

Re: Connection is closed across multiple EntityManager instances in a JEE Container

Posted by Kevin Sutter <kw...@gmail.com>.

I was going to suggest that as well, Paulo, but Andrew indicated that this
same issue applies to Glassfish...  And, Andrew is using Postgres...  I
thought the resultSetHoldability is a DB2 only property...  Maybe Glassfish
and Postgres have similar functionality, but I kind of doubt it...

Andrew, can you verify my statements and your environment?


On Tue, Oct 1, 2013 at 9:44 AM, Paulo Borges <ab...@hotmail.com> wrote:

> Andrew:
>
> In WebSphere under the custom properties for your datasource change the
>
> resultSetHoldability from 2 (CLOSE_CURSORS_AT_COMMIT) to 1
> (HOLD_CURSORS_OVER_COMMIT)
>
> and see if it solves your problem.
>
> ----------------------------------------
> > Date: Tue, 1 Oct 2013 12:27:25 +0100
> > From: andrew@ahastie.net
> > To: users@openjpa.apache.org
> > Subject: Connection is closed across multiple EntityManager instances in
> a JEE Container
> >
> > Hi all,
> >
> > OK, I suspect what I need here is more along the lines of advice on how
> > to proceed diagnosing a problem .....
> >
> > I have a JEE EJB3 application which makes us of Statefull Session Beans
> > where my EntityManager is rooted. Connections are non-XA and managed by
> > the container (Glassfish or WebSphere) and transactions under JTA
> control.
> >
> > OpenJPA version = v2.1.1
> > Persistence database = PostgreSQL 9.2
> > OS = Linux and Windows 2008 Server
> > JEE Container = Glassfish V3.2 and IBM WebSphere V8.5.5
> >
> > Here's the persistence.xml (stripped down for salient bits and removal
> > of sensitive data items):-
> >
> > *<?xml version="1.0" encoding="UTF-8" ?>
> > <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> > xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
> > http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
> > version="2.0">
> >
> > <persistence-unit name="..........." transaction-type="JTA">
> > <description>
> > ............
> > </description>
> >
> <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
> > <jta-data-source>jdbc/.......</jta-data-source>
> > <class>.....</class>
> > *** <class>.....</class>
> > *
> > <properties>
> > <property name="openjpa.TransactionMode" value="managed"/>
> >
> > <property name="openjpa.jdbc.DBDictionary"
> > value="postgres(SearchStringEscape=\)"/>
> > </properties>
> > </persistence-unit>
> > </persistence>*
> >
> >
> > When stress testing the application, I see failures emanating from the
> > underlying database connection associated with the EntityManager stating
> > that the "connection is closed". The problem only appears to show up
> > when the application is under load with multiple session bean instances,
> > but I can find no fixed pattern that triggers the error.
> >
> > Here's a snippet from the stack trace:-
> >
> >
> [#|2013-09-30T15:43:39.848+0100|SEVERE|glassfish3.1.2|com.rocketsoftware.ascentserver.dataaccess.catalog.JpaDAO|_ThreadID=34;_ThreadName=Thread-2;|JPA
> > Nested Throwable: org.apache.openjpa.lib.jdbc
> > .ReportingSQLException:*Connection closed* {SELECT t0.appcode_id,
> > t0.asRecNoMax, t0.asTableType, t0.comment, t0.creationDate,
> > t0.CULTURE_ID, t0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification,
> > t0.efficiency, t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded,
> > t0.internalName, t0.lastModified, t0.lastModifyCommand,
> > t0.lockTimestamp, t0.locked, t0.lockingUserID, t0.maxCycle, t0.panel,
> > t0.password, t0.passwordProtected, t0.recordCount, t0.recordSize,
> > t0.reservationLock, t0.reserved, t0.reservedTimestamp,
> > t0.reservingUserID, t0.shareable, t0.tableName, t0.tableNamekey,
> t0.tablePr
> > otected, t0.title, t0.usable, t0.writtenTo FROM ASCENTTABLE t0 WHERE
> > t0.id = ?} [code=0, state=null]
> > java.lang.RuntimeException:
> > org.apache.openjpa.lib.jdbc.ReportingSQLException: Connection closed
> > {SELECT t0.appcode_id, t0.asRecNoMax, t0.asTableType, t0.comment,
> > t0.creationDate, t0.CULTURE_ID, t
> > 0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification, t0.efficiency,
> > t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, t0.internalName,
> > t0.lastModified, t0.lastModifyCommand, t0.lockTimestam
> > p, t0.locked, t0.lockingUserID, t0.maxCycle, t0.panel, t0.password,
> > t0.passwordProtected, t0.recordCount, t0.recordSize, t0.reservationLock,
> > t0.reserved, t0.reservedTimestamp, t0.reservingUserID,
> > t0.shareable, t0.tableName, t0.tableNamekey, t0.tableProtected,
> > t0.title, t0.usable, t0.writtenTo FROM ASCENTTABLE t0 WHERE t0.id = ?}
> > [code=0, state=null]
> > at
> >
> org.apache.openjpa.jdbc.kernel.FinderQueryImpl.execute(FinderQueryImpl.java:160)
> > at
> >
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getInitializeStateResult(JDBCStoreManager.java:566)
> > at
> >
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initializeState(JDBCStoreManager.java:378)
> > at
> >
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:333)
> > at
> >
> org.apache.openjpa.kernel.DelegatingStoreManager.initialize(DelegatingStoreManager.java:112)
> > at
> >
> org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreManager.java:57)
> > at
> > org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:1027)
> > at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:985)
> > at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:907)
> > at
> > org.apache.openjpa.kernel.BrokerImpl.isDetached(BrokerImpl.java:4563)
> > ...
> > ...
> > Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
> > *Connection closed* {SELECT t0.appcode_id, t0.asRecNoMax,
> > t0.asTableType, t0.comment, t0.creationDate, t0.CULTURE_ID,
> > t0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification, t0.efficiency,
> > t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, t0.internalName,
> > t0.lastModified, t0.lastModifyCommand, t0.lockTimestamp, t0.locked,
> > t0.lockingUserID, t0.maxCycle, t0.panel, t0.password,
> > t0.passwordProtected, t0.recordCount, t0.recordSize, t0.reservationLock,
> > t0.reserved, t0.reservedTimestamp, t0.reservingUserID, t0.shareable,
> > t0.tableName, t0.tableNamekey, t0.tableProtected, t0.title, t0.usable,
> > t0.writtenTo FROM ASCENTTABLE t0 WHERE t0.id = ?} [code=0, state=null]
> > at
> >
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:281)
> > at
> >
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:261)
> > at
> >
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:72)
> > at
> >
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:313)
> > at
> >
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
> > at
> >
> org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:158)
> > at
> >
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
> > at
> >
> org.apache.openjpa.jdbc.sql.PostgresDictionary$PostgresConnection.prepareStatement(PostgresDictionary.java:980)
> > at
> >
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
> > at
> >
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1653)
> > at
> >
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:144)
> > at
> >
> org.apache.openjpa.jdbc.sql.SelectImpl.prepareStatement(SelectImpl.java:490)
> > at
> >
> org.apache.openjpa.jdbc.kernel.FinderQueryImpl.execute(FinderQueryImpl.java:144)
> > ... 125 more
> >
> > So far I've come up with the following conclusions:-
> > a. On the assumption I may have got a database connection in a closed
> > state in the containers connection pool, I configured the connection
> > pool for "connection validation", but this made no difference.
> > b. After the failures, multiple EJB Statefull Session Beans, each with
> > their own private EntityManager, experience the same "connection closed"
> > exception
> > c. No errors relating to the connection pool (i.e. exhaustion of
> > connections or connection timeouts) are logged by the container
> > d. No errors reported by the PostgreSQL database layer
> >
> > Anybody able to offer any advice on what to look for here? Any
> > diagnostics in OpenJPA that would help me track this down?
> >
> > Thanks
> > Andrew
> >
> >
> >
>

RE: Connection is closed across multiple EntityManager instances in a JEE Container

Posted by Paulo Borges <ab...@hotmail.com>.

Andrew:

In WebSphere under the custom properties for your datasource change the 

resultSetHoldability from 2 (CLOSE_CURSORS_AT_COMMIT) to 1 (HOLD_CURSORS_OVER_COMMIT)

and see if it solves your problem. 

----------------------------------------
> Date: Tue, 1 Oct 2013 12:27:25 +0100
> From: andrew@ahastie.net
> To: users@openjpa.apache.org
> Subject: Connection is closed across multiple EntityManager instances in a JEE Container
>
> Hi all,
>
> OK, I suspect what I need here is more along the lines of advice on how
> to proceed diagnosing a problem .....
>
> I have a JEE EJB3 application which makes us of Statefull Session Beans
> where my EntityManager is rooted. Connections are non-XA and managed by
> the container (Glassfish or WebSphere) and transactions under JTA control.
>
> OpenJPA version = v2.1.1
> Persistence database = PostgreSQL 9.2
> OS = Linux and Windows 2008 Server
> JEE Container = Glassfish V3.2 and IBM WebSphere V8.5.5
>
> Here's the persistence.xml (stripped down for salient bits and removal
> of sensitive data items):-
>
> *<?xml version="1.0" encoding="UTF-8" ?>
> <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
> http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
> version="2.0">
>
> <persistence-unit name="..........." transaction-type="JTA">
> <description>
> ............
> </description>
> <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
> <jta-data-source>jdbc/.......</jta-data-source>
> <class>.....</class>
> *** <class>.....</class>
> *
> <properties>
> <property name="openjpa.TransactionMode" value="managed"/>
> 
> <property name="openjpa.jdbc.DBDictionary"
> value="postgres(SearchStringEscape=\)"/>
> </properties>
> </persistence-unit>
> </persistence>*
>
>
> When stress testing the application, I see failures emanating from the
> underlying database connection associated with the EntityManager stating
> that the "connection is closed". The problem only appears to show up
> when the application is under load with multiple session bean instances,
> but I can find no fixed pattern that triggers the error.
>
> Here's a snippet from the stack trace:-
>
> [#|2013-09-30T15:43:39.848+0100|SEVERE|glassfish3.1.2|com.rocketsoftware.ascentserver.dataaccess.catalog.JpaDAO|_ThreadID=34;_ThreadName=Thread-2;|JPA
> Nested Throwable: org.apache.openjpa.lib.jdbc
> .ReportingSQLException:*Connection closed* {SELECT t0.appcode_id,
> t0.asRecNoMax, t0.asTableType, t0.comment, t0.creationDate,
> t0.CULTURE_ID, t0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification,
> t0.efficiency, t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded,
> t0.internalName, t0.lastModified, t0.lastModifyCommand,
> t0.lockTimestamp, t0.locked, t0.lockingUserID, t0.maxCycle, t0.panel,
> t0.password, t0.passwordProtected, t0.recordCount, t0.recordSize,
> t0.reservationLock, t0.reserved, t0.reservedTimestamp,
> t0.reservingUserID, t0.shareable, t0.tableName, t0.tableNamekey, t0.tablePr
> otected, t0.title, t0.usable, t0.writtenTo FROM ASCENTTABLE t0 WHERE
> t0.id = ?} [code=0, state=null]
> java.lang.RuntimeException:
> org.apache.openjpa.lib.jdbc.ReportingSQLException: Connection closed
> {SELECT t0.appcode_id, t0.asRecNoMax, t0.asTableType, t0.comment,
> t0.creationDate, t0.CULTURE_ID, t
> 0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification, t0.efficiency,
> t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, t0.internalName,
> t0.lastModified, t0.lastModifyCommand, t0.lockTimestam
> p, t0.locked, t0.lockingUserID, t0.maxCycle, t0.panel, t0.password,
> t0.passwordProtected, t0.recordCount, t0.recordSize, t0.reservationLock,
> t0.reserved, t0.reservedTimestamp, t0.reservingUserID,
> t0.shareable, t0.tableName, t0.tableNamekey, t0.tableProtected,
> t0.title, t0.usable, t0.writtenTo FROM ASCENTTABLE t0 WHERE t0.id = ?}
> [code=0, state=null]
> at
> org.apache.openjpa.jdbc.kernel.FinderQueryImpl.execute(FinderQueryImpl.java:160)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getInitializeStateResult(JDBCStoreManager.java:566)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initializeState(JDBCStoreManager.java:378)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:333)
> at
> org.apache.openjpa.kernel.DelegatingStoreManager.initialize(DelegatingStoreManager.java:112)
> at
> org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreManager.java:57)
> at
> org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:1027)
> at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:985)
> at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:907)
> at
> org.apache.openjpa.kernel.BrokerImpl.isDetached(BrokerImpl.java:4563)
> ...
> ...
> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
> *Connection closed* {SELECT t0.appcode_id, t0.asRecNoMax,
> t0.asTableType, t0.comment, t0.creationDate, t0.CULTURE_ID,
> t0.currentCycle, t0.DATASOURCE_ID, t0.dataSpecification, t0.efficiency,
> t0.help, t0.hostCodepage, t0.id, t0.indexReorgNeeded, t0.internalName,
> t0.lastModified, t0.lastModifyCommand, t0.lockTimestamp, t0.locked,
> t0.lockingUserID, t0.maxCycle, t0.panel, t0.password,
> t0.passwordProtected, t0.recordCount, t0.recordSize, t0.reservationLock,
> t0.reserved, t0.reservedTimestamp, t0.reservingUserID, t0.shareable,
> t0.tableName, t0.tableNamekey, t0.tableProtected, t0.title, t0.usable,
> t0.writtenTo FROM ASCENTTABLE t0 WHERE t0.id = ?} [code=0, state=null]
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:281)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:261)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:72)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:313)
> at
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
> at
> org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:158)
> at
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
> at
> org.apache.openjpa.jdbc.sql.PostgresDictionary$PostgresConnection.prepareStatement(PostgresDictionary.java:980)
> at
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:155)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1653)
> at
> org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:144)
> at
> org.apache.openjpa.jdbc.sql.SelectImpl.prepareStatement(SelectImpl.java:490)
> at
> org.apache.openjpa.jdbc.kernel.FinderQueryImpl.execute(FinderQueryImpl.java:144)
> ... 125 more
>
> So far I've come up with the following conclusions:-
> a. On the assumption I may have got a database connection in a closed
> state in the containers connection pool, I configured the connection
> pool for "connection validation", but this made no difference.
> b. After the failures, multiple EJB Statefull Session Beans, each with
> their own private EntityManager, experience the same "connection closed"
> exception
> c. No errors relating to the connection pool (i.e. exhaustion of
> connections or connection timeouts) are logged by the container
> d. No errors reported by the PostgreSQL database layer
>
> Anybody able to offer any advice on what to look for here? Any
> diagnostics in OpenJPA that would help me track this down?
>
> Thanks
> Andrew
>
>
>