Re: odd jdbc driver synchronization issue

Kris,

Thanks for the test program. It helped a lot, and I believe I've found
my problem. The main issue seems to be the transaction isolation and
locking behavior of PostgreSQL as compared to MSSQL Server, and the way
I was writing my code (specifically, commits) between all my different
tests. My workload uses two stored procedures; each one issues a few
selects, possibly updates, and then an insert. The update branch is what
caused the issues, as a table could be concurrently updated by any
number of clients (basically, something like balance = balance +
amount).

Here's my work section, modeled after your example:

public void execute(int num) throws SQLException {
for (int i=0; i<num; i++) {

parentCall.execute();
// #1
for (int j=0; j<9; j++) {
childCall.execute();
// #1
}
// #2
}
// #3
conn.commit();
}

#1 a conn.commit() placed here allowed multiple clients/postmasters to
work. No postmaster process went above 8% cpu.
#2 a conn.commit() placed here allowed multiple clients/postmasters to
work. Faster than #1, naturally. No postmaster process went above 25%
cpu.
#3 a conn.commit() placed here will serialize all clients/postmasters
(if you have updates in your functions). 1 postmaster process always at
99% cpu. Although it finished with the highest throughput, it's not the
correct isolation level for the workload.

Here's some comparison data for those who are interested,

Postgres #1 39.38 tps
Postgres #2 108.50 tps
Postgres #3 563.76 tps (near pgAdmin levels?)
MSSQL #1 389.81 tps
MSSQL #2 459.18 tps
MSSQL #3 767.23 tps

Thanks to Dave, Oliver, and Kris for the help.

George

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Kris Jurka
Sent: Monday, January 05, 2004 4:42 PM
To: George Lessmann
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] odd jdbc driver synchronization issue

On Mon, 5 Jan 2004, George Lessmann wrote:

>
> 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.
>
> 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.

I've attached a program I wrote to try and verify your claims. I see no
serialization despite a single prepareCall and many executes. This
program runs with

java CallTest <num clients> <num runs per client>

each client does a loop over numRuns, calling a SELECT stored
procedure and then inserts that value into a table via another stored
procedure.

Let me know if you see a problem with this test or how your application
differs.

Kris Jurka