Threading problem - Mailing list pgsql-jdbc

Hi!

Our java servlets/jsp app has worked fine with pg-7.4.6 and ditto jdbc.

Now we recently updated to pg-8.0.2 and jdbc-8.0.311. And we have a
problem. It seems, for some reason, that two threads get hold of the same
database connection. At least we see pgsql logs for two different set of
queries being performed in the same postgres process, in parallel. One of
these threads set con.setAutoCommit(false), does some simple work, and then
set con.setAutoCommit(true) again (we use transactions only sparsely).

Problem is twofold.

First, we cannot see anything in our code that would offer the possibility
for two threads to get hold of the same postgresql connection. There might
be something we're missing, but the only changes made from a working system
is updates of postgresql and its JDBC driver, so it's easy to suspect a bug
in the jdbc driver...

Second, there is a problem that when the thread using the transaction sets
the transaction back to autocommit(true), the jdbc driver believes we're in
autocommit=true, but the postgres process sees a COMMIT, bumps transaction
id, but only once, and then stays in the transaction, forever. All
subsequent queries are run in the transaction, and in a short while the app
comes to a grinding halt due to the long time open transaction, all procs
waiting to insert/update. JDBC believes there is in fact no open
transaction.

Here's an example from the postgres logs, for *one* postgres process (two
threads with different sets of queries):

Thread 1            Thread 2
--------            --------
                    insert (not in transaction)

                    select (transaction id bumped
                            for every query)

setAutoCommit(false)
(logged as BEGIN)
                    update
select
                    select
update

insert

setAutocommit(true);
logged as COMMIT)

                    select ...
                    (continues in a new transaction id, but
                     *in* a transaction, forever, since
                    jdbc thinks there is no transaction)


This is on a rather busy server, running FreeBSD 4.10, Java 1.4.2, tomcat
5.0.30, postgresql 8.0.2 and postgresql-jdbc-8.0.311. It has happened maybe
once a day since upgrading postgresql from 7.4.6. We cannot back to
postgresql-7.4.6 (we need stuff in 8.0.2), but we could back the postgresql
driver if needed. Better still would be to fix it, of course.

I'm aware that it might be a problem in our code, but we really triple
checked everything, and cannot see that there would be anything that would
hand out the connection twice. The second problem is definitely a problem
with the driver, but since a it is a bad idea to use the same Connection
object in multiple threads, it is not an important problem, I guess?

Any input appreciated.

Regards,
Palle