openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Hastie <and...@ahastie.net>
Subject Re: Connection is closed across multiple EntityManager instances in a JEE Container
Date Tue, 01 Oct 2013 16:50:05 GMT
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 <abudar63@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
>>>
>>>
>>>

Mime
View raw message