Re: JDBC gripe list - Mailing list pgsql-jdbc

From Quartz
Subject Re: JDBC gripe list
Date
Msg-id 421934.99909.qm@web33205.mail.mud.yahoo.com
Whole thread Raw
In response to JDBC gripe list  (Dave Cramer <pg@fastcrypt.com>)
List pgsql-jdbc
And I would add, the batch is considered a single transaction even though autocommit is true, which causes deadlock
whenyou set a batch of updates in an order that will cause a deadlock. 

Example: make a batch of updates for pk ID=1 to 10 while you make a 2nd batch from id=10 to 1. To increase the chance
ofsuccess with a deadlock, you can toss in a pg_sleep() call to slow things down. 

You will see a deadlock even though you never intended to use a lock.
As you might guess, this break our application ported from mysql...

The workaround is very bad: either sorting updates batch by PK which is costly, and sometimes not possible unless you
totallyknow the semantic of the sql you are attempting, or interlacing a hack "COMMIT" as a sql statement in the batch,
whichis not legal since there was no "BEGIN" (but triggers something in postgres). 



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

> From: Quartz <quartz12h@yahoo.com>
> Subject: Re: [JDBC] JDBC gripe list
> To: pgsql-jdbc@postgresql.org
> Received: Tuesday, March 29, 2011, 3:29 PM
> addBatch()/executeBatch() is broken
> under autocommit=true.
>
> Every statement is clearly supposed to be independant.
> Example: 5 insert statements, let's say the 2th and the 4th
> are on duplicate of primary key. All 3 others should still
> be performed but they aren't.
>
> This breaks our application. We migrated from mysql, and
> BOOM...
>
>

pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: JDBC gripe list
Next
From: Dave Cramer
Date:
Subject: Re: JDBC gripe list