Re: odd jdbc driver synchronization issue - Mailing list pgsql-jdbc

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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Kris Jurka
Sent: Monday, January 05, 2004 4:42 PM
To: George Lessmann
Cc: pgsql-jdbc@postgresql.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