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

[Andrew Hastie]

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 <abuda...@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: and...@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