Skip site navigation (1) Skip section navigation (2)

Re: Threading problem

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Palle Girgensohn <girgen(at)pingpong(dot)net>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Threading problem
Date: 2005-04-22 11:29:42
Message-ID: 4268E026.3060000@fastcrypt.com (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-jdbc by date

Next:From: alex dDate: 2005-04-22 12:17:53
Subject: COPY support for PostgreSql JDBC v8.0
Previous:From: Oliver SiegmarDate: 2005-04-22 11:01:49
Subject: Re: Interval support for Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group