Re: Threading problem - Mailing list pgsql-jdbc

Palle,

Well, your evidence seems to suggest it is a problem with the driver,
however the driver is not responsible for which thread gets it's
connections.
I have a question: Which pooling implementation are you using ? I
strongly suggest dbcp if you aren't already using it.

In another case I wrote a test case just for the pooling code for
another customer and found that their (homegrown ) pool didn't do what
they thought it did.

Dave

Palle Girgensohn wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561