facebook

Postgresql "idle in transaction" annoyance.

  1. MyEclipse IDE
  2.  > 
  3. Off Topic
Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #258261 Reply

    Hi folks,

    Hoping someone might have a quick answer for this. I’m developing a web applicatoin where I’m using Hibernate 3.0 against a Postgresql 8.1.3 server. My JDBC driver is 8.2 dev 500 that I downloaded a while back from the Postgresql web site.

    The issue I’m running into is after my web app has run a few times I hvae dozens of Postgres processes running on the machine that state they are “idle in transaction.” Since I am not a priveleged user on this machine I have no way of killing them off and I hate opening up tickets every other thing to have them killed.

    Why kill them you ask?

    If I try to update my schema (and since I’m still in deveopment that happens a lot) it hangs.

    Anybody out there have a fix for this? My code looks an awful lot like the examples by the way… 🙂

    #258289 Reply

    Haris Peco
    Member

    blue_cowdawg,

    You (or anybody who use database) have commit (or rollback) transaction if you start one

    Best
    Peco

    #258295 Reply

    Peco,

    The basic structure of my code looks like this:

    for stores:
    create object
    get current session
    set trasaction begin
    save object
    transaction commit.

    for reads
    get current session
    create query
    get results
    return results

    Now, one change I made that seemed to exasperate things was I added a session close to my code. This had the effect of my web application now has internal server errors where it compains that the controlling session has been closed.

    Still searching for answers

    #258298 Reply

    Haris Peco
    Member

    Peter,

    Postgresql will start transaction implicit for read operation (query) – try do read(query) operations in transaction too

    You can catch excepton in hibernate code and rollback transaction in catch block.Try next :

    try {
    begin_transaction
    // some hibernate code
    commit transaction
    } catch (…) {
    rollback transaction
    }

    for read operation you can do rollback transaction (without commiting) in finally clause as well

    if you got ‘session is closed’ when you close session you can look on this pattern http://hibernate.org/43.html
    For more about hibernate session and transaction look at http://hibernate.org/42.html

    Best
    Peco

    #263379 Reply

    Just thought i should share this:

    I finally figured out (after much research and trail and error) that all I needed to do was add one line to my design pattern for my Hibernate access services.

    Here is an exampe:

    
             try {
                session=HibernateSessionFactory.getSession();
                tx     = session.beginTransaction();
                
                session.update(am);
                session.flush();
                
                tx.commit();
            } catch (HibernateException e) {
                session.flush();
                tx.rollback();
                throw new RuntimeException(e);
                
            }
    

    Of interested are the lines where I have “session.flush()” Maybe I’m showing myself to be a newbie here, but somewhere along the line I missed that step. I don’t remember seeing in done in any of the tutorial examples that I used to learn from either.

Viewing 5 posts - 1 through 5 (of 5 total)
Reply To: Postgresql "idle in transaction" annoyance.

You must be logged in to post in the forum log in