Skip site navigation (1) Skip section navigation (2)

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

From: Scott Harrington <scotth01(at)sns-usa(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: oliver(at)opencloud(dot)com
Subject: Re: Separate threads for FE<=>BE writing/reading
Date: 2010-03-05 16:55:48
Message-ID: alpine.WNT.2.00.1003051119480.5636@corsica (view raw or flat)
Thread:
Lists: 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

In response to

pgsql-jdbc by date

Next:From: Kronus DavidDate: 2010-03-05 21:48:11
Subject: optional JAAS login
Previous:From: Scott HarringtonDate: 2010-03-05 15:51:02
Subject: Separate threads for FE<=>BE writing/reading

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group