Hi,

I'd like to find out how Hibernate deals with JDBC connections obtained from connection pool. My app runs in Sun app server. Connection pool points to Oracle db. I use CMT for transaction management.

When I need to get Hibernate session I call SessionFactory.getCurrentSession(). I understand that session is bound to JTA transaction.

What I don't understand is the underlaying management of db connections (obtained from the pool) by Hibernate session. When I call SessionFactory.getCurrentSession() does the session check out a db connection from the pool and use the same connection for the duration of the transaction? So all other calls to SessionFactory.getCurrentSession() performed within the same transaction end up using sessions bound to the same db connection?

Or is the session always the same (within one transaction), but connections are dynamically checked out from the pool?

I'd appreciate a verbose explanation of how this works. I need to call a dbms package in Oracle to set user login (some tables in the db are restricted to certain users only), and I'd like to find out when should I make that call. If all data coming from session is bound to single connection, that would make things easy.
Regards,

Bratek

Probably an incomplete explanation...

When using Hibernate with a connection pool, whenever a Session needs a JDBC Connection, it allocates it from the pool.

When the session is done, it's behavior depends upon ConnectionReleaseMode. For Hibernate 3.1, the default ConnectionReleaseMode is AFTER_TRANSACTION (was ON_CLOSE), meaning that a Session releases Connection back to pool at the transaction end (commit or rollback).

Since you should always use transaction, this means that between your transactions the Session does not 'have' any JDBC Connection - it is back to the pool.

When using a JTA datasource, you may configure ConnectionReleaseMode to AFTEER_STATEMENT, meaning that Session will release Connection after each statement ... but there are some caviats ... and I don't use JTA.


Note there is a method Session.isConected(). The Session interface documemntation is somewhat incorrect, because it will return 'true' when a Session has a JDBC Connection, OR IT HAS MEANS of getting one (such as allocating it from a pool - in essence, normally it always returns 'true'.

Now, the above is all nice, EXCEPT:
There is a bug (or 'feature' at least in 3.1): When you use lazy fetching, and lazy fetch occurs outside of your transaction, Session will allocate but NOT release JDBC Connection.
This is VERY UNPLEASANT, as your application may HOG multiple connections - and in fact, HANG when your pool connection limit is too low (our thick client with connection limit of 5 had that nasty habbit).

My current workaround is periodically calling ConnectionManager.afterTransaction() on sessions which are not in use and have a non-null ConnectionManager.connection (requires introspection = private member).

[And yes, I am trying to put together a simple case for bug report]

You mean connections with auto-commit transactions and you probably didn't turn that on in the Hibernate configuration. So the Session will start a transaction (well, the JDBC connection provider will) when it needs a connection "outside of transaction demarcation boundaries".

Well, I do not want auto-commit.
I want all my transactions to be explicit and well-behaved, and there has been thousands of warning about dangers of tinkering with (Hibernate owned) JDBC Connection auto-commit mode.

The problem I describe is that despite using default settings, whenever lazy load happens "outside transaction boundaries", I end up with a JDBC Connection taken out of the pool, and sitting attached to my Session's ConnectionManager - unless I make another transaction on that Session (which often does not happen for a while).

And scanning all reference documentation, especially the ConnectionReleaseMode, I do not see any configuration setting such as "release connection after lazy load".
I hope you do not refer to:

hibernate.connection.autocommit
Enables autocommit for JDBC pooled connections (not recommended).
eg. true | false

Well, I do not want auto-commit.
I want all my transactions to be explicit and well-behaved, and there has been thousands of warning about dangers of tinkering with (Hibernate owned) JDBC Connection auto-commit mode.

The problem I describe is that despite using default settings, whenever lazy load happens "outside transaction boundaries", I end up with a JDBC Connection taken out of the pool, and sitting attached to my Session's ConnectionManager - unless I make another transaction on that Session (which often does not happen for a while).

And scanning all reference documentation, especially the ConnectionReleaseMode, I do not see any configuration setting such as "release connection after lazy load".
I hope you do not refer to:

hibernate.connection.autocommit
Enables autocommit for JDBC pooled connections (not recommended).
eg. true | false

Of course I do. How else it is supposed to work? There is no magic other mode, you either get a connection with explicit transaction boundaries or you get one in auto-commit mode. Not enabling auto-commit and using the Session outside of transaction boundaries is not going to work. That is what the switch is for.

And if you want to know why this is "not recommended", chapter 10 of the book in my signature explains that in much detail. The only real usecase for it I've found is in desktop applications that access a database server directly with Hibernate.

Christian, thanks for your attention to this.
I am sorry but I am probably not explaining it correctly.

I am NOT using Session "outside transaction boundaries".

Hibernate is.

Hibernate is using Session "outside transaction boundaries", to perform lazy intialization - and that lazy intialization, performed by Hibernate outside MY transaction boundaries, leaves an UNRELEASED JDBC Connection.

My unit test is using a lot of "our" layer over Hibernate, so here goes a simple stranscription of my problem:

TestInst class has a set of TestData, lazily loaded (see mapping below).



The boolean hasJDBCConnection(Session) essentially returns (via introspection)
ses.getJDBCContext().getConnectionManager().connection != null;
as ses.isConnected() is not very usefull

The TestInst to TestData mapping is:

Oops, the line
Iterator<TestData> iter = inst_2.getDataSet().iterator();
should be
Iterator<TestData> iter = inst.getDataSet().iterator();

Going back to the original question:

"If all data coming from session is bound to single connection, that would make things easy. "

I am afraid that the answer depends also upon the connection pool, but with most pool implementations, individual calls to allocate a connection from a pool (caused by individual transactions) will each return different JDBC Connection (or, more precisely, Connection wrapper).

However, changing the ConnectionReleaseMode to ON_CLOSE would keep the same connection for the entire Session duration - which may be what the original poster wants.

?! What's the difference. You need to enable auto-commit mode for that or wrap your lazy initialization in a transaction block. If you do neither, it breaks. No surprise there.

"! What's the difference. You need to enable auto-commit mode for that or wrap your lazy initialization in a transaction block".

Well, I dare to disagree.

"What's the difference?"
The difference is that it is not the application code causing JDBC Connection "leak". It is the Hibernate implementation, violating the contract defined by ConnectionreleaseMode. Hibernate re-allocated JDBC Connection without my knowledge, outside of my control.

1) "You need to enable auto-commit mode for ..."
I do not see any need to enable auto-commit mode (with all it's drawbacks), because I am not using it - and I am not commiting any data. In fact I am NOT accessing the database at all - Hibernate is. I am using Hibernate as advertised - to perform lazy loads.

2) "Wrap your lazy initialization in a transaction block"
You must be tired to suggest that. The whole purpose of lazy intialization is that it happens without my code knowing when and where it happens - this being the main 'appeal' of Hibernate. Are you serious to suggest that I must wrap every access to my Hibernate-persisted object using lazy intialization in a transaction (with my code not even having access to the session, hidden in the proxy)?

