Thread: Threading problem
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
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
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 >
Palle Girgensohn wrote: > 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... There's really no shared state between different connections -- they're each independent objects with their own protocol layer objects, TCP connection etc. So it's hard to see how there could be a bug where two different Connection objects end up using the same physical connection.. I'd be more suspicious of your application or connection pool. -O
--On lördag, april 23, 2005 10.08.12 +1200 Oliver Jowett <oliver@opencloud.com> wrote: > Palle Girgensohn wrote: > >> 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... > > There's really no shared state between different connections -- they're > each independent objects with their own protocol layer objects, TCP > connection etc. So it's hard to see how there could be a bug where two > different Connection objects end up using the same physical connection.. > I'd be more suspicious of your application or connection pool. Honestly, so am I. Only some change in pg 8 or pg-jdbc-8.0 triggered the problem. It has worked like a charm for ages before this upgrade. Still, you're probably right, I'll have to find the problem in our own code. /Palle