Information
Photography
Syndicate This Blog
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:
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...
postgres: <dbname> <dbname> <host> idle in transactionThese 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.
Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as
(Linear | Threaded)
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.
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.
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
thanks
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.
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
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