My simple explanation is as follows:
When Hibernate was using ConnectionReleaseMode.ON_CLOSE, it was perfectly fine to keep JDBC Connection 'unreleased' when performing a lazy load.
When Hibernate 3.1 was changed to default ConnectionReleaseMode.AFTER_TRANSACTION, nobody noticed that lazy loads re-allocate the connection (without releasing it) -- because in most cases, applications perform additional transactions after the lazy load, and such transactions then release the connection. The bug only applies when no other transaction takes place in that session, and session remains open for a substantial time period.

You seem to argue that 'lazy load' should be an exception to the ConnectionReleaseMode setting, but I claim this to be a serious bug. After all, Hibernate should surround "lazy load" with a (Hibernate) transaction.

The bug severity' is underscored by the fact that:
- there are NO interfaces allowing application to detect that session has an un-released connection
- there are NO interfaces allowing application to release such un-released connection
- even if there were such interface, how would application 'know' that a lazy load occured? (use an interceptor??)
- un-released connections can (and DO) hang-up application

Auto-commit mode has nothing to do with "committing data". Hibernate will release the connection, just as you wish, if you enable auto-commit mode. This is how this mode works. This is also the fourth and last time I'm repeating myself.

OK, again and again, I appreciate your patience with a dumbkopf like me.

You keep saying that to fix the problem, I should configure Hibernate to use autocommit.
Granted, that releases JDBC Conection - because it essentially changes ConnectionReleaseMode to AFTER_STATEMENT

However, using auto-commit mode is UNACCEPTABLE.
Using auto-commit=true can make sense in some specific scenario, but as you so eloquently describe in your book,
"Auto commit mode is almost always inappropriate in application"
(in my case application twice as big as Hibernate, using Hibernate both as a thick client and on JBoss side)

Hibernate InAction, 5.1.1, JDBC and JTA transactions clearly states:
"If a database connection is in auto commit mode, the database transaction will be commited immediatelly after each SQL statement,
and a new transaction will be started".
(I am sorry i did not buy your latest book yet - I did not plan on spending so much effort on Hibernate troubleshooting).

Unless there is yet another documentation surprize (and Hibernate autocommit is a different animal than JDBC autocommit),
setting hibernate.connection.autocommit to 'true' means that each statement will be commited separately - destroying my transaction integrity.
I have tons of fairly complex transactions, which need to either commit or be completely rolled back (as often happens on deadlocks).

What I CONSIDER A BUG is:
Hibernate "lazy load" does not honor ConnectionReleaseMode.AFTER_TRANSACTION, leaving (unexpectedly) allocated connections.

WHY I CONSIDER THIS A BUG is:

1) Lazy load is one of the fundamental Hibernate fetures

2) I can NOT find a piece of documentation that would tell me that
ConnectionReleaseMode.AFTER_TRANSACTION is NOT (meant to be) honored by a lazy load.

3) For a real application, which has to use autocommit = false, there is (currently) no workaround.
In fact, the application has no documented way of detecting the problem.
The application will simply 'hog' connections - behaving as ConnectionReleaseMode.ON_CLOSE.

4) From a logical standpoint, I see no reason WHY a lazy load could not 'restore' the session state.
At the "lazy load" begin, connection was released.
After the "lazy load", connection should be released as well.
(Granted, interceptor log shows that lazy load does NOT report using a transaction, but thare is clearly a lazy load start/end).

All that said, I was working on bugreport with (hopefully) a fix proposal.
I can not imagine that fixing this problem would have adverse effect.

Unfortunately, (in Denver, CO), we are "snowed in" again, and hence I am working from my laptop, which has no Hibernate source as of yet.

This is the mode you want, right? So why don't you do what I keep telling you and argue with me instead?



Make up your mind! Either you want it or not. You can't have anything else because there is nothing else.



Reflect on the words "almost always".



AT THE JDBC/SQL LEVEL.

This is the auto-commit behavior AT THE HIBERNATE LEVEL if you ENABLE it: The Session gets a connection, the connection is (put) in auto-commit mode, the Session executes a SQL statement (maybe to initialize a proxy or collection), the statement is committed immediately, the Session returns the connection to the pool.

THERE IS NO OTHER MODE if you don't want to wrap an EXPLICIT transaction block around your lazy loading (which is what most applications do, see "almost always").

Your magic "Hibernate should wrap a Hibernate transaction around this" is exactly what the auto-commit mode already does. YOU NEED TO ENABLE IT.



You sure seem to expect that others put in a lot of effort to explain things to you. Nice. EOD from my side.