Postgres Pro
Downloads
Home   >   mailing lists

Thread: idle in transaction

idle in transaction

From
"Sriram Dandapani"
Date:

I use postgres 8.1.2 database and postgres 8.1.404 jdbc driver .

 

I keep seeing “idle in transaction” connections on the postgres box all the time. The application logic closes all connections , so it makes me wonder if the driver is issuing a begin statement after every commit to leave a floating transaction open.

 

This is causing autovacuum to not finish (and hence I have to frequently issue database-wide vacuums to prevent the transaction id wraparound problem)

 

Any help here is appreciated

 

Thanks

 

Sriram

Re: idle in transaction

From
Markus Schaber
Date:
Hi, Sriram,

Sriram Dandapani wrote:
> I use postgres 8.1.2 database and postgres 8.1.404 jdbc driver .
>
> I keep seeing “idle in transaction” connections on the postgres box all
> the time. The application logic closes all connections, so it makes me
> wonder if the driver is issuing a begin statement after every commit to
> leave a floating transaction open.

Current driver versions (and 8.1.404 is current enough) should not show
this behaviour any more, which was annoying in older drivers.

Are you shure that you have the correct driver version? We occasionally
get reports from users thinking they run a certain driver version, but
they accidentally have another driver earlier in the class search path.
Especially the $JRE/lib/ext directory is dangerous, people tend to drop
.jars there and then forget them.

Another possibility is that you use a broken connection pooling that
fails to reset the connections properly. Could you modify your
application logic to set all connections to autocommit mode before
closing / returning them to the pool?

HTH,
Markus

Re: idle in transaction

From
"Sriram Dandapani"
Date:
I use only the postgres 8.1.404 driver(checked that no other drivers
were in the classpath)

I also set autocommit to true before I close the connection. I use jboss
to get the datasource

The autocommit change does not change the behaviour. I still see idle in
transaction.

-----Original Message-----
From: Markus Schaber [mailto:schabi@logix-tt.com]
Sent: Saturday, September 16, 2006 1:27 AM
To: Sriram Dandapani
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] idle in transaction

Hi, Sriram,

Sriram Dandapani wrote:
> I use postgres 8.1.2 database and postgres 8.1.404 jdbc driver .
>
> I keep seeing "idle in transaction" connections on the postgres box
all
> the time. The application logic closes all connections, so it makes me
> wonder if the driver is issuing a begin statement after every commit
to
> leave a floating transaction open.

Current driver versions (and 8.1.404 is current enough) should not show
this behaviour any more, which was annoying in older drivers.

Are you shure that you have the correct driver version? We occasionally
get reports from users thinking they run a certain driver version, but
they accidentally have another driver earlier in the class search path.
Especially the $JRE/lib/ext directory is dangerous, people tend to drop
.jars there and then forget them.

Another possibility is that you use a broken connection pooling that
fails to reset the connections properly. Could you modify your
application logic to set all connections to autocommit mode before
closing / returning them to the pool?

HTH,
Markus

Re: idle in transaction

From
João Paulo Ribeiro
Date:
Hi.<br /><br /> Are you using DODS/Enhydra to manage your database connection pool?<br /> If yes, see my email in the
Enhydramailing list:<br /><a class="moz-txt-link-freetext"
href="http://mail-archive.objectweb.org/dods/2006-05/msg00000.html">http://mail-archive.objectweb.org/dods/2006-05/msg00000.html</a><br
/><aclass="moz-txt-link-freetext"
href="http://mail-archive.objectweb.org/dods/2006-06/msg00000.html">http://mail-archive.objectweb.org/dods/2006-06/msg00000.html</a><br
/><br/> The latest DODS dont have this issue anymore. You just use "RollbackOnReset" option.<br /><br /> Bests
regards.<br/> João Paulo Ribeiro<br /><br /> Sriram Dandapani wrote: <blockquote
cite="mid6992E470F12A444BB787B5C937B9D4DF05DF1974@ca-mail1.cis.local"type="cite"><style>
 
<!--/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"TimesNew Roman";}
 
