DataSourceUtils.prepareConnectionForTransaction can throw SQLException when calls con.setTransactionIsolation(definition.getIsolationLevel()) [SPR-7184]

[spring-projects-issues]

Alberto Mozzone opened SPR-7184 and commented

The call to the method can throw an SQLException if the JDBC driver doesn't support the change of transaction isolation after the connection is in use. This is the case with Postgresql. The problem is that the method is called even if the transaction isolation of the connection is the same of the configured transaction isolation (in the transaction definition). In this case, the JDBC driver (Postgresql) bombs. I suggest to call the setter only when the configured transaction isolation is different than the connection one. Example:

...
// Apply specific isolation level, if any.
Integer previousIsolationLevel = null;
if (definition != null && definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) {
	if (logger.isDebugEnabled()) {
		logger.debug("Changing isolation level of JDBC Connection [" + con + "] to " +
			definition.getIsolationLevel());
	}
	previousIsolationLevel = new Integer(con.getTransactionIsolation());


	con.setTransactionIsolation(definition.getIsolationLevel());
}
...

---

Affects: 3.0.2

Referenced from: commits 853eab8

[spring-projects-issues]

Alberto Mozzone commented

Sorry! Here is the complete code suggestion:

// Apply specific isolation level, if any.
Integer previousIsolationLevel = null;
if (definition != null && definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) {
	if (logger.isDebugEnabled()) {
		logger.debug("Changing isolation level of JDBC Connection [" + con + "] to " +
			definition.getIsolationLevel());
	}
	previousIsolationLevel = new Integer(con.getTransactionIsolation());
	// Just a little check to avoid throwing an unnecessary SQLException
	if (con.getTransactionIsolation() != definition.getIsolationLevel() {
		con.setTransactionIsolation(definition.getIsolationLevel());
	}
}

[spring-projects-issues]

Juergen Hoeller commented

Good point! Fixed for 3.0.3, setting and resetting the connection isolation only when actually different.

This will be available in tonight's 3.0.3 snapshot. Feel free to give it an early try...

Juergen