Обсуждение: 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

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

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.

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:

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.

-- 
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.