Re: Separate threads for FE<=>BE writing/reading
От | Scott Harrington |
---|---|
Тема | Re: Separate threads for FE<=>BE writing/reading |
Дата | |
Msg-id | alpine.WNT.2.00.1003051119480.5636@corsica обсуждение исходный текст |
Ответ на | Separate threads for FE<=>BE writing/reading (Scott Harrington <scotth01@sns-usa.com>) |
Список | pgsql-jdbc |
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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
В списке pgsql-jdbc по дате отправления: