We are migrating from JBoss 4.2.3 GA and MS SQL Server 2008 R2 to Wildfly 11 and PostgreSQL 10. We use the Hibernate version shipped with Wildfly (5.1.10.Final)
We have a very complex application where we use an external API that cannot be debugged by myself. We are using EJB remote proxies of this API for executing operations.
There are several DB-Operations executed with JPA (NamedQuery on an Entity) in one container managed transaction (xa-datasource configured in Wildfly 11).
The NamedQuery works fine for most cases, but there's one case an error occurs without any visible notice in the logfiles.
The transaction is marked with status rollback, but I don't have any idea why. No exception, no debug message, nothing.
The only difference to the same NamedQuery in this scenario is that rows are deleted from the tables a few steps before within the same transaction.
We already had some problems with PostgreSQL and our application as PostgreSQL does not implement standard transaction isolation level "read uncommited" as the MS SQL Server does, see https://www.postgresql.org/docs/10/static/transaction-iso.html
I don't know if this may be related to my problem.
The only guess I have right now is an error and supressed exception in the external API or org.jboss.jca.adapters.jdbc.WrapperDataSource.
This is the relevant Stacktrace where Hibernate fails to prepare the statement:
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1934) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1903) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881) at org.hibernate.loader.Loader.doQuery(Loader.java:925) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342) at org.hibernate.loader.Loader.doList(Loader.java:2622) at org.hibernate.loader.Loader.doList(Loader.java:2605) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434) at org.hibernate.loader.Loader.list(Loader.java:2429) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) ... 172 more
Caused by: java.sql.SQLException: IJ031070: Transaction cannot proceed: STATUS_MARKED_ROLLBACK at org.jboss.jca.adapters.jdbc.WrapperDataSource.checkTransactionActive(WrapperDataSource.java:245) at org.jboss.jca.adapters.jdbc.WrappedConnection.checkTransactionActive(WrappedConnection.java:1928) at org.jboss.jca.adapters.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:1943) at org.jboss.jca.adapters.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:1917) at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:447) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) ... 189 more
Does anone have any idea what might cause the transaction to be marked status rollback without any notice?
I've found the real problem. As already guessed there was an exception thrown that was swallowed by the application and caused the transaction was set to status ROLLBACK.
The stacktrace I posted was a following error, because preparing a statement is not allowed when a transaction is already set to ROLLBACK (check in WrapperDataSource).
I finally found the error after setting the log level of Arjuna to trace.
The real exception was thrown because of an inconsistency in the data (business logic) and was coming of different transaction/connection configuration and behaviour of SQL Server and PostgreSQL. But that's a different story...