Hibernate and PostgreSQL Require Transactions

Daniel E. Markle's Blog

Projects

Syndicate This Blog

Photography

Thursday, August 10. 2006

Hibernate and PostgreSQL Require Transactions

At least with the Hibernate/PostgreSQL combination (possibly other databases), you MUST encapsulate all queries, even just SELECT statements in transactions. If you do not, you will get hanging PostgreSQL processes like this:

postgres: <dbname> <dbname> <host> idle in transaction

These will eventually fill up your connection pool / exhaust your RAM / cause your application server to lock when trying to redeploy, etc. Not that I have experienced these personally...

The code to do these transactions correctly:


org.hibernate.Session dbSession = HibernateUtil.getSession();
dbSession.getTransaction().begin();

... do your work here ...

dbSession.getTransaction().commit();
dbSession.close();


Note that you cannot just do a commit and skip the transation initialization when doing selects as a shortcut; although Hibernate will start a transaction, it won't know it has and you will be stuck with a non commitable open transaction.

The other workaround would be to turn on autocommit, but this is very evil, don't do it.
Posted by Daniel E. Markle in Programming at 20:25 | Comments (5) | Trackbacks (0)

Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)

Seems strange to me, but I'm ignorant :)
Is it documented somewhere?
#1 Davide Fugazza on 2006-10-02 10:45 (Reply)
I haven't found it documented anywhere, I found out about it by snooping the SQL Hibernate was sending to the DB after noticing 'idle in transaction' PostgreSQL processes stacking up.

Note that I'm using 3.2.x, so I cannot confirm if this is a problem with earlier versions.

A few looks on google shows other people are noticing the issue though, and I can attest that many of the query samples in the Hibernate documentation will indeed leave hanging 'idle in transaction' processes, so I suspect it isn't correct behavior.
#1.1 Daniel E. Markle (Homepage) on 2006-10-02 22:40 (Reply)
I using postgres 8.1, hibernate 3.0 and tomcat jndi pool connection, i had close all connection but before i commited all transactions, also i configurate the parameter of the pool all rigth. BUT i get when i use the command ps -aux the connections of postgres in 'idle', what is the solution for that???
thanks
#1.1.1 mcorrea on 2006-12-14 15:19 (Reply)
The 'idle' processes are correct behavior; they are the shared persistent connections to the database the connection pool leaves open to accelerate database access. Only when you have hanging 'idle in transaction' threads do you have a problem, as those consume connections permanently since they are hung processes and are not shared.
#1.1.1.1 Daniel E. Markle (Homepage) on 2006-12-15 06:36 (Reply)
Dan,

Your "enclose in a transaction" worked for me as well. I wasn't able to to create/update objects until I used it.

Versions: Hibernate 3.2, postgres 8.1.8

Thanks,

Larry
#2 Larry Dennison on 2007-04-01 21:35 (Reply)

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA
 
 

Information

Quicksearch

Lexiyntax @ Twitter

@CPOSC An iPad? A closed device that disallows open source as a prize for an open source conference? Very ironic. Friday, September 3 2010
Garden Salsa flavor Sun Chips are possibly my favorite flavor. More testing/tasting required. Monday, August 30 2010
Looking for pbcopy and pbpaste from OS X on Linux? Check out 'xsel'; it automatically does the right operation without needing two commands. Thursday, August 26 2010
Watched Moon http://en.wikipedia.org/wiki/Moon_(film) tonight. Predictable but fun movie. Thursday, August 26 2010
RT @CPOSC: Central PA Open Source Conference 10/16 Harrisburg, PA. 21 speakers, 3 tracks. Tickets avail now! http://cposc.org Wednesday, August 25 2010
Eating breakfast on the road, I really miss real maple syrup. I should carry a bottle with me hidden in my coat, sneaking it onto my food. Saturday, August 14 2010
Addicted to mutt (http://mutt.org)? Use Google Contacts? Check out http://code.google.com/p/goobook/ Thursday, August 12 2010
Hershey's Ice Cream 'Extreme Chocolate' is excellent. Rich dark chocolate flavor, not too sweet, and almost black in color. Tuesday, August 10 2010
Here I am at the Debconf baseball game http://www.flickr.com/photos/aigarius/4863382564/in/set-72157624469399089/ Monday, August 9 2010
Aigarius shot me at Debconf http://www.flickr.com/photos/aigarius/4869036038/in/set-72157624469399089/ Monday, August 9 2010

Categories



All categories

Archives