Driver transaction management (idle in transaction)

From: Steve Krulewitz <shooz(at)mm(dot)st>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Driver transaction management (idle in transaction)
Date: 2004-04-09 19:25:03
Message-ID: 4076F88F.6090601@mm.st
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-jdbc

Hey folks --

I am using the pg74.1jdbc3.jar driver against Postgresql 7.4.2 from
within Apache Cocoon, which uses connection pooling (provided by an
Apache Excalibur component). I am running in autocommit false mode and
managing my own transactions with commit/rollback.

I am experiencing connections being left "idle in transaction" after a
web request is complete. The problem is that when I call commit() on
the connection, the JDBC driver issues a "commit;begin;" to the server,
which puts the connection immediately back in a transaction. This
usually wouldn't be a huge problem, but in a connection pooled
environment, the connection is never actually closed, and the connection
will sit idle in transaction.

It seems to me that the driver should only issue a "begin" before the
next sql statement is executed, rather than right after the commit. Or
maybe the begin could be removed all together and let the server
implicitly start the next transaction?

This problem was also just recently brought up on the hackers list:

http://archives.postgresql.org/pgsql-hackers/2004-04/msg00269.php

cheers,
-steve

Browse pgsql-jdbc by date

  From Date Subject
Next Message Alexander Staubo 2004-04-09 20:26:53 Re: Under what circumstances does PreparedStatement use stored
Previous Message -- 2004-04-09 19:20:36 Re: cannot connect to db from remote machine