"Wynn, Robin" <RWynn@northropgrumman.com> writes:
> I actually suspect that I have a handle on what's going on... what I
> think it boils down to is that there are two threads (there are more, but
> these two are what I think are causing the problem). One periodically
> updates an element called pct_complete in a table called JTRANSACTION, the
> other periodically does a select from that same table to get the current
> value of pct_complete. Both have the same connection ID. Randomly, we hit
> (what I suspect is) a race condition where both things are happening at the
> same time, and the one performing the SELECT hangs. That happens to be the
> parent thread, for what that's worth. At any rate, it always seems to be
> the case (at least, so far) that when the backend message comes through, the
> parent thread is hung. So, I'm going to dig around some more and see what's
> been recommended for avoiding this condition... I'll also try making a new
> connection with one of the threads (thus, a different backend, from what I
> understand) and see if that avoids this problem. Any other suggestions?
> Could this theoretically happen with an INSERT/SELECT combination, or is it
> unique to the UPDATE/SELECT pairing?
AFAIK it's extremely bad practice in general to share a connection
between two threads, unless you protect it with some kind of lock to
avoid simultaneous use. Using a connection per thread is a much
better idea. The only issue with that is that one thread won't see
results of an in-progress transaction until the other thread commits.
-Doug