Setting TRANSACTION_READ_COMMITED on Interbase Configuration properties for Interbase Interclient Hibernate


#1

Our project is using Firebird / Interbase databases. In Firebird we can set additional
Configuration parameters via the following code:

However, this does not apply (work) for Interbase. Where are the capabilities of the Interbase hibernate driver documented?

import org.hibernate.cfg.Configuration;

public class Test{

public final Configuration getConfiguration(final ConnectParameters connectParams) {
	
	final String configFile = connectParams.getConfigFile();
	final URL configLoc = this.getClass().getResource(configFile);
	final Configuration config= new Configuration().configure(configLoc);

	config.setProperty("hibernate.connection.username", connectParams.getUsername());
	config.setProperty("hibernate.connection.password", connectParams.getPassword());
	config.setProperty("hibernate.connection.url", connectParams.getConnectString());
	config.setProperty("hibernate.connection.dialect", connectParams.getDialect());
	config.setProperty("hibernate.connection.driver_class", connectParams.getDriverClass());

	 if (connectParams.getConnectString().contains("firebird")) {
	  setupConfig.setProperty(
	  "hibernate.connection.TRANSACTION_READ_COMMITTED",
	  "isc_tpb_nowait,isc_tpb_read_committed,isc_tpb_no_rec_version");
	 }

	return config;

}

}


#2

If you only use the hibernate.connection configuration, Hibernate will pick the DriverManagerConnectionProvider which is not meant for production.

Better use the HikariConnectionProvider and set the isolation level at the connection pool level:

HikariConfig config = new HikariConfig();
config.setAutoCommit(false);
config.setTransactionIsolation("TRANSACTION_READ_COMMITTED");

HikariCP is one of the best connection pool in Java.

You might want to set the hibernate.connection.provider_disables_autocommit property to delay the connection acquisition. For more details, check out this article.


#3

Thanks for the prompt reply Vlad. I’ll check it out and will get back with you. Your book looks very informative.


#4

Thanks. Check out the the testimonials I got from my readers.


#5

This doesn’t address the problem of the OP. The OP wants to redefine a transaction isolation level, not set a transaction isolation level. This is possible with Jaybird, the JDBC driver for Firebird. Jaybird has a default for READ_COMMITTED as isc_tpb_read_committed,isc_tpb_rec_version,isc_tpb_write,isc_tpb_wait (or read committed, read/write, read latest committed record version, wait for locks).

The configuration shown redefines this to read committed, read/write, wait for rows that have an uncommitted record version (ie being modified by a concurrent transaction), which combined with isc_tpb_nowait means ‘fail immediately when reading/updating a row with an uncommitted version’), and don’t wait for locks (ie fail when you can’t get a lock immediately).

Firebird was forked from InterBase about 18 years ago, and the OP wants to do the same for InterBase. I do not know if the InterBase JDBC driver has a similar configuration option.


#6

Gentlemen,

Thanks for the dialog on this subject. I did receive a reply overnight from someone in the Interbase Expert Group. Those parameters are changeable in JDBC via the following:

setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
setLockResolution(Connection.LOCK_RESOLUTION_NO_WAIT);
setVersionAcknowledgement(Connection.IGNORE_UNCOMMITTED_RECORD_VERSIONS_ON_READ);

But, as Vlad pointed out we’re currently using a default hibernate.connection package and cannot access the Connection object easily. I think what i’m going to do is to pick a Connection pool manager (probably Hikari) and then, since I have direct access to the Connection object I should be able to apply the Connection object settings listed above. My original problem was since our team was using the hibernate default connection pool we could not gain access to the underlying JDBC Connection object easily.