Why useFirebirdAutocommit=true won't work and additionally disable JDBC autocommit

Question

I use to connect with Firebird:

org.apache.commons.dbcp2.BasicDataSource;

But if I set:

ds.addConnectionProperty("autoCommit", "true");
ds.addConnectionProperty(FBConnectionProperties.USE_FIREBIRD_AUTOCOMMIT, "true");

or

ds.addConnectionProperty("useFirebirdAutocommit", "true");

Autocommit won't work at all. It only works, if I will not use "useFirebirdAutocommit" property.

Or if I add isc_tpb_autocommit it also works:

ds.addConnectionProperty("TRANSACTION_READ_COMMITTED", "isc_tpb_read_committed,"+
                "isc_tpb_rec_version,isc_tpb_write,isc_tpb_wait,**isc_tpb_autocommit**");

It seems that use of internal server auto-commit, disable autocommiting by driver, but internal server auto-commit not work.

By the way - in result of that, I was sure that I have autocommit - but each update make another version of record with not committed transaction that could not be sweeped. Database file grow very fast from 3MB to 15GB in few days 24/7 (about 1000 updates per second).

Answer 1

In this answer, I'm assuming the growth of record versions and file size are the only problems you're experiencing. If that is not the case, then please update your question and provide more details of the problem.

As described in the documentation, when you enable the useFirebirdAutocommit option, then:

Jaybird itself will not commit until connection close (or switching to autoCommit=false).

In other words, the fact that the driver does not commit in auto-commit, when useFirebirdAutocommit is enabled, is intended behaviour of this feature (in fact, it is its whole raison d'être). Instead, the server will internally perform a commit retain after each executed statement, while preserving the transaction handle. This will make the changes visible to other transactions, while avoiding the overhead of extra network round-trips.

However, the use of commit retain has downsides: because it retains the original transaction handle, it will pin the original transaction and the transactions it is interested in, causing garbage collection to be inhibited until the transaction handle is actually committed. In addition, when using a transaction isolation level other than read committed, it will not change the visible record set.

So, if you use this with long-running connections that never use actual transactions (i.e. you never disable auto-commit), you will inhibit garbage collection for a very long time. If you have a lot of updates, this can indeed lead to extreme growth of record versions and the database file itself.

This is the primary reason this feature is marked experimental, and why the documentation says:

Support for this option is experimental, and should only be enabled if you 1) know what you’re doing, and 2) really need this feature. Internally isc_tpb_autocommit uses commit_retaining, which means that using this feature may increase the transaction gap with associated sweep and garbage collection impact.

Unless you use short-living connections, or otherwise switch between auto-commit enabled and disabled regularly, then the useFirebirdAutocommit mode is not something you should be using.

If you do insist on using this feature, then make sure that your connections are short-lived (e.g. reduce the maximum connection lifetime, DBCP property maxConnLifetimeMillis), or that you regularly force a real commit by disabling (and enabling) auto-commit.

These downsides are the reason this feature is experimental, and will likely always remain so.

As to why redefining TRANSACTION_READ_COMMITTED does (seem to) work, is because - assuming useFirebirdAutocommit is not enabled - the auto-commit transaction will still be really committed by the driver after each statement completion. Manually adding isc_tpb_autocommit to a transaction definition when you only ever use auto-commit has little to no benefit (in fact, it has slightly more overhead because of the internal commit retain and the actual commit).

Disclaimer: I am the maintainer of Jaybird