Postgresql autocommit

#1

Flowable complains that autocommit is enabled when I go to http://localhost:8080/flowable-rest/service/runtime/tasks/xxxxxxxx/variables.

org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
  • CentOS 7.6
  • Flowable v6.4.0
  • Tomcat v8.5.42
  • PostgreSQL 10.7
  • pgSQL connector v42.2.6

db configuration

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://x.x.x.x:5432/flowable?autocommit=false
spring.datasource.username=flowable
spring.datasource.password=***hidden***

note: the db property ?autocommit=false doesn’t work, btw there is no option like this (https://github.com/pgjdbc/pgjdbc#connection-properties) so auto-commit cannot be disabled in the connection URL

#2

How are you running Flowable? The UI’s or the engine embedded?
Autocommit should be disabled (as multiple statements are added together in one transaction), so I wonder if it’s maybe a system default of some sorts in this setup?

#3

It’s the standard suite of Flowable applications within Tomcat, using an external PostgreSQL database which has autocommit enabled. The problem is that the database connection still uses autocommit no matter what we do. Here are a couple of settings we tried to add to application.properties:
spring.datasource.auto-commit=false
spring.datasource.hikari.auto-commit=false
spring.datasource.defaultAutoCommit=false
None of these had any effect, including various variants we found on the internet. So we tried setting up the JNDI data source as described in the documentation. This has apparently replaced the default Hikari connection pool as there were no Hikari-related messages on startup and we saw some messages from Apache Commons DBCP 2 instead. We also confirmed that the data source is actually used by changing the number of connections in the JNDI DS configuration and observing that the number of connections on DB end has indeed changed accordingly. However, despite the connection number setting working, the autocommit setting (defaultAutoCommit="false") had no effect.

From what I’ve learned so far, it seems that the database setup in Flowable consists of multiple layers depending on how it’s configured, so I suspect that one of the layers above the DataSource might be overriding the settings and setting autocommit back on. I’ve seen this issue for example: https://github.com/brettwooldridge/HikariCP/issues/1116 . Or maybe it could be MyBatis?

Anyway, the bottom line is that we cannot turn autocommit off on the DB level, so any advice is welcome. If Flowable depends on having a connection without autocommit, it would be nice to make sure the connection has autocommit explicitly disabled. If this is the case, let me know and I can make the necessary changes and turn this into a pull request.

#4

Even more weirdly, I discovered that in PostgreSQL, autocommit is enabled by default. So this turns into one of those issues where I’m wondering how Flowable could ever work with PostgreSQL without explicitly disabling autocommit.

#5

@joram I was able to find the root cause, I think it could be a bug in how the V5 compatibility is implemented. It is quite complicated, so please bear with me.
When a command such as GetExecutionVariablesCmd is executed, the operation is performed at the end of an Interceptor chain, one of which is CommandContextInterceptor, which holds the context for the command. When the CommandContext.execute() method is finished, the CommandContext is closed, which triggers the closeSessions() method. After a sequence of close() calls on deeper and deeper objects, the JdbcTransaction within MyBatis is closed. This inner transaction shares a connection with the outer transaction, so when it is closed, the connection is reverted to the standard state of having autocommit enabled. The obtained data is returned back as a result of the V5 command and the whole process is almost finished. So the outer transaction is closed and commit() is called on the same connection which just had its autocommit turned back on and an exception is thrown.

For reference, here’s the stack trace showing where the connection is reverted to autocommit (not where the application crashes): https://pastebin.com/6r7BN7Tj

Normally, MyBatis has a ManagedTransaction and thus ignores calls to commit(), but in the V5 session it has JdbcTransaction instead, and so it’s trying to manage the lifecycle. I’m not sure why this happens.

Do you have any ideas for a workaround? Right now it seems that it’s just not possible to run Flowable with V5 compatibility on PostgreSQL with autocommit on.

#6

Thanks for the (very!) detailed analysis, that is very helpful.

Indeed, and the v5 context shouldn’t create a new transaction to start with, it should reuse what’s started by the outer interceptors. So this feels like it’s not taking over the settings from the outer (v6) engine.

Do you happen to have a unit test that we could give a try? Or a way to reproduce it? Or can it be reproduced by doing any v5 call on a default postgres install?