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"
