System error, could not commit transaction XAException (null) on resuming transaction

[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


--
Giulio Speri


*Mp Styl**e Srl*
via Antonio Meucci, 37
41019 Limidi di Soliera (MO)
T 059/684916
M 334/3779851

www.mpstyle.it

[Michael Brohl-3]

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
>
>

[Giulio Speri - MpStyle Srl]

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 <[hidden email]> 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
> >
> >
>

[Giulio Speri - MpStyle Srl]

Hello Michael,

I tried to increase a larger timeout on the parent transaction on the
parent transaction, but the result was the same, but I could debug a little
bit more the service, and the error is raised in the method commit() of
TransactionUtil and searching between all the debug objects I found a
detail message of the Exception that states "*error during phase two commit*",
that is related to XA resources.

I keep investigating and I will keep you up to date.

Giulio



Il giorno ven 19 feb 2021 alle ore 23:46 Giulio Speri - MpStyle Srl <
[hidden email]> ha scritto:

> 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 <[hidden email]>
> 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
>> >
>> >
>>
>

--
Giulio Speri


*Mp Styl**e Srl*
via Antonio Meucci, 37
41019 Limidi di Soliera (MO)
T 059/684916
M 334/3779851

www.mpstyle.it

[Michael Brohl-3]

Hi Giulio,

are there any other ressources in play except OFBiz and a direct
connection to the MariaDB database? Like another database for e.g. audit
data or else being part of the XA transaction?

It seems that one of the participants of the transaction does not
respond during the two phase commit. Have you checked timeout settings
for the connection pool?

That's plain guessing without having the code and environment visible so
please excuse if it goes in the wrong direction.

Regards,

Michael Brohl

ecomify GmbH - www.ecomify.de


Am 20.02.21 um 19:43 schrieb Giulio Speri - MpStyle Srl:

> Hello Michael,
>
> I tried to increase a larger timeout on the parent transaction on the
> parent transaction, but the result was the same, but I could debug a little
> bit more the service, and the error is raised in the method commit() of
> TransactionUtil and searching between all the debug objects I found a
> detail message of the Exception that states "*error during phase two commit*",
> that is related to XA resources.
>
> I keep investigating and I will keep you up to date.
>
> Giulio
>
>
>
> Il giorno ven 19 feb 2021 alle ore 23:46 Giulio Speri - MpStyle Srl <
> [hidden email]> ha scritto:
>
>> 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 <[hidden email]>
>> 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
>>>>
>>>>

[Giulio Speri - MpStyle Srl]

Hi Michael,

thanks for the suggestions.

I think I have something: I added try-catch block around the commit
statement within the internal transaction and I find out that at a certain
point, a transaction for a page X, goes time out but since that was inside
a loop without exiting (that's my fault: I rewrote this part), it keeps
going and keep trying committing the other pages apparently without errors,
until it ends and tries to resume the parent transaction.
I think that the problem could be the timeout during the loop occured for
the page X, that "invalidates" or sets in an "error state" the XAResource
associated to it (I beg pardon if here I say sillies, but I am not an
expert of Transactions, so please correct me if I am wrong), and when the
parent transaction is resumed and the TransactionManager wait for
"affirmative" answers from all XAResources involved for the second phase
commit, it gets a negative one and thus tha failure.

I solved halving each page size (from 1000 to 500) and breaking the inner
loop if a commit fails, rolling back the current transaction and making the
service exit, with, however, a bit of deletion work done.
However just halving the page size makes the service run smoothly until the
end deleting all the records without any errors.

What do you think about it? Could that be a possible scenario for the
"error during phase two commit" exception?

I hope my explanation is good enough for you to understand, if not I ask
sorry but as I said I am still trying to understand how transactions work.


Thanks,
Giulio


Il giorno sab 20 feb 2021 alle ore 23:19 Michael Brohl <
[hidden email]> ha scritto:

> Hi Giulio,
>
> are there any other ressources in play except OFBiz and a direct
> connection to the MariaDB database? Like another database for e.g. audit
> data or else being part of the XA transaction?
>
> It seems that one of the participants of the transaction does not
> respond during the two phase commit. Have you checked timeout settings
> for the connection pool?
>
> That's plain guessing without having the code and environment visible so
> please excuse if it goes in the wrong direction.
>
> Regards,
>
> Michael Brohl
>
> ecomify GmbH - www.ecomify.de
>
>
> Am 20.02.21 um 19:43 schrieb Giulio Speri - MpStyle Srl:
> > Hello Michael,
> >
> > I tried to increase a larger timeout on the parent transaction on the
> > parent transaction, but the result was the same, but I could debug a
> little
> > bit more the service, and the error is raised in the method commit() of
> > TransactionUtil and searching between all the debug objects I found a
> > detail message of the Exception that states "*error during phase two
> commit*",
> > that is related to XA resources.
> >
> > I keep investigating and I will keep you up to date.
> >
> > Giulio
> >
> >
> >
> > Il giorno ven 19 feb 2021 alle ore 23:46 Giulio Speri - MpStyle Srl <
> > [hidden email]> ha scritto:
> >
> >> 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 <[hidden email]>
> >> 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
> >>>>
> >>>>
>

--
Giulio Speri


*Mp Styl**e Srl*
via Antonio Meucci, 37
41019 Limidi di Soliera (MO)
T 059/684916
M 334/3779851

www.mpstyle.it