Is it possible to disable the thread-bound Connection behavior?

[Antony Warner]

Hi,

We have an application that is beginning to make use of JdbcTemplate. I notice that this class, via DataSourceUtils, will get a Connection from the supplied DataSource then hold on to it for the duration of the transaction. This is referred to as a thread-bound Connection in the javadocs of DataSourceUtils.

Is there any way to turn this behaviour off, so that JdbcTemplate will call Connection.close() (returning the Connection to the application servers pool) rather than put the Connection into a ConnectionHolder and hold on to it?

One possible reason for turning this behavior off is that, relatively speaking, a transaction might span some time, with for example some database access at the beginning via JdbcTemplate to read some data, then lot's of non-database business logic (during which time the Connection is held by Spring in a ConnectionHolder) followed by some more database access at the end.

Thanks.

[karldmoore]

If you want to release the connection more quickly, I would have thought either running without transactions or narrowing the scope of the transactions would be your best bet. If you just wrap the specific calls in a transactions then you should be able to achieve what you are after. Has this actually become a problem for you?

[Antony Warner]

Thanks for your input. The scope of the transactions is the usual j2ee way: a service layer of spring managed beans with a transaction wrapping each service invocation. So the scope of the transactions is reasonable I think.

Has this actually become a problem for you?

That's a good question. We are having SQLExceptions on calls to Connection.setTransactionIsolation on WebSphere that has coincided with introducing SpringJdbc (so we currently have a mix of straight JDBC and SpringJdbc). There is some debate here whether calling Connection.setTransactionIsolation on a Connection supplied by WebSphere, or any other application server, is even allowed, however previous to this particular issue JBoss, WebSphere and WebLogic seemed happy enough with this method of setting isolation levels. I don't know if this problem surfacing at the same time as the introduction of SpringJdbc is anything more than a coincidence, but it is a place to start investigating. We don't understand what's happening yet. One scenario that caused a different (but possibly related issue) was:

1. A single SpringJdbc SQL update outside of a transaction
2. Transaction started via UserTransaction, several statements inside a bean managed transaction, transaction commited.
3. Another single SpringJdbc SQL update outside of a transaction

WebSphere didn't seem to like that SpringJdbc was trying to use a Connection received outside of a transaction (in 1.) inside a transaction (in 2.). I don't have the exact error message now but the gist of it was a mix of 1 phase and 2 phase participants in a transaction. That was fixed by putting 1. and 3. inside their own transactions. Based on the above, there is possibly some difference as far as WebSphere is concerned with Connections handed to SpringJdbc and those handed to our straight JDBC code (at this point you see I am beginning to falter in understanding what is happening ). Hence the question, if we could avoid Spring holding onto the Connection when it has finished with it, we can perhaps get a clue as to what is going on or at least eliminate it from our inquiries.

[Antony Warner]

A quick update. I have put some code into our DAO framework code that pulls the rug out from underneath Spring by releasing any thread-bound connection it finds (this is a hack that involves a subclass of ConnectionHolder). The effect of this is that, prior to any straight JDBC happening, any thread-bound connection hanging around as a result of JdbcTemplate usage is released (Connection.close()). This has stopped WebSphere complaining, and points to some issues with mixing straight JDBC/SpringJdbc, where JdbcTemplate usage all takes place under a single thread-bound Connection whereas the straight JDBC follows the pattern of getting a Connection from the DataSource, using it, then calling Connection.close() on it.

Ideally we would like to configure the Spring framework to follow this same pattern of releasing the Connection once JdbcTemplate has finished executing the SQL, which isn't currently possible as far as I can tell. Aside from our issues, as hinted at in my first post I can a further justification for this in the case of potentially long-running transactions.

Is there a possibility of this sort of feature making it into Spring? Are there any suggestions of a neat way to persuade Spring to release Connections after each use?

[Antony Warner]

I've realized (by reading the manual, embarrassingly) that "Transaction synchronization" is about binding resources to the current thread, and is optional. So the answer is yes, you can have resources such as a JDBC connection be released straight-away by configuring the transaction manager to never use transaction synchronization e.g.

<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
    <property name="transactionSynchronization" value="2" />
</bean>