Re: Separate threads for FE<=>BE writing/reading

Replying to self...

Please ignore my issue (a) for now, it appears JDBC conceptually cannot
provide me the multi-statement batch I want; therefore this is not a FE/BE
thread or deadlock issue.

As for issue (b), Oliver: I just noticed your AntiDeadlockStream patch in
[3] after re-reading the deadlock discussion [2] to its Jan 2009
conclusion. I will try that out as I'm trying to evaluate my deadlock
issue.

[3] http://archives.postgresql.org/pgsql-jdbc/2009-01/msg00061.php as
amended by http://archives.postgresql.org/pgsql-jdbc/2009-01/msg00062.php

On Fri, 5 Mar 2010, Scott Harrington wrote:

> Has anyone looked into a JDBC Connection implementation that uses two
> threads, so that the writing and reading would never be deadlocked and
> multiple non-batch statements could be executed without having to Sync after
> each one?
>
> I come to this question as I am currently digging into the latest JDBC code
> for two reasons:
>
> (a) when a single Connection sends multiple prepared INSERTs, performance
> degrades from e.g. ~1ms update time to 10+ms update time, and this
> degradation is more noticable when the connection is across a LAN versus
> localhost;
>
> (b) I get occasional deadlocks when INSERTs on concurrent Connections cause
> the "duplicate key value violates unique constraint" error.
>
> The (a) issue is pretty easy to see with loglevel=2: the driver makes
> multiple round-trips to the backend, performing a Sync after each
> Parse/Bind/Execute (sendOneQuery), when it seems like it would be more
> efficient to make multiple calls to sendOneQuery and then just one Sync. The
> driver is designed to do this optimization when I addBatch for multiple
> inserts using a single PreparedStatement, but my transaction is executing
> multiple different PreparedStatements. Note this behavior is completely
> unaffected by changing to prepareThreshold=1 from =5 default.
>
> For (b), I've studied the new deadlock avoidance code, and the previous
> discussions [1] and [2] on this list, but have yet to boil my specific issue
> down to a reliably reproducible test harness and bug report (working on
> that). I've been seeing it against 8.3.x servers with both
> postgresql-8.3-603.jdbc3.jar and the current CVS Head.
>
> [1] http://archives.postgresql.org/pgsql-jdbc/2008-10/msg00036.php
> "Connection hanging on INSERT apparently due to large batch size and 4 CPU
> cores"
>
> [2] http://archives.postgresql.org/pgsql-jdbc/2009-01/msg00045.php "Deadlock
> detection"
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>