Roy Smith wrote:
> To get this control you shouldn't be using autocommit. You should
> turn that off and then rollback if you get a SQLException. With
> autocommit, the cats already out of the bag.
>
>
> Hmmm, not sure I agree but I'm happy to be persuaded. You're saying that
> a single JDBC call is *not* atomic in and of itself? As a computer
> scientist I find that disturbing if it's true. It basically means that
> running any JDBC session in autocommit mode is bogus, since individual
> update statements become non-deterministic.
Well, in general autocommit isn't safe and neither is non-autocommit.
If you get a network disconnect at just the wrong time (during commit),
you can't tell what happened to your transaction. You'll get a
SQLException informing you your connection is gone, but did it commit or
not? You need two phase commit (XA) to be able to name and lookup in
doubt transactions at a later date in case something bad happens. In
your case something odd is going on (which we may still be able to
resolve), but is it worth moving every single application to XA to
defend against these possibilities? I don't think so.
> I do appreciate that where an executeUpdate is carrying out more than
> one database write there isn't much you can do about it.
Even in the single write case, there is some code in the JDBC driver and
it must have some failure modes right? What about an out of memory
condition when trying to retrieve the server's response? The server has
committed it and the driver can't interpret that result.
> Nested transactions would be an answer, but of course there is no
> such thing.
>
There are savepoints, but again I don't see how that's helpful in
autocommit mode.