Skip site navigation (1) Skip section navigation (2)

Re: [JDBC] Idle in transaction state.

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Jack Orenstein <jao(at)geophile(dot)com>
Cc: General <pgsql-general(at)postgresql(dot)org>, pgsql-jdbc(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Peter(at)berghold(dot)net
Subject: Re: [JDBC] Idle in transaction state.
Date: 2006-09-10 12:21:02
Message-ID: 6C383267-9302-4D8B-88DA-B3EF444AEFFF@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-jdbc
On 10-Sep-06, at 12:12 AM, Jack Orenstein wrote:

> Tom Lane wrote:
>> "Peter L. Berghold" <Peter(at)berghold(dot)net> writes:
>>> What I'm seeing is the first time my web application is being run  
>>> there
>>> is a bunch of processes running around that look like:
>>> "postgres: peter peter_trialdb 127.0.0.1(46222) idle"
>> "idle" is fine, "idle in transaction" is not so fine, because those
>> might be holding locks that block things like schema changes.  You  
>> need
>> to figure out why your client-side code isn't closing out its
>> transactions promptly.
>> You'd probably be better off asking on the pgsql-jdbc list about  
>> this,
>> as the folks likely to know about Java-stack issues hang out there.
>
> I haven't seen the discussion show up on the JDBC list so I'll post
> to both lists.
>
> I noticed the same problem in my JDBC/postgresql application. My
> application does Connection.setAutoCommit(false) for every connection;
> connections were kept in a home-grown connection pool.
>
> I began to suspect that turning off auto-commit was resulting in "idle
> in transaction" processes, and also causing VACUUM to reclaim fewer
> tuple versions than I thought it should be reclaiming. This was true
> even though my application always either commits or aborts before
> returning the connection to the pool.
>
> Now what I do is setAutoCommit(true) after the commit or abort, and
> before returning the connection to the pool. Then, on taking a
> connection from the pool, I setAutoCommit(false). This seems to have
> solved both problems. I don't really understand why this works,
> however. I would expect the commit or abort to suffice.
>
> (I can post a test program demonstrating the problem if there is
> interest.)

Pools should set autocommit to true upon close(). Since pools wrap  
close() the driver has no way to deal with being put back into a pool  
and left idling in a transaction.

Dave
>
> Jack Orenstein
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>


In response to

pgsql-jdbc by date

Next:From: jbrasDate: 2006-09-11 09:21:59
Subject: Re: postgresql / openoffice
Previous:From: Jack OrensteinDate: 2006-09-10 04:12:37
Subject: Re: Idle in transaction state.

pgsql-general by date

Next:From: mjDate: 2006-09-10 13:51:10
Subject: Rules / Triggers and Return Row Count
Previous:From: Dino VlietDate: 2006-09-10 11:46:46
Subject: execute in pl/pgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group