Hi all,
We're having some pretty serious problems with transactions via JDBC. We're
running on Linux with PG 7.0.3 and Sun's 1.2.2 Java VM.
The issue is that we have a reasonably large number of connections to our
database (as much as 80 simultaneously although the problem happens even at
around 30). What happens is that we have a transaction that 1) deletes all
items from a table for a given user ID, 2) inserts in the new items for that
user ID. This is wrapped in a single transaction. The problem is that
sometimes, the insert fails because the delete didn't actually delete
(apparently). I've put in debugging info and doing a query before and after
the delete bears this out, this is, the delete sometimes doesn't delete.
I've also looked at the postgres logs but it is very difficult to tell
where 1 connectios transaction starts compared to a differenct connection. I
think it would be useful if all lines were labeled with some sort of
connection ID.
Any way, has anyone seen anything like this? And before you ask, yes, we're
quite sure the code properly starts and ends a connection (well, we think
we're sure anyway ;-).
The only possibility I've been able to come up with is if we're
inadvertantly using the same JDBC connection more than once at a time. I've
looked into this a lot and it does not seem to be the case.
Any help would be GREATLY appreciated.
Thanks in advance.
--Rainer