Re: odd jdbc driver synchronization issue

On Mon, Jan 05, 2004 at 03:01:16PM -0600, George Lessmann wrote:
> Oliver, all,
>
> The original design of my test application allocated one Connection and
> two CallableStatements per client (one for the parent insert and one for
> the child insert). As I reported earlier, each client would then be
> serialized, even though they each existed in their own
> thread/connection/callablestatement.

This shouldn't happen. We really need a standalone testcase to locate the
problem down, though.

> I then refactored the application to allocate a connection per client,
> and prepareCall() before every execute(). This change resulted in each
> client being able to concurrently access the database, even though, in
> my opinion, it violates the whole reason behind a prepareCall() which is
> to reuse it often without recompilation.

As you say, this shouldn't be necessary.

One possibility is that your original code was (accidentally)
using CallableStatements that originated from the same Connection for
different clients. In this case, execution of those statements will
serialize (as they contend for the same underlying physical connection).

If the CallableStatements are indeed from different Connections, I can't see
how they'd serialize on the Java side .. the two Connection objects are
entirely independent and don't hold references to each other at all.

IIRC you were using a connection pool -- is it possible the contention is
happening in the pool implementation? Some pool implementations provide
their own proxying Connection and *Statement implementations; perhaps the
pool's CallableStatement is causing the problem.

Did you try any of my suggestions below? If you can produce a thread dump
while some of the clients are blocked we can probably narrow the problem
down without a testcase.

> Ideally we need a selfcontained, compilable, testcase that demonstrates
> the problem. Otherwise it's just guesswork.
>
> Some things you could try to narrow down the problem:
>
> - Run a couple of instances of your test in separate JVMs, see if you
> get any concurrency between them.
>
> - Get thread dumps from the JVM while your test is running (under
> solaris & linux sending SIGQUIT to the JVM does this) and see where your
>
> client threads are blocked.
>
> - Turn on statement logging (in postgresql.conf) and see exactly when
> the queries are being submitted.
>
> - Run the same queries by hand via psql and see if you get the same
> behaviour.
>
> -O

-O