What does release mode mean and what impact does it have?

If I am using jtaTransaction manager with a "read committed" isolation level, if I start a transaction will it not then fetch a connection from the pool and assign it to the session for the duration of the session?

It could do this lazily and that would make more sense and it would also mean that the release mode would come into play.

Release mode of "on_close" is simple, the connection is held for the life of the session and only released when the session closes.

But after_transaction and after_statement are a little nebulous.

If I'm only reading from the persistent store then it makes sense that after each statement the connection would be released and it could be made available to other connections, but if I then do an update on that session and a flush occurs, from then on, I have to keep the same connection surely? Can other sessions then use that one to read with?

The documentation says that I can only use after_statement if I can get back the same connection again - but in this case, surely some other session could not use that transaction to run update/inserts on? The two will interfere with one another? So do I gain anything by using after_statement release mode in this case?

Finally, my application, even though it is using a jtaTransaction manager defaults to on_close release mode. I think this is because I'm demarcating transactions with spring and not explicitly setting the transaction manager on the session factory. I defer the transaction manager creation till later.

How inefficient is it to use the on_close release mode rather than other recommendations?

You definitely don't want ON_CLOSE. The manual IMHO is pretty clear about what each of these mean. I'm not sure what pool & TX manager you're using, but for JBoss AS it is important to use AFTER_STATEMENT as the JCA DataSource handles the actual caching and tracking of connections in a transaction.

http://www.redhat.com/docs/manuals/jbos ... Modes.html

Unless you've got a pretty crazy use case, the default (AUTO) is going to pick the right strategy based on ConnectionProvider and transaction lookup configuration.

-Chris

I read the cited section in manual and thought it over thoroughly before coming to this forum and was bothered by the same question as mwiles. Of course, I can just set release mode to after_statement as the manual suggests and not bother, but I'd like to understand what real implications does it have and I can't see how it could help.

I never heard of JDBC connections supporting multiple concurrent transactions; if a datasource returns the same connection to two transactions, even after the first one releases it (but before it's completion), then only one of them could be permitted to use it to modify data without violating isolation. This would mean that all the time we'd be seeing errors caused by a transaction trying to execute a DML statement on a connection already used by another transaction.

I guess the data source could try to return the same connection to a second transaction before the transaction that used and released it completes only when both have oracle's isolation level of READ_ONLY or in a similar situation. Even then, one transaction could (or couldn't it?) change the isolation level explicitly.

My best guess would be that releasing the connection doesn't return it to the pool, but releases all resources associated with executed statement (resultsets, etc.), but it's NOT what the reference says.

Could anybody savy enlighten me what advantage can I really get and in what situations (what AS, for example) ?