Re: jdbc spec violation for autocommit=true & addbatch/executeBatch

Forgot some details:

JDK 1.6
postgres driver postgresql-9.0-801.jdbc4.jar
postgres server 9.0.2, on windoze XP 64bit and linux 64 bits.

--- On Tue, 1/18/11, Quartz <quartz12h@yahoo.com> wrote:

> From: Quartz <quartz12h@yahoo.com>
> Subject: [JDBC] jdbc spec violation for autocommit=true & addbatch/executeBatch
> To: pgsql-jdbc@postgresql.org
> Received: Tuesday, January 18, 2011, 12:28 PM
> jdbc spec violation for
> autocommit=true & addbatch/executeBatch
>
> We are moving to postgresql. I have unexpected deadlocks on
> batches even though I don't use transaction (I use
> autocommit=true).
>
> The test program is simple. On a 20+ row table with primary
> key 'id', one thread does 20 updates added with
> statement.addBatch() from id 1 to 20. Meanwhile the other
> thread does updates from row id 20 down to 1.
>
> To facilitate the contention, I used a "pg_sleep(3)" call
> inside each update. That gives me time to start both program
> (i.e. both threads).
>
> update data set f1=if(exists(select * from pg_sleep(3)), 1,
> 0) where id=1
> update data set f2=if(exists(select * from pg_sleep(3)), 1,
> 0) where id=2
> and so on.
>
> (I just made 20 fields to see the f1, f2 .. in the sql
> string to distinguish at which statement a connection is at.
> Could have been the same field all the time.)
>
> (Under mysql 5+:
> update data set f1=sleep(3) where id=1
> update data set f2=sleep(3) where id=2
> and so on.)
>
> In a nutshell, each program should run for about 60 seconds
> (20x3 seconds).
>
> The results:
>
> Mysql myisam (table locking) never fails, but the 2
> programs are interlaced and take nearly 120s as expected.
> Mysql innodb without transaction never fails, never
> deadlock.
> Mysql innodb with transaction (autocommit=false &
> commit()) does deadlock as expected.
>
> Postgres with transaction (autocommit=false & commit())
> does deadlock as expected.
> Postgres without transaction (autocommit=true) deadlocks
> UNEXPECTEDLY.
>
> The jdbc javadoc says clearly:
> http://download.oracle.com/javase/6/docs/api/java/sql/Connection.html#setAutoCommit%28boolean%29
>
> "If a connection is in auto-commit mode, then all its SQL
> statements will be executed and committed as individual
> transactions."
>
> This is simply not true for postgresql.
> For now I cannot tell if this is a bad driver setting up
> the connection, of if postgresql server simply cannot be
> wrapped by jdbc for this API.
>
> All I can tell is that this is a show stopper for everyone
> doing simple transaction-less batches, as there is no way to
> avoid deadlock risks completely, besides an expensive
> (cpu-wise and design-wise) sort of intended statements to
> produce a predictable row locking order.
>
> Yet, I ran many threads doing the id 1..20 updates
> concurrently, and guess what? The 2nd instance was blocked
> for the entire duration of the 1st instance, showing about
> 60 seconds of duration for the statement on row id=1. This
> is unacceptable concurrency.
>
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>