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

[Michael Brohl]

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 <
giulio.sp...@mpstyle.it> 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 <michael.br...@ecomify.de>
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