On Thu, Jan 31, 2013 at 4:30 PM, Kevin Sutter <kwsut...@gmail.com> wrote: > So, which do you want? Lots of connections, or data getting to the > database? :-)
Preferably the later. ;) > > Try starting the spring transaction before creating the EM. Either that, > or join the EM to the transaction. I tried the former - it didn't change the outcome. Not certain what you meant by "join EM to the transaction". BTW, I don't know if you noticed my previous post about logging, but I tried setting openjpa.Log = slf4j and that didn't work - no errors, just no addtional output. Thanks for helping so far... > > Kevin > > On Thu, Jan 31, 2013 at 3:26 PM, Chris Wolf <cwolf.a...@gmail.com> wrote: > >> Kevin, >> >> Someone on the us...@apache.camel.org 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 <cwolf.a...@gmail.com> 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 <cwolf.a...@gmail.com> >> 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 <kwsut...@gmail.com> >> 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 <cwolf.a...@gmail.com> >> 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 <kwsut...@gmail.com> >> 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 <cwolf.a...@gmail.com> >> >>>> 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 >> >>>> >> >> >>>> >>