Re: Threading problem - Mailing list pgsql-jdbc

Hi Dave,

Thanks for the reply. See comments below.

--On fredag, april 22, 2005 07.29.42 -0400 Dave Cramer <pg@fastcrypt.com>
wrote:

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

Nope, probably true... unless there's a threading bug that makes the driver
hand otu the same connection to two consecutive getConnection calls under
stress, but that seems *very* unlikely...

> I have a question: Which pooling implementation are you using ? I
> strongly suggest dbcp if you aren't already using it.

Yes, we do have our homegrown pool, and of course there can be a problem
with that. It's just that it has worked flawlessly for a long time, until
we upgraded the postgresql-jdbc driver.

We will continue to investigate this, of course. In any case, the second
problem is what really kills the app... I thought the Connection class was
thread safe, so it could actually be used by more than one thread, although
it is rather stupid, and the transaction started in one thread will be
valid for both. Here, what happens is that one thread starts a transaction,
and the other thread uses the same transaction, and when the first commits
and shuts down the transaction, the other thread still is still in a
transaction according to the server, but jdbc thinks not (i.e.
getAutoCommit == true).

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

If you have the test case around and don't mind sharing it with me, I could
run it to test our pool.

Regards,
Palle


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