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