So, which do you want? Lots of connections, or data getting to the database? :-)
Try starting the spring transaction before creating the EM. Either that, or join the EM to the transaction. Kevin On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <[email protected]> wrote: > Kevin, > > Someone on the [email protected] mailing list suggested that I > let Springframework manage the transactions, so I changed my code to > look like: > > The apparent connection "leak" went away, and no exceptions are > thrown, and I only see one connection being opened, as expected - > however, nothing goes into the database. > > static void saveToDB(List<MdBaseData> data) throws Exception { > EntityManagerFactory emf = Persistence > .createEntityManagerFactory("marketdata"); > JpaTransactionManager jpaTxMgr = new > JpaTransactionManager(emf); > > > EntityManager em = > > jpaTxMgr.getEntityManagerFactory().createEntityManager(); > TransactionStatus txStatus = > jpaTxMgr.getTransaction(new > DefaultTransactionDefinition()); > try { > //em.getTransaction().begin(); > for (MdBaseData bd : data) { > em.persist(bd); > } > //em.getTransaction().commit(); > jpaTxMgr.commit(txStatus); > } catch (Exception e) { > jpaTxMgr.rollback(txStatus); > e.printStackTrace(); > } finally { > em.close(); > } > } > > On Thu, Jan 31, 2013 at 3:49 PM, Chris Wolf <[email protected]> wrote: > > Even more specifically, I drilled down into the guts of OpenJPA-2.1, > > into the call to > > EntityManager.getTransaction().begin(); > > > > The place were all the connections are opened until exhausted is in: > > > > org.apache.openjpa.kernel.BrokerImpl.flush(int reason) > > > > There is a loop where the connections are all being opened: > > > > Collection mobjs = null; > > _flags |= FLAG_PRESTORING; > > try { > > if (flush) { > > // call pre store on all currently transactional objs > > for (Iterator itr = transactional.iterator(); > > itr.hasNext();) //<=== EATING UP ALL CONNECTIONS > > ((StateManagerImpl) itr.next()).beforeFlush(reason, > _call); > > > > > > Let me know if there's anything else I can to do help solve this issue. > > > > Thanks, > > > > -Chris > > > > On Thu, Jan 31, 2013 at 3:18 PM, Chris Wolf <[email protected]> > wrote: > >> Kevin, > >> > >> I isolated where all the connections are opened at once - upon calling > >> entityManager.getTransaction().commit(); > >> > >> I assume this is correct from all the exampled I've seen so far... > >> > >> EntityManager entityManager = > >> entityManagerFactory.createEntityManager(); // here is where just one > >> connection is opened, as expected. (not using connection pool yet) > >> entityManager.getTransaction().begin(); > >> for (MdBaseData bd : data) { > >> em.persist(bd); > >> } > >> entityManager.getTransaction().commit(); // here is where all the > >> connections are opened ***** > >> entityManager.close(); > >> > >> Note that the entity has a M2M to child entities (about 100 per > >> MdBaseData) via a link table. > >> > >> Thanks for any ideas, > >> > >> -Chris > >> > >> On Thu, Jan 31, 2013 at 1:56 PM, Kevin Sutter <[email protected]> > wrote: > >>> Hi Chris, > >>> Good to know your plans. Thanks. > >>> > >>> You can get almost all of the JDBC connection access via the JDBC > channel > >>> in our logging framework [1]. Unfortunately, I looked at the code and > the > >>> constructors don't seem to have a log entry... :-( But, you do get > all of > >>> the closes, commits, rollbacks, etc -- all of the normal operations > through > >>> a Connection object. So, I'd start with that. > >>> > >>> Updating the Ctor for additional logging would be very easy to do. I > would > >>> probably do it in the LoggingConnectionDecorator, like you thought. > If you > >>> have issues with building a version of OpenJPA for your testing, ping > me > >>> back with the version of OpenJPA that you are using and maybe I can > find > >>> time to do a quick update. But, try the JDBC log channel first and > see if > >>> that gives you enough information for your specific scenario. > >>> > >>> Thanks, > >>> Kevin > >>> > >>> [1] > >>> > http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_logging_channels > >>> > >>> On Thu, Jan 31, 2013 at 12:02 PM, Chris Wolf <[email protected]> > wrote: > >>> > >>>> I am fully aware that pooling is the way to go, but I want to just > >>>> prove out the simple case first. Also the final deployment will be in > >>>> a JEE container, so it will be doing the pooling. > >>>> > >>>> I really wish there was a logging setting to trace acquire/release of > >>>> JDBC Connections - I looked at the source for (I forget now, something > >>>> like JDBCDatStore) it had logging but not of acquire/release of > >>>> Connections. > >>>> > >>>> What is this LoggingConnectionDecorator? would that help me log > >>>> connection activity? If so, how is it configured? > >>>> > >>>> Thanks, > >>>> > >>>> -Chris > >>>> > >>>> On Thu, Jan 31, 2013 at 12:19 PM, Kevin Sutter <[email protected]> > wrote: > >>>> > Hi Chris, > >>>> > Normally, OpenJPA will only request a connection "on demand" [1]. > As > >>>> each > >>>> > database access is requested, a connection is obtained, but then > it's > >>>> > released when we're done with it. Unless there is some processing > or > >>>> > configuration that is telling OpenJPA to hang onto the > connection... If > >>>> > nothing jumps out at you, I would suggest tracing (maybe both > OpenJPA and > >>>> > database) to see why all of the connections are getting requested > and > >>>> > nothing is getting closed. > >>>> > > >>>> > As an aside, I would highly recommend the use of some type of > connection > >>>> > pooling. Overall, you will get much better performance if > connections > >>>> can > >>>> > be re-used instead of constantly dropping and re-creating > connections. > >>>> > Whether you use DBCP or Oracle pooling or some application server's > >>>> > connection pooling mechanism, it doesn't really matter. But, I > would > >>>> > suggest using some connection pooling. > >>>> > > >>>> > Good luck, > >>>> > Kevin > >>>> > > >>>> > [1] > >>>> > > >>>> > http://people.apache.org/~mikedd/nightly.builds/apache-openjpa-2.3.0-SNAPSHOT/docs/docbook/manual.html#ref_guide_dbsetup_retain > >>>> > > >>>> > On Thu, Jan 31, 2013 at 10:51 AM, Chris Wolf <[email protected]> > >>>> wrote: > >>>> > > >>>> >> If I process a small number of records, everything works, however > when > >>>> >> I try to process a "real-world" number of records, I get an > >>>> >> "ORA-12519". At first, I thought it was an Oracle issue and after > >>>> >> searching around and getting hits on "solutions" involving > increasing > >>>> >> Oracle sessions and processes (there are at least 125 configured), > I > >>>> >> was still getting "ORA-12519". I then tailed the TNS listener log > and > >>>> >> saw that everytime my OpenJPA process ran, it would consume all the > >>>> >> JDBC connections as if it was using connection pooling with some > high > >>>> >> min-connections setting. > >>>> >> > >>>> >> In fact, as the stack trace shows, it's only using > >>>> >> "SimpleDriveDataSource", which I thought didn't pool connections > and I > >>>> >> don't have the DBCP jar on my classpath, so why is OpenJPA opening > all > >>>> >> these JDBC connections? > >>>> >> > >>>> >> > >>>> >> Thanks, > >>>> >> > >>>> >> > >>>> >> Chris > >>>> >> > >>>> >> > >>>> >> 183 marketdata INFO [main] openjpa.Runtime - Starting OpenJPA > 2.2.1 > >>>> >> 214 marketdata INFO [main] openjpa.jdbc.JDBC - Using dictionary > >>>> >> class "org.apache.openjpa.jdbc.sql.OracleDictionary". > >>>> >> Exception in thread "main" <openjpa-2.2.1-r422266:1396819 fatal > store > >>>> >> error> org.apache.openjpa.persistence.RollbackException: Listener > >>>> >> refused the connection with the following error: > >>>> >> ORA-12519, TNS:no appropriate service handler found > >>>> >> > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594) > >>>> >> at > ms.algo.adapt.test.BeanIODemo.saveToDB(BeanIODemo.java:153) > >>>> >> at > ms.algo.adapt.test.BeanIODemo.beanIOTest(BeanIODemo.java:127) > >>>> >> at ms.algo.adapt.test.BeanIODemo.main(BeanIODemo.java:50) > >>>> >> Caused by: <openjpa-2.2.1-r422266:1396819 fatal general error> > >>>> >> org.apache.openjpa.persistence.PersistenceException: Listener > refused > >>>> >> the connection with the following error: > >>>> >> ORA-12519, TNS:no appropriate service handler found > >>>> >> > >>>> >> at > >>>> >> > org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4958) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4918) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:971) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:240) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.getConnection(AbstractJDBCSeq.java:163) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.allocateInternal(NativeJDBCSeq.java:217) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.NativeJDBCSeq.nextInternal(NativeJDBCSeq.java:201) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.AbstractJDBCSeq.next(AbstractJDBCSeq.java:60) > >>>> >> at > >>>> >> > org.apache.openjpa.util.ImplHelper.generateValue(ImplHelper.java:160) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.util.ImplHelper.generateFieldValue(ImplHelper.java:144) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignField(JDBCStoreManager.java:778) > >>>> >> at > >>>> >> > org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:493) > >>>> >> at > >>>> >> > org.apache.openjpa.util.ApplicationIds.assign(ApplicationIds.java:469) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.assignObjectId(JDBCStoreManager.java:762) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.DelegatingStoreManager.assignObjectId(DelegatingStoreManager.java:135) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.StateManagerImpl.assignObjectId(StateManagerImpl.java:600) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.SingleFieldManager.preFlushPC(SingleFieldManager.java:803) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.SingleFieldManager.preFlushPCs(SingleFieldManager.java:762) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:664) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:589) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.SingleFieldManager.preFlush(SingleFieldManager.java:505) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.StateManagerImpl.preFlush(StateManagerImpl.java:3028) > >>>> >> at > >>>> >> org.apache.openjpa.kernel.PNewState.beforeFlush(PNewState.java:44) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.StateManagerImpl.beforeFlush(StateManagerImpl.java:1042) > >>>> >> at > >>>> org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2114) > >>>> >> at > >>>> >> > org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2074) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1992) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81) > >>>> >> at > >>>> >> org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1516) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:933) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:570) > >>>> >> ... 3 more > >>>> >> Caused by: java.sql.SQLException: Listener refused the connection > with > >>>> >> the following error: > >>>> >> ORA-12519, TNS:no appropriate service handler found > >>>> >> > >>>> >> at > >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517) > >>>> >> at > >>>> >> > >>>> > oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:557) > >>>> >> at > >>>> oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:233) > >>>> >> at > >>>> >> > >>>> > oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29) > >>>> >> at > >>>> oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getSimpleConnection(SimpleDriverDataSource.java:84) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:39) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:93) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:304) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:982) > >>>> >> at > >>>> >> > >>>> > org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:967) > >>>> >> ... 31 more > >>>> >> Caused by: oracle.net.ns.NetException: Listener refused the > connection > >>>> >> with the following error: > >>>> >> ORA-12519, TNS:no appropriate service handler found > >>>> >> > >>>> >> at oracle.net.ns.NSProtocol.connect(NSProtocol.java:457) > >>>> >> at > >>>> >> oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625) > >>>> >> at > >>>> oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365) > >>>> >> ... 44 more > >>>> >> > >>>> >
