Hi,
Thanks for your input.
I have 2 separate methods up for different data access patterns. In one
method I use the connection pool and the JOOQ automatically opening and
closing feature with autocommit turned on, pretty much so every select /
update is in it's own transaction. This is just for convenience really
for simple queries here and there that it doesn't matter if it is
fetched outside of a transaction (But I understand what you mean. I can
potentially get in a lot of trouble and get seemingly random, hard to
find issues because then I'm not isolating "transactions" from each
other. I will have to keep that in mind and not get lazy and just use
the convenience). Then I have another method to manage where I need
transactions, which automatically handles closing connections and
actually rolls back if I don't explicitly commit or an exception occurs
during the transaction. It's a bit of an improvement on the try{}
catch{} finally{}. I'm not working in a standard "request"/"response"
type setup of a web app, so this is working quite well for me. And JOOQ
is working quite well too (When I use it properly!).
Thanks, Ryan
On 4/12/2012 1:18 AM, Durchholz, Joachim wrote:
I'd like auto commit on so any updates are automatically committed.
So. Am I using this in the wrong way?
Yes. You should not use autocommit mode.
You'll get into nasty surprises as soon as you have dependencies between data
rows. You'll get into all kinds of race conditions, and these are the ultimate
in nastiness to debug even if networking delays aren't involved.
Long version:
You'll have to pick your evil.
With autocommit, you will get rare, below-the-radar problems. Once you become
aware of them, you'll have massive problems reproducing and fixing them.
Without autocommit, you will get frequent but highly visible problems, easily
diagnosed and fixed (but embarrassing if they manage to ship).
The usual solution is to delegate transaction management to a container. That
way, you don't risk race conditions and you don't risk forgetting to code a
commit.
The downside here is that choosing and configuring a container isn't trivial.
It's worth it though; containerless transactions mean a lot of
try-catch-finally code, resource leaks, and general unhappiness.
Oh, and there's another catch, reported on
http://stackoverflow.com/questions/4453782/why-set-autocommit-to-true:
Since the JDBC 3 spec, connections in "auto-commit" mode can't
have more than one Statement open. Thus, nested UPDATEs or even
SELECT within an outer SELECT loop will cause problems.
I can't vouch for the correctness of this bit of information though.
