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

Quartz wrote:

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

It's worth noting that the javadoc changes without warning between
releases - there have been many "clarifications" over the years that are
actually unannounced behavioral changes - have you checked historical
javadoc to see what it says? Maybe we implemented against a spec that
said something different.

That said - yes, all statements within addBatch() are run in a single
transaction. (Autocommit is triggered by the final Sync message, which
is only sent at the end of the batch)

A related gotcha is that if you execute a multiple statement query (i.e.
execute("select 1; select 2; select 3") then, again, there is only one
enclosing autocommit transaction. (This was deliberate, as it mirrored
the behavior of protocol-version-2 drivers back in the 7.x days)

I'm not sure if we can implement this according to the current spec
without losing any performance benefit of addBatch()/executeBatch() -
we'd have to send a Sync after every individual query. As a workaround I
suggest you just run the queries individually, not in a batch - there
won't be much performance difference. (And if you really don't care
about ordering or atomicity as you imply, you could parallelize it
across multiple connections if latency is a problem)

Oliver