Re: JDBC gripe list

And I would add, the batch is considered a single transaction even though autocommit is true, which causes deadlock when you 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 of success 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 totally know the semantic of the sql you are attempting, or interlacing a hack "COMMIT" as a sql statement in the batch, which is not legal since there was no "BEGIN" (but triggers something in postgres).

--- On Tue, 3/29/11, Quartz <quartz12h(at)yahoo(dot)com> wrote:

> From: Quartz <quartz12h(at)yahoo(dot)com>
> Subject: Re: [JDBC] JDBC gripe list
> To: pgsql-jdbc(at)postgresql(dot)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...
>
>