Hello Michael, thank You for the reply, I will check it for sure. But this bring me another question: if the parent transaction timeout is low, when I resume it, shouldn't it fail with a "Error committing transaction: Transaction has timed out" error? I would expect it.
Giulio On Thu, Feb 18, 2021, 23:49 Michael Brohl <[email protected]> wrote: > Hi Giulio, > > have you checked that the transaction timeout is set high enough for the > parent transaction? > > Regards, > > Michael Brohl > > ecomify GmbH - www.ecomify.de > > > Am 17.02.21 um 01:08 schrieb Giulio Speri - MpStyle Srl: > > Hello everyone, > > > > I hope you are all doing well. > > > > I write because I am facing an exception not so clear to me while > writing a > > Java service. > > In this service I am using a paged list (EntityUtil.getPagedList(..)) > with > > an iterator object retrieved using delegator.find(..) method, to process > > and delete a lot of records. > > Since the records in the target table are several hundreds of thousands, > to > > avoid loading everything in RAM (using for example a findList method), I > > suspend the "service" transaction, start internally a new transaction, > > process and commit each page, and at the end I resume the parent > > transaction. > > I build pages of 1000 records each and I loop > > (start_transaction-process_data-commit) all the pages. > > > > In short the service structure is: > > > > 1.iterator <- delegator.find("ShoppingList",...) > > 2.suspend-parent-transaction > > 3.paginatedList <- EntityUtil.getPaginatedList(iterator,page1,1000) > > 4.while(page <= totPages): > > 4.a)start new transaction; > > 4.b)nextPage -> EntityUtil.getPagedList(iterator, page+1,1000) > > 4.c)delete data; > > 4.d) commit trx > > 5.finally: > > 5.a)close the iterator; > > *5.b)resume parent trx;* > > > > If the number of pages are not too high (for example 100) the service run > > just fine, but if the number of pages is higher (for example I tested the > > service with 269 pages), it commits everything as expected, but when it > > tries to resume the parent transaction (step *5.b*), the following > > exception is thrown: > > > > org.ofbiz.entity.transaction.GenericTransactionException: System error, > >> could not commit transaction: javax.transaction.xa.XAException (null) > >> at > >> > org.ofbiz.entity.transaction.TransactionUtil.commit(TransactionUtil.java:304) > >> ~[ofbiz-entity-test.jar:?] > >> at > >> > org.ofbiz.entity.transaction.TransactionUtil.commit(TransactionUtil.java:252) > >> ~[ofbiz-entity-test.jar:?] > >> at > org.ofbiz.service.ServiceDispatcher.runSync(ServiceDispatcher.java:546) > >> [ofbiz-service.jar:?] > >> at > org.ofbiz.service.ServiceDispatcher.runSync(ServiceDispatcher.java:232) > >> [ofbiz-service.jar:?] > > ... > > ... > > > >> Caused by: javax.transaction.xa.XAException > >> at > >> > org.apache.commons.dbcp.managed.LocalXAConnectionFactory$LocalXAResource.commit(LocalXAConnectionFactory.java:214) > >> ~[commons-dbcp-1.4.jar:1.4] > >> at > >> > org.apache.geronimo.transaction.manager.TransactionImpl.commitResource(TransactionImpl.java:622) > >> ~[geronimo-transaction-3.1.1.jar:3.1.1] > >> at > >> > org.apache.geronimo.transaction.manager.TransactionImpl.commit(TransactionImpl.java:305) > >> ~[geronimo-transaction-3.1.1.jar:3.1.1] > >> at > >> > org.apache.geronimo.transaction.manager.TransactionManagerImpl.commit(TransactionManagerImpl.java:252) > >> ~[geronimo-transaction-3.1.1.jar:3.1.1] > >> at > >> > org.ofbiz.entity.transaction.TransactionUtil.commit(TransactionUtil.java:266) > >> ~[ofbiz-entity-test.jar:?] > >> ... 37 more > >> Caused by: java.sql.SQLNonTransientConnectionException: Could not send > >> query: Connection reset > >> at > >> > org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:125) > >> ~[mariadb-java-client-1.5.4.jar:?] > > ... > > ... > > > >> Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Could not > >> send query: Connection reset > >> at > >> > org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:144) > >> ~[mariadb-java-client-1.5.4.jar:?] > >> at > >> > org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:251) > >> ~[mariadb-java-client-1.5.4.jar:?] > >> at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:273) > >> ~[mariadb-java-client-1.5.4.jar:?] > >> at org.mariadb.jdbc.MariaDbConnection.commit(MariaDbConnection.java:598) > >> ~[mariadb-java-client-1.5.4.jar:?] > >> at > >> > org.apache.commons.dbcp.managed.LocalXAConnectionFactory$LocalXAResource.commit(LocalXAConnectionFactory.java:211) > >> ~[commons-dbcp-1.4.jar:1.4] > >> at > >> > org.apache.geronimo.transaction.manager.TransactionImpl.commitResource(TransactionImpl.java:622) > >> ~[geronimo-transaction-3.1.1.jar:3.1.1] > >> at > >> > org.apache.geronimo.transaction.manager.TransactionImpl.commit(TransactionImpl.java:305) > >> ~[geronimo-transaction-3.1.1.jar:3.1.1] > >> at > >> > org.apache.geronimo.transaction.manager.TransactionManagerImpl.commit(TransactionManagerImpl.java:252) > >> ~[geronimo-transaction-3.1.1.jar:3.1.1] > >> at > >> > org.ofbiz.entity.transaction.TransactionUtil.commit(TransactionUtil.java:266) > >> ~[ofbiz-entity-test.jar:?] > >> ... 37 more > >> Caused by: java.net.SocketException: Connection reset > >> at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113) > >> ~[?:1.8.0_102] > >> at java.net.SocketOutputStream.write(SocketOutputStream.java:153) > >> ~[?:1.8.0_102] > >> at > >> > org.mariadb.jdbc.internal.stream.PacketOutputStream.send(PacketOutputStream.java:933) > >> ~[mariadb-java-client-1.5.4.jar:?] > > > > I did some research and I found that some tuning on MariaDB timeouts > could > > solve the issue (or move it later if number records are higher), but I > > already wrote services that stop the parent transaction to use an > internal > > one, but I never saw this exception, and I am not quite sure on what I am > > doing wrong. > > > > Has anyone ever experienced a similar issue before? > > > > Could be more at code level (service implementation) or db configuration > > level? > > > > Thanks in advance, > > Giulio > > > > >
