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

Quartz, 18.01.2011 22:55:
> This is very sad. The batch never mean transaction. It happens to be
> the supporting fixture for the transaction, but also to avoid
> roundtrips with connection pools and such overhead when not using
> transaction as autocommit=true suggest.

The JDBC Specification (jdbc-4.0-fr-spec.pdf, Chapter 14.1.1) has a little note on this topic:

"auto-commit should always be turned off when batch updates are done.
The commit behavior of executeBatch is always implementation-defined when an error
occurs and auto-commit is true"

I cannot find any clear definition whether the batch should be treated as a single transaction or multiple transactions depending on the state of autocommit.

There is one hint though:

Chapter 10.1

"The Connection attribute auto-commit specifies when to end transactions. Enabling
auto-commit causes a transaction commit after each individual SQL statement as
soon as that statement is complete. The point at which a statement is considered to
be “complete” depends on the type of SQL statement as well as what the application
does after executing it:"

For Data Manipulation Language (DML) statements such as Insert, Update,
Delete, and DDL statements, the statement is complete as soon as it has finished
executing.

....

For CallableStatement objects or for statements that return multiple results,
the statement is complete when all of the associated result sets have been closed,
and all update counts and output parameters have been retrieved.

I think the second item (actually the third because I left out one) is interesting. A prepared statement that is using batches might be considered as one that returns multiple results (as it can return multiple update counts).

So according to that note, I would actually say that batched statements _can_ be treated as a single transaction.

Pretty vague though I have to admit.

Regards
Thomas