a:link, span.MsoHyperlink{color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{color:purple;text-decoration:underline;}
span.EmailStyle17{mso-style-type:personal-compose;font-family:Arial;color:windowtext;}
@page Section1{size:8.5in 11.0in;margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1{page:Section1;}
--> </style><div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial;">I use postgres 8.1.2 database and postgres 8.1.404 jdbc driver .</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">I keep seeing “idle in
transaction”connections on the postgres box all the time. The application logic closes all connections , so it makes me
wonderif the driver is issuing a begin statement after every commit to leave a floating transaction
open.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size: 10pt; font-family:
Arial;"> </span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size: 10pt; font-family:
Arial;">Thisis causing autovacuum to not finish (and hence I have to frequently issue database-wide vacuums to prevent
thetransaction id wraparound problem)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;">Any help here is appreciated</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size: 10pt; font-family: Arial;">Thanks</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size: 10pt; font-family: Arial;">Sriram</span></font></div></blockquote><br
/><br/><pre class="moz-signature" cols="72">-- 
 
João Paulo Ribeiro | Senior Software Engineer
<a class="moz-txt-link-abbreviated" href="mailto:jp@mobicomp.com">jp@mobicomp.com</a>

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
<a class="moz-txt-link-abbreviated" href="http://www.mobicomp.com">www.mobicomp.com</a>

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for
theindividual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any
usewhatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.
 
DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does
notaccept liability for any fact which may interfere with the integrity of its content.</pre>

Re: idle in transaction

From
"Sriram Dandapani"
Date:

Hi

 

I use JBOSS 4.0.3 to manage my connection pooling. Is there a way to configure / modify code to take care of this

 

Thanks for your input

 

Sriram

 

From: João Paulo Ribeiro [mailto:jp@mobicomp.com]
Sent: Monday, September 18, 2006 2:15 AM
To: Sriram Dandapani
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] idle in transaction

 

Hi.

Are you using DODS/Enhydra to manage your database connection pool?
If yes, see my email in the Enhydra mailing list:
http://mail-archive.objectweb.org/dods/2006-05/msg00000.html
http://mail-archive.objectweb.org/dods/2006-06/msg00000.html

The latest DODS dont have this issue anymore. You just use "RollbackOnReset" option.

Bests regards.
João Paulo Ribeiro

Sriram Dandapani wrote:

I use postgres 8.1.2 database and postgres 8.1.404 jdbc driver .

 

I keep seeing “idle in transaction” connections on the postgres box all the time. The application logic closes all connections , so it makes me wonder if the driver is issuing a begin statement after every commit to leave a floating transaction open.

 

This is causing autovacuum to not finish (and hence I have to frequently issue database-wide vacuums to prevent the transaction id wraparound problem)

 

Any help here is appreciated

 

Thanks

 

Sriram




-- 
João Paulo Ribeiro | Senior Software Engineer
jp@mobicomp.com
 
PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
www.mobicomp.com
 
________________________________________________________________
 
About Solutions | Wireless World
 
CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for the individual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any use whatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.
DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does not accept liability for any fact which may interfere with the integrity of its content.

Re: idle in transaction

