Wildfly 29 - Transaction rollback

[Hasitha Wijesekara]

I am using Wildfly 29 with Postgres 16 database. In Wildfly log, there is roll back errors under the error codes STATUS_MARKED_ROLLBACK, STATUS_ROLLING_BACK and STATUS_ROLLEDBACK. When these errors happen Postgres database has rollback connections in pg_stat_activity table.

The issue is this rollback connection never removed and keep it forever. That's mean Wildfly always create new connection rather reuse. This will be end up in total Wildfly connection pool is used. What happen is after that Wildfly could't get any new connection and application get stuck. I am using container managed connections. What is cause for this rollback connections not removed?

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uk_rpas_report_id"
  Detail: Key (rpas_report_id)=(13688) already exists.
at org.pos...@42.6.0//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
at org.pos...@42.6.0//org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
at org.pos...@42.6.0//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
at org.pos...@42.6.0//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
at org.pos...@42.6.0//org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
at org.pos...@42.6.0//org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
at org.pos...@42.6.0//org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152)
at jdk.internal.reflect.GeneratedMethodAccessor736.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.pos...@42.6.0//org.postgresql.ds.PGPooledConnection$StatementHandler.invoke(PGPooledConnection.java:441)
at org.pos...@42.6.0//com.sun.proxy.$Proxy200.executeUpdate(Unknown Source)
at org.jboss.ironjac...@3.0.3.Final//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
at org.hi...@6.2.6.Final//org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:275)
... 348 more

2024-05-06 13:12:44,241 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 4) SQL Error: 0, SQLState: null
2024-05-06 13:12:44,241 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 4) IJ031070: Transaction cannot proceed: STATUS_MARKED_ROLLBACK

[Scott Marlow]

Try updating your datasource definition to set use-ccm="false" which some users found helpful as per comments on the https://issues.redhat.com/browse/WFLY-19082 issue.  When use-ccm is "false", WildFly will not automatically handle (application) leaked database connections and will also not handle lazy connection enlistment (e.g. connection is obtained from connection pool before JTA transaction is started and later when JTA is started with the connection still open, the connection is associated with the JTA tx, you lose this feature also).

Regardless, please try use-ccm="false" and let us know if it helps or not.  It will be kind of weird if setting use-ccm="false" helps solve the connection pool leak problem your experiencing but please do try it.

Scott

--
You received this message because you are subscribed to the Google Groups "WildFly" group.
To unsubscribe from this group and stop receiving emails from it, send an email to wildfly+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/wildfly/49325773-44f9-4e87-95d9-4007d116bffdn%40googlegroups.com.

[Hasitha Wijesekara]

Thanks Scott, I tried this  use-ccm="false" but it's not successful. Still not removed ROLLBACK connections.

My pool size is 10. There are 2 ROLLBACKs still there. This is not removed or reused.

To view this discussion on the web visit https://groups.google.com/d/msgid/wildfly/735af59c-a816-4b94-b792-d9fba514c7a8%40redhat.com.

[Hasitha Wijesekara]

We are recently migrate WidlFly 18 to  WidlFly 29. I switched back to WidlFly 18 again and do the same things. Then there is no any connection hanging issue happen when transaction get rolled back. Is that something related to Wildfly 29 or any library (Hibernate, JPA) incompatibility issue?  

Thanks.

[Scott Marlow]

On 5/9/24 7:12 AM, Hasitha Wijesekara wrote:

We are recently migrate WidlFly 18 to  WidlFly 29. I switched back to WidlFly 18 again and do the same things. Then there is no any connection hanging issue happen when transaction get rolled back. Is that something related to Wildfly 29 or any library (Hibernate, JPA) incompatibility issue? 

With WildFly 29, are the leaked database connections also leaking on the the WildFly side?  Or is it only noticed on the database server side via pg_stat_activity table?  Thanks again for the discussion on this!

Scott

[Hasitha Wijesekara]

Thanks for the quick response.

The issue was our application connection leaking. It is not related to Wildfly 29.  We resolved it.

Rgs,

Hasitha