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
--
Giulio Speri
*Mp Styl**e Srl*
via Antonio Meucci, 37
41019 Limidi di Soliera (MO)
T 059/684916
M 334/3779851
www.mpstyle.it
