Quick Links

Re: idle in transaction

From:	João Paulo Ribeiro <jp(at)mobicomp(dot)com>
To:	Sriram Dandapani <sdandapani(at)counterpane(dot)com>
Cc:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: idle in transaction
Date:	2006-09-18 16:50:54
Message-ID:	450ECE6E.2090401@mobicomp.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-jdbc

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi. 
In a more generic way, you need to garantee 2 things: 
1) You always release your connections back to the pool. 
2) Any connection released must not have a valid transaction with
resources allocated. 
 
Its ok to have a connection in the pool with setautocommit(false), that
leads to "idle in connection", if the transaction is a new one and no
resources or lock have been made at the db. 
 
If you want now what is blocking your vaccum try this: 
 
SELECT pg_class.relname AS table, pg_database.datname AS database,
transaction, pid, mode, granted FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid AND pg_locks.database =
pg_database.oid order by pg_class.relname, pid; 
 
You will see in wich table the vaccum is waiting to acquire the lock
and wich process is holding it at the moment. 
 
Best regards. 
João Paulo Ribeiro 
 
Sriram Dandapani wrote:
<blockquote
cite="mid6992E470F12A444BB787B5C937B9D4DF05DF1A7F(at)ca-mail1(dot)cis(dot)local"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PersonName">

<style>

</style>
</o:SmartTagType>
<div class="Section1">
Hi<o:p></o:p>
<o:p> </o:p>
I use JBOSS
4.0.3 to manage my connection
pooling. Is there a way to configure / modify code to take care of this<o:p></o:p>
<o:p> </o:p>
Thanks for
your input<o:p></o:p>
<o:p> </o:p>
Sriram<o:p></o:p>
<o:p> </o:p>
<div>
<div class="MsoNormal" style="text-align: center;" align="center">
<hr tabindex="-1" align="center" size="3" width="100%"></div>
From: João
Paulo Ribeiro [<a class="moz-txt-link-freetext" href="mailto:jp(at)mobicomp(dot)com">mailto:jp(at)mobicomp(dot)com</a>] 
Sent: Monday,
September 18, 2006
2:15 AM 
To: Sriram Dandapani 
Cc: <st1:PersonName
w:st="on"><a class="moz-txt-link-abbreviated" href="mailto:pgsql-jdbc(at)postgresql(dot)org">pgsql-jdbc(at)postgresql(dot)org</a></st1:PersonName> 
Subject: Re: [JDBC]
idle in
transaction<o:p></o:p>
</div>
<o:p> </o:p>
Hi. 
 
Are you using DODS/Enhydra to manage your database connection pool? 
If yes, see my email in the Enhydra mailing list: 
<a href="http://mail-archive.objectweb.org/dods/2006-05/msg00000.html">http://mail-archive.objectweb.org/dods/2006-05/msg00000.html</a> 
<a href="http://mail-archive.objectweb.org/dods/2006-06/msg00000.html">http://mail-archive.objectweb.org/dods/2006-06/msg00000.html</a> 
 
The latest DODS dont have this issue anymore. You just use
"RollbackOnReset" option. 
 
Bests regards. 
João Paulo Ribeiro 
 
Sriram Dandapani wrote: <o:p></o:p>
I use postgres 8.1.2
database and postgres 8.1.404
jdbc driver .<u1:p></u1:p><o:p></o:p>
<u1:p> </u1:p><o:p></o:p>
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.<u1:p></u1:p><o:p></o:p>
<u1:p> </u1:p><o:p></o:p>
This is causing
autovacuum to not finish (and hence I
have to frequently issue database-wide vacuums to prevent the
transaction id
wraparound problem)<u1:p></u1:p><o:p></o:p>
<u1:p> </u1:p><o:p></o:p>
Any help here is
appreciated<u1:p></u1:p><o:p></o:p>
<u1:p> </u1:p><o:p></o:p>
Thanks<u1:p></u1:p><o:p></o:p>
<u1:p> </u1:p><o:p></o:p>
Sriram<u1:p></u1:p><o:p></o:p>
 
 
 
<o:p></o:p>
<pre>-- <o:p></o:p></pre>
<pre>João Paulo Ribeiro | Senior Software Engineer<o:p></o:p></pre>
<pre><a href="mailto:jp(at)mobicomp(dot)com">jp(at)mobicomp(dot)com</a><o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>PHONE: + 351 253 305 250<o:p></o:p></pre>
<pre>FAX  : + 351 253 305 250<o:p></o:p></pre>
<pre><a href="http://www.mobicomp.com">www.mobicomp.com</a><o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>________________________________________________________________<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>About Solutions | Wireless World<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>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.<o:p></o:p></pre>
<pre>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.<o:p></o:p></pre>
</div>
</blockquote>
 
 
<pre class="moz-signature" cols="72">--
João Paulo Ribeiro | Senior Software Engineer
<a class="moz-txt-link-abbreviated" href="mailto:jp(at)mobicomp(dot)com">jp(at)mobicomp(dot)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 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.</pre>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	12.2 KB

In response to

Re: idle in transaction at 2006-09-18 16:02:59 from Sriram Dandapani

Browse pgsql-jdbc by date

	From	Date	Subject
Next Message	Albert Cardona	2006-09-18 17:04:34	Re: about monitoring the input stream
Previous Message	Sriram Dandapani	2006-09-18 16:02:59	Re: idle in transaction