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?

#7

@joram Hi, your note about taking over the settings from the outer engine sent me on another long debugging session where I found that DefaultProcessEngineFactory was being used to create the engine. When I saw the type hierarchy of the class, I immediately noticed that there is another class named DefaultSpringProcessEngineFactory. Inside, I saw that it does things related to contexts and transactions, so by then I was pretty sure that I’m on the right track.

After a while I discovered that the reason why DefaultProcessEngineFactory was used is that when setting up the engine configuration, we were only calling the setFlowable5CompatibilityEnabled(true) method, but we were not setting the Flowable5CompatibilityHandlerFactory to SpringFlowable5CompatibilityHandlerFactory as described int he migration guide. Also the flowable5-spring-compatibility JAR was not even present on the server. With other databases, the engine apparently still worked fine despite being misconfigured. After fixing these issues, the error no longer appears. So I believe the mystery is solved and there is no bug in Flowable.

I am a bit worried about the servers with different DBs though. Since they have autocommit off by default, I think that some situations might have occurred where the connection was mishandled by the V5 engine when being a part of a transaction originating from the outer V6 engine, and then the V6 engine may have tried to rollback a transaction which was already committed by the V5 engine. Do you think this could happen?

#8

That’s great to hear (and nice work on that low-level debugging)!

I wonder if there’s a way we could detect this setup and throw an error when running without the jar.

Yes, with the setup as outlined above I think that could happen. However, the exception will bubble up to v6, as Flowable always rethrows exceptions. The exception will cause the v6 transaction to roll back too. So from a data correctness point of view, this is ok. From a resource perspective, two transactions are used which isn’t optimal.

Also note here: https://github.com/flowable/flowable-engine/blob/master/modules/flowable5-compatibility/src/main/java/org/flowable/compatibility/DefaultFlowable5CompatibilityHandler.java#L1149 that all specific v5 exceptions (like an optimistic locking exception) are captured an rethrowed as their v6 counterpart.

#9

@joram Thank you, so hopefully nothing bad should happen with the DB. This is good because we had this setup running for quite some time on another DB without any issues obviously stemming from this.

I looked into the options of detecting this setup and it seems to be possible if we check whether some core component class of Spring, such as ApplicationContext, is loaded. Would you like to see a pull request for this?