From
João Paulo Ribeiro
Date:
Hi.<br /> In a more generic way, you need to garantee 2 things:<br /> 1) You always release your connections back to
thepool.<br /> 2) Any connection released must not have a valid transaction with resources allocated.<br /><br /> Its
okto have a connection in the pool with setautocommit(false), that leads to "idle in connection", if the transaction is
anew one and no resources or lock have been made at the db.<br /><br /> If you want now what is blocking your vaccum
trythis:<br /><br /> SELECT pg_class.relname AS table, pg_database.datname AS database, transaction, pid, mode, granted
FROMpg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid AND pg_locks.database = pg_database.oid
orderby pg_class.relname, pid;<br /><br /> You will see in wich table the vaccum is waiting to acquire the lock and
wichprocess is holding it at the moment.<br /><br /> Best regards.<br /> João Paulo Ribeiro<br /><br /> Sriram
Dandapaniwrote: <blockquote cite="mid6992E470F12A444BB787B5C937B9D4DF05DF1A7F@ca-mail1.cis.local" type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}/* Style Definitions
*/p.MsoNormal,li.MsoNormal, div.MsoNormal{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman";color:black;}
a:link, span.MsoHyperlink{color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{color:purple;text-decoration:underline;}
pre{margin:0in;margin-bottom:.0001pt;font-size:10.0pt;font-family:"Courier New";color:black;}
span.EmailStyle17{mso-style-type:personal;font-family:Arial;color:windowtext;}
span.EmailStyle19{mso-style-type:personal-reply;font-family:Arial;color:navy;}
@page Section1{size:8.5in 11.0in;margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1{page:Section1;}
--> </style><div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10pt;font-family: Arial; color: navy;">Hi</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial; color: navy;"> </span></font><p class="MsoNormal"><font
color="navy"face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">I use JBOSS 4.0.3 to
managemy connection pooling. Is there a way to configure / modify code to take care of this</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color:
navy;"> </span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial; color: navy;">Thanks for your input</span></font><p class="MsoNormal"><font color="navy"
face="Arial"size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color:
navy;">Sriram</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial; color: navy;"> </span></font><div><div align="center" class="MsoNormal" style="text-align:
center;"><fontcolor="black" face="Times New Roman" size="3"><span style="font-size: 12pt; color: windowtext;"><hr
align="center"size="3" tabindex="-1" width="100%" /></span></font></div><p class="MsoNormal"><b><font color="black"
face="Tahoma"size="2"><span style="font-size: 10pt; font-family: Tahoma; color: windowtext; font-weight:
bold;">From:</span></font></b><fontcolor="black" face="Tahoma" size="2"><span style="font-size: 10pt; font-family:
Tahoma;color: windowtext;"> João Paulo Ribeiro [<a class="moz-txt-link-freetext"
href="mailto:jp@mobicomp.com">mailto:jp@mobicomp.com</a>]<br /><b><span style="font-weight: bold;">Sent:</span></b>
Monday,September 18, 2006 2:15 AM<br /><b><span style="font-weight: bold;">To:</span></b> Sriram Dandapani<br
/><b><spanstyle="font-weight: bold;">Cc:</span></b> <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-jdbc@postgresql.org">pgsql-jdbc@postgresql.org</a><br/><b><span style="font-weight:
bold;">Subject:</span></b>Re: [JDBC] idle in transaction</span></font><font color="black"><span style="color:
windowtext;"></span></font></div><pclass="MsoNormal"><font color="black" face="Times New Roman" size="3"><span
style="font-size:12pt;"> </span></font><p class="MsoNormal"><font color="black" face="Times New Roman" size="3"><span
style="font-size:12pt;">Hi.<br /><br /> Are you using DODS/Enhydra to manage your database connection pool?<br /> If
yes,see my email in the Enhydra mailing list:<br /><a
href="http://mail-archive.objectweb.org/dods/2006-05/msg00000.html">http://mail-archive.objectweb.org/dods/2006-05/msg00000.html</a><br
/><a
href="http://mail-archive.objectweb.org/dods/2006-06/msg00000.html">http://mail-archive.objectweb.org/dods/2006-06/msg00000.html</a><br
/><br/> The latest DODS dont have this issue anymore. You just use "RollbackOnReset" option.<br /><br /> Bests
regards.<br/> João Paulo Ribeiro<br /><br /> Sriram Dandapani wrote: </span></font><p class="MsoNormal"><font
color="black"face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">I use postgres 8.1.2 database and
postgres8.1.404 jdbc driver .</span></font><p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font color="black" face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial;">I keep seeing “idle in transaction” connections on the
postgresbox all the time. The application logic closes all connections , so it makes me wonder if the driver is issuing
abegin statement after every commit to leave a floating transaction open.</span></font><p class="MsoNormal"><font
color="black"face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font><p
class="MsoNormal"><fontcolor="black" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">This is
causingautovacuum to not finish (and hence I have to frequently issue database-wide vacuums to prevent the transaction
idwraparound problem)</span></font><p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font color="black" face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial;">Any help here is appreciated</span></font><p
class="MsoNormal"><fontcolor="black" face="Arial" size="2"><span style="font-size: 10pt; font-family:
Arial;"> </span></font><pclass="MsoNormal"><font color="black" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial;">Thanks</span></font><p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font color="black" face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial;">Sriram</span></font><p class="MsoNormal"><font color="black"
face="TimesNew Roman" size="3"><span style="font-size: 12pt;"><br /><br /><br /></span></font><pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">-- </span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">João Paulo Ribeiro | Senior Software
Engineer</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;"><a
href="mailto:jp@mobicomp.com">jp@mobicomp.com</a></span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">PHONE: + 351 253 305 250</span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;">FAX  : + 351 253 305 250</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;"><a
href="http://www.mobicomp.com">www.mobicomp.com</a></span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">________________________________________________________________</span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">About Solutions | Wireless World</span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;">CONFIDENTIALITY NOTICE: This message, as well
asexisting attached files, is confidential and intended exclusively for the individual(s) named as addressees. If you
arenot the intended recipient, you are kindly requested not to make any use whatsoever of its contents and to proceed
tothe destruction of the message, thereby notifying the sender.</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">DISCLAIMER: The sender of this message can not ensure the
securityof its electronic transmission and consequently does not accept liability for any fact which may interfere with
theintegrity of its content.</span></font></pre></div></blockquote><br /><br /><pre class="moz-signature" cols="72">--

João Paulo Ribeiro | Senior Software Engineer
<a class="moz-txt-link-abbreviated" href="mailto:jp@mobicomp.com">jp@mobicomp.com</a>

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
<a class="moz-txt-link-abbreviated" href="http://www.mobicomp.com">www.mobicomp.com</a>

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for
theindividual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any
usewhatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.
 
DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does
notaccept liability for any fact which may interfere with the integrity of its content.</